18:37

ALTER TABLE … SET WITHOUT OIDS big gotcha

21 February 2023

Normally, when you drop a column from PostgreSQL, it doesn’t have to do anything to the data in the table. It just marks the column as no longer alive in the system catalogs, and gets on with business.

There is, however, a big exception to this: ALTER TABLE … SET WITHOUT OIDS. This pops up when using pg_upgrade to upgrade a database to a version of PostgreSQL that doesn’t support table OIDs (if you don’t know what and why user tables in PostgreSQL had OIDs, that’s a topic for a different time).

ALTER TABLE … SET WITHOUT OIDS rewrites the whole table, and reindexes the table as well. This can take up quite a bit of secondary storage space:

1. On the tablespace that the current table lives in, it can take up to the size of the table as it rewrites the table.
2. On temporary file storage (pg_tmp), it can take significant storage doing the reindexing, since it may need to spill the required sorts to disk. This can be mitigated by increasing maintenance_work_mem.

So, plan for some extended table locking if you do this. If you have a very large database to upgrade, and it still has tables with OIDs, this may be an opportunity to upgrade via logical replication rather than pg_upgrade.

There are no comments yet.