Recently during a post-migration activity, we had to populate a very large table with a new UUID column (NOT NULL with a default) and backfill it for all existing rows.
Instead of doing a straight:
ALTER TABLE ... ADD COLUMN ... DEFAULT ... NOT NULL;
we chose the commonly recommended performance approach:
- Create a new table (optionally UNLOGGED),
- Copy the data,
- Rename/swap the tables.
This approach is widely used to avoid long-running locks and table rewrites but it comes with hidden gotchas. This post is about one such gotcha: object dependencies, especially views, and how PostgreSQL tracks them internally using OIDs.
A quick but important note
On PostgreSQL 11+, adding a column with a constant default is a metadata-only operation and does not rewrite the table. However:
- This is still relevant when the default is volatile (like
uuidv7()), - Or when you must immediately enforce
NOT NULL, - Or when working on older PostgreSQL versions,
- Or when rewriting is unavoidable for other reasons.
So the rename approach is still valid but only when truly needed.
The Scenario: A Common Performance Optimization
Picture this: You’ve got a massive table with millions of rows, and you need to add a column with unique UUID default value and not null constraint values. The naive approach? Just run ALTER TABLE ADD COLUMN. But wait for large tables, this can lock your table while PostgreSQL rewrites every single row and can incur considerable time.
So what do we do? We get clever. We use the intermediate table(we can also use unlogged table) with rename trick, below is an sample created to show the scenario’s.
drop table test1;
create table test1
(col1 integer, col2 text, col3 timestamp(0));
insert into test1
select col1, col1::text , (now() - (col1||' hour')::interval)
from generate_series(1,1000000) as col1;
create view vw_test1 as
select * from test1;
CREATE TABLE test1_new
(like test1 including all);
alter table test1_new
add column col_uuid uuid default uuidv7() not null;
insert into test1_new
select * , uuidv7() from test1;
BEGIN;
ALTER TABLE test1 RENAME TO test1_old;
ALTER TABLE test1_new RENAME TO test1;
COMMIT;
Looks perfect, right? Fast, efficient, minimal downtime.
But do take a note on what happen to when we want to drop old table.
demo=# drop table test1_old;
ERROR: cannot drop table test1_old because other objects depend on it
DETAIL: view vw_test1 depends on table test1_old
HINT: Use DROP ... CASCADE to drop the dependent objects too.The Silent Killer: Views Still Point to the Old Table
Even though test1 was renamed, the view still references test1_old:
demo=# \dv vw_test1
List of views
┌────────┬──────────┬──────┬──────────┐
│ Schema │ Name │ Type │ Owner │
├────────┼──────────┼──────┼──────────┤
│ public │ vw_test1 │ view │ postgres │
└────────┴──────────┴──────┴──────────┘
(1 row)
demo=# \sv vw_test1
CREATE OR REPLACE VIEW public.vw_test1 AS
SELECT col1,
col2,
col3
FROM test1_old
Why?
Understanding PostgreSQL’s OID and its usage in dependency.
PostgreSQL does not track dependencies by object names. It uses internal object identifiers (OIDs).
When a view is created:
- The SQL is parsed,
*is expanded,- Table and column references are stored by OID and attribute number in
pg_rewrite.
Renaming a table only changes the human-readable name in pg_class. The OID remains the same.
But when you swap tables, you are not renaming — you are introducing a new object with a new OID. The view still points to the old one.
-- Check what your view is actually referencing
SELECT DISTINCT
source_ns.nspname AS source_schema,
source_table.relname AS source_table,
source_table.relkind AS object_type
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class view_class ON view_class.oid = r.ev_class
JOIN pg_class source_table ON source_table.oid = d.refobjid
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE view_class.relname = 'vw_test1'
AND d.deptype = 'n' -- normal dependency
AND source_table.relkind IN ('r', 'v', 'm', 'f') -- tables, views, mat views, foreign tables
ORDER BY source_schema, source_table;
And It’s Not Just Views, it’s just the visible symptom. This approach also affects:
- Grants and privileges
- RLS policies
- Triggers
- Foreign keys
- Logical replication publications
- Statistics objects
- Atomic Functions that reference the table
All of these are bound to the old table’s OID.
Ready to enhance your PostgreSQL development skills? My course on PL/pgSQL will help you excel as Database Developer. Click here to start your journey!!
The Right Way to Handle It
So how do we fix this? We have a few options:
Option 1: Recreate the Views (Safest)
-- After the rename, drop and recreate all dependent views
demo=# drop view vw_test1;create view vw_test1 as select * from test1;
DROP VIEW
CREATE VIEW
Option 2: Avoid table swapping if you can, Batch updates are often safer:
Sharing an sample just for reference.
-- Add column without default (fast)
ALTER TABLE users ADD COLUMN status TEXT;
-- Update in batches (no long lock)
DO $$
DECLARE
batch_size INT := 10000;
last_id BIGINT := 0;
BEGIN
LOOP
UPDATE users
SET status = 'active'
WHERE id > last_id
AND status IS NULL
AND id <= last_id + batch_size;
EXIT WHEN NOT FOUND;
last_id := last_id + batch_size;
COMMIT;
END LOOP;
END $$;
-- Then add the default for future rows
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
Always Check Dependencies First
SELECT
dependent_view.relname as view_name
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid
WHERE source_table.relname = '<<table_name>>'
AND dependent_view.relkind = 'v';
If you see views, you know you’ll need to recreate them.
The Takeaway
PostgreSQL’s OID-based dependency system is elegant and efficient — but it also means:
Names are for humans. OIDs are what PostgreSQL actually uses.
The rename/swap trick can be a powerful optimization, but it trades execution-time safety for operational complexity. If you use it:
- Audit dependencies,
- Script object recreation,
- Test in staging,
- And document everything.
Otherwise, the optimization meant to avoid a lock may quietly introduce correctness, maintenance, and operational risks that cost far more later.
Database and Migration Insights 