Intermediate LevelPostgreSQL

Why is ADD COLUMN with DEFAULT fast in PostgreSQL 11+?

❓ Interview Prompt: Explain `ALTER TABLE ADD COLUMN` performance, metadata catalog storage of defaults in Postgres 11+, and avoiding table rewrites.

Prior to PostgreSQL 11, adding a column with a non-null default value ('ALTER TABLE t ADD COLUMN c INT DEFAULT 0') rewrote every page of the table on disk, locking large tables for hours!

Postgres 11+ Fast Default:

Postgres 11+ stores column default values in the 'pg_attribute' catalog metadata. Data pages on disk are NOT rewritten; missing values are filled dynamically on read in milliseconds!

Code Implementation Example

sql

-- Instantaneous column addition in Postgres 11+ without table rewrite!
ALTER TABLE orders 
ADD COLUMN is_archived BOOLEAN NOT NULL DEFAULT false;

💡Adding a column with default value instantaneously in Postgres 11+.

🎯 Interview Key Takeaway

Adding columns with constant default values is instant in Postgres 11+ because defaults are stored in catalog metadata.

#ALTER TABLE#ADD COLUMN#Performance#Intermediate

Back to all PostgreSQL Questions→

How to use pgvector in PostgreSQL for vector similarity search with HNSW indexes?

Beginner LevelPostgreSQL

What is PostgreSQL architecture and how do client backends connect via process fork model?