I have a Rails app backed by an Azure-managed Postgres database and I use NewRelic for monitoring. What I've noticed is that the INSERT performance degrades almost linearly over time - what started off as about 7ms became about 150ms average over a few weeks.

I then restarted the DB server and the INSERT was back to about 7ms.

What are some of the things that could cause this that I can look into? The table is pretty small at only 250k rows but it's at a pretty constant load which I expect to increase in future making these restarts impossible without impacting the customers.

  • 1
    Do you have any indexes on the table? Does a VACUUM on the table get rid of the problem? Can you reproduce this with a PostgreSQL on an operating system that you control? If yes, it would be interesting to know how perf top output changes over time (you need PostgreSQL debugging symbols installed). – Laurenz Albe Jun 3 '19 at 13:42
  • Check if you have any constraint in the table, for example a UNIQUE in a column will not be as fast for 5 records than 5M records. Indexes also slow down a lot the insertion process – Imanol Y. Jun 3 '19 at 15:43
  • Most probably, you are not refreshing statistics on the table after a certain amount of inserts, thus degrading performance. As Laurenz Albe said, try the VACUUM or ANALYZE commands. postgresql.org/docs/9.3/sql-vacuum.html postgresql.org/docs/9.3/sql-analyze.html – Luis Alberto Barandiaran Jun 3 '19 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.