I have a Postgres database into which about 20 million rows are inserted every day.
The records are inserted via a powershell script that calculates the average time taken to insert each record. It typically takes about 1.5ms per record (including some record parsing and a trigger).
Every now and again (typically after about 3 million rows are added) the processing time jumps up to about 12ms/record (it's a very noticeable step change that happens very quickly)
I've already found the cure. I stop the process that adds the records, do an ANALYSE on the table, and restart the process. The insert time goes back to 1.5ms/record. Although this only take 5-10 minutes, I do end up loosing several thousand records during this downtime.
The database is already shutdown for a brief period at night and all the tables get ANALYSEd regularly, but is there any way I avoid doing additional ANALYSEs during the day?
(note - I've tried simply stopping and restart the INSERT process - it makes no difference. It's only stopping, doing an ANALYSE and then restarting that makes a difference)