Back to Devoured - July 23, 2026
DEVOURED
The Database Is a Liability You Can't Revert

The Database Is a Liability You Can't Revert

Data databaseinfrastructurepostgresql DeepSQL
Database schema choices like primary keys and partition keys are permanent, high-stakes decisions that lack the 'revert' button available in application code.
What: Venkat Sakamuri, an ex-Oracle engineer, argues that database schema changes exist on a 'ladder of irreversibility,' where mistakes in partitioning or data types lead to multi-year technical debt rather than simple rollbacks. He highlights how poor primary key choices, like UUIDs, cause performance degradation by bloating indexes, and outlines how his tool, DeepSQL, attempts to catch these errors by analyzing workload patterns in pg_stat_statements.
Why it matters: This highlights the danger of treating database schema changes with the same casual agility as application code, pointing to a need for better automated guardrails in CI/CD pipelines to prevent long-term architectural failures.
Deep dive
  • Code is ephemeral and easy to revert, but schema changes often require table rewrites and downtime at scale.
  • The 'Green Zone' (low risk) includes session parameters and concurrent index creation.
  • The 'Yellow Zone' (disruptive) includes changing column types and adding constraints, which can trigger massive table locks.
  • The 'Red Zone' (irreversible) involves partition keys, primary key strategies, and collation, often requiring full database migrations to fix.
  • UUIDs as primary keys can cause index bloat and B-tree rebalancing overhead compared to BIGINT.
  • Choosing the wrong partition key leads to 'partition pruning' failure, forcing the database to scan all partitions for queries.
  • Tools should analyze real workload patterns via pg_stat_statements before allowing DDL changes to reach production.
Decoder
  • WAL: Write-Ahead Logging, the mechanism used by databases to record changes before committing them to the data files, ensuring data integrity during crashes.
  • pg_stat_statements: A built-in PostgreSQL module that tracks execution statistics of all SQL statements executed by the server.
  • DDL: Data Definition Language, the subset of SQL used to define and change the database schema, such as CREATE, ALTER, and DROP.
  • Access Exclusive Lock: The strongest lock level in PostgreSQL, which blocks all other access to the table, including reads and writes.
Original article

The Database Is a Liability You Can’t Revert

A bad code commit is a revert. A bad schema decision is a five-year problem. Database mistakes are asymmetric, and prevention is the only viable strategy.

TL;DR

  • Code is ephemeral; schema is permanent. You can git revert a bad microservice deploy. You cannot git revert a BIGINT primary key on a 5TB table that has propagated to 300 foreign keys.
  • Database decisions exist on a ladder of irreversibility. Changing work_mem is cheap. Changing a column type is a table-rewriting nightmare. Changing a partition key is a company-level migration project.
  • Prevention is not a nice-to-have; it is the only sane strategy. The cost of a bad schema decision made in a two-line pull request can exceed the cost of the entire engineering team for a year.

At Oracle, working on the query engine, we had a mantra: the database must protect the user from themselves. We spent more time building guardrails—things like Zonemaps to automatically prune blocks, or robust resource management—than we did chasing raw benchmark numbers. Why? Because we knew a simple truth that the application world often forgets: you can’t undo gravity, and you can’t undo a bad schema decision at scale.

Code is cheap. A buggy Python service gets rolled back. A runaway LLM API call burns a few thousand dollars, you set a cap, and you move on. The feedback loop is measured in minutes.

Your database is different. The feedback loop for a bad schema choice isn't minutes; it's years. The cost isn't a few thousand dollars; it's a permanent tax on every query, a drag on every engineer, and a multi-quarter migration project that threatens the stability of the entire business.

This asymmetry is the most dangerous part of modern data engineering. A junior developer, tasked with adding a feature, can introduce a schema change that creates a decade of technical debt—all with a single approved pull request.

The Ladder of Irreversibility

Not all database changes are created equal. Think of them on a ladder. The higher you climb, the more permanent and painful your choices become.

Rung 1: The Cheap & Reversible (The Green Zone)

