Postgresql – Free space of toasted content

disk-spacepostgresqlupdate

I have just deleted a big part of the data stored in one jsonb inside my users table (Its not needed anymore) and just keep an id inside that column. I expected to free a lot of disk space with this action.

UPDATE users SET my_jsonb = '{id: xxxx }';

The problem is that when i try to see the free space in the disk remains the same

/dev/md125 935G 501G 387G 57%

Also the table

 Table                                            Size        External
 users                                          | 497 GB     | 478 GB

My last option is to try a VACUUM FULL users but since this table is used in production environment it would not be convenient to lock it a unkown ammount of time.

Im running a postgres 10.5 database on a CENTOS RHEL7 machine.

Best Answer

A plain VACUUM will make the free space available for new data (i.e., other TOASTed JSON data).

If you really want to give the free space back to the OS, and you cannot afford the exclusive lock, try pg_repack:

To perform a full-table repack, pg_repack will:

  1. create a log table to record changes made to the original table
  2. add a trigger onto the original table, logging INSERTs, UPDATEs and DELETEs into our log table
  3. create a new table containing all the rows in the old table
  4. build indexes on this new table
  5. apply all changes which have accrued in the log table to the new table
  6. swap the tables, including indexes and toast tables, using the system catalogs
  7. drop the original table

It still needs enough space for the original table and the new copy.

Related Question