1

I changed table structure and I am about to remove old JSONB column from about 2.5M rows big table. The JSONB makes this table really big - pg_total_relation_size: 408GB

Performing ALTER TABLE ... DROP COLUMN ..; was so heavy that it blocked whole db and caused 502 in most of the services.

QUESTION: if I set that JSONB column NULL before performing ALTER TABLE DROP COLUMN will it go faster? Or is there any other option excluding the one with coping all the collumns except the one have aimed for droping into seperate table and swapping names afterwards?

1 Answer 1

1

ALTER TABLE ... DROP COLUMN is always very fast, because it is a metadata operation. If it takes long for you, that is because there are concurrent long running transactions using the table: ALTER TABLE statements that modify the table structure require an ACCESS EXCLUSIVE lock that conflicts with everything else.

You'll have to wait for a time when nobody uses the table to run your statement.


The bigger problem is that ALTER TABLE ... DROP COLUMN will not reduce the size of the table — it will only cause PostgreSQL to "ignore" the column data in the existing rows.

To actually get rid of the column, you'd have to preform a no-effect data modification like

UPDATE tab SET id = id;

which will rewrite the whole table. However, that will increase the table size considerably because of PostgreSQL's multi-version architecture.

So you'd end up doing it in batches like

WITH some_ids AS (
   SELECT ctid FROM tab
   WHERE id BETWEEN 1 AND 100000
)
UPDATE tab
SET id = tab.id
FROM some_ids
WHERE some_ids.ctid = tab.ctid;

VACUUM tab;

and run VACUUM on the table after each batch.


An alternative, simpler way to shrink the table after dropping the column is to pg_dump, DROP and restore it.

4
  • so the best solution will be the one I wanted to avoid that is to copy all the columns except that one big with JSONB into new table, drop the whole old table (this will cause reclaim of space, right?), and rename new table to name of old.
    – andilabs
    Feb 8 at 18:04
  • Pretty much, yes. Feb 8 at 18:30
  • @Charlieface Sure, but unless I misunderstood, this question is about storage in the table, not the index. Feb 11 at 11:05
  • @Charlieface VACUUM (FULL) just copies the tuples, it doesn't modify them, for example by removing a dropped column. Feb 12 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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