These are the tuning knobs and ephemeral objects. They are low-risk and easy to change.

  • Session Parameters: Setting work_mem for a large sort or hash join. If you set it too high, you might get an OOM kill on a single backend. You adjust and rerun. The blast radius is tiny.
  • Indexes: CREATE INDEX CONCURRENTLY in Postgres is a lifesaver. You can add an index without catastrophic locking. If you choose the wrong columns, you DROP INDEX CONCURRENTLY and try again. It costs CPU and I/O, but it doesn't corrupt your logic or require downtime.
  • Views: A view is just a stored query. CREATE OR REPLACE VIEW is effectively free. It’s pure logic, completely decoupled from the physical storage of your data.

Rung 2: The Painful & Disruptive (The Yellow Zone)

Here, you're starting to touch the physical layout of the table. Mistakes are recoverable, but at the cost of high-drama migrations and potential downtime.

  • Changing a Column Type: ALTER TABLE users ALTER COLUMN id TYPE BIGINT;. Seems innocent. On a 100-row table, it is. On a one-billion-row users table, Postgres will rewrite the entire table and its indexes. This means acquiring an ACCESS EXCLUSIVE lock (blocking all reads and writes), generating titanic amounts of WAL, and putting extreme pressure on your I/O subsystem and autovacuum. This isn't a migration; it's a planned outage that you'll have to rehearse for weeks.
  • Adding NOT NULL: You forgot to add NOT NULL to a column. A year later, a NULL finally slips in and causes a sev-1 when it hits a join condition. The fix? ALTER TABLE ... SET NOT NULL. To do this, Postgres must scan the entire table to validate the constraint. If it finds a single NULL, the command fails. Now you're on a data-cleaning fire drill just to apply a constraint you should have had on day one.

Rung 3: The Near-Permanent (The Red Zone)

Welcome to the top of the ladder. If you get this wrong, you don't 'fix' it. You live with it, or you begin a year-long project to rebuild your entire data platform.

  • Primary Key Choice: You chose UUIDs for your primary keys. Now every single one of your indexes suffers from terrible locality and a 4x bloat compared to a BIGINT. The B-tree is constantly being rebalanced, the buffer cache hit rate is abysmal because the keys are random, and every foreign key in your entire system is now a bloated 16 bytes. Reversing this means changing hundreds of tables, rewriting all application code, and performing the mother of all data migrations.
  • Partitioning / Sharding Key: This is the mistake I see most often. You have a 5TB events table and you partition it by RANGE(created_at). It works beautifully for time-series queries. Then the product changes, and 90% of your query load becomes SELECT * FROM events WHERE user_id = ?. The planner looks at this query and has no choice but to scan every single partition, because user_id has no correlation with created_at. 
    -- Querying a table partitioned by the wrong key
-- EXPLAIN SELECT * FROM events WHERE user_id = 42 AND created_at > '2023-10-01';

-- PLAN (Simplified)
Append  (cost=0.00..84502.41 rows=12 width=128)
  ->  Seq Scan on events_p2023_10 WHERE user_id = 42 ...
  ->  Seq Scan on events_p2023_11 WHERE user_id = 42 ...
  ->  Seq Scan on events_p2023_12 WHERE user_id = 42 ...
  -- ... and so on for every relevant partition.

    A query that should have taken 20ms by hitting a single partition now takes seconds and causes a massive I/O storm. The fix? Create a new table partitioned by (user_id). Set up a dual-write pattern in your application. Backfill 5TB of data. Validate everything. Then, in a terrifying maintenance window, switch the application to read from the new table. You just paid double the storage and spent a full engineering quarter to fix a single line of DDL.

  • Collation: You initialized your cluster with the wrong LC_COLLATE. Now your string sorting order is subtly wrong ('a' vs 'A' vs 'á'), or worse, your indexes on TEXT columns can't be used for LIKE queries. The only fix is a full pg_dump and pg_restore into a brand new cluster. For a multi-terabyte system, that's not a migration; it's a company-threatening event.

What DeepSQL does about this

You cannot expect every engineer to have the institutional knowledge of a 20-year Oracle kernel veteran. That's an impossible ask. The only solution is to codify that expertise and place it as a guardrail in front of your database. When a migration script containing PARTITION BY RANGE (created_at) is proposed, DeepSQL doesn't just check for syntax. It analyzes your actual query workload via pg_stat_statements and sees that 95% of your predicates are on user_id. It then flags this DDL as a high-risk, irreversible decision and recommends a more appropriate partitioning strategy. It prevents the problem before the pull request is merged, turning a potential five-year liability into a five-minute code revision.

Explore all 56 articles in today's digest

Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi