29 Dec 2020

Which SQL causes a Table Rewrite in Postgres?

EDIT: Updated to v17 (devel) - (Jan 2024).

While developing SQL based applications, it is commonplace to stumble on these 2 questions:

  1. What DDLs would block concurrent workload?
  2. Whether a DDL is going to rewrite the table (and in some cases may need ~ 2x disk space)?

Although completely answering Question 1 is beyond the scope of this post, one of the important pieces that helps answering both of these questions is whether a DDL is going to cause a relfilenode change..

For a brief background, each regular table in Postgres stores data in one or more files, each of which is referenced in the postgres 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)

The table below shows which DDLs would cause a table rewrite. As has been discussed here, we need some more info to completely answer Question 1, however meanwhile this table helps in making some concurrency / disk-usage related decisions for all Postgres versions supported today.


intgr said...

Additionally, `ALTER TABLE ALTER COLUMN TYPE TEXT` can avoid a rewrite if the old type is binary compatible. For example when increasing the length limit of VARCHAR(n), or converting VARCHAR(n) to TEXT.

James Zicrov said...
This comment has been removed by a blog administrator.
Robins Tharakan said...

@intgr - Appreciate the feedback. Updated the grid with said feedback.

Unlock PostgreSQL Superpowers with pg_tle

pg_tle  - A Must-Know for Developers PostgreSQL is a fantastic database, packed with features. But sometimes, you need to add a little somet...