Postgres database insert become slow after 10 days, and then it needs a full vacuum We have a postgres database which is almost 15GB's in size after vacuum. With a dedicated hardware i,e 32GB of ram with 12 Cores. The data automatically gets inserted around 300k inserts per day, and we also process the data. The data is deleted after 3 months, in order to keep the db size down.
NORMAL INSERT SPEED USED TO BE: 1200 INSERTS / MINUTE
One of the important table has quite a large trigger on it (We cant do anything about it unfortunately), the DB can insert roughly around 1200 records per minute, which is quite acceptable for us. These records are not inserted in bulk, but rather individually after processing.
AFTER THE PROBLEM STARTED: 200 INSERTS / MINUTE
About 10 days ago, our database suddenly got extremely slow i.e 200 inserts per minute. So after a few sleepless nights we vacuumed top 10 largest tables, but only increased the performance to 300 inserts per minute.
AFTER VACUUM 10 LARGE TABLE: 300 INSERTS / MINUTE
So we vacuum analyzed the whole database, and also REINDEXED the 10 largest tables. This worked extremely well and we were quite satisfied with the results
AFTER FULL VACUUM ANALYSE AND REINDEX: 1400 INSERTS / MINUTE
But just after 10 days we began to have the same problem again, now the inserts are back to 200 inserts/min. We didn't change any configurations of postgres.
AFTER 10 DAYS, BACK TO: 200 INSERTS / MINUTE
Can you please help me identify the problem? i know this is not much information to work with, but if u guys did have this problem before you might instantly identify it. Can this be problem with Disk or too many deadlocks?
Hard disk might not be a problem, as we did gain good speeds after the vacuum, but please let me know if you think that.
Thank you for helping me out.
If performance is restored after a VACUUM ANALYZE, I would question whether the table(s) in question are being auto_vacuumed and auto_analyzed.
If you look at the pg_stat_user_tables view, do the values for last_autovacuum and last_autoanalyze seem reasonable?
If tables are constantly being locked then auto vacuum might not be able to run.