July 1, 2024
Summary: In this tutorial, you will learn which DDL commands will cause table rewrite in PostgreSQL.
Table of Contents
Introduction to table rewrite
In practice, the DDL commands that causes table rewrite is very dangerous, they may lead to an uncertain period of downtime, and in some cases may need ~ 2x disk space.
In general, a DDL command that causes table rewrite, in other words, the DDL command that causes a relfilenode change, it would usually block concurrent workloads.
For a brief background, each regular table in PostgreSQL stores data in one or more files, each of which is referenced in the system catalog with a relfilenode. A simple way to check whether the current implementation is going to create / refer to another copy (file) is whether the relfilenode changes. TRUNCATE is a standout here, which by design is going to purge the table data, so although the relfilenode would change here, in total it obviously wouldn’t consume anywhere close to 2x disk-space.
Which DDL commands will cause table rewrite?
The table below shows which DDLs would cause a table rewrite. This table can help you in making some concurrency / disk-usage related decisions for all PostgreSQL versions supported today.
Table Rewrite Scenarios | v10 | v11 | v12 | v13 | v14 | v15 | v16 | v17 |
---|---|---|---|---|---|---|---|---|
ALTER TABLE ADD COLUMN INTEGER | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN INTEGER NULL | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN INTEGER NOT NULL | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN INTEGER DEFAULT 100000 | YES | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN INTEGER DEFAULT 100000 NULL | YES | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN INTEGER DEFAULT 100000 NOT NULL | YES | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN TEXT COLLATE german | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN TEXT COLLATE german NULL | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN TEXT COLLATE german NOT NULL | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN TEXT COLLATE german DEFAULT ‘ß’ | YES | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN TEXT COLLATE german DEFAULT ‘ß’ NULL | YES | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ADD COLUMN TEXT COLLATE german DEFAULT ‘ß’ NOT NULL | YES | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE DROP COLUMN | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN TYPE VARCHAR(1000) -- Binary compatible (Varchar(M) -> Varchar(N)) | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN TYPE TEXT -- Binary compatible (Varchar -> Text) | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN TYPE TEXT -- Binary incompatible (Int -> Text) | YES | YES | YES | YES | YES | YES | YES | YES |
ALTER TABLE ALTER COLUMN TYPE BIGINT -- Commonly sought after (for e.g. Int -> Bigint) | YES | YES | YES | YES | YES | YES | YES | YES |
ALTER TABLE ALTER COLUMN TYPE TEXT COLLATE german; -- Previous column type Int | YES | YES | YES | YES | YES | YES | YES | YES |
ALTER TABLE ALTER COLUMN TYPE TEXT COLLATE german; -- Previous column type Text | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN TYPE TEXT COLLATE german; -- Previous column type Varchar() | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN SET DEFAULT 10000 | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN DROP DEFAULT | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN SET NOT NULL | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN DROP NOT NULL | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN ADD GENERATED ALWAYS AS IDENTITY | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN DROP IDENTITY | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN SET STATISTICS -1 -- Change from non-default | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN SET STATISTICS 1000 -- Change from non-default | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN SET STATISTICS 1000 -- Change from default | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN SET (n_distinct=100) | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN RESET (n_distinct) | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN SET STORAGE MAIN | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN SET STORAGE PLAIN | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN SET STORAGE EXTERNAL | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE ALTER COLUMN SET STORAGE EXTENDED | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE CLUSTER ON; | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE SET WITHOUT CLUSTER | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE SET WITH OIDS | YES | YES | [1] | [1] | [1] | [1] | [1] | [1] |
ALTER TABLE SET WITHOUT OIDS | YES | YES | YES[2] | YES[2] | YES[2] | YES[2] | YES[2] | YES[2] |
ALTER TABLE SET TABLESPACE tmp_tblspc | YES | YES | YES | YES | YES | YES | YES | YES |
ALTER TABLE SET LOGGED | YES | YES | YES | YES | YES | YES | YES | YES |
ALTER TABLE SET UNLOGGED | YES | YES | YES | YES | YES | YES | YES | YES |
ALTER TABLE SET (FILLFACTOR=10) | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE RESET (FILLFACTOR) | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER TABLE INHERIT | NO | NO | NO | NO | NO | NO | NO | NO |
ALTER INDEX set tablespace tmp_tblspc | NO | NO | NO | NO | NO | NO | NO | NO |
DELETE FROM | NO | NO | NO | NO | NO | NO | NO | NO |
CLUSTER | YES | YES | YES | YES | YES | YES | YES | YES |
COMMENT ON TABLE IS ’testing’ | NO | NO | NO | NO | NO | NO | NO | NO |
TRUNCATE TABLE | YES | YES | YES | YES | YES | YES | YES | YES |
VACUUM FULL | YES | YES | YES | YES | YES | YES | YES | YES |
[1] SQL deprecated as of this version.
[2] marked for WITHOUT OIDs purely out of caution. Rewrite happens only during major version upgrade where table was WITH OIDs.
Forbid the DDL commands that causes table rewrite
PostgreSQL supports table_rewrite
event. The table_rewrite
event occurs just before a table is rewritten by some actions of the commands ALTER TABLE
and ALTER TYPE
. While other control statements are available to rewrite a table, like CLUSTER
and VACUUM
, the table_rewrite
event is not triggered by them.
Thanks to the table_rewrite
event, it is possible to implement a table rewriting policy simply forbidding those DDL commands. Here’s an example implementing such a policy.
CREATE OR REPLACE FUNCTION forbid_table_rewrites()
RETURNS event_trigger
AS $$
BEGIN
RAISE EXCEPTION 'command % prohibited', tg_tag;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER forbid_rewrites
ON table_rewrite
EXECUTE FUNCTION forbid_table_rewrites();
ALTER TABLE foo ALTER COLUMN id TYPE BIGINT;
ERROR: command ALTER TABLE prohibited
CONTEXT: PL/pgSQL function forbid_table_rewrites() line 3 at RAISE