A school keeps its exam scripts in one giant cabinet. Every script since the school opened is in there, in the order it arrived. For years this works. Then one morning the head asks for this term’s scripts, and the clerk spends an hour searching a cabinet that holds thirty years of paper.

Nothing is broken. The clerk is not slow. The cabinet is simply built for keeping everything, not for finding what matters today.

So the school makes two changes. First, it adds drawers, one per year, so the clerk opens only the drawer that matters. Second, it takes the oldest drawers to the store room, where they are safe and cheap to keep, and out of the way of daily work. When a year’s retention period ends, the school removes the whole drawer in one move, instead of shredding it page by page.

That is partitioning and archiving. A database table gets the same treatment, and this post covers how it works in PostgreSQL, SQL Server, Oracle and MongoDB, what it costs, and what it means for security.

In a previous post here, we saw indexes, a cabinet of cards that points at the data. In a previous post here, we learned to read query plans. Partitioning works at a different level: instead of making the search faster, it makes the pile smaller.

Partitioning in plain words
Partitioning splits one large table into smaller pieces, called partitions, using a key such as the order date. To the application it still looks like one table. PostgreSQL supports range, list and hash partitioning [S1]. Range partitions use an inclusive lower bound and an exclusive upper bound, so a partition for October runs from 1 October up to, but not including, 1 November [S1].

The first payoff is partition pruning. When your query filters on the partition key, the database can prove which partitions cannot contain the rows and skip them [S1]. SQL Server calls it partition elimination and says the engine might skip some partitions when the query predicate references the partitioning column [S2]. Oracle gives a clear example: a query for one week of a two-year weekly-partitioned Orders table accesses one partition instead of 104, and Oracle says pruning can often improve query performance by several orders of magnitude [S3]. Notice the wording in each: might, can, often. The speed-up depends on the query filtering on the partition key.

The second payoff is removing old data, and it is the one I think teams underrate. PostgreSQL’s manual says dropping a partition with DROP TABLE or DETACH PARTITION is far faster than a bulk operation, and avoids the VACUUM overhead of a bulk DELETE [S1]. SQL Server lets you switch individual partitions out of a table and into an archive table [S2]. Oracle describes a rolling window, where a new partition is added by an exchange load, which is much more efficient than modifying the entire table [S3].

The four databases, side by side
1. PostgreSQL needs you to create the partitions yourself. Rows that fit no partition cause an error unless you define a default partition [S1]. A monthly setup looks like this:

CREATE TABLE orders (
id bigint,
order_date date NOT NULL,
seller_id bigint,
total numeric,
PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2026_10 PARTITION OF orders
FOR VALUES FROM (‘2026–10–01’) TO (‘2026–11–01’);

— retire a month later
ALTER TABLE orders DETACH PARTITION orders_2026_10 CONCURRENTLY;
 — back it up or process it, then:
 — DROP TABLE orders_2026_10;

Note the primary key includes the partition key. That is not optional, and I come back to it under the tradeoffs. DETACH PARTITION CONCURRENTLY takes only a SHARE UPDATE EXCLUSIVE lock on the parent instead of ACCESS EXCLUSIVE, and a detached partition stays a regular table you can still read, so you can back it up or process it before dropping it [S1].

2. SQL Server builds partitioning from two objects. A partition function sets the boundaries, and a partition scheme maps the partitions to filegroups [S2]. Microsoft’s own example uses RANGE RIGHT, so each boundary date is the lower bound of its partition [S2]:

CREATE PARTITION FUNCTION pf_orders_month (datetime)
AS RANGE RIGHT FOR VALUES (‘2026–09–01’, ‘2026–10–01’, ‘2026–11–01’);

CREATE PARTITION SCHEME ps_orders_month
AS PARTITION pf_orders_month ALL TO ([PRIMARY]);

— later, move the oldest partition out into an archive table
ALTER TABLE dbo.orders SWITCH PARTITION 1 TO dbo.orders_archive;

The engine supports up to 15,000 partitions per table or index, and since SQL Server 2016 SP1 the feature is no longer limited to certain editions [S2]. The SWITCH statement needs an archive table with a matching structure, and I did not verify the full list of requirements, so check Microsoft’s SWITCH documentation before you rely on it.

3. Oracle partitions are transparent to applications: from the application’s perspective, a partitioned table is identical to a nonpartitioned one, with no changes needed to SQL queries or DML [S3]. Range partitioning is the most common type and is often used with dates [S3]. Interval partitioning goes a step further and tells the database to automatically create partitions of a specified interval when data arrives beyond the existing ranges [S3]:

CREATE TABLE orders (
id NUMBER, order_date DATE NOT NULL, seller_id NUMBER, total NUMBER
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, ‘MONTH’))
(PARTITION p_start VALUES LESS THAN (DATE ‘2026–01–01’));

Oracle’s recommendations add some pitfalls. You cannot manually add partitions to an interval-partitioned table, and hash partitions are not an effective way to manage historical data, because only equality or IN-list predicates are supported for pruning [S4]. Oracle also notes a licensing detail: in its 11g licensing table, Partitioning is an Enterprise Edition option marked as extra cost [S5]. That page is old, so check the current terms for your version before you plan around it.

4. MongoDB is different. It does not split a collection into partitions the way these three do. Splitting data across machines is sharding, which we cover later. For retention, MongoDB gives you the TTL index. A TTL index is a single-field index on a date that removes documents after a set number of seconds [S6]:

// delete documents 90 days (7,776,000 seconds) after createdAt
db.events.createIndex({ createdAt: 1 }, { expireAfterSeconds: 7776000 })

For time series data, a time series collection can remove expired data automatically, and it does so by whole bucket: a bucket goes only when every document in it has expired [S7].

Archiving is not deleting
Archiving means moving rarely used data to somewhere cheaper and safer. In PostgreSQL that is a detached partition you back up and then drop, or move to cheaper storage, which the manual lists as a benefit [S1]. In SQL Server it is the archive table you switch into [S2]. Oracle’s guide describes spreading groups of data across different types of storage and managing them individually [S3]. In MongoDB it is a copy you take out before the TTL removes the original.

The point is the decision. Each piece of old data should have a rule: keep it live, keep it archived, or delete it, and a date for each.

The tradeoff
Partitioning looks like free speed. It is not. Here is what it costs.

- Only queries that filter on the key get faster. Microsoft is direct about this: queries that cannot skip partitions could take longer to execute as the number of partitions increases, TOP, MAX or MIN queries on non-partitioning columns might get slower because every partition must be evaluated, and partitioning rarely improves performance in OLTP systems where such queries are common [S2]. Joins between tables that are not partitioned on the join field can even be slowed by partitions [S2]. Worse, Microsoft says to avoid designs with many hundreds or thousands of partitions unless strictly necessary [S2].

- More partitions means more planning work. PostgreSQL’s planner handles partition hierarchies of up to a few thousand partitions fairly well, as long as pruning leaves only a few; planning time and memory grow with the number of partitions left after pruning [S1].

- It may not be needed at all. PostgreSQL gives a rule of thumb that a table should exceed the server’s physical memory before partitioning pays off [S1]. If your table is smaller, try an index first, as in a previous post here, and check your plan, as in a previous post here.

- Uniqueness gets harder. In PostgreSQL, a primary key or unique constraint on a partitioned table must include all the partition key columns [S1]. A unique order id alone can no longer be enforced by the database across all partitions. If duplicates are expensive, for example, duplicate payments, that guarantee has to come from somewhere else. This is where the idempotency idea from a previous post here earns its keep.

- You must plan the future. PostgreSQL errors on rows with no matching partition unless you define a default one [S1], so someone has to create next month’s partition before the month starts. Oracle’s interval partitioning avoids this, at the cost of the restrictions above [S3] [S4].

- It may cost money. Oracle lists Partitioning as an extra-cost option, at least in its 11g documentation [S5].

- TTL is not a stopwatch. MongoDB’s background task runs every 60 seconds, deletion can start between 0 and 60 seconds after the index is built, and under heavy workloads expired documents may remain for some time [S6]. Each cycle also has limits: up to 50,000 documents or one second of deleting per index [S6].

My rule: partition when most queries filter on one key, when the table is big, and when you want to remove old data cheaply. Do not partition just because the table feels large.

The security angle
Old data is still data, and “we archived it” is not the same as “we protected it”. Here is what I check.
- Keep only what you are allowed to keep. GDPR’s storage limitation principle says personal data should be kept in a form which permits identification for no longer than is necessary for the purposes it is processed for, with longer storage allowed only for archiving in the public interest, research or statistics, subject to appropriate safeguards [S8]. Commentary on Nigeria’s Data Protection Act 2023 summarizes the same principle: the retention period should not be longer than necessary to achieve the lawful purposes [S9]. That source is a KPMG commentary, not the Act itself, so check the statute or ask your counsel before you quote it as law. Either way, the technical lesson is the same: partitioning by date is what makes a retention rule cheap to enforce.

- An archive is a copy that needs a guard. A detached PostgreSQL partition remains a regular table you can still access [S1]. So a forgotten detached table can quietly outlive its retention date. Give every archive an owner, an expiry date and the same protections as live data: encryption at rest, access rules and logging. GDPR also requires processing that ensures appropriate security, including protection against unauthorised processing and accidental loss [S8]. That rule applies to archives too.

- Give the retention job its own limited account. A job that can drop partitions can drop your data. Use a dedicated maintenance role that can only do that one job, and log what it drops. This is engineering judgment, not a quote from a manual.

- Back up and test a restore before you drop. Dropping a partition is quick, and that is the danger. PostgreSQL’s design lets you back up or process the detached table before dropping it [S1]. Use that window, and prove you can restore.

- Do not count on instant deletion. If a rule says data must be gone by a certain moment, remember MongoDB’s TTL does not guarantee immediate deletion [S6] [S7]. Also remember that deleted rows can still exist in backups and on replicas. In a previous post here, we saw that a replica applies changes a little later, so a deletion is not finished until every copy agrees. That is judgment, not a documented guarantee.

- Check what the partition key reveals. If you partition by customer or region, the partition names can leak facts about your business to anyone who can list tables. This one is judgment too.

- Watch the uniqueness gap. A weaker uniqueness guarantee [S1] is also a security matter when it protects payments or one-time tokens. Re-check any rule that used to rely on a single unique column.

Three questions before you partition:
1. Do most of my queries filter on one key, such as a date?
2. How long must each kind of data stay, and who decided?
3. Who can open the store room, and who proves the drawers are really gone?

Decide the retention rule before you choose the partition key.

Sources
S1: PostgreSQL manual, Table Partitioning. https://postgresql.org/docs/current/ddl-partitioning.html
S2: Microsoft Learn, Partitioned tables and indexes. https://learn.microsoft.com/sql/relational-databases/partitions/partitioned-tables-and-indexes
S3: Oracle Database 19c VLDB and Partitioning Guide, Partitioning Concepts. https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/partition-concepts.html
S4: Oracle Database 21c VLDB and Partitioning Guide, Recommendations for Choosing a Partitioning Strategy. https://docs.oracle.com/en/database/oracle/oracle-database/21/vldbg/recommendations-partition-strategy.html
S5: Oracle Database 11g Release 2 Licensing Information, Feature availability by edition (older page). https://docs.oracle.com/cd/E17116_01/doc/license.112/e10594/editions.htm
S6: MongoDB manual, TTL Indexes. https://mongodb.com/docs/manual/core/index-ttl/
S7: MongoDB manual, Automatic Removal for Time Series Collections. https://mongodb.com/docs/rapid/core/timeseries/timeseries-automatic-removal
S8: GDPR, Article 5, principles relating to processing of personal data (consolidated text). https://gdpr-text.com/read/article-5/
S9: KPMG Nigeria, The Nigeria Data Protection Act, 2023 (commentary). https://assets.kpmg.com/content/dam/kpmg/ng/pdf/the-nigeria-data-protection-act-2023.pdf

Imagine a company with one head office and ten branch shops. The head office keeps the official price list. Every branch holds a copy, delivered by courier. Customers walk into the branches, so the head office is never swamped, and everyone is happy.

