Hi, > > Our application requires a number of processes to select and update rows > > from a very small (<10 rows) Postgres table on a regular and frequent > > basis. These processes often run for weeks at a time, but over the > > space of a few days we find that updates start getting painfully slow. > > We are running a full vacuum/analyze and reindex on the table every day, > Full vacuum, eh? I wonder if what you really need is very frequent > non-full vacuum. Say, once in 15 minutes (exact rate depending on dead > tuple rate.) > Is there a difference between vacuum and vacuum full? Currently we have a cron job going every hour that does:
VACUUM FULL VERBOSE ANALYZE plc_fldio REINDEX TABLE plc_fldio The most recent output was this: INFO: --Relation public.plc_fldio-- INFO: Pages 1221: Changed 3, reaped 256, Empty 0, New 0; Tup 108137: Vac 4176, Keep/VTL 108133/108133, UnUsed 19, MinLen 84, MaxLen 84; Re-using: Free/Avail. Space 445176/371836; EndEmpty/Avail. Pages 0/256. CPU 0.04s/0.14u sec elapsed 0.18 sec. INFO: Index plcpage_idx: Pages 315; Tuples 108137: Deleted 4176. CPU 0.03s/0.04u sec elapsed 0.14 sec. INFO: Rel plc_fldio: Pages: 1221 --> 1221; Tuple(s) moved: 0. CPU 0.03s/0.04u sec elapsed 0.36 sec. INFO: Analyzing public.plc_fldio VACUUM REINDEX We'll up it to every 15 minutes, but i don't know if that'll help because even with the current vacuuming the updates are still getting slower and slower over the course of several days. What really puzzles me is why restarting the processes fixes it. Does PostgreSQL keep some kind of backlog of transactions all for one database connection? Isn't it normal to have processes that keep a single database connection open for days at a time? Regarding the question another poster asked: all the transactions are very short. The table is essentially a database replacement for a shared memory segment - it contains a few rows of byte values that are constantly updated byte-at-a-time to communicate data between different industrial control processes. Thanks for the thoughts everyone, Alison ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match