Renaming and Splitting Tables Online

Table-level refactors are where schema design debt finally gets paid: user_accounts becomes accounts, the 90-column orders table sheds its rarely used fulfilment columns into order_fulfilment, the billing_* tables move into their own schema ahead of being extracted into a separate service. Each is simple as DDL — ALTER TABLE ... RENAME, CREATE TABLE, ALTER TABLE ... SET SCHEMA all complete in milliseconds — and each is dangerous as a deploy, because every query in every running version of every service refers to the table by its old name and shape. The instant the rename commits, the old code’s queries fail. This part of Zero-Downtime Schema Evolution Patterns covers how to change table names, boundaries and locations while code that expects the old ones is still running: compatibility views, atomic renames, dual-writes and staged cut-overs. It serves engineers doing structural refactors and platform teams preparing tables for extraction into other databases or services.

The organising principle is the same as for columns in Expand and Contract Methodology: at every moment, both the old and the new shape must be usable, until nothing uses the old one.

Concept & Mechanism

Renames. ALTER TABLE old RENAME TO new in PostgreSQL and RENAME TABLE old TO new in MySQL are catalog operations that take a brief exclusive lock (ACCESS EXCLUSIVE in PostgreSQL; an exclusive metadata lock in MySQL). They are instant — and they break every query that names the old table. In PostgreSQL, objects that reference the table internally (views, foreign keys, sequences, triggers, indexes) follow it by OID, so they keep working; queries written as text in application code do not. The bridge is a view with the old name selecting from the new table. In PostgreSQL, a simple view over one table — CREATE VIEW user_accounts AS SELECT * FROM accounts — is automatically updatable: INSERT, UPDATE and DELETE through the view work without triggers. MySQL views over a single table without aggregates are also updatable. Because both the rename and the view creation are fast catalog operations, they can run in one short transaction on PostgreSQL, so no query ever sees neither name. MySQL’s DDL is not transactional, but RENAME TABLE can swap several names atomically in one statement.

Splits. Moving columns from one table into another is a data migration, not a catalog change. The new table is created and populated in the background; new writes must reach both places during the transition (by triggers or application dual-writes); reads switch to the new table once it is complete and verified; and the old columns are dropped last. The techniques are the ones described in Dual-Write Synchronization and Backfill Optimization, applied to a table boundary.

Moves. ALTER TABLE ... SET SCHEMA moves a table between schemas instantly in PostgreSQL; code that uses unqualified names can be bridged with search_path or a view. Moving a table to another database is a replication problem — copy, keep in sync with logical replication or CDC, cut over — covered in dual-writing across two databases during a cutover.

A few engine details shape which bridge is available. In PostgreSQL, DDL is transactional, so a rename and the view that replaces the old name can commit together and no query ever observes the gap. MySQL runs every DDL statement in its own implicit transaction, so a rename followed by CREATE VIEW leaves a gap of a few milliseconds; the multi-table RENAME TABLE statement is atomic, but it only renames tables, so MySQL renames are usually staged — a view under the new name first, code moved to it, then the swap. Views also behave differently across a rename: PostgreSQL views store a parsed reference to their base table’s OID, so they follow a renamed table automatically, whereas MySQL views store the table’s name, so renaming a table invalidates MySQL views that reference it until they are recreated.

Triggers, row-level security policies and publication membership deserve the same attention. In PostgreSQL they are attached to the table and move with it through renames and schema moves, which is usually what you want — but a compatibility view has none of them. Writes through an automatically updatable view still fire the base table’s triggers and pass its policies, because the write is rewritten onto the table; reads through the view apply the base table’s row-level security only if the view is created with security_invoker = true (PostgreSQL 15+), otherwise the view’s owner’s privileges apply. Check this before relying on a view as a bridge for tables protected by row-level security, or old-name readers may see more rows than they should.

The same bridges support larger reorganisations than a single rename. Consolidating two nearly identical tables into one, for example, runs as a split in reverse: create the combined table, sync both sources into it, backfill, switch reads, switch writes, retire the sources — with a view under each old name if external consumers query them directly. Extracting a set of tables into a new service starts with a schema move, so ownership and permissions are clear, and continues with replication to the new database. Treat each step as its own release with its own verification, and resist combining a rename, a split and a move into one migration, however tempting the single diff looks.

Prerequisites & Decision Criteria

Structural refactors touch every consumer of a table, so inventory them before planning.

Checklist before starting any table refactor:

• Every consumer (services, jobs, reports, replicas feeding analytics) is listed with an owner.
• The bridge (view, dual-write, search path) has been tested with each consumer’s query patterns, including writes.
• The release plan orders steps so old and new code both work at every point.
• Grants for the bridge object match those on the table, so permission errors do not appear at cut-over.
• A rollback — renaming back or pointing the view elsewhere — is written down and rehearsed.

Step-by-Step Procedure

The procedure renames user_accounts to accounts on PostgreSQL. Splits and moves follow the same release structure, with the dual-write or search-path bridge in place of the view.

1. Confirm the view will be updatable for your query patterns. Create a scratch copy and exercise inserts, updates, deletes, RETURNING and ON CONFLICT through a view. Simple views support the first four; INSERT ... ON CONFLICT through a view is supported for simple updatable views in current PostgreSQL versions, but test your exact statements.

