Hi Gus! This reminds me of a costly mistake I made and you want to avoid: it was a mission critical database (say physical safety, real people) and the vacuum froze the DB for 24 hours, until I finally took it offline.
If you can take it offline (and you have a couple of hours) - disconnect the DB - drop indexes (that's the killer) - remove unnecessary data - vaccuum manually (or better, copy the relevant data to a new table and rename it - this will save the DELETE above and will defragment the table) - rebuild indexes - connect the DB The better solution would be partitioning: - choose a metrics (for instance a timestamp) - create partition tables for the period you want to keep - copy the relevant data to the partitions and create partial indexes - take the DB off line - update the last partition with the latest data (should be a fast update) - truncate the original table - connect partitions - connect the DB In the future, deleting historic data will be a simple DROP TABLE. Hope it helps -- Olivier Gautherot Tel: +33 6 02 71 92 23 El mié, 28 de ene de 2026, 5:06 a.m., Tom Lane <[email protected]> escribió: > Ron Johnson <[email protected]> writes: > > Hmm. Must have been START TRANSACTION which I remember causing issues > in DO > > blocks. > > Too lazy to test, but I think we might reject that. The normal rule > in a procedure is that the next command after a COMMIT automatically > starts a new transaction, so you don't need an explicit START. > > regards, tom lane > > >
