To test your theory about COMMIT; BEGIN; you could check the PostgreSQL log,
it is likely to contain a line:
WARNING: there is no transaction in progress
"Jeff Boes" <jboes@qtm.net> wrote in message
news:6b79e0aa2a1c3668937e1ce087c4a819@news.teranews.com...
> 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?).
>
