1

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?

András Váczi
  • 31,278
  • 13
  • 101
  • 147
Suresh Dewasi
  • 23
  • 1
  • 1
  • 4
  • 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. – RBB May 07 '19 at 06: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 07 '19 at 06: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. – András Váczi May 07 '19 at 07:47
  • @SureshDewasi, refer https://dba.stackexchange.com/questions/174663/how-to-vacuumlo-an-rds-postgresql-database Uprego's part 2.B. answer. – RBB May 07 '19 at 07: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) –  May 07 '19 at 08:07

1 Answers1

1

"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".

jjanes
  • 39,726
  • 3
  • 37
  • 48