Databases and data modelling

An index is a sorted structure, in nearly every case a B-tree, that turns a full table scan into a targeted lookup. On a large table that is the difference between reading a million pages and reading four, which is why the first index on a hot query is often a thousandfold improvement and the fifteenth is not.

Every index must be maintained on write, so each one slows inserts, updates and deletes and occupies disk and memory. An unused index is pure cost with no benefit, and most mature databases can tell you which of theirs have never been scanned. Removing those is the rare optimisation that improves writes without risking reads.

Composite indexes follow the prefix rule, which is the single most useful piece of indexing knowledge: an index on (a, b) serves a query on a, and on a and b together, and cannot seek on b alone. It is a phone book ordered by surname then first name. Choosing the column order is choosing which queries the index can answer, and the usual guidance is equality columns first, then the range or ordering column.

A covering index is the next step: if the index contains every column the query needs, the database answers from the index without touching the table at all. On a read-heavy endpoint that removes half the work, and it is why adding one included column sometimes beats adding a cache.

There are ways to disable an index by accident, and they are worth memorising because each looks harmless in review. Wrapping the column in a function, comparing it against a different type so the database has to cast, and starting a LIKE pattern with a wildcard all mean the index's ordering no longer matches the question being asked. Expression indexes exist for the first case, when the function is genuinely needed.

Finally, indexes are not only B-trees. Hash indexes serve equality alone, GIN and inverted indexes serve containment and full text, and BRIN indexes are tiny and effective for columns that correlate with physical order, such as an append-only timestamp. Recognising when the default is the wrong shape is worth more than tuning the default.

Normalising means storing each fact exactly once, so an update touches one row and the data cannot contradict itself. The customer's address lives in the customer table and every order refers to it. That is the default, it is right most of the time, and it costs joins on read. Denormalising duplicates data to avoid those joins. Reads get faster and simpler, and every copy becomes a thing that can drift from the original. The trade is write complexity for read speed, and it is worth making when reads dominate and there is a reliable mechanism for keeping the copies current: a trigger, an outbox, a stream consumer, a scheduled rebuild.

There is a category of duplication that is not denormalisation at all, and confusing the two causes real bugs. The price on an order line is not a cached copy of the product price; it is the price at the time of sale, and it must not change when the product is repriced. Any value that is part of a historical record belongs on that record permanently. Copying it is correct modelling rather than an optimisation.

The usual mature shape is a normalised source of truth plus deliberately denormalised read models, updated asynchronously. That is the same idea as a materialised view, and where the database offers one, using it is preferable to hand-rolled duplication, because the refresh path is somebody else's tested code rather than yours.

The classical normal forms are worth knowing at a level of one sentence each. First: no repeating groups, one value per column. Second: every non-key column depends on the whole key. Third: and on nothing but the key. Almost every practical schema stops there, and the higher forms exist mostly in exam papers.

The one thing to avoid is denormalising early because joins feel expensive. A join on an indexed foreign key is one of the cheapest operations a relational database performs, and the intuition that it is slow usually comes from a missing index rather than from the join itself.

Relational databases are the default for reasons that are easy to undervalue until they are gone: you can query the data in ways nobody anticipated, the database enforces constraints rather than trusting every writer, and a transaction can span several rows in several tables. Choose otherwise with a reason you can state in a sentence.

Document stores suit data that is read as a whole unit and whose shape varies per record: a product with wildly different attributes per category, an event payload, a document that genuinely is a document. The gain is that the record matches the object, and the loss is that a question the schema did not anticipate becomes a scan or a second copy of the data.

Key-value stores serve lookups by one known key at very high volume, and nothing else. Wide-column stores such as Cassandra sit near them and are designed around the query: you model the tables from the access patterns, accept duplication as normal, and get linear write scaling and no ad hoc querying at all.

The honest summary is that most applications fit a relational model, and a managed Postgres will carry far more load than most products ever see. It also handles JSON well, with indexing on document fields, which covers the semi-structured part of a schema without a second database and a second operational burden.

Schemaless is the claim that deserves the most scepticism, because there is always a schema: it either lives in the database, where it is enforced once, or in every piece of code that reads the data, where each has its own slightly different idea of what a record looks like. The second option is not the absence of a schema, it is a schema nobody can query and nobody maintains.

The strongest argument for a second store is a genuinely different access pattern rather than a performance claim: full text search, time series at high ingest rates, a graph traversal that would be a self-join twelve levels deep. Those are real, and they are much rarer than the number of polyglot architectures in the wild suggests.

A database connection is not a cheap handle. In Postgres each one is an entire operating system process with its own memory, so the cost is measured in megabytes rather than kilobytes, and the practical ceiling arrives well before most people expect, often in the low hundreds. Past it the failure is not gradual: the database refuses new connections outright while the ones it holds are still being served, so the symptom is a wall rather than a slope.

The instinct is to raise the limit, and it buys very little. Each additional slot is another process, so the ceiling moves a little and then the machine runs out of memory instead, and along the way every connection makes the shared work more expensive, because the internal bookkeeping the database does across its backends grows with their number. There is a real effect where throughput peaks at a concurrency close to the number of cores and then declines as more connections compete, which means a database with four hundred connections can complete less work per second than the same database with fifty.

A pooler sits in front and multiplexes many client connections onto a small number of real ones. Clients connect to it, it holds a modest set of server connections, and it hands one to whichever client currently has work. The application sees the connection count it wants and the database sees the one it can serve, which is the whole trick and the reason this is a configuration change rather than an architectural one.

Which pooling mode you choose decides how much that buys. Session pooling holds a real connection for the duration of a client's session and therefore helps almost not at all, since the ratio is unchanged for any client that stays connected. Transaction pooling returns the connection at every commit, so a hundred idle clients occupy no server connections at all, and that is where the large multiple comes from. Statement pooling goes further still and is rarely usable.

Transaction pooling has a price, and it is specific: anything whose lifetime exceeds a transaction stops working, because the next statement may arrive on a different server connection. Session variables, temporary tables, advisory locks, LISTEN and NOTIFY, and prepared statements are the usual casualties, and prepared statements are the one that bites hardest because ORMs and drivers often use them silently. Modern poolers can track and replay prepared statements per connection, which removes most of that, and it is worth confirming rather than assuming.

Serverless makes the whole problem sharper rather than different. Instance count follows traffic and each instance opens its own connections, so demand for slots peaks exactly when the database is busiest, and a function that scales to a thousand concurrent invocations will ask for a thousand connections from something that can serve two hundred. That is why every serverless platform either ships a pooler or expects you to put one in front, and why a data API over HTTP is the other common answer: it removes the connection from the equation entirely.