ALTER TABLE

---

In Data Engineering, the ALTER TABLE command is your tool for "Schema Evolution." As business requirements change, your tables must adapt without losing the existing data.

Because Postgres is a strictly typed relational database, ALTER operations can sometimes be "expensive" (locking the table) or "cheap" (metadata only). Knowing the difference is key to maintaining high availability.

---

Common ALTER TABLE Operations

Here are the most frequent structural changes you will perform:

A. Adding and Dropping Columns

-- Adding a column with a default value
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);

-- Dropping a column
ALTER TABLE users DROP COLUMN middle_name;

B. Modifying Column Types

This is a "heavy" operation. Postgres usually has to rewrite the entire table to convert the data.

-- Changing an INT to a BIGINT
ALTER TABLE orders ALTER COLUMN price TYPE NUMERIC(12,2);

C. Renaming

These are "cheap" metadata-only operations. They happen almost instantly.

ALTER TABLE users RENAME COLUMN user_name TO login_id;
ALTER TABLE users RENAME TO customers;

---

Managing Constraints and Defaults

You can use ALTER TABLE to tighten or loosen the "guardrails" on your data.

• Add a Constraint: ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

• Set/Drop NOT NULL: ALTER TABLE users ALTER COLUMN email SET NOT NULL;

• Change a Default: ALTER TABLE users ALTER COLUMN status SET DEFAULT 'pending';

---

The Performance "Gotcha": Table Rewrites

As a Data Engineer, you must be aware of Access Exclusive Locks. When you run an ALTER TABLE command, it often prevents any other queries (even SELECT) from touching the table until it's done.

Operation	Performance Cost	Locking Level
Rename Column/Table	Ultra-Fast (Metadata)	Access Exclusive
Add Column (NULL)	Ultra-Fast (Metadata)	Access Exclusive
Add Column (WITH DEFAULT)	Fast (In modern Postgres)	Access Exclusive
Change Data Type	Slow (Full Table Rewrite)	Access Exclusive
Add Check Constraint	Medium (Scans all rows)	Access Exclusive

---

Pro-Tip: Adding Constraints without Downtime

If you have a table with 100 million rows, adding a CHECK constraint normally locks the table while it validates every row. In Postgres, you can do this in two steps to minimize downtime:

1. Add the constraint as NOT VALID: This adds the rule for new data but doesn't check old data yet. It requires a very short lock.

2. Validate the constraint: This scans the table but doesn't block reads or writes.

-- Step 1: Add the rule (Fast)
ALTER TABLE transactions 
ADD CONSTRAINT check_positive_amount CHECK (amount > 0) NOT VALID;

-- Step 2: Verify existing data (Slow, but Non-blocking)
ALTER TABLE transactions VALIDATE CONSTRAINT check_positive_amount;

---

Visualizing Table Evolution

Summary

• Metadata Changes (Rename, Add Nullable Column) are near-instant.

• Data Changes (Change Type, Add Constraint) require scanning or rewriting the table.

• Always use NOT VALID when adding constraints to large production tables to avoid long blocking locks.

Would you like to tackle the "Analytical Boss" of SQL—Window Functions (ROW_NUMBER, LAG/LEAD)—next, or should we talk about View creation?

---