DB performance of frequently updated table

[Michael Šimáček]

Hi,

We've been facing some DB performance issues in Koschei production 
machine recently. Our central table (package) has only ~10000 rows but 
sequential scan of the table was taking unreasonably long (~4s). Other 
tables that are orders of magnitude bigger were faster to query. I was 
investigating the problem and it turned out that the table occupied very 
large amount of disk space:

koschei=> analyze verbose package;
INFO:  analyzing "public.package"
INFO:  "package": scanned 30000 of 322434 pages, containing 1086 live 
rows and 12973 dead rows; 1086 rows in sample, 35640 estimated total rows

Regular vaccum didnt help much, but vacuum full on the table returned 
the performance back to normal (and slowly degrades again, so currently 
we just periodically vaccum full).

My question is how could it get into such poor condition (over like 2 
days) when almost no code that manipulates the table was changed since 
the version that was deployed in cloud and was running fine for months? 
The table is frequently updated (recalculating the package priority 
every few seconds), is there some difference in configuration of 
[auto]vacuuming in the production DB from postgres defaults (which were 
used on cloud instance)?

Thanks,
Michael Simacek