Rename a PostgreSQL table with ALTER TABLE old_name RENAME TO new_name, but plan for an ACCESS EXCLUSIVE lock and application code that may still use the old name.
The catalog change is quick once it acquires the lock. Waiting for that lock and coordinating deployed clients are the parts that turn a one-line migration into production work.
Table of contents
- Run the rename in the correct schema
- Understand lock behavior
- Know which names do not follow automatically
- Choose a rollout strategy
- Verify and rollback
- How this fits the rest of the stack
- FAQ
Run the rename in the correct schema
ALTER TABLE public.customer_order RENAME TO customer_orders;Schema-qualify the source so search_path cannot select an unexpected object. The new name is an identifier within the same schema; ALTER TABLE RENAME does not move the table or rewrite its rows. Quote identifiers only when the existing design genuinely uses mixed case or unusual characters.
Test in a transaction on staging and inspect the application, views, functions, jobs, and monitoring queries that reference the old name. The database can track many internal dependencies, but text embedded in code and external tools is outside that graph.
Understand lock behavior
ALTER TABLE RENAME requires an ACCESS EXCLUSIVE lock. The catalog operation is normally fast, yet it can wait behind a long query or idle transaction, and once queued it may contribute to a lock pileup. Set an appropriate lock timeout so a deploy fails cleanly instead of waiting indefinitely while traffic backs up.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE public.customer_order RENAME TO customer_orders;
COMMIT;Observe pg_stat_activity and pg_locks before the maintenance. Resolve long transactions through application behavior, not by terminating sessions blindly.
Know which names do not follow automatically
Dependent views generally continue to reference the same table object after the rename, but human-readable names of indexes, constraints, sequences, and triggers may retain the old wording. That is functionally acceptable but can confuse operations. Rename them separately only when consistent naming is worth the additional lock and migration steps.
Raw SQL in applications, report builders, migration tools, permissions scripts, and dashboards will not be rewritten. Search repositories and operational configuration for the old identifier and include dynamic SQL paths that simple text search may miss.
Choose a rollout strategy
If every client deploys atomically with the migration, stop or drain the application, rename, deploy, and verify. For zero-downtime systems with mixed client versions, introduce a compatibility layer first: create a view or API boundary using the future name, migrate readers and writers, then perform the final cleanup in a later release.
Dual-writing two physical tables is usually excessive for a rename. Prefer one source of truth and a compatibility view where semantics permit it. Document whether writes through that view are supported before relying on it.
Verify and rollback
- Confirm the new relation with to_regclass
- Run application smoke tests
- Check grants and row-level security behavior
- Inspect jobs and reports
- Monitor errors mentioning the old name
- Keep a reverse rename migration ready
A reverse rename is simple only if no new deployment or object has claimed the old name. Treat rollback as a coordinated release action, not an isolated SQL command.
Prepared statements and long-lived connection pools deserve an explicit test. A connection that prepared SQL against the old relation name may fail after the migration even when newly opened connections work, so recycle or invalidate prepared statements as part of the rollout. Check logical replication, change-data-capture connectors, and audit tooling that may identify relations by name. Update ORM metadata and regenerate code before deploying clients that expect the new identifier. If a compatibility view is used, reproduce grants and security-barrier behavior deliberately, and test inserts or updates rather than assuming the view is writable. After the final cleanup, remove temporary aliases so they do not become permanent undocumented API surface.
How this fits the rest of the stack
Schema changes belong in the same operational picture as the service using them. The RunxBuild hosting calculator models database and runtime costs together, and the RunxBuild dashboard keeps deployment logs close to migration events.
Useful related references:
- Postgres-XL: What the Distributed Fork Was, and Why It Lost to Citus
- Postgres Switch Database: \connect, USE, and psql Defaults
- Postgres Port 5432: What to Check Before Deploying
- Databases on RunxBuild
FAQ
What is the PostgreSQL syntax to rename a table?
Use ALTER TABLE schema.old_name RENAME TO new_name. The new table remains in the same schema.
Does renaming a table rewrite its data?
No. It changes catalog metadata, but it still requires an ACCESS EXCLUSIVE lock.
Do indexes rename automatically?
They continue to work, but their human-readable names may retain the old table wording.
Will views break after a table rename?
Catalog-tracked view dependencies normally follow the object, while raw SQL strings in applications and tools must be updated.
How can I avoid a long deployment block?
Inspect long transactions, set a lock timeout, and use a compatibility rollout when old and new clients must overlap.