Hi,
I wrote a script that monitored the size of a specific table of mine(dead tuples_mb vs live tuples_mb). The script run a query on pg_stattuple every 15 minutes : select * from pg_stattuple('table_name'). I know that every night there is a huge delete query that deletes most of the table`s content. In addition, I set the following parameters for the table :
toast.autovacuum_vacuum_scale_factor=0,
toast.autovacuum_vacuum_threshold=10000, toast.autovacuum_vacuum_cost_limit=10000,
toast.autovacuum_vacuum_cost_delay=5
After a week of monitoring I generates a csv of the results and I created a graph from that data. However, the graph that I created confused me very much.
A small sample of all the data that I gathered :
| date | toasted_live_tup_size_MB | toasted_dead_tup_size_mb |
| 6/16/19 0:00 | 58.8537941 | 25.68760395 |
| 6/16/19 0:15 | 8.725102425 | 25.02167416 |
| 6/16/19 0:30 | 8.668716431 | 25.08410168 |
| 6/16/19 0:45 | 8.810066223 | 24.94327927 |
| 6/16/19 1:00 | 8.732183456 | 25.02435684 |
| 6/16/19 1:15 | 8.67656517 | 20.01097107 |
| 6/16/19 1:30 | 9.573832512 | 20.76298809 |
| 6/16/19 1:45 | 9.562319756 | 20.7739706 |
| 6/16/19 2:00 | 9.567030907 | 21.01560402 |
| 6/16/19 2:15 | 9.576253891 | 70.62042999 |
| 6/16/19 2:30 | 9.715950966 | 492.2445602 |
| 6/16/19 2:45 | 9.59837532 | 801.455843 |
| 6/16/19 3:00 | 9.599774361 | 1110.201434 |
| 6/16/19 3:15 | 9.606671333 | 1402.255548 |
| 6/16/19 3:30 | 9.601698875 | 1698.487226 |
| 6/16/19 3:45 | 9.606934547 | 2003.051514 |
| 6/16/19 4:00 | 9.600641251 | 2307.625901 |
| 6/16/19 4:15 | 9.61320591 | 2612.196963 |
| 6/16/19 4:30 | 9.606646538 | 2916.773588 |
| 6/16/19 4:45 | 9.61294651 | 3221.337314 |
| 6/16/19 5:00 | 9.607636452 | 3525.914713 |
| 6/16/19 5:15 | 5.447218895 | 3826.313025 |
| 6/16/19 5:30 | 9.621054649 | 4130.883012 |
| 6/16/19 5:45 | 11.48730659 | 4433.29188 |
| 6/16/19 6:00 | 7.311745644 | 4742.039024 |
| 6/16/19 6:15 | 12.31321144 | 5135.994677 |
| 6/16/19 6:30 | 12.12382507 | 5671.512811 |
| 6/16/19 6:45 | 8.029448509 | 6171.677253 |
| 6/16/19 7:00 | 7.955677986 | 6666.846472 |
| 6/16/19 7:15 | 12.21173954 | 7161.934807 |
| 6/16/19 7:30 | 7.96325779 | 7661.273341 |
| 6/16/19 7:45 | 12.20623493 | 8156.362462 |
| 6/16/19 8:00 | 7.960205078 | 8655.704986 |
| 6/16/19 8:15 | 12.13819695 | 33.60424519 |
| 6/16/19 8:30 | 12.21746635 | 57.87192154 |
| 6/16/19 8:45 | 12.2179966 | 33.52415848 |
| 6/16/19 9:00 | 12.14417744 | 33.60204792 |
| 6/16/19 9:15 | 12.21954441 | 26.85134888 |
| | |
As you can see in this example, The size of the dead rows from 2am until 8am increased while there isnt any change in the size of the live rows. During that time I know that there were a delete query that run and deleted a lot of rows. That is why I'm confused here, if more dead rows are generated because of a delete, it means that number of live_tuples should be decreased but it doesnt happen here. Any idea why ?