Our largest (by total byte count) table has a nearly-equal flow of data in and
out on a daily basis (that is, we add some 40k rows during the day, and then
every night expire stuff that is timestamped beyond our cutoff, which results in
about 40k deletions).
After the deletions, the table gets vacuumed (not FULL):
INFO: --Relation public.articles_content--
INFO: Index ix_articles_content_pk: Pages 398; Tuples 180175: Deleted 0.
CPU 0.00s/0.08u sec elapsed 0.08 sec.
INFO: Removed 122 tuples in 77 pages.
CPU 0.00s/0.01u sec elapsed 0.02 sec.
INFO: Pages 3886: Changed 0, Empty 0; Tup 180175: Vac 122, Keep 26437, UnUsed
135721.
Total CPU 0.02s/0.17u sec elapsed 0.19 sec.
INFO: --Relation pg_toast.pg_toast_634643688--
INFO: Index pg_toast_634643688_index: Pages 27156; Tuples 1256923: Deleted 732.
CPU 2.32s/0.80u sec elapsed 27.93 sec.
INFO: Removed 732 tuples in 250 pages.
CPU 0.01s/0.04u sec elapsed 0.05 sec.
INFO: Pages 1667633: Changed 71514, Empty 0; Tup 1256923: Vac 732, Keep 172914,
UnUsed 5499031.
Total CPU 148.07s/12.52u sec elapsed 809.71 sec.
INFO: Analyzing public.articles_content
The "Keep" and "UnUsed" numbers seem high to me, compared to other tables. Can
anyone interpret these and tell me anything about what we could do with this
table to make it "look" better? (I'm not convinced that the extra space affects
anything but sequential scans, and we don't do that to this table.)
If it helps: I have a theory that database connections from our webserver (which
is running PHP and mod_perl) are in "idle in transaction" states (in other
words, they issue "Commit; Begin;" and then sleep), which causes the tables to
retain bulk (nice image, eh?).