2. Rename and create the bridge in one transaction.

-- PostgreSQL · migration role · brief ACCESS EXCLUSIVE; old and new names both work after COMMIT
-- WARNING: grants on the view must match the table's, or old code gets permission errors.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE user_accounts RENAME TO accounts;
CREATE VIEW user_accounts AS SELECT * FROM accounts;
GRANT SELECT, INSERT, UPDATE, DELETE ON user_accounts TO app;
COMMIT;
-- ROLLBACK PATH: BEGIN; DROP VIEW user_accounts; ALTER TABLE accounts RENAME TO user_accounts; COMMIT;

3. Deploy code that uses the new name. Every service switches to accounts at its own pace; the view keeps unconverted services working.

4. Verify nothing uses the old name. Query statistics show whether any statement still references user_accounts.

-- PostgreSQL · read-only · requires pg_stat_statements; reset stats after the last deploy for a clean signal
SELECT calls, left(query, 120) FROM pg_stat_statements WHERE query ILIKE '%user_accounts%';

5. Drop the view in a later release, with a lock timeout.

6. Clean up names of dependent objects — indexes, constraints and sequences keep their old names (user_accounts_pkey, user_accounts_id_seq); rename them for clarity, each with a brief lock.

Verification & Observability

The primary signal is query-level: which statements still use old names or old columns. pg_stat_statements (reset after the cut-over deploy) or database audit logs answer that directly; on MySQL, performance_schema.events_statements_summary_by_digest serves the same purpose.

-- MySQL 8.0 · read-only · statements still referencing the old table name
SELECT COUNT_STAR, LEFT(DIGEST_TEXT, 120) AS stmt
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%`user_accounts`%'
ORDER BY COUNT_STAR DESC;

Query statistics need care to be trustworthy. pg_stat_statements accumulates since its last reset, so a statement that ran last month still appears; reset it after the deploy that should have removed the last old-name caller (SELECT pg_stat_statements_reset()), then watch over a full business cycle, including weekly and monthly batch jobs. Statements issued through the compatibility view are recorded with the text the client sent, so old-name queries are easy to spot. On a fleet with read replicas, check the replicas’ statistics too — reports and analytics often run only there.

During the transition, watch error rates for relation does not exist (42P01), permission errors on the bridge (42501), and — for splits — divergence between old and new copies of the moved data, using reconciliation queries like those in reconciling divergence between dual-written tables.

Communication is part of observability for structural changes. Because the old name or shape must survive until the last consumer moves, publish the transition — the old and new names, the bridge, and the date the bridge will be removed — to every team that owns a consumer, and track each consumer’s migration explicitly. The query statistics tell you who is still behind; the published plan tells them what to change and by when. Bridges that are removed on a date announced weeks in advance cause far fewer surprises than bridges removed as soon as the numbers look quiet.

Rollback Path

Every step up to dropping the bridge is reversible with another catalog operation: drop the view and rename the table back in one transaction, and every consumer — old and new — keeps working as long as the new-name consumers are rolled back too. After the bridge is dropped, rolling back means recreating it, which is equally quick. Splits are the exception: once reads have switched to the new table and the old columns are dropped, reverting needs a reverse data migration, so keep the old columns (still dual-written) for at least one release after switching reads. The general policy is in Rollback Automation.

Common Errors & Fixes

ERROR: relation "user_accounts" does not exist after a rename. Root cause: renamed without a bridge. Fix: create the compatibility view immediately (CREATE VIEW user_accounts AS SELECT * FROM accounts) with matching grants.

ERROR: cannot insert into view "user_accounts" / DETAIL: Views that do not select from a single table or view are not automatically updatable. Root cause: the bridge view joins or aggregates. Fix: keep the bridge a simple single-table view, or add INSTEAD OF triggers.

ERROR: permission denied for view user_accounts. Root cause: grants were not copied to the view. Fix: grant the same privileges on the view as the table had.

SELECT * consumers break after a split. Root cause: columns moved out of the table. Fix: keep dropped columns until consumers are updated; avoid SELECT * in application code.

Child Page Index

Four guides cover the common refactors. Renaming a table with an updatable view expands the procedure above, including ORMs and INSERT ... RETURNING through views. Splitting a wide table into two handles moving columns with dual-writes and a backfill. Moving a table to another schema uses SET SCHEMA with search-path and view bridges. And renaming a MySQL table atomically with RENAME TABLE covers MySQL’s multi-table rename and its metadata-lock behaviour.

Column-level renames, which follow the same principles within a table, are in renaming a column with expand and contract.

Frequently Asked Questions

Is a table rename expensive? No. It is a catalog update in both PostgreSQL and MySQL, taking an exclusive lock for milliseconds. The risk is entirely in the queries that still use the old name, which is what a compatibility view prevents.

Can the application write through a compatibility view? Yes, if the view is a simple selection from one table. PostgreSQL makes such views automatically updatable, and MySQL treats single-table views without aggregates as updatable. Complex views need INSTEAD OF triggers.

Do foreign keys and indexes survive a rename? Yes. They reference the table internally, not by name, so they continue to work. Their names keep the old table’s prefix until you rename them.

How long should the compatibility view stay? Until query statistics show no use of the old name across a full business cycle, including weekly and monthly jobs. Removing it earlier risks breaking a rarely run report or batch job.