We are using a Postgres database (9.3) as our production database. It is generating huge lo objects. Due to this, the size is growing very fast. So we need to do database cleaning frequently. To remove these lo objects we are having long downtimes.

To clean the DB, we are performing the below steps in sequence:

  1. vacuumlo database_name

  2. vacuumdb database_name -t pg_largeobject

  3. vacuumdb database_name -t pg_largeobject_metadata

  4. reindexdb -t pg_largeobject database_name

  5. reindexdb -t pg_largeobject_metadata database_name

Can we do the above online (during the night) without having any downtime?

  • 1
    If you handled lo_unlink well while you remove them via application, and autovacuum is enabled, I guess you shouldn't worry about anything. – Spike May 7 '19 at 6:35
  • Thanks Spike for your prompt response. We have already enabled autovacuum process. We are not using lo_unlink. Can you elaborate how can we do it through aplication? – Suresh Dewasi May 7 '19 at 6:44
  • How do these large objects being generated? Could you please describe your use case a bit more, so that it becomes clear, why @Spike's suggestion doesn't work for you? Also, unrelated: 9.3 is already out of support, please consider upgrading soon. – dezso May 7 '19 at 7:47
  • @SureshDewasi, refer dba.stackexchange.com/questions/174663/… Uprego's part 2.B. answer. – Spike May 7 '19 at 7:58
  • 1
    Unrelated, but: Postgres 9.3 is no longer supported you should plan the upgrade to the current version (11) as soon as possible (upgrading might also improve the situation with auto-vacuum as that has been improved also in the last 6 years) – a_horse_with_no_name May 7 '19 at 8:07

"reindexdb" takes the most stringently lock, and is the only thing likely to be causing downtime.

Unless you have evidence that it is necessary, just stop doing the "reindexdb". It might be necessary to do it once, in order to get you out of the hole, but shouldn't be necessary to do each time you do "vacuumlo".

| improve this answer | |
  • Thanks jjanes. will try in test environment and will let you know the results here. – Suresh Dewasi May 15 '19 at 5:13

Your Answer

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

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