PostgreSQL Tutorial: DDL commands that causes table rewrite

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

Search

Categories

Tags