3

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.

Improve this question
3
  • 1
    There is no way to know based on this. Catch the slow query in the act with the auto_explain extension by setting log_nested_statements and log_min_duration. Or at least, next time reindex one index at a time so you know which one was the problem.
    – jjanes
    Apr 10, 2021 at 17:54
  • Thank you @jjanes. I understand that this is not much information to start with, the reason i didn't mention any query is because of the nature of application, there is not a single query that does this. Secondly i dont think this is related to a query because this has been running for almost an year without any problem and suddenly started having this problem. What i mean is that there is no changes to the application or the database.
    – omer Farooq
    Apr 11, 2021 at 11:45
  • Try the auto_explain extension with auto_explain.log_nested_statements = on.
    – Laurenz Albe
    Apr 12, 2021 at 10:28

2 Answers 2

Reset to default
2

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.

Improve this answer
-1

I have no experience with Postgre. But I have had a similar experience with a different database engine. Based on that, I would say that the insert transactions are not being committed properly. Perhaps they are auto-commit.

My advice would be to go through all the queries and rethink the transaction and visibility being used.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.