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.

  • 2
    The only way to return disk space to the operating system is vacuum full there is no alternative. But why do you care? The space will be re-used for new or changed rows. Aug 22 '19 at 7:09
  • The size statistic is not showing the real free space you mean? @a_horse_with_no_name
    – Imanol Y.
    Aug 22 '19 at 9:21
  • 1
    You are looking at the free space on the filesystem. The actual data file contains "empty space" after the UPDATE that Postgres will re-use for future data Aug 22 '19 at 9:32

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.

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.