Databases and State

Your database is where your application keeps everything it needs to remember between requests. User accounts, transaction history, preferences, content — all of it lives in a database. The database you choose determines how fast you can query data, how much data you can store, whether you can guarantee consistency, and how much it costs to operate. These constraints shape what your product can do.

Relational databases like PostgreSQL and MySQL organize data into tables with predefined schemas. Each row is a record, each column is a field, and relationships between tables are enforced with foreign keys. The strength of relational databases is structure and consistency — they guarantee that your data follows rules and that transactions are atomic. The weakness is rigidity: changing your schema as your product evolves requires careful migrations.

NoSQL databases like MongoDB, DynamoDB, and Cassandra trade structure for flexibility. Instead of tables and rows, you store documents or key-value pairs. You can add fields to documents without touching every other record. The schema is whatever you decide to write. The cost is that you lose guarantees: NoSQL databases prioritize availability and partition tolerance over strict consistency, which means two users might see different versions of the same data for a brief window.

The CAP theorem states that in a distributed database, you can have at most two of three properties: consistency, availability, and partition tolerance. Partition tolerance is non-negotiable in any distributed system — network failures happen. That leaves you choosing between consistency (everyone sees the same data immediately) and availability (the system always responds, even if the data is slightly stale). Relational databases choose consistency. Most NoSQL databases choose availability.

Read-heavy and write-heavy workloads require different optimizations. A news site that serves millions of page views per hour but publishes articles only a few times per day is read-heavy — you can cache aggressively and serve stale data. A stock trading platform that processes thousands of orders per second is write-heavy — you need fast writes and strong consistency. If your engineers cannot tell you whether your workload is read-heavy or write-heavy, you are optimizing blind.

Database indexes are how you make queries fast. An index is a data structure that lets the database find rows without scanning the entire table. Without an index, querying a million-row table for a specific user ID means checking all million rows. With an index, it is logarithmic. The cost is storage and slower writes — every index must be updated when data changes. Over-indexing slows down your database. Under-indexing makes it unusable.

Vertical scaling means adding more CPU, RAM, or disk to a single database server. Horizontal scaling means splitting data across multiple servers. Relational databases scale vertically well but struggle to scale horizontally because maintaining consistency across distributed nodes is hard. NoSQL databases are designed for horizontal scaling but sacrifice consistency to achieve it. Your choice depends on whether you expect to hit the limits of a single server and how much you care about strict consistency.

Database migrations are how you change your schema in production without breaking your application. Adding a column is straightforward. Renaming a column requires deploying code that handles both the old and new names simultaneously, then migrating data, then removing support for the old name. Dropping a column requires ensuring no code references it first. Migrations gone wrong cause downtime. If your engineers are not testing migrations in staging before running them in production, you will have an outage.