skip to content

Why is CREATE TABLE orders_backup AS SELECT * FROM orders a weak rollback plan before a risky migration?

level: seniorimportance: should knowfreq 36%

answer

  1. it copies a result set, not a table
  2. no keys, indexes or defaults come with it
  3. a restore needs the original DDL anyway
  4. frozen at one instant
  5. later writes are not in the copy

basics

~20 s

It captures rows and inferred column types at one instant and nothing else — no keys, indexes, defaults, constraints or identity state. Restoring means rebuilding all of that by hand, and every write that lands after the snapshot is lost.

solid answer

~50 s

A CTAS snapshot copies a **result set**, so what you get back is columns, types and rows. Absent are the primary key, foreign keys in both directions, unique and check constraints, indexes, column defaults, identity or sequence state, triggers and privileges. Restoring from it is therefore not `rename and go`: you must recreate the original DDL from source control anyway, reload the data, and reset the identity high-water mark so new inserts do not collide with restored rows. The snapshot is also a point in time. Any row written between the snapshot and the failure is not in it, so a naive restore silently discards live traffic, and any foreign keys pointing at `orders` are dangling in the interim. It works as a cheap safety net for a small, quiescent table during a short window. For anything live, prefer additive, reversible migrations, keep the real DDL in version control, and use the engine's backup or export tooling for the data.

code

sql · 8 lines
sql
-- The snapshot: rows and inferred types only
CREATE TABLE orders_backup_2026_08_20 AS SELECT * FROM orders;

-- The restore is not a rename: recreate the real table from checked-in DDL,
-- reload, then fix the generator before anything inserts again.
INSERT INTO orders (order_id, customer_id, net_amount, created_at)
SELECT order_id, customer_id, net_amount, created_at
FROM orders_backup_2026_08_20;

go deeper

for a junior

Know that this statement copies rows and column types only, so the copy is not a working replacement for the original table.

for a middle

List concretely what is missing — keys, indexes, defaults, constraints, identity state — and explain why the restore needs the original DDL regardless.

for a senior

Reason about the live-system consequences: the snapshot is a point in time, later writes are lost on restore, and the copy itself costs a full read and doubles storage. Offer additive migrations as the better plan.

for a principal

Own the rollback doctrine across teams: which changes require a real backup, which must be designed to be reversible without one, and how backup tables are named, owned and expired so they do not accumulate.

## What the snapshot actually contains `CREATE TABLE orders_backup AS SELECT * FROM orders` is a CTAS, and a CTAS is defined by its input: a query result. The new table therefore has one column per select-list item, with types the engine inferred, holding the rows the query returned at that moment. Everything that lives in the source table's *definition* rather than in its *result* is gone: - primary key and unique constraints - foreign keys declared on `orders`, and the ones other tables declare *against* `orders` - `CHECK` constraints - every index - column `DEFAULT` clauses - identity / auto-increment behaviour and the underlying sequence position - triggers, comments, privileges In PostgreSQL the copied columns are not even `NOT NULL`. So `orders_backup` is not a spare `orders`; it is a pile of rows with the right shape. ## What a restore would actually require Because of that, the restore path is longer than people assume when they write the snapshot: 1. Recreate `orders` with its true DDL — which has to come from source control or the catalog, not from `orders_backup`. 2. Load the rows back with `INSERT ... SELECT`. 3. Re-establish the identity or sequence high-water mark, or the next insert collides with a restored key. 4. Recreate indexes and re-enable or re-validate constraints, which on a large table is itself a long operation. 5. Verify the foreign keys pointing at `orders` from other tables still resolve. If step 1 depends on having the DDL in version control anyway, the snapshot was only ever solving the data half of the problem — and the data half is what backup tooling already does better. ## The point-in-time problem The snapshot freezes the table as of the moment the statement ran. In a live system the interesting question is what happens to the writes that arrive afterwards: - If the migration fails an hour later and you restore, every order placed in that hour vanishes. Nobody notices until a customer does. - If the application is still writing while you restore, you get two divergent versions of the truth and no way to merge them. - A rollback that loses committed, acknowledged writes is usually worse than the broken state it replaces. So the snapshot is only a real rollback plan when writes are stopped for the whole window — which is a maintenance window, and if you have one you probably had better options. ## Cost and blast radius A `SELECT *` snapshot of a large table doubles that table's data volume and takes as long as reading the whole thing. Teams often discover this at the worst moment: the "quick safety copy" before a migration turns out to be the longest step in the change. And the copy tends to survive — `orders_backup_2024_03`, `orders_backup_final`, `orders_backup_real` accumulate in the schema until someone dares to drop them. ## What to do instead - **Prefer migrations that do not need a rollback.** Additive changes — add a nullable column, backfill, start writing, switch reads, drop later — are reversible at each step without restoring anything. - **Keep the authoritative DDL in version control**, so recreating the table is a script rather than an archaeology exercise. - **Use the engine's backup or export mechanism** for the data, since it captures a consistent state and is designed to be restored. - **If you do take a CTAS snapshot, treat it as a data-only companion** to the checked-in DDL, name it with a date, and give it an owner and a deletion date. - **Bound the window.** A snapshot is defensible for a small, low-traffic table during a five-minute change; it is not a plan for a hot table over a multi-hour migration. ## When it is fine None of this makes CTAS snapshots useless. Before an ad-hoc `UPDATE` on a small configuration or lookup table, `CREATE TABLE settings_backup AS SELECT * FROM settings` is a perfectly sensible thirty-second insurance policy: the table is small, nothing else writes to it during the change, and the restore really is an `INSERT ... SELECT` back. The failure mode is treating that habit as a rollback strategy for a large, live, referenced table.

  • If you keep the CTAS snapshot anyway, what should accompany it?
    The authoritative `CREATE TABLE` DDL from version control, since the snapshot cannot rebuild keys, defaults, constraints or indexes. Add a dated name, a documented owner, and a deletion date so the schema does not accumulate orphan backup tables. And record the identity or sequence value at snapshot time, because restoring rows without resetting it produces key collisions on the next insert.
  • What kind of migration removes the need for a rollback snapshot altogether?
    An additive, staged one. Add a nullable column, backfill in batches, dual-write, switch reads once the new path is verified, and only then remove the old column in a separate later change. Each step is individually reversible by stopping, so there is no moment where recovery depends on restoring a copy of the data.
  • Why does restoring rows from a CTAS snapshot risk key collisions?
    Because the identity or sequence that generated the original keys is a property of the source table and is not copied. After a restore the sequence may sit below the highest restored key, so the next insert tries to reuse an existing value and violates the primary key. The restore has to advance the generator past the maximum restored value explicitly.

saying these in an interview costs you the question

  • Assumes swapping the table names is a complete restore
  • Thinks constraints and indexes come back with the rows
  • Forgets writes that arrive after the snapshot
  • Ignores identity or sequence state on restore
  • Treats a copy of a huge live table as cheap

context

resolving links…