Then, at 9:00, a price changes. The head office updates the list at once. The courier is still on the road. For the next few minutes, one branch quotes the old price to anyone who asks. Nothing is broken. The system is doing exactly what it was built to do. It is just not instant.

That is a read replica. It is a second database that receives every change from the main one, the primary, and answers read-only questions. Your reads spread across many machines, and the primary gets room to breathe. The price is that a replica is always a little behind, and sometimes a lot behind.

In a previous post here, we made copies of data inside one database, and saw that every copy needs someone to keep it fresh. A replica is the same idea at the level of a whole database, and the same bill arrives.

What the manuals admit about lag
PostgreSQL’s manual says it directly: the data on the standby takes some time to arrive from the primary, so there will be a measurable delay between primary and standby [S1]. Its standby is strictly read-only, and not even temporary tables may be written there [S1].

PostgreSQL gives you three clocks for measuring this. On the primary, the view pg_stat_replication shows write_lag, flush_lag and replay_lag for each standby [S2]. They measure the time between flushing recent WAL on the primary (WAL is the database’s diary of changes) and being told the standby has written it, flushed it, or fully applied it [S2]. Only replay_lag tells you when a reader on the standby could actually see the change [S2]. Two cautions from the same page: these are not predictions of how long catching up will take, and on a fully caught up, idle system the columns return to NULL after a short time, so your monitoring must decide whether NULL means zero or missing [S2]:

SELECT application_name, sync_state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

Microsoft says the same thing about SQL Server’s readable secondary replicas: there is latency, usually only a matter of seconds, but in unusual cases, such as network issues reducing throughput, it can become significant, and it rises when I/O bottlenecks occur or data movement is suspended [S5]. The guidance is blunt: use secondaries if your read workloads can tolerate some latency, and where latency is unacceptable, run them against the primary [S5].

Oracle can open a standby for queries while it keeps applying changes, a feature called real-time query, and it requires the Oracle Active Data Guard option licence [S8]. Apply lag is defined as the time between when a change became visible on the standby and when it was first visible on the primary, and you can watch it in V$DATAGUARD_STATS [S8].

MongoDB is the most direct of all. Its manual says all read preference modes except primary may return stale data, because secondaries replicate from the primary asynchronously, and you should make sure your application can tolerate that [S10]. Also, the default mode is primary, so replica reads in MongoDB are something you opt into [S10].

The nastiest effect of lag is not a stale dashboard. It is a user who changes something and is immediately shown the old value. A customer saves a new address, the next page loads from a replica that has not caught up, and the old address stares back. They save again. They contact support.
There are three honest ways out:
First, send that user’s reads to the primary for a short window after they write. It is simple and works on every database. In a previous post here, we looked at connection pools, and a separate pool for the primary and for the replicas is a natural place to make this choice.

Second, make the write wait. PostgreSQL’s synchronous_commit setting accepts remote_apply, which makes a commit wait until the standby has replayed it, so queries on that standby can see it [S3]. The manual warns this causes much larger commit delays than the other settings, because it waits for replay [S3]. It only has an effect when synchronous standbys are configured [S3].

Third, use the database’s own consistency tools. MongoDB’s manual notes that causal consistency guarantees with majority read and write concern hold across all members, and that read preference does not change that [S10]. Reads inside a transaction must use the primary [S10].

Set a limit on staleness
Most of the time you do not need a replica to be perfect. You need it not to be badly behind. Each database lets you say so.
In Oracle, a session can set a tolerance. A nonzero value STANDBY_MAX_DATA_DELAY lets queries run only if apply lag is at or below it, and otherwise returns error ORA-3172 [S8]. Setting it to 0 asks for exactly the primary’s answer, and it also needs synchronous transport, real-time apply, and a synchronized status [S8]. Your code catches ORA-3172 and retries on the primary:

ALTER SESSION SET STANDBY_MAX_DATA_DELAY = 2;

In MongoDB, maxStalenessSeconds tells the driver to stop using any secondary whose estimated lag is above the value [S11]. The minimum is 90 seconds, because the estimate is coarse and based on periodic checks, and by default there is no maximum at all [S11]:

mongodb://host1,host2,host3/?replicaSet=rs0&readPreference=secondaryPreferred&maxStalenessSeconds=120

In SQL Server, an application opts in by declaring a read-intent connection, and read-only routing through the availability group listener sends it to a readable secondary [S5]. Microsoft notes that read-intent requests can be load balanced across replicas, and that all queries on a secondary are mapped to snapshot isolation with locking hints ignored [S5]:

Server=ag-listener;Database=shop;ApplicationIntent=ReadOnly;

In PostgreSQL, the manual does not give a built-in per-query lag limit on the page I used. The usual answer is to watch replay_lag and route traffic away from a replica that falls behind your limit [S2].

How a replica can hurt the primary
This is the part that surprises people, because the replica is supposed to be the safe place to run heavy queries.
In PostgreSQL, the primary does not know what queries run on a standby, so it will not wait for them [S1]. When a standby query conflicts with incoming changes and the delay limit passes, the query is cancelled, which usually shows up as just a cancellation error [S1]. The fix many teams reach for is hot_standby_feedback, which stops VACUUM from removing recently dead rows so cleanup conflicts do not occur, but it delays cleanup on the primary and may cause undesirable table bloat [S1].

SQL Server has two versions of this. Because secondary reads use row versioning, the primary adds 14 bytes of overhead to changed rows when read access is enabled, which can cause page splits, and tempdb on the secondary needs space [S5]. And read workloads on a secondary can block DDL changes being applied through the log, and the cleanup of ghost records on the primary can be blocked by transactions on a secondary, which also prevents log truncation [S5].

The tradeoff
A read replica gives you more read capacity, a place to run reports away from the primary, and in most setups a warm copy for failover. It costs you in these ways:
- Answers are sometimes old, so every read needs an opinion about how old is acceptable.
- A user’s own writes may not be visible right away, so you need a routing rule.
- Waiting for the replica on every commit makes writes slower, and not waiting means you accept lag [S3].

A replica adds work for the primary and for your team. The primary may hold more history, as PostgreSQL’s feedback setting shows [S1], and someone has to watch the lag, the disk, and the connection to each replica.

A replica multiplies reads, not writes. Every replica applies the same stream of writes as the primary, so a write-heavy system does not get faster by adding more of them. That last point is engineering judgment, not a quote from a manual.

My rule is to try the cheapest fixes first: an index from a previous post here, a query plan check from a previous post here, and a cache from a previous post here. Add a replica when reads are the real bottleneck and some of them can honestly be a few seconds old.

The security angle
A replica is not a lesser copy. It holds the same data as the primary, so anything that protects the primary has to protect it too. Here is what I check.

Treat the replica as production data. PostgreSQL’s manual stresses that only trusted users should be able to read the WAL stream, because it is easy to extract privileged information from it [S4]. The same logic covers the replica itself: encryption at rest, network rules, audit logging, and patching should match the primary. That is judgment, but it follows from the fact that the replica holds the same rows.

Give the replication link its own account, with only that right. PostgreSQL’s manual says the standby must authenticate as an account with the REPLICATION privilege, recommends a dedicated account with REPLICATION and LOGIN, and notes that this privilege does not let the user modify data on the primary [S4]. Access is controlled by a pg_hba.conf record that uses the special word replication in the database field, and the manual’s example limits it to one address with scram-sha-256 [S4]:

host replication foo 192.168.1.100/32 scram-sha-256

Authenticate and encrypt the link between nodes. Each database has its own way:
SQL Server configures authentication and encryption on the database mirroring endpoint used by availability groups [S6]. Windows Authentication is recommended when every instance runs under a domain account, and certificates are required when a built-in or non-domain account is used [S6]. An older Microsoft page, for SQL Server 2012, says encryption is required by default, recommends requiring it unless you can guarantee the network is secure, and recommends AES over RC4, which it calls deprecated [S7]. That page is old, so check the current default on your version.

Oracle sends redo over Oracle Net sessions [S9]. SSL can be used for redo transport authentication when a set of conditions is met, including Oracle Internet Directory, wallets, and certificates. Otherwise, each database uses a remote login password file, and the password of the authenticating user, SYS by default, must match at both ends [S9]. That page does not state that redo is encrypted in transit, so confirm network encryption separately.

MongoDB members can authenticate each other with keyfiles or X.509 certificates, and enabling internal authentication also enables client authorization [S12]. A keyfile must be 6 to 1024 characters of base64 [S12].

For PostgreSQL, the streaming replication page I read does not mention encrypting the connection, and its sample connection string even contains a plaintext password [S4]. Look up the TLS options for replication connections in the PostgreSQL documentation for your version before you decide.

Never let a lagging replica make an authorization decision. If you revoke a user’s access, disable an account, or invalidate a session, the change reaches the replica a little later. For that window, a replica can still show the user as active. So permission checks, token and session revocation, balances, stock counts, and anything where being wrong has a security or money cost should read from the primary. SQL Server’s guidance to use the primary where latency is unacceptable [S5] and MongoDB’s rule that transactions read from the primary [S10] point the same way. The specific revocation example is my judgment, derived from the documented lag.

Remember who reads from the replica. Reporting tools and analysts often get replica access because it feels safe. It is read-only, but reading is exactly how data leaks. Give those accounts the least access they need, and do not hand out the replica’s admin rights.

Deletion is not finished until the replicas agree. If you delete a customer’s record on the primary, a lagging replica still holds it for a while, and so do your backups. For privacy and retention commitments, make sure your process counts replicas and backups, not only the primary. This is judgment, not a quote.

A stuck replica can take the primary down. PostgreSQL replication slots keep WAL until every standby has received it, and the manual warns they can retain so many segments that they fill the space allocated for pg_wal, with max_slot_wal_keep_size as the limiting setting [S4]. A forgotten or broken replica is therefore an availability risk, not just a monitoring gap.

Three questions before you add one
1. Which reads can honestly be a few seconds old, and which cannot?
2. What does a user see right after their own write?
3. Who watches the lag, and what do we do when it passes our limit?
Write the answers down, and give every read a freshness label before you choose where it runs.

Sources
S1: PostgreSQL manual, Hot Standby. https://postgresql.org/docs/current/hot-standby.html
S2: PostgreSQL 17 manual, Cumulative Statistics System (pg_stat_replication). https://www.postgresql.org/docs/17/monitoring-stats.html
S3: PostgreSQL manual, Write Ahead Log settings (synchronous_commit). https://postgresql.org/docs/current/runtime-config-wal.html
S4: PostgreSQL manual, Log-Shipping Standby Servers (streaming replication). https://postgresql.org/docs/current/warm-standby.html
S5: Microsoft Learn, Readable secondary replicas in Always On availability groups. https://learn.microsoft.com/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups
S6: Microsoft Learn, The database mirroring endpoint. https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/the-database-mirroring-endpoint-sql-server?view=sql-server-2017
S7: Microsoft Learn (SQL Server 2012 archive), Transport Security for Database Mirroring and AlwaysOn Availability Groups. https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms186360(v=sql.110)
S8: Oracle Database 19c Data Guard Concepts and Administration, Managing Physical and Snapshot Standby Databases. https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/managing-oracle-data-guard-physical-standby-databases.html
S9: Oracle Database 21c Data Guard Concepts and Administration, Redo Transport Services. https://docs.oracle.com/en/database/oracle/oracle-database/21/sbydb/oracle-data-guard-redo-transport-services.html
S10: MongoDB manual, Read Preference. https://www.mongodb.com/docs/manual/core/read-preference/
S11: MongoDB manual, Read Preference maxStalenessSeconds. https://www.mongodb.com/docs/manual/core/read-preference-staleness/
S12: MongoDB manual, Internal/Membership Authentication. https://www.mongodb.com/docs/manual/core/security-internal-authentication/

Then business grows, and every day a hundred people ask her the same question: “How much did we sell today?” Each time, she must run her finger down the whole ledger and add. At first it takes a minute. Later it takes an hour—customers queue. The ledger is not wrong. It is simply not built for being asked the same question a hundred times.

So she does what any sensible shopkeeper would do. She hangs a board by the door: “Today’s total: 84,300.” Now the hundred people read the board and leave in two seconds. Her day is saved.

But the board has a price. Every sale now needs two writes, one in the ledger and one on the board. If she is busy and forgets, the board lies, and every customer who trusts it is misled. And the board only answers one question. Ask for sales by item, and she is back to the ledger.

That is data modeling for reads versus writes. The ledger and the board are the same data in two shapes, and every database design is a decision about how many boards you can afford to keep up to date.

In a previous post here we saw one kind of board: the index, a cabinet of cards that points at the data. Today we go one level up, to whole copies of data shaped for the people reading them.

Book one: keep the truth in one place
Database designers call this normalizing. Each fact is stored exactly once. A customer’s address lives in one row in one table, and every order points to that customer. When she moves house, you change one row, and every order now shows the new address. Nothing can disagree with itself, because there is only one copy to disagree with. This is the right default for anything that gets written to. It makes writes simple and keeps your data honest.

The price is paid at read time. To show an order page, you might need the order, the customer, the items, the product names, and the shipping address, which means joining five tables. For one page, that is fine. For ten thousand people looking at dashboards every minute, it adds up. MongoDB’s documentation makes the same point from the other side: embedded data models are often considered denormalized because frequently accessed data is duplicated, and that duplication is the trade you accept for reading it in one go [S5].

Book two: a copy shaped for the readers
The first tool most relational databases give you is the materialized view. A normal view is just a saved query, run again every time. A materialized view stores the answer. PostgreSQL’s manual describes it as persisting the results in a table-like form, so that reading it is like reading from a table [S1].

The manual is refreshingly honest about the cost: while access to the data stored in a materialized view is often much faster than accessing the underlying tables directly, the data is not always current, yet sometimes current data is not needed [S1]. Its own example is a sales summary for a dashboard that graphs historical sales, refreshed nightly [S1]. The dashboard does not care about the last five minutes. It cares about being fast.

There is even a striking number in the manual. In a separate example that loads a word list from a file into a materialized view with indexes, a spell-check lookup that took about 188ms against the original source took about 0.12ms against the materialized view [S1]. That example is about a foreign data source, not an ordinary table, so do not expect those exact numbers from your own tables. The lesson is the size of the gap when you stop recomputing the same answer.

Here is the shopkeeper’s board, in all three relational databases. We will use the same dashboard question: sales per seller per day.

PostgreSQL keeps the copy until you refresh it:

CREATE MATERIALIZED VIEW daily_sales AS
SELECT seller_id, order_date,
SUM(total) AS sales_total, COUNT(*) AS order_count
FROM orders
WHERE order_date < CURRENT_DATE
GROUP BY seller_id, order_date;

CREATE UNIQUE INDEX daily_sales_key ON daily_sales (seller_id, order_date);

-run from a nightly job
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

A few details from the manual are worth knowing. A refresh completely replaces the contents of the view [S2]. Without CONCURRENTLY, a refresh that touches many rows may block people reading the view, though it tends to use fewer resources and finish faster [S2]. With CONCURRENTLY, readers are not locked out, but it is only allowed when the view has at least one UNIQUE index made of plain column names that covers all rows, which is why the example creates one [S2]. And only one refresh at a time may run against a view [S2].

SQL Server calls its version an indexed view. The result is stored by putting a unique clustered index on the view, and the first index on a view must be that unique clustered one [S3]. The view must be created WITH SCHEMABINDING, and when it groups, it must include COUNT_BIG(*) [S3]:

CREATE VIEW dbo.daily_sales
WITH SCHEMABINDING
AS
SELECT seller_id, order_date,
SUM(total) AS sales_total,
COUNT_BIG(*) AS order_count
FROM dbo.orders
GROUP BY seller_id, order_date;
GO

CREATE UNIQUE CLUSTERED INDEX ix_daily_sales
ON dbo.daily_sales (seller_id, order_date);

Microsoft lists more rules for indexed views, so read the full list before you build one [S3]. There is a bonus: once it exists, the optimizer can use it to speed up matching queries even when the query does not mention the view [S3]. The bill is on the write side. Microsoft warns that when a table referenced by many indexed views, or by a few complex ones, takes an INSERT, UPDATE or DELETE, those views must be updated too, and DML performance can degrade significantly [S3]. This is the shopkeeper updating a very busy board on every sale.

Oracle gives you a choice of how the board is kept fresh. A materialized view stores precomputed results of expensive joins and aggregations [S4]. It can refresh ON DEMAND (when someone runs a refresh procedure, the default) or ON COMMIT, which refreshes automatically when a transaction that changed a base table commits [S4]. Oracle notes that ON COMMIT makes the commit take slightly longer, so it may not suit heavily concurrent workloads [S4]. A fast refresh, which applies only the changes, normally needs a materialized view log on the base table, and COUNT(*) must be present in the view for all types of fast refresh [S4]. The shape below follows Oracle’s own example [S4]:

CREATE MATERIALIZED VIEW LOG ON orders
WITH SEQUENCE, ROWID (seller_id, order_date, total)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW daily_sales
REFRESH FAST ON COMMIT AS
SELECT seller_id, order_date,
COUNT(*) AS order_count,
SUM(total) AS sales_total,
COUNT(total) AS total_count
FROM orders
GROUP BY seller_id, order_date;

Oracle also has a warning for the careless: if a view fails during an ON COMMIT refresh, it stops refreshing automatically until you invoke a refresh yourself after fixing the error [S4]. That is a board that quietly stops being updated, which is the nightmare version of the shopkeeper forgetting.

The simplest board of all: a counter
Not every board needs a fancy feature. Sometimes the board is one number. Keep an order_count column on the customer row, and change it in the same transaction that inserts the order. It is fast to read and cheap to keep, as long as every code path that creates or cancels an order remembers to update it. The risk is exactly the shopkeeper’s: one forgotten path and the number drifts. If you do this, put the update next to the insert, in one transaction, and add a nightly job that recounts and compares.

MongoDB: embed or reference
MongoDB does not have joins in the same everyday way, so the same decision shows up as a modeling choice. Embedding puts related data inside the parent document. According to MongoDB’s documentation, it gives better read performance, the ability to retrieve related data in a single operation, and the ability to update related data in a single atomic write [S5]. It suits one-to-many relationships where the many are viewed in the context of the one [S5].

The limits are firm. Documents in MongoDB must be smaller than 16 mebibytes [S5]. And the documentation names a classic mistake, the unbounded array: if you do not limit how many elements an array can hold, your documents may exceed the 16 MB limit, and an unbounded array can strain application resources and decrease index performance [S6]. A book that embeds every review it will ever receive is the textbook case [S6].

The documented fixes are to use subsetting and referencing to bound arrays [S6]. Referencing means each review lives in its own collection, and the book only holds IDs. It solves the size problem but adds latency, because you must query the reviews collection to read them [S6]. The subset pattern splits the difference: keep a few recent reviews inside the book for instant display, and the rest in their own collection [S6].

// books: only the 3 newest reviews live inside the document
{
title: “Example Book”,
recent_reviews: [
{ reviewer: “Ada”, rating: 5, text: “…” },
{ reviewer: “Tunde”, rating: 4, text: “…” },
{ reviewer: “Chidi”, rating: 4, text: “…” }
]
}

// reviews: one document per review, all of them
db.reviews.find({ book_id: bookId }).sort({ created_at: -1 }).limit(20)
Do not split too far

Once you taste fast reads, the next temptation is to split the whole system in two: one model for writing and a completely different model for reading. This pattern has a name, CQRS, short for Command Query Responsibility Segregation. Microsoft’s Azure Architecture Center describes it as separating read and write operations into different data models [S7].

The benefits are real. Reads and writes can scale independently, and the read side can use a schema optimized for queries, avoiding complex joins [S7]. But there is a cost the article states plainly: when the read and write databases are separated, the read data might not show the most recent changes immediately, which means stale data, and keeping the two in sync is hard [S7]. Because you usually cannot put a message broker and a database in one distributed transaction, the article recommends the Transactional Outbox pattern and idempotent consumers to mitigate this [S7]. That is the outbox from a previous post here, and the idempotency idea from a previous post here. Finally, Microsoft says CQRS might not be suitable when the domain or business rules are simple, or when simple CRUD-style access is enough [S7].

My own rule: try the cheapest board first. An index, then a counter, then a materialized view, then a separate read model. Each step is faster to read and more expensive to keep honest.

Three questions before you copy anything
How often is it read compared with written? A copy pays off when reads far outnumber writes. If the data changes on every request, the board will cost more than the ledger saves.
How stale can it be? Nightly, every minute, or instantly? The answer decides your refresh style. If a stale number could cause real harm, such as a balance, keep reading from the truth.
How big can the list grow? If the answer is “no limit”, it should not be embedded in one document or one row.

Then write down who keeps each copy fresh. A board without an owner is a board that lies.

The common thread
Databases are not slow because of bad luck. They are slow because the same question was asked a million times and answered from scratch each time. The fix is not magic. It is a second book, kept honest, shaped for the crowd. In a previous post here, we saw what happens when a shortcut like a cache hides a slow database until the day it breaks. A precomputed copy is the same trade done on purpose: know what it costs, know how stale it can be, and know who keeps it fresh.

A quick audit for your own system: list every copy of data you keep, whether it is a cache, a counter, a summary table, or a view. For each one, write down what refreshes it, how stale it can get, and what happens when the refresh quietly fails.

Sources
S1: PostgreSQL manual, Materialized Views. https://www.postgresql.org/docs/current/rules-materializedviews.html
S2: PostgreSQL manual, REFRESH MATERIALIZED VIEW. https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html
S3: Microsoft Learn, Create Indexed Views. https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver17
S4: Oracle Database Data Warehousing Guide, Basic Materialized Views. https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html
S5: MongoDB manual, Embedded Data in Your MongoDB Schema. https://www.mongodb.com/docs/manual/data-modeling/embedding/
S6: MongoDB manual, Avoid Unbounded Arrays. https://www.mongodb.com/docs/manual/data-modeling/design-antipatterns/unbounded-arrays/
S7: Microsoft Azure Architecture Center, CQRS Pattern. https://learn.microsoft.com/en-us/azure/architecture/patterns/cqrs

Databases work in the same way.

When you send a query, the database does not just run it. First it plans: which table to read first, whether to use an index, how to join things together. That plan is a route, and the database picks it using a set of guesses about how much data is where. Then it follows the route. If the guesses were good, the query is quick. If they were bad, you get the database equivalent of a taxi taking the long way round.

The good news is that every serious database will show you the route, and most will also show you how the trip actually went. Almost nobody looks. They add an index by instinct, or throw more hardware at the problem, or blame the network. Today is about reading the plan, which is the single most useful skill in database performance work.

In a previous post here, we looked at indexes, the cabinet of cards that lets a database go straight to what it needs. A query plan is how you prove the database is actually using that cabinet.

1. Ask for the plan
Each database has its own way of asking. Here is the same question from our previous post here (this customer’s open orders, newest 20) in all four.

PostgreSQL uses the EXPLAIN command [S1][S2]:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42 AND status = ‘open’
ORDER BY created_at DESC
LIMIT 20;

SQL Server shows plans in SQL Server Management Studio. Choose Include Actual Execution Plan on the Query menu and run the query [S3]. If you prefer code, SET STATISTICS XML ON returns the plan information for each statement after it runs [S3]. You need the SHOWPLAN permission on every database the query touches [S3].
SET STATISTICS IO ON;
SELECT TOP (20) id, created_at, total
FROM orders
WHERE customer_id = 42 AND status = ‘open’
ORDER BY created_at DESC;

Oracle gives you two routes. EXPLAIN PLAN followed by DBMS_XPLAN.DISPLAY shows the estimated plan [S7]. For the plan that really ran, add a GATHER_PLAN_STATISTICS hint to the query and then call DISPLAY_CURSOR [S7]
SELECT /*+ GATHER_PLAN_STATISTICS */ id, created_at, total
FROM orders
WHERE customer_id = 42 AND status = ‘open’
ORDER BY created_at DESC
FETCH FIRST 20 ROWS ONLY;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => ‘ALLSTATS LAST’));

MongoDB has the explain method, and the executionStats mode runs the query and reports what happened [S8]
db.orders.find({ customer_id: 42, status: “open” })
 .sort({ created_at: -1 })
 .limit(20)
 .explain(“executionStats”)

A safety rule that saves careers. In PostgreSQL, plain EXPLAIN only plans. Adding ANALYZE makes the statement actually execute, and the manual is blunt about it: any side effects will happen as usual [S1][S2]. That is fine for a SELECT. For an INSERT, UPDATE, or DELETE, the manual’s advice is to wrap it so the change is undone [S2]
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = ‘closed’ WHERE customer_id = 42;
ROLLBACK;

Remember, even a rolled back statement still did the work, so be careful running it against a busy production database.

2. Spot the scan type
Every plan, in every database, has a line that says how the table is read. This is the line that tells you whether your index is being used.

PostgreSQL’s manual uses an example table to show three ways to read it. A sequential scan reads the whole table, and when a query has no WHERE clause, it is the only sensible choice [S1]. A bitmap scan is used for moderately selective conditions [S1]. An index scan is used for very selective lookups, such as fetching a single row [S1]. The manual shows the difference in the plan lines. These two come from two different queries on the same table, so do not read them as a before and after:

Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)

Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
Index Cond: (unique1 = 42)

SQL Server uses its own names. A Table Scan retrieves all rows from a table without a clustered index, and a Clustered Index Scan reads the whole clustered index [S5]. An Index Seek uses the seeking ability of an index to process only the rows that satisfy its seek predicate [S5]. The operator to watch for is the Key Lookup: when the index found the rows but did not hold every column the query needs, SQL Server goes back to the clustered index to fetch the rest, and Microsoft says a Key Lookup with a high number of executions relative to the table size might benefit from a covering index [S5]. That is the covering index idea from a previous post here.

MongoDB reduces it to two words. COLLSCAN means the server scanned the entire collection document by document, which Mongo’s documentation says is generally expensive and can result in slow queries. IXSCAN means an index was used [S8].

Here is the best example I know of, taken from MongoDB’s own documentation [S8]. A query for movies released between 2000 and 2005 returns 3,809 documents:
With no index, MongoDB examined all 21,349 documents to find those 3,809.
After adding an index on year, it examined only 3,809 index keys and 3,809 documents.
For a second query that also filtered on the rating (235 matches), a compound index with the rating first meant it examined exactly 235 keys and 235 documents.

When the number of things examined matches the number returned, the database did no wasted work. That ratio is worth memorising.

3. Compare the guess with reality
Back to the map app. The promised time and the real time are the two numbers that matter, and so it is with plans. A plan shows what the database expected, and the execution shows what it found.

PostgreSQL’s manual says the thing that’s usually most important to look for is whether the estimated row counts are reasonably close to reality [S1]. In the output, the estimate appears as rows inside the first set of brackets, and the real number appears as rows inside the actual section [S1]. There is a third number worth understanding, called loops: how many times that step ran. The timings and row counts shown are averages per run, so you multiply by loops to get the true total [S1].

Oracle makes the comparison explicit. With the statistics hint, the Rows column is replaced by E-Rows (estimated) and A-Rows (actual) side by side [S7]. SQL Server’s actual plan carries the real row counts, and its Analyze Actual Execution Plan feature exists to make it easier to find scenarios of inaccurate cardinality estimation (that is the word for a wrong row count guess) and to suggest mitigations [S4]. MongoDB gives you the pair directly: nReturned, and the number of documents and keys examined [S8].

What do you do when the guess is far off?
The usual cause is stale information. A database makes its estimates from statistics it collected about your data. PostgreSQL’s ANALYZE command refreshes them, and the manual notes that its estimates come from a random sample and can change a little after each ANALYZE [S1]. If a table has changed a lot since statistics were last gathered, the database is planning a route using an old map. Refresh the map first, then look at the plan again.

4. Count the work, not just the seconds
Time is a noisy measure. The same query can be fast the second time simply because the data is now in memory. So alongside time, look at how much work was done.

PostgreSQL’s BUFFERS option reports how many blocks were found already in cache (a hit means a read was avoided because the block was already in memory), and how many had to be read in [S2]. In current versions, buffer information is included automatically when you use ANALYZE [S2]. SQL Server’s SET STATISTICS IO reports scan count, logical reads (pages read from the data cache), and physical reads (pages read from disk) [S6]. Oracle’s plan with runtime statistics also includes Starts, A-Time, and Buffers columns [S7]. And MongoDB’s equivalent is the examined versus returned pair from step two [S8].

A rule of thumb that works on all four: if the database touches thousands of rows or pages to hand back twenty, something is wrong, and it is usually a missing index, the wrong column order, or a function wrapped around a column, all topics from a previous post here.

5. Know the traps
Cost is not time. PostgreSQL’s cost numbers are in arbitrary units, with the cost of reading one page sequentially as the baseline of 1.0 [S1]. They are for comparing plans, not for predicting seconds. The same manual says the actual time figure, in milliseconds, is a different thing and not comparable to cost units [S1].

Small tables lie. The manual warns that a plan that looks expensive on a tiny test table may not reflect production behaviour [S1]. A table with a hundred rows is often faster to scan than to index, so the database will happily choose the scan. Test on realistic amounts of data.

LIMIT can make numbers look smaller than estimates. The manual explains that when a query has a LIMIT, the actual row counts and times for some steps can look lower than the estimates, and that this is a display artifact, not an estimation error [S1].

A plan shows one query, not the whole page. If a page runs 10,000 small queries, each plan will look fine, and the page will still crawl. That is the N+1 problem from a previous post here. Plans find slow queries. Logs and traces find slow pages.

A routine you can repeat
1. Pick the slow query and find the exact version your application sends, with real values.
2. Get the plan with runtime statistics, using the safety rule if it changes data.
3. Check the scan type, then compare estimated against actual rows.
4. Check the work done, meaning blocks, reads, or documents examined versus returned.
5. Change one thing, such as an index or refreshed statistics, and read the plan again.

Step five is the one people skip. A plan before and after is the evidence that your change did what you think. Without it, you are guessing, and with the right index in the wrong order, a guess can feel like a fix.

The common thread
Databases are not mysterious. They make a plan, they record how it went, and they will show you both if you ask. The engineers who look fast at a problem are rarely smarter than the rest. They simply read the plan before they start changing things.

Sources
S1: PostgreSQL manual, Using EXPLAIN. https://www.postgresql.org/docs/current/using-explain.html
S2: PostgreSQL manual, EXPLAIN command reference. https://www.postgresql.org/docs/current/sql-explain.html
S3: Microsoft Learn, Display an Actual Execution Plan. https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver17
S4: Microsoft Learn, Analyze an actual execution plan. https://learn.microsoft.com/en-us/sql/relational-databases/performance/analyze-an-actual-execution-plan?view=sql-server-ver17
S5: Microsoft Learn, Logical and Physical Showplan Operator Reference. https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-ver17
S6: Microsoft Learn, SET STATISTICS IO. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql?view=sql-server-ver17
S7: ORACLE-BASE, DBMS_XPLAN: Display Execution Plans. https://oracle-base.com/articles/9i/dbms_xplan
S8: MongoDB manual, Interpret Explain Plan Results. https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan/

Picture a school registry holding 2 million exam scripts, filed in the order they arrived. A parent asks for one student’s script. Without any help, a clerk must open box after box until she finds it. Some days she is lucky. Most days she is not.

Now add a small cabinet of index cards, sorted by student name. The clerk goes to the card, reads which box and which position, and walks straight to it. The scripts did not move. The registry just gained a second, much smaller list that points into the first.

That cabinet is a database index. The PostgreSQL manual describes the same difference: without an index, the system has to scan the whole table row by row, while with one it might only walk a few levels deep into a search tree [S1]. The manual compares it to the index at the back of a non-fiction book, and notes that just as the author must anticipate what readers will look up, the database programmer must foresee which indexes will be useful [S1].

Indexes are the first tool most people reach for when a database is slow, and they are also the tool most often misused. Today is about using them well, on any of the four databases most teams run: PostgreSQL, SQL Server, Oracle and MongoDB. In a previous post here, we saw why a page that is instant with 10 records crawls with 10,000: the hidden query loop called N+1. An index makes each of those queries faster, but it cannot turn 10,000 queries into one, so fix the loop first and the lookup second.

1. Every index has a price
Back at the registry, imagine a new script arrives. Before, the clerk simply put it in a box. Now she must also write a card and slot it into the cabinet in the right place. If there are four cabinets (by name, by subject, by date, by school), she writes four cards.

Databases work the same way. PostgreSQL’s manual says that after an index is created, the system has to keep it synchronized with the table, which adds overhead to data manipulation operations, and that indexes that are seldom or never used should be removed [S1]. SQL Server’s design guide says a large number of indexes on a table affects the performance of INSERT, UPDATE, DELETE and MERGE statements, and calls out a common design mistake: creating many indexes speculatively to give the optimizer choices, which slows data modifications and can cause concurrency problems [S5]. Oracle’s documentation says the same: many indexes on a table degrade DML performance because the database must also update them [S7]. MongoDB’s documentation puts it in one line: indexes have a performance cost, but are worth it for frequent queries on large data sets, and indexes that are no longer used should be dropped [S10].

So the real question is never “should I add an index?” but “which query is slow enough, and run often enough, to be worth a card for every write?”

2. Which kind of index?
For most work, the answer is the default. Oracle’s documentation says B-trees, short for balanced trees, are the most common type of database index, and are excellent for highly selective indexes, where few rows match each entry, and for primary keys [S7]. PostgreSQL, SQL Server and MongoDB all use tree based indexes for ordinary lookups.

Two specialised cases are worth knowing:
Oracle has bitmap indexes, designed mainly for data warehouses and ad hoc queries across many columns, and best for columns with few distinct values on tables that are read only or rarely modified. Oracle says that because one key points to many rows, an update locks all of those rows, so bitmap indexes are not appropriate for many OLTP applications [S7]. If your system takes lots of small writes from many users, stay with B-tree.

SQL Server distinguishes clustered from nonclustered indexes. A clustered index stores all rows of the table sorted by the index key, and there can be only one per table. A nonclustered index holds a subset of the columns, sorted differently, plus a pointer back to the row [S5]. The clustered key is part of every nonclustered index on the table, so a narrow clustered key reduces the storage and memory cost of all of them [S5].

3. Column order is the whole game
Most real queries filter on more than one column. Take a common one: show this customer’s open orders, newest first, 20 at a time.

A composite index (an index on several columns) is like cards sorted by customer first, then by status within each customer, then by date within each status. The order of columns decides what the index can help with.

For PostgreSQL, the manual states the rule: equality constraints on leading columns, plus any inequality constraint on the first column without an equality constraint, are what limit the portion of the index scanned [S2]. Constraints on columns further right are checked inside the index, which saves visits to the table, but they do not necessarily reduce how much of the index must be read [S2]. MongoDB gives a name to its version of this rule, ESRb(Equality fields first), then Sort, then Range. The documentation says equality fields keep the remaining index entries in sorted order, and putting sort fields before range fields avoids in-memory sorts [S9]. It also notes one exception: if your range filter is very selective, you may place it before the sort fields [S9].

For our query, the same idea is written in each database like this.
PostgreSQL, SQL Server and Oracle:
sql
CREATE INDEX ix_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

MongoDB:
javascript
db.orders.createIndex({ customer_id: 1, status: 1, created_at: -1 })

Customer and status are the equality conditions, so they come first. The creation date is the sort, so it comes next, and the first 20 rows are already in order. This is also what makes the keyset pagination from a previous post here fast. Keyset pagination only works well when an index matches the sort columns.

Resist the urge to add more columns. PostgreSQL’s manual says multicolumn indexes should be used sparingly, that a single column index is usually enough, and that indexes with more than three columns are unlikely to be helpful unless the usage of the table is extremely stylized [S2]. SQL Server’s guide says to keep indexes narrow, with as few columns as possible, and to avoid over-indexing heavily updated tables [S5].

4. Hiding the column hides the index
Here is a trap that catches experienced engineers. Suppose users log in by email, and you compare in lower case so that “Ada@Example.com” matches “ada@example.com”:
sql
SELECT * FROM users WHERE lower(email) = ‘ada@example.com’;

