Four Database Choices You Can't Easily Undo
Most database problems are fixable. A slow query gets an index. A hot table gets partitioned. A node runs out of memory and you add another. The feedback loop is tight enough that you can experiment your way to a better configuration without touching application code or migrating data.
But a handful of decisions don’t work that way. They’re structural. They shape the schema, the replication topology, and how the application accesses the database. Once production data is flowing, reversing course means rewriting foreign keys across dozens of tables, migrating millions of rows, or telling engineering teams that “write succeeded” now means something different than it used to. These are the decisions that haunt architects years later. They made a permanent call without realizing it.
Here are four of them.
Primary Key Strategy
Your primary key type governs insert locality in B-tree indexes, shard assignment in distributed databases, and the coordination overhead required to generate new IDs. Changing it later can mean hours of downtime on a large table, plus cascading updates to foreign keys, indexes, views, and application code.
Sequential integers are the default for a reason. Inserts are sequential, which means each new row appends to the right edge of the B-tree index. That keeps page utilization high and avoids the random-write fragmentation pattern that kills index performance over time. The downside: sequential integers are predictable. Anyone watching your API responses can count your users or infer your order volume. They also don’t shard naturally; you need offset sequences or a central ID server to avoid collisions between nodes.
UUID v4 solves the coordination problem. IDs are generated locally with no need for a sequence or a central ID server. But UUID v4 values are random, which means each new insert lands at an unpredictable position in the index. Over time, this causes page splits and fragmentation, and Postgres has to do significantly more I/O to maintain the index. On write-heavy workloads, this will be noticeable.
UUID v7 gives you time-ordered, globally unique IDs that can be generated without coordination and still maintain B-tree locality. UUID v7 is natively supported in Postgres 18. If you’re starting fresh, it’s the right default for most workloads.
Tenancy model
How you isolate tenants in a multi-tenant system determines how you handle compliance, backup granularity, connection pooling, and operational overhead for the rest of the system’s life. The three common models are shared schema, schema-per-tenant, and database-per-tenant. Each fits a different set of constraints, and switching between them at scale is extremely expensive.
Shared schema stores all tenants in the same tables, using a tenant_id column to filter rows. This is the cheapest model to operate: one schema to migrate, one connection pool to manage, and straightforward cross-tenant analytics. The weakness is isolation. A misconfigured query can return another tenant’s data. Row-level security in Postgres helps, but it requires discipline to apply consistently. There’s also a risk of creating a hot spot: a large or high-traffic tenant concentrates its rows on the same table pages, which creates lock contention and cache pressure that affects every other tenant on the system.
Schema-per-tenant creates a separate Postgres schema for each tenant in the same database. Data is physically separated at the schema level, migrations can be run tenant-by-tenant, and RLS is no longer the primary isolation mechanism. The problem is connection count: Postgres connections are expensive, and with thousands of tenants each needing at least a few connections, you hit pgBouncer limits faster than expected.
Database-per-tenant offers the strongest isolation: separate backup/restore, separate encryption keys, separate access controls. It’s also the most operationally expensive. At dozens of customers it’s manageable; at tens of thousands, the overhead of schema migrations, monitoring, and connection routing becomes substantial.
The deciding factors are tenant count, tenant size variance, and compliance requirements. Shared schema works when your application hosts a lot of tenants who are roughly equal in size, you don’t have strict isolation requirements, and you need cross-tenant analytics. Schema-per-tenant is worth the connection overhead when tenants need schema changes rolled out independently rather than all at once, or light customization. Database-per-tenant is the right call when you have a small number of large enterprise customers with data residency or compliance requirements. The operational cost is real, and it only makes sense if the contracts justify it.
Data model
The data model is the choice that most directly encodes your assumptions about how the system will be queried. Relational, document, and wide-column models each suit a different access pattern. Switching models later requires migrating data, rewriting the application’s data access layer, and often reconsidering consistency guarantees that the application already depends on.
Relational modeling with normalization gives you flexibility in querying. Data can be combined at read time in ways that weren’t anticipated at write time. Third Normal Form keeps update anomalies low; when a value changes, it changes in one place. The cost shows up at scale, where joins across large tables require careful index design and sometimes pre-aggregation.
Document models store data as self-contained objects. A single read returns everything needed for the common case, with no joins. This works well when entities have irregular shapes that don’t fit a fixed schema. The problems show up when you need to query across documents, or when a field nested inside every document needs to be updated globally.
Wide-column stores have high write throughput and efficient time-range scans. But the query model can be constrained by the partition key and clustering columns defined at table creation. You can’t filter on arbitrary columns, and there are no joins. Query patterns have to be designed upfront and encoded in the schema. A table designed around one access pattern cannot easily serve a second one without denormalization and data duplication.
Write path architecture
How writes flow through your system determines what durability and consistency guarantees are available to application code. This is the most conceptually difficult choice to reverse because it changes what “write succeeded” actually means.
Single-leader asynchronous replication is the default in most relational databases. The primary receives a write, confirms it locally, and returns success before the replica has acknowledged. Replication lag is a problem, and a failover can promote a replica that hasn’t received the last few writes. Synchronous replication eliminates that risk but adds latency to every write, because the primary waits for at least one replica to confirm before returning success.
Multi-leader and leaderless architectures trade consistency for write availability. Multiple nodes can accept writes simultaneously, which means no single point of contention. The cost is conflict resolution. When two nodes accept conflicting writes for the same key, the system has to decide which one wins. Last-write-wins discards data silently. Application-level conflict resolution is correct but complex.
Event sourcing takes a different approach. Writes are immutable events appended to a log, and current state is maintained in read models that are updated as events arrive. The audit trail is complete, temporal queries are natural, and you can rebuild the read models from scratch by replaying the log. But event sourcing is one of the hardest patterns to remove once it’s in production. An application built around appending events doesn’t have a clean path back to mutable rows. And schema evolution in the event log requires careful versioning.
Change data capture tools read the database’s replication log and publish changes to downstream consumers. CDC is powerful for feeding data pipelines and search indexes without coupling writes to those consumers. The structural commitment is in the downstream systems. Once ten pipelines depend on the CDC stream, changing the source schema requires coordinating all of them.
None of these choices look obviously wrong in the prototype phase. A users table with an integer primary key, a single tenant, a normalized schema, and asynchronous writes to one Postgres instance looks completely reasonable when you have fifty rows. The constraints only become visible when the volume of data, the number of tenants, or the concurrency of writes gets large enough to expose them. By then, the cost of changing course is high. That’s what makes them worth thinking through before the first row of production data arrives.