A plain index on email is sorted by the raw value, not by the lower case value, so the database usually cannot use it for this condition. PostgreSQL’s manual describes exactly this case: the query can use an index if one has been defined on the result of the lower function [S4]. The same idea exists in Oracle as a function-based index, which stores the value of a function or expression and is efficient for statements that contain that function in their WHERE clause [S7].
sql
- PostgreSQL
CREATE INDEX ix_users_lower_email ON users (lower(email));

- Oracle
CREATE INDEX ix_users_lower_email ON users (LOWER(email));

There is a cost. PostgreSQL’s manual says index expressions are relatively expensive to maintain because the expression must be computed for each row insertion and non-HOT update, so they make sense when retrieval speed matters more than insertion and update speed [S4]. Oracle notes that the database must still evaluate the function on every INSERT and UPDATE [S7]. For SQL Server and MongoDB, check how your version handles an indexed expression or computed value before relying on the same pattern.

Covering indexes. Sometimes the index can answer the whole question without visiting the table at all, because it already holds every column the query needs. SQL Server calls this a covering index: a nonclustered index that satisfies all data access by a query directly, which reduces disk reads [S5]. You can add extra columns to the leaf level with an INCLUDE clause, so they do not become part of the sort key [S5]. PostgreSQL has the same clause, and says an index-only scan can return the contents of those columns without visiting the table [S3]. MongoDB’s documentation says an index supports a query when it contains all the fields the query scans [S10].
sql
- SQL Server and PostgreSQL
CREATE INDEX ix_orders_customer_status
ON orders (customer_id, status)
INCLUDE (total);

Both vendors warn about overdoing it. PostgreSQL says to be conservative about adding non-key columns, especially wide ones [S3]. SQL Server says avoid creating a covering index with too many columns, because that diminishes its benefit while inflating storage, I/O and memory use [S5].

5. Adding an index to a live system
This is where many teams get hurt. On a busy production database, the question is not only “is the index good?” but “what happens to my users while it builds?”

PostgreSQL. A standard CREATE INDEX locks out writes, though not reads, until it finishes [S3]. The manual says that in production environments this is often unacceptable [S1]. The CONCURRENTLY option builds the index without taking locks that prevent concurrent inserts, updates or deletes [S3].
The trade-offs are real: it must perform two scans of the table, waits for existing transactions, takes significantly longer, and cannot run inside a transaction block [S3]. If it fails, for example from a deadlock or a uniqueness violation, it leaves behind an invalid index that is ignored for queries but still consumes update overhead, so you should drop it and try again [S3]. Only one concurrent build can run on a table at a time [S3].
sql
CREATE INDEX CONCURRENTLY ix_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

SQL Server. With ONLINE = ON, long-term table locks are not held for the duration of the index operation, and during the main phase only an intent shared lock is held on the source table so queries and updates can continue [S6]. Brief locks are still taken at the start and end [S6]. Online index operations are not available in every edition of SQL Server, so check your edition first [S6]. Newer versions also support RESUMABLE builds, which can be paused and restarted, and require ONLINE = ON [S6].
sql
CREATE INDEX ix_orders_customer_status_created
ON orders (customer_id, status, created_at DESC)
WITH (ONLINE = ON);

Oracle. The ONLINE keyword allows DML on the table during creation of the index [S8].
sql
CREATE INDEX ix_orders_customer_status_created
ON orders (customer_id, status, created_at DESC) ONLINE;

MongoDB. Index builds use an optimized process that holds an exclusive lock on the collection briefly at the beginning and end, while the rest of the build yields to interleaving reads and writes [S11]. There is no special flag. The documentation warns that building under heavy write load can reduce write performance and make the build longer, and recommends a maintenance window with reduced writes [S11].
javascript
db.orders.createIndex({ customer_id: 1, status: 1, created_at: -1 })

Test before you commit
Oracle can create an INVISIBLE index. It is maintained by DML but is not used by the optimizer unless you set a parameter to allow it, which lets you try an index before exposing it to every query [S8]. In general, try a new index on a copy of production data first, and check the query plan to confirm the database really uses it.

Clean up
PostgreSQL exposes per index statistics in its statistics views, which it describes as useful for determining which indexes are being used and how effective they are [S12]. SQL Server’s guide says to monitor index usage and remove unused indexes over time [S5]. MongoDB’s documentation says to drop indexes that are no longer used [S10]. Unused indexes are pure cost: they slow every write and give nothing back.

The common thread
An index is a trade. You pay a little on every write to save a lot on specific reads. That trade is excellent when the query is frequent, selective and well matched to the index, and wasteful when it is none of those.

A quick audit for your busiest table
List your table indexes and for each one, name the query that needs it. Check whether the column order matches that query’s equality, sort and range conditions. Look for functions wrapped around indexed columns in your queries. Then find the indexes nobody uses. The ones with no query to name are the first to go.

Sources
S1: PostgreSQL manual, Indexes: Introduction. https://www.postgresql.org/docs/current/indexes-intro.html
S2: PostgreSQL manual, Multicolumn Indexes. https://www.postgresql.org/docs/current/indexes-multicolumn.html
S3: PostgreSQL manual, CREATE INDEX (CONCURRENTLY and INCLUDE). https://www.postgresql.org/docs/current/sql-createindex.html
S4: PostgreSQL manual, Indexes on Expressions. https://www.postgresql.org/docs/current/indexes-expressional.html
S5: Microsoft Learn, SQL Server Index Architecture and Design Guide. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver17
S6: Microsoft Learn, CREATE INDEX (Transact-SQL). https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver17
S7: Oracle Database Concepts, Indexes and Index-Organized Tables. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/indexes-and-index-organized-tables.html
S8: Oracle Database SQL Language Reference, CREATE INDEX. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html
S9: MongoDB manual, The ESR (Equality, Sort, Range) Guideline. https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline/
S10: MongoDB manual, Indexing Strategies. https://www.mongodb.com/docs/manual/applications/indexes/
S11: MongoDB manual, Index Builds on Populated Collections. https://www.mongodb.com/docs/manual/core/index-creation/
S12: PostgreSQL manual, The Cumulative Statistics System. https://www.postgresql.org/docs/current/monitoring-stats.html

Imagine a city where every street lamp can phone a control room. One evening the power fails. When it returns, 100,000 lamps notice they are offline and try to call in at the exact same second.

The control room is not broken. The lamps are not broken either. The problem is that every device made the same sensible decision at the same moment. Most device fleets that fall over do so for this reason. It has little to do with any single device and everything to do with what happens when thousands of them behave identically.

This is the world of IoT: meters, sensors, vehicles, point-of-sale terminals, lamps, pumps and thermostats. The earlier days covered what happens inside your servers and databases. Today is about the part that sits outside them, a crowd of small devices on weak networks that you do not control. Four decisions matter most.

1. The language devices speak: MQTT
Most fleets talk through MQTT, a lightweight publish and subscribe protocol standardized by OASIS and ISO. Instead of every device calling your server directly, devices connect to a middle server called a broker. A device publishes a message to a named topic, and any system interested in that topic subscribes to it. The sender and receiver never need to know about each other, and the smallest MQTT packet is only 2 bytes, which suits small devices.

A simple topic layout helps you scale later. For example:
fleet/{region}/{device_id}/telemetry
fleet/{region}/{device_id}/status
fleet/{region}/{device_id}/commands

A backend service can then subscribe to fleet/+/+/telemetry to receive readings from every device (in MQTT, + stands for “any single level”), while a dashboard watches only one region.

How sure must delivery be? MQTT gives you three levels, called QoS. QoS 0 is at most once: send it and forget it, so a message can be lost. QoS 1 is at least once: the sender keeps the message until the receiver acknowledges it, so it always arrives but may arrive more than once. QoS 2 is exactly once: a four step handshake prevents duplicates, but it costs more traffic. One more rule to know: the level actually used is the lower of what the publisher sent and what the subscriber asked for.

A common choice for readings is QoS 1, because losing a measurement is usually worse than receiving it twice. But that choice has a consequence. Duplicates will arrive, so your backend must handle them. This is the same lesson as in a previous post here, where queues deliver a job at least once and the same job can run twice, and in a previous post here, where an idempotency key lets a server recognise a repeated request and return the same answer instead of doing the work again. Give each reading a unique ID and ignore ones you have already stored.

Knowing which devices are online. Two MQTT features work together here. When a device connects, it can register a Last Will and Testament, which is a message the broker publishes for it if the device vanishes without saying goodbye. After connecting, the device publishes a retained “online” message to the same topic, and a retained message is kept by the broker so anyone who subscribes later still sees the latest state. If the device drops, the broker publishes the “offline” will, which replaces the retained message. When it returns, its new “online” message replaces the will. Anyone can check one topic and know the device’s status. HiveMQ describes this pattern in managing device state in MQTT. The basic version only covers ungraceful disconnects, so a graceful goodbye needs its own message.

Scaling the consumers. With MQTT 5, you can use shared subscriptions. Normally every subscriber gets every message. With a shared subscription such as $share/ingest/fleet/+/+/telemetry, a group of workers named “ingest” splits the traffic, and each message goes to only one worker. If a worker disconnects before acknowledging a QoS 1 message, the broker resends it to another worker in the group. There is no ordering guarantee between workers, so do not rely on order across devices.

2. The reconnect storm
Back to the street lamps. After an outage, every device thinks the same thing: “I am offline, retry.” If every device retries after exactly 5 seconds, then 10, then 20, the broker is hit by the entire fleet at once, again and again.

The textbook fix has two parts. First, exponential backoff: wait longer after each failure. Second, jitter: add a random element so devices do not move in lockstep. Backoff alone is not enough, because devices that started together still stay bunched. The AWS Architecture Blog simulated this and found that the version without jitter was the clear loser, doing more work and taking longer, and that full jitter cut the number of calls by more than half with 100 contending clients.

The “full jitter” formula is short and works in any language:

delay = random_between(0, min(cap, base * 2 ^ attempt))

Pick a base such as 1 second and a cap such as a few minutes. Each device draws its own random delay, so the crowd spreads out into a steady trickle. Also make devices wait a random moment after power returns before the first attempt, because the first attempt is where the biggest spike happens.

The server also needs a defence for when devices misbehave anyway. That is the job of rate limiting, covered in a previous post here, where a bus conductor says “full” so the bus does not break. Keep alive settings matter too. Platforms have bounds: AWS IoT Core, for example, treats a requested keep alive of 30 to 1,200 seconds as the effective range. Shorter intervals detect dead devices faster but create more traffic across a large fleet.

3. Ingestion: do not wire the broker straight to the database
Readings arrive in bursts. If the broker pushes every message directly into the database, a burst turns into a pile of simultaneous writes, and the database falls over, taking your history with it.

A sturdier path puts a queue between them: devices publish to the broker, workers pull from a shared subscription or a queue and write in batches, and the data lands in a store built for it. A burst then waits in line instead of crashing the writers, which is the idea behind a previous post here. Batching also happens at the device: sending ten readings in one message is usually cheaper than ten messages. Mind the limits of your platform. AWS IoT Core url, for example, rejects a publish larger than 128 KB, and that limit cannot be raised.

How big can a broker get? Vendors publish their own tests, and they are worth reading with care. EMQX reported 100 million MQTT connections on a 23 node cluster, with a steady background of about 90,000 messages per second and peaks above 1 million. The same write-up says it used custom forks of tools to run the test, connected load generators directly to some nodes with no load balancer, and ran on large bare metal instances. It is a proof that the ceiling is high, not a promise that your fleet will behave like that.

For storage, time series data grows fast and is rarely edited, so plan for keeping recent data detailed and older data summarised. We will go deeper on partitioning and archiving in the database days later in this series.

4. Updating firmware without bricking the fleet
An update is the riskiest thing you will ever send to a fleet. A bad version can leave thousands of devices unable to start, often in places where nobody can walk up and reset them. Four habits reduce the risk.
- Write to a spare partition. In an A/B update design, the device runs on partition A while the new version is written to partition B. Only after the write succeeds does the bootloader switch to B. If the new version fails, the device rolls back to A and stays usable. The running system is never overwritten, which also protects against a power cut halfway through the write.
- Confirm before committing. Do not mark the update as good just because the device booted. Run checks first, such as network connectivity and whether the critical application started, and revert if boot fails a set number of times or no success report arrives within a timeout.
- Sign the image. Code signing means the device checks a digital signature before it runs new firmware, so it accepts only your code. Validate the hash or signature before boot.
- Roll out in waves. Start with a small canary group, watch the results, then widen. Platforms help here. AWS IoT Jobs lets you set a rollout rate, for example starting at 50 devices per minute and doubling as the notified or succeeded count reaches 1,000, then 2,000, then 3,000 (the page’s own example). You can also set abort criteria, such as a threshold percentage of failures, so the rollout stops itself.
Note the details: when an abort fires, queued executions are canceled but those already in progress are not, and retries (up to 10 per device) can add cost, so AWS recommends pairing them with an abort setting. The same ideas exist on other platforms.

The common thread
All four decisions come from one habit: assume every device will do the same thing at the same moment, then design so that this is fine. Randomised retries, queues in front of databases, duplicate tolerant handlers and wave based updates are not clever, but they are what keep a fleet boring on the worst day.

A quick audit for your own fleet: what do devices do when the network returns, what happens to a message delivered twice, how many writes can your database take in one second, and how would you stop a bad update at 2 percent instead of 100?

Imagine you walk into a huge library and ask for page 50,000 of the catalogue. The librarian has no bookmark, so she starts at page 1 and counts. Asking for page 1 is instant. Asking for page 50,000 takes minutes, and everyone queued behind you waits too.

A lot of APIs treat their database exactly like that librarian. Nothing is wrong with the code, the server is healthy and the database is not even busy by the usual measures, yet the conversation between the app and the server is wasteful. At small scale nobody notices. At large scale, that conversation is often the biggest cost in the whole system.

I want to walk through four decisions that shape it. They are language and framework neutral, so they apply whether your backend is Node, Java, .NET, Python or anything else.

1. How you turn the page
The most common way to paginate is offset pagination: skip some rows, return the next batch. In plain terms, “skip 1,000,000 rows and give me 20”.

The problem is that the database cannot teleport. To skip a million rows it has to walk past them. The PostgreSQL manual states it plainly: the rows skipped by an OFFSET clause still have to be computed inside the server, so a large OFFSET might be inefficient. The MongoDB documentation says the same about skip(): the server must scan from the beginning of the result set, and skip() becomes slower as the offset grows. The EF Core documentation from Microsoft describes the same cost, and adds a second problem. If rows are added or deleted while a user is paging, results can shift, so one entry gets skipped or shown twice. Microsoft’s SQL Server documentation says stable OFFSET and FETCH paging needs two conditions: the underlying data does not change between page requests, and the ORDER BY contains a column or combination of columns guaranteed to be unique.

The alternative is keyset pagination, sometimes called the seek method or cursor pagination. Instead of “skip N rows”, the client says “give me the next 20 rows after the last one I saw”. With an index on the sort columns, the database jumps straight to that spot, so page 50,000 costs about the same as page 1. The MongoDB documentation recommends exactly this range query approach, and the EF Core documentation calls keyset the recommended alternative to offset.

Here is the same idea in four databases. Assume an orders table sorted newest first, with a tie breaker on id so the order is unique, and an index on (created_at, id). The last row the client saw had created_at = :last_created and id = :last_id.

PostgreSQL:
sql
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < (:last_created, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

SQL Server (OFFSET 0 with FETCH works since SQL Server 2012, and TOP works too):
sql
SELECT id, created_at, total
FROM orders
WHERE created_at < @last_created
OR (created_at = @last_created AND id < @last_id)
ORDER BY created_at DESC, id DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

Oracle (row limiting clause, available from 12c):
sql
SELECT id, created_at, total
FROM orders
WHERE created_at < :last_created
OR (created_at = :last_created AND id < :last_id)
ORDER BY created_at DESC, id DESC
FETCH FIRST 20 ROWS ONLY;

MongoDB:
javascript
db.orders.find({
$or: [
{ createdAt: { $lt: lastCreated } },
{ createdAt: lastCreated, _id: { $lt: lastId } }
]
}).sort({ createdAt: -1, _id: -1 }).limit(20)

The expanded OR form in the last three works on every engine. PostgreSQL also accepts the shorter row comparison shown first.

Keyset pagination has a real trade-off, and it is better to say so. It does not support random access. A user cannot jump straight to page 500, because the database needs a starting point. Microsoft’s own guidance says it suits interfaces where users move forward and backward, and suggests a hybrid when you also need jumps. For feeds, timelines, logs, order histories and infinite scroll, which is most of the traffic on most APIs, it is the better fit. For the rare “go to page 500” screen, keep offset but limit how deep it can go.

Two habits make this safe at scale. First, hand the client an opaque cursor instead of raw column values, so you can change the internals later. Stripe’s list endpoints show the shape: you pass starting_after with the ID of the last object you received, and the response carries a has_more flag telling you whether another page exists. Second, cap the page size. Stripe’s limit parameter accepts 1 to 100 with a default of 10, and GitHub’s documentation says the maximum per_page is 100 on most endpoints, silently reducing larger values to 100. A cap stops one client from asking for everything at once.

2. How heavy each answer is
The next cost is the size of what you send back. Many responses carry forty fields when the screen shows five. Sending only what the screen needs, through field selection or purpose built response shapes, shrinks the payload before compression even starts.

Then compress what remains. Gzip is supported by essentially every server and browser. Brotli is newer and, according to measurements from Paul Calvano’s analysis of web compression, can often reduce files 15 to 25 percent more than gzip at its highest level. Zstandard arrived in Chrome from version 123, released in March 2024. The catch is CPU. The same analysis notes that Brotli’s higher levels come at a significant latency cost, which is why servers default to middle levels for content generated on the fly (for example, Apache’s mod_brotli defaults to level 5). The practical rule is to compress text such as JSON, HTML and CSS, skip files that are already compressed such as images and video, and use a middle level for live responses.

One security caution belongs here. The BREACH attack can work against sites that use HTTP compression, reflect user input in the response, and also reflect a secret such as a CSRF token in the same response body. If your pages or responses mix a secret with attacker influenced text, talk to your security team before assuming compression is free.

3. How many trips
Every request has a fixed cost, however small its content. If drawing one screen takes ten separate calls, and some of them wait for earlier ones to finish, the user feels the sum of all ten waits. This is the same shape as the N+1 query problem inside a database, which I covered in a previous post here, only it happens across the network.

There are two honest fixes. The first is batching or combining: let the client fetch what it needs for a screen in one call, or send several operations in one request. Keep batches bounded, and return a result for each item so that one failure does not hide the other nine successes. The second is not asking at all when nothing changed. A server can attach an ETag, which is a version label, to a response. On the next request the client sends that label back in an If-None-Match header. If the data has not changed, the server replies with 304 Not Modified, which by definition has no body, and the client reuses its cached copy. That saves the bytes, and if checking the version is cheap on your side, it saves the work too.

4. What happens when the same request arrives twice
This is the one that costs money. Networks time out, phones lose signal, load balancers retry, and message queues deliver at least once. So the same request will eventually arrive twice. If that request is “charge this customer” or “create this order”, you now have a duplicate.

The standard answer is an idempotency key. The client generates a unique key for each logical action and sends it with the request. The server records the key with the result. If the same key arrives again, the server returns the stored result instead of doing the work a second time.

There is active standards work on this. The IETF’s HTTP API working group has an Internet-Draft called the Idempotency-Key HTTP Header Field. It is still a draft, not a published standard, so treat it as a direction rather than a rule. It targets methods such as POST and PATCH, which are not naturally safe to repeat, and it suggests three responses worth copying: 409 when the same key arrives while the first request is still running, 422 when the same key is reused with a different request body, and 400 when an endpoint that requires the key does not receive one.

Store keys with an expiry, check the stored request against the new one, and make the key check and the operation succeed or fail together, ideally inside one database transaction.

The common thread
All four choices come from the same habit: design the conversation for the worst day, not the demo day. Bounded pages, small answers, few trips, and safe retries are boring to build, and they are exactly what keeps an API calm when traffic is 100x higher than the test environment.

If you want a quick audit, pick your busiest endpoint and ask four questions:
1. How deep can a client page?
2. How many bytes does the biggest response carry, and is it compressed?
3. How many calls does its main screen make?
4. What happens if the same request is sent twice?

The answers usually point to the first thing worth fixing.

The petrol station
A petrol station has four pumps. Cars arrive, fill up and leave, and life is good. Then one driver parks at a pump and walks off to buy snacks. The station now has three working pumps, and a queue is forming behind a car that is not even fuelling.

Your database is that station, and the pumps are connections. Each request needs one to talk to the database, and the number is limited. The driver who wanders off is a request that keeps its connection while it waits for something slow, such as a call to a payment partner.

The big idea: capacity hides in config files
Your app’s real capacity is rarely set by CPU. It is set by small numbers that nobody reviews:
- How many connections the database allows
- How big each app instance’s connection pool is
- How many workers or threads each instance runs
- How long the load balancer and your app keep idle connections open

If these numbers disagree with each other, the weakest one decides your ceiling.

Surprise 1: a bigger pool is often slower
The HikariCP documentation, the connection pool used by most Java applications, makes a striking point using a demonstration from Oracle’s Real-World Performance group. Reducing the pool from 2,048 connections to 96, with no other change, cut response times from about 100ms to about 2ms, more than a 50 times improvement. The wiki goes further and argues that even 96 is probably too many unless the database server has a lot of cores.

Why? A database can only do so much work at once. With far more active connections than CPU cores, the database wastes time switching between them. The best design is a small pool kept busy, with the rest of your requests waiting in line inside the application, where waiting is cheap.

The wiki’s starting formula, which it says has held up across benchmarks for years, is connections equals twice the database server’s core count plus the number of effective disk spindles. Two things to remember: it is a starting point to test, and it applies to the database, not to each app instance. Pool sizing is ultimately specific to your deployment.

You can also size from traffic using Little’s Law: connections in use equals queries per second times the seconds each query holds a connection. At 2,000 queries per second and 50 ms each, about 100 connections are busy. If the query slows to 500 ms, you suddenly need 1,000, and the pool becomes your bottleneck before the CPU does.

Surprise 2: autoscaling can take down your own database
Here is a failure that catches even experienced teams. Each instance of your app keeps a pool, so total connections equal the pool size times the number of instances.
- 4 instances with a pool of 50 each is 200 connections.
- A traffic spike triggers autoscaling to 40 instances, and you now ask for 2,000 connections.
- The database, already under stress, is hit by a wall of new connections.

PostgreSQL’s default max_connections is 100. SQL Server’s ADO.NET pool defaults to a maximum of 100 per connection string, and MongoDB drivers default to up to 100 open connections in the pool. So the multiplication happens quickly. Serverless functions make it worse, because each concurrent execution can open its own connection.

Fixes: put a connection pooler or proxy between the app and the database, keep per instance pools small, cap your maximum instance count according to your database limit, and give each service its own database role with a connection limit.

Your database, one by one
1. PostgreSQL: each client connection gets its own backend process, which is why connections are expensive and why the default limit is 100. PgBouncer is the most widely used pooler. It supports session pooling (the default), transaction pooling and statement pooling. Transaction mode gets the most out of few connections, but it breaks some session features, such as LISTEN/NOTIFY, and session level settings. Since PgBouncer 1.21, protocol level prepared statements are tracked per client, which fixed a long standing pain point, so check your version.
2. SQL Server: the ADO.NET pool keeps a separate pool for each distinct connection string, so small differences in a string can create extra pools. The default maximum is 100, and when it is reached, requests queue. If they wait too long you get the familiar error saying the timeout elapsed before a connection could be obtained from the pool. In Azure SQL Database, connection limits also depend on the service tier you chose.
3. MongoDB: drivers keep their own pool per client instance, with a default maximum of 100 connections, so many app instances multiply quickly. Set maxPoolSize deliberately, and watch the total across your fleet.
4. Oracle: sessions and processes are limited at the database, and Oracle offers connection pooling options such as Database Resident Connection Pooling and the Universal Connection Pool, so check what fits your architecture.
5. MySQL: the same idea applies, with a max_connections limit and proxies such as ProxySQL.

How pools go wrong
1. Leaks. Code that opens a connection and forgets to release it on an error path. Always release in a finally block or a using style construct.
2. Holding a connection while doing something slow. Never keep a connection or an open transaction while calling an external API. Get the data, release the connection, then call out.
3. Long transactions. In PostgreSQL, sessions stuck in an “idle in transaction” state hold connections and locks without doing anything.
4. Chatty code. The N+1 pattern from one of my previous posts here holds a connection for much longer than needed.
5. Long wait timeouts. If a request waits 30 seconds for a connection, everything behind it is stuck too. Make the connection acquisition timeout shorter than your request timeout, so you fail fast and visibly.

Watch these signals: connections in use, connections idle, and above all the number of requests waiting for a connection. In HikariCP with Micrometer, for example, a pending connections metric shows threads waiting.

Worker limits — three numbers that must agree. The traffic path has three limits in a row:
- Concurrent requests the load balancer will send to each instance
- Workers or threads in each instance
- Connections in the pool, which should be fewer than the workers

Typical examples: Node.js runs one event loop per process, so you run several processes. Python servers such as gunicorn use a configured number of worker processes. PHP-FPM has a maximum number of child processes. Java servers use thread pools. .NET uses its thread pool. Many workers share a small pool, and that is fine, as long as the waiting queue is bounded and has a timeout.

Load balancing in plain words
A load balancer is the traffic warden sending cars to pumps. Things worth knowing:
1. Layer 4 and layer 7. A layer 4 balancer forwards connections without reading them. A layer 7 balancer reads HTTP, so it can route by path or header and apply smarter rules.
2. Algorithms. Round robin is simple. Least connections adapts when some requests are slow. Weighted variants help when servers differ. Hash-based routing keeps a key on the same server. Some balancers also offer “power of two choices,” picking the better of two random servers, which works well at scale.
3. Health checks. A shallow check asks “is the process alive?” A deep check queries dependencies such as the database. Be careful with deep checks: if the database blips, every instance fails its check at the same time, the balancer removes them all, and a small problem becomes a total outage. Keep the load balancer’s check shallow, and use separate readiness and liveness endpoints in orchestration systems.
4. Graceful deploys. When an instance is removed, it should stop accepting new requests, finish current ones, then exit. Load balancers have a draining or deregistration delay for this, and orchestrators have a termination grace period, which in Kubernetes defaults to 30 seconds. Make sure the app handles the shutdown signal.
5. Stateless apps. Avoid sticky sessions where you can, and keep sessions in a shared store, so any server can serve any user.

The timeout trap behind random 502 errors
This one confuses even experienced engineers. An AWS Application Load Balancer keeps idle connections to your servers open for 60 seconds by default. Node.js closes idle connections after 5 seconds by default. In the unlucky moment when a new request arrives just as the server closes the connection, the load balancer sends the request into a dead connection and reports a 502 Bad Gateway, even though your app is healthy.

The rule: the backend’s keep-alive timeout must be longer than the load balancer’s idle timeout. For a 60-second balancer, a common setting is 65 seconds. In Node.js, also keep headersTimeout higher than keepAliveTimeout. The same logic applies in other stacks, such as the — keep-alive option in gunicorn. A good general ordering is that the client’s idle timeout is shortest, then the load balancer, then the backend.

The scale lens
- The connection arithmetic. 50 per instance times 40 instances is 2,000 connections against a default limit of 100. At the same time, 2,000 queries per second at 50 ms is only about 100 busy connections. The danger is not usually the work; it is the number of idle and wasted connections.
- Connection storms. After a deploy, a database failover, or an outage, every instance reconnects at once. Use backoff with jitter on reconnects, stagger rolling deployments, and avoid opening a full pool at startup.
- Pools hide slow queries until they do not. A query that gets ten times slower needs ten times the connections, so a slow query can look like a connection problem.
Retries multiply. Load balancer retries, client retries, and application retries stack up. Keep one layer in charge of retries.
- Capacity plan the whole chain, from load balancer to workers to pool to database, with load tests, and know which limit breaks first.

A security note
- Least privilege per service. Give each service its own database credentials with only the permissions it needs, and cap connections per role where the database supports it.
- Encrypt in transit. Use TLS between the load balancer, the app, and the database, and do not expose the database or the pooler to the public internet.
- Be careful with forwarded headers. Many apps read the client’s IP from headers set by proxies. If your app trusts them from anywhere, attackers can fake them and bypass IP-based limits; see more about that here. Only trust them from your known proxies.
- Keep health endpoints minimal. Do not reveal versions, dependency names or internal details to the public.
- Slow connection attacks. Short timeouts for headers and bodies protect your workers from clients that hold connections open on purpose.
- Rotate credentials safely. Set a maximum connection lifetime in your pool, so rotated credentials take effect without a restart.

The tradeoffs
Poolers and proxies add a hop, a component to run, and sometimes restrictions, such as transaction mode limits.
Small pools make requests wait, which is intentional, but you must set sensible timeouts and monitor the queue.
Aggressive health checks improve failover speed but risk false alarms and flapping.
Long keep-alive timeouts use more resources on the server.
A limit that is too tight wastes hardware, and one that is too loose lets a spike take everything down.

Tools to start with
- Pool metrics from your library, such as HikariCP with Micrometer, ADO.NET performance counters, and driver metrics
- pg_stat_activity in PostgreSQL and the equivalent session views in SQL Server, Oracle, and MongoDB, to see who holds connections
- Poolers and proxies: PgBouncer, ProxySQL, and the managed database proxies your cloud provider offers
- Load balancer metrics: target response time, 5xx counts, connection counts
- Load tests that scale instance counts to catch the connection multiplication before production does

In your stack. Check each one’s documentation for your version:
- Java: HikariCP
- .NET: ADO.NET pooling and EF Core’s DbContext pooling
- Node.js: pg Pool, Prisma’s connection limit setting, and the pool options in TypeORM and Sequelize
- Python and Django: persistent connections with CONN_MAX_AGE, and native pooling in recent Django versions
- PHP and Laravel: PHP-FPM keeps a connection per process, so use a pooler such as PgBouncer or ProxySQL for large fleets
- Go: SetMaxOpenConns and its relatives in database/sql
- MongoDB drivers: maxPoolSize

Sometimes the biggest limits are not in your code. They are in the numbers nobody reviews.

The conductor
A bus conductor in a busy city has one job that matters more than any other: knowing when to say “full.” A bus that takes every passenger who shows up does not carry more people. It breaks its axle, and nobody gets to work.

A conductor who says “full” is not being unkind. He is the reason the bus keeps moving.

Your app has the same problem. When more traffic arrives than the system can handle, you have two choices. You can accept everything and fail slowly for everyone, or you can say no quickly to some requests and keep serving the rest.

These terms are often mixed up, so here they are in plain words
1. Rate limiting: a rule about how many requests one client may make in a period of time, for example 10 per second.
2. Quotas: a larger allowance over a longer period, for example 100,000 requests per month on a pricing plan.
3. Load shedding: when the whole system is under pressure, deliberately dropping low priority work so critical work survives.
4. Backpressure: when one part is full, it signals the parts feeding it to slow down, instead of letting work pile up without limit.

Did you know that most time, your worst client is usually you?
When people think about limits, they imagine attackers. In practice, the biggest sources of overload are friendlier and closer to home:
- Your own mobile app retrying in a loop after an error
- A partner’s script with a bug
- Your own service hammering another one after an outage
- A well meaning customer running a huge export at peak time

And there is a second surprise: where a request comes from is a weak identity. On mobile networks, many real customers can share one IP address, so an IP based limit can block innocent people, while IPs are also easy to fake. OWASP’s guidance for API protection recommends configuring limits on other keys, such as tokens or fingerprints, where possible. Limit by who the caller is, such as the API key, account or user token, and use IP only as an extra signal.

The algorithms, in plain words
- Fixed window. Count requests in each time slice, such as 100 per minute, and reset at the boundary. It is simple, but it allows a burst at the edge: 100 requests at the last second of one minute and 100 in the first second of the next, so 200 in two seconds.
- Sliding window. Spreads the counting across the boundary and smooths that burst, at the cost of more bookkeeping.
- Token bucket. Each client has a bucket that refills at a steady rate, say 10 tokens per second, up to a maximum of 20. Each request spends a token. A client who has been quiet can burst 20 requests, then settles at 10 per second on average. Stripe documents that it uses a token bucket for its request rate limiter.
- Leaky bucket. Smooths traffic into a steady outflow, useful when a downstream system needs an even pace.
- Concurrency limiter. Instead of counting requests over time, it caps how many are in flight at once. This suits expensive endpoints, because the cost is how long each request holds a worker.

Stripe’s four layers
Stripe published how it protects its API in production, and the design is a good template:
1. A request rate limiter (token bucket), which Stripe calls by far the most important and recommends starting with.
2. A concurrent requests limiter, to control CPU intensive endpoints.
3. A fleet usage load shedder, which reserves a share of capacity for critical requests, so non-critical ones are rejected when the fleet is busy.
4. A worker utilization load shedder, the last line of defense, which starts dropping lower priority traffic when a machine is under heavy pressure.

What to return when you say no
- HTTP 429 Too Many Requests when a client exceeded its limit, or 503 Service Unavailable when the server itself is overloaded.
- A Retry-After header, which tells the client how long to wait. HTTP defines it for 429 and 503 responses.
- Optional limit headers. An IETF draft proposes the RateLimit-Limit, RateLimit-Remaining and RateLimit-Reset fields. It was still a draft in the versions I found, and many APIs use their own names, so document whichever you choose.
- A clear message. Say which limit was hit, so the developer on the other side can fix it.

Where to enforce limits
Use several layers, from the outside in:
- The edge: a CDN or WAF handles huge volumes and abusive patterns, including volumetric attacks, before they reach you.
- The gateway or reverse proxy: NGINX, Envoy, Kong, and the cloud API gateways, for per-key limits.
- The application: business aware limits, such as per tenant, per plan and per endpoint.
- The workers: concurrency caps on queue consumers, so they cannot overload the database.
- The database itself: the last wall.

Protect the database directly
A limit at the front does not help if one expensive query can still flatten your database. Every serious database offers ways to cap the damage:
- PostgreSQL: a connection pooler to cap connections, and statement_timeout to stop runaway queries.
- SQL Server: Resource Governor, to limit how much CPU and memory a workload can take.
- Oracle: Database Resource Manager, for the same purpose.
- MongoDB: maxTimeMS to cap query time, and connection pool limits in the drivers.
- All of them: set sensible pool sizes.

Backpressure in practice
- Bound your queues. An unbounded queue does not remove the overload, it only postpones the crash and hides the problem. When the queue is full, reject new work visibly.
- Cap prefetch and concurrency. Consumers should take only as much work as they can handle, as with RabbitMQ’s prefetch limit. Pull based systems, such as Kafka consumers or SQS pollers, are naturally paced by the consumer.
- Use streams that respect pressure. Node.js streams, for example, signal when a buffer is full so the producer pauses. Reactive Streams libraries on the JVM and .NET include backpressure for the same reason.
- Set timeouts at every hop, and pass deadlines along, so a request nobody is waiting for any more does not keep consuming resources.

Good manners for clients
The best limit is one that clients respect:
- Obey Retry-After.
- Use exponential backoff with jitter, so clients do not return together.
- Keep a retry budget. If every failed request is retried three times, your load can multiply by four at exactly the moment you are weakest.
- Use circuit breakers to stop calling something that is clearly down.

Rolling out limits safely
Stripe’s own lessons are a good checklist:
- Hook limiters into the middleware stack safely, so a bug in the limiter cannot break the API.
- Show clear errors to users.
- Build a switch to turn limiters off, for emergencies.
- Dark launch each limiter, which means running it in log-only mode first to see what it would have blocked before it blocks anything.
- Design the limiter so its own failure does not take the API down. Decide in advance what happens if the counter store, such as Redis, is unreachable. Usually, fail open for ordinary traffic, but think carefully for login and payments.

Counting across many servers
With many servers, the count must be shared. A common approach is a Redis based token bucket, where an atomic script updates the bucket in one step. Stripe’s published example does exactly this. Tradeoffs: a shared store adds a network call and a dependency, so some teams use approximate local limits that sync periodically. Use server time for refills, not client clocks, and remember that counters cost memory: 10 million active keys at roughly 100 bytes each is about 1 GB. (Illustrative numbers.)

In your stack
Check each one’s documentation for your version:
- ASP.NET Core: built in rate limiting middleware since .NET 7, with fixed window, sliding window, token bucket and concurrency limiters
- NestJS and Express: @nestjs/throttler and express-rate-limit
- Laravel: RateLimiter and the throttle middleware
- Django: the throttling classes in Django REST framework
- Spring and Java: Bucket4j and Resilience4j
- Go: the x/time/rate package
- Gateways and edge: NGINX limit_req, Envoy, Kong, AWS API Gateway and WAF rate based rules, Azure API Management policies, Cloudflare rate limiting

The scale lens
- Per user limits do not protect the total. 10 requests per second per user sounds safe, but 1 million users each using it is 10 million per second in theory. You need a global ceiling too.
- Set the ceiling from tested capacity. If 20 servers each handle 500 requests per second, your capacity is 10,000. A common starting point is to leave headroom, for example a ceiling around 70 to 80% of what a load test proves. Treat it as a starting point, not a law.
- Tiers and fairness. Free, standard and enterprise plans get different limits, and one noisy tenant should not starve others.
- Retry amplification. Three retries per failure can multiply load fourfold during an incident.
- Measure. Track how many requests get limited, who gets limited, and which limits are near their ceiling. A rising 429 rate is a signal, not just a statistic.
- Cost. Limits also cap cloud bills. OWASP’s own example describes a monthly bill jumping from about US$13 to US$8,000 because no limits or cost alerts existed.

A security note
Rate limiting is one of the most important security controls an API has. OWASP lists it as API4:2023, Unrestricted Resource Consumption.
- Login and password reset: limit by account and by IP, add progressive delays, and combine with multi factor authentication to resist brute force and credential stuffing.
- SMS and OTP endpoints: without limits, attackers can trigger huge numbers of messages, which costs real money.
- Enumeration and scraping: limit endpoints that reveal whether an account or record exists, and cap pagination sizes.
- GraphQL: attackers can batch many operations into one request to slip under limiters that count HTTP requests. Limit query depth and complexity, not only request counts.
- Payload and upload sizes: set maximums.
- Volumetric DDoS: application limits cannot stop floods that fill your network. Use your cloud provider’s or CDN’s DDoS protection.

The tradeoffs
- Limits can block legitimate users, especially on shared networks or during genuine spikes.
- Tight limits frustrate good customers, and loose limits protect nothing, so tune them from real traffic.
- Distributed counting adds a dependency and a failure mode.
- Load shedding means deciding which requests matter more, which is a business decision, not only a technical one.
- Backpressure pushes the problem to the user, so design clear messages and graceful behavior.

Tools to start with
- Your gateway’s or framework’s built in rate limiter, from the list above
- A load testing tool such as k6, to find your real capacity
- Log-only or dark launch mode for new limits
- Dashboards for 429 rates, limit utilization, queue depth and saturation
- Alerts for cost and abnormal usage

Saying no early is a feature, not a failure.

The laundry ticket
You drop your clothes at a laundry and the attendant hands you a ticket with a number. You do not wait while the machines run. The shop works in the background, and you come back later or check the ticket.

Now imagine a laundry with no tickets. Every customer stands at the counter until their clothes are done. On a busy Monday, the shop is full of people waiting, and nobody new can even get in.

Many apps are built like the second laundry. A user clicks “Place order,” and the server saves the order, sends an email, generates a PDF, resizes images, calls a partner’s system and updates a report, all before it answers. The user stares at a spinner for something they never needed to wait for.

The big idea is that you should do only what the user must wait for

A simple test for each step in a request: does the user need this result right now to continue?
- Yes: keep it in the request. For example, validating input and saving the order.
- No: put a message on a queue and let a background worker do it. For example, confirmation emails, PDF receipts, image and video processing, webhooks to other systems, search indexing, analytics events, bulk imports and cleanup tasks.

The request then returns quickly with a job ID. An HTTP 202 Accepted response means “received, not finished yet,” and the user can check a status page or get notified later.

A queue does not speed up a single job. What it gives you is survival:
- A shock absorber. A sudden spike of orders piles up in the queue instead of crashing your database.
- Retries. A failed job can be tried again instead of being lost.
- Isolation. A slow partner system delays one background job, not your whole website.

There is a catch that surprises many developers. Most queues promise at least once delivery, not exactly once. Amazon SQS standard queues guarantee that every message is delivered at least once, but in some cases it may be delivered more than once, and the documentation tells you to design your application with idempotent operations. SQS standard queues also provide only best effort ordering. SQS FIFO queues add exactly once processing, with a five minute deduplication interval for repeated sends, at the cost of different throughput characteristics.

So your handler will sometimes run twice. If it sends an email or charges a card, “twice” can hurt.

The reliability toolkit
1. Acknowledge after success, not before. The broker should only delete a job once the worker finishes it. If the worker crashes, the job comes back.
2. Make handlers idempotent. Give every job a unique key. Before doing the work, check whether that key is already done. Better still, make the work and the “done” marker part of the same database transaction where you can.
3. Set the visibility or lock timeout longer than the job takes. With SQS, a message becomes invisible to other consumers for the visibility timeout, 30 seconds by default and up to 12 hours. If your job takes longer, the message reappears and another worker starts a duplicate.
4. Retry with backoff and a limit. Wait longer after each failure, add random jitter, and stop after a fixed number of attempts.
5. Use a dead letter queue. Jobs that keep failing move to a separate queue, so one bad message does not cycle forever or block the others. SQS supports dead letter queues for exactly this, and it is also how you find problem messages. Then monitor and alert on it, because a dead letter queue nobody reads is a trash can.
6. Use separate queues for different kinds of work. A flood of slow report jobs should never delay a password reset email.
7. Set a timeout per job so a stuck job cannot hold a worker forever.

A small pseudo code illustration:

POST /reports
job_id = new_unique_id()
db.save(job_id, status = “queued”)
queue.send({ job_id, user_id })
return 202 Accepted { job_id, status_url = “/jobs/” + job_id }

worker:
message = queue.receive()
if db.is_done(message.job_id): # safe to run twice
ack(message); return
do_the_work()
db.mark_done(message.job_id)
ack(message) # acknowledge only after success

The dual write problem, and the outbox
Here is a classic trap. When an order is placed, your code saves it in the database and then publishes an “OrderPlaced” message to the broker. Those are two separate systems. If the app crashes between the two steps, you either lose the message, or, if you publish first, you send a message about an order that never saved.

The standard solution is the transactional outbox:
1. In one database transaction, save the order and also insert a row in an outbox table.
2. A separate relay process reads the outbox and publishes messages to the broker, marking them as sent.

Because the order and the outbox row commit together, either both exist or neither does. It works in any database with transactions, such as PostgreSQL, SQL Server, Oracle and Db2. In MongoDB, use multi-document transactions where available, or change streams.

You can write the relay yourself, or let change data capture do it. Debezium reads the database’s transaction log and provides connectors for PostgreSQL, SQL Server, MongoDB, Db2 and Oracle, among others. It includes an outbox event router for relational databases, and MongoDB has its own separate outbox router, so check the connector’s maturity for your database and version.

Start simple — the database as a queue
For modest volumes, you do not always need a separate broker. A jobs table can work as a queue if workers claim rows safely so two workers never take the same one:
- PostgreSQL and Oracle: SELECT … FOR UPDATE SKIP LOCKED
- SQL Server: UPDLOCK with the READPAST hint
- MongoDB: an atomic findOneAndUpdate that flips a job from “queued” to “processing”

This keeps one system to operate. The cost is more load on your primary database, and you will outgrow it at higher volumes.

Choosing a queue
- Message brokers such as RabbitMQ: messages are delivered to consumers and removed once acknowledged.
- Event logs such as Apache Kafka: messages are kept for a retention period and consumers track their own position, so they can replay history. Ordering is guaranteed within a partition.
- Cloud queues: Amazon SQS and SNS, Azure Service Bus and Google Pub/Sub remove most operations work.
- Redis based job libraries: simple and fast, with a durability tradeoff you must understand.
- A durable workflow engine such as Temporal: for long, multi step processes that must survive failures.

A warning if you use Redis for jobs
BullMQ, a popular Node.js job queue, documents that its Redis must use the noeviction memory policy, because it cannot work properly if Redis evicts keys arbitrarily. If you point a cache with an eviction policy and a job queue at the same Redis, memory pressure can silently delete jobs. Use separate Redis instances for cache and queues. BullMQ also keeps completed and failed jobs forever by default, so set automatic removal, and its job data is stored in clear text, so avoid putting sensitive data in jobs.

In your stack, check each one’s documentation for your version:
- Node.js and NestJS: BullMQ
- Python and Django: Celery, with RabbitMQ or Redis, and RQ
- PHP and Laravel: the built in queues, with Redis, SQS or database drivers, and Horizon for Redis queues
- .NET: Hangfire, MassTransit and Azure Service Bus
- Java and Spring: Spring AMQP, Spring Kafka and Quartz
- Go: Asynq, or Temporal
- Ruby and Rails: Sidekiq

The scale lens
- The backlog arithmetic. If 1 million jobs arrive in a minute and your workers process 500 per second, it takes 2,000 seconds, about 33 minutes, to clear. If jobs arrive faster than workers can process them, the queue grows without limit. Set a target for how long each type of job may wait, and size your workers for it.
- Alert on age, not only depth. The age of the oldest message tells you how long users are waiting. A queue with a modest length but an old oldest message is already hurting people.
- Scale workers on the backlog. Autoscale on queue depth or age, and cap concurrency so you do not overload the database the workers depend on.
- Retry storms. When a downstream system fails, every worker retries at once and makes it worse. Use backoff with jitter and a circuit breaker.
- Fairness. One huge tenant can fill a shared queue and starve everyone else. Use per tenant concurrency limits, priorities or separate queues.
- Ordering and partitions. If order matters, use a key so related messages go to the same partition or message group, and watch for hot partitions where one key gets too much traffic.
- Rare becomes daily. At 10 million jobs per day, a 0.01% duplicate rate is 1,000 duplicate executions every day. (Illustrative numbers.) Idempotency is not optional.

A security note
- Secure the broker. Use authentication, TLS and least privilege per queue, and never expose a broker or a Redis instance to the internet.
- Keep secrets and personal data out of messages. Pass IDs, not whole records, and look up the data when the job runs.
- Validate messages like any other input. Anything that can reach the queue can forge a message. Verify webhook signatures, limit payload sizes, and avoid unsafe deserialization.
- Protect the dead letter queue. It holds the messages that failed, often the most interesting ones, so apply access control and retention limits.
- Limit who can enqueue. An open endpoint that creates jobs can be abused to flood your queue, so rate limit it.
- Audit money related jobs. Keep a record of what ran, when and with which key.

The tradeoffs
- The user sees “processing” instead of an immediate result, which needs honest design: status pages, notifications and clear errors.
- Eventual consistency means different parts of the system can briefly disagree.
- Debugging is harder, because the work no longer happens inside one request. Use a correlation ID that follows the job from the request through the queue to the worker.
- Queues add infrastructure to run, monitor and pay for.
- Not everything belongs in a queue. Fast local work does not need one, and some results, such as a payment authorization decision, are needed immediately.

Tools to start with
1. Your broker’s dashboard, plus queue depth and oldest message age metrics
2. Distributed tracing with a correlation ID across request, queue and worker
3. A job dashboard for your library, such as Horizon for Laravel or Bull Board for BullMQ
4. Load tests that include worker crashes and duplicate messages
5. A “kill the worker mid job” test, run on purpose

A queue is a promise to do the work later. Make sure you can keep that promise twice.