Scaling database practices

Scaling databases can feel like a scary topic. I'm not going to wait until I "know everything" to write about it though, there is always a higher mountain. This post is just my little hill of scaling database practices, based on what I've seen so far.

I will try to cover this topic as much as possible, based on my current understanding of databases. I welcome all comments on what I'm missing or covered too shallowly. This post is also how I learn: writing it down forces me to understand each practice deeply, so I'll try to explain the why behind everything, not just the what.

I will pick a specific database to narrow down the scope, and I will guide you through a journey of scaling from an MVP to a large scale system. Think of this post as a guided tour rather than a textbook.

What database are we scaling?

There are many kinds of databases, but in general this will be the main database for any application, it could be SQL based or NoSQL based.

We will start with a naive setup: we have a SaaS app, a couple of users and a dozen tables to serve the main functionality, the web app directly opens connections, no pool, a few indexes.

At this point almost any database is capable, so we will pick Postgres, because it is open source, a multi purpose database, and has a lot of extensions in case we go into a niche.

Good enough at this stage:

• Single VM running Postgres

• For monitoring, we rely on the VM's own monitoring: CPU, RAM, Disk usage. If CPU is constantly high, memory is full, or disk is about to run out, we know something is wrong and go investigate.

• Automatic backup: daily or hourly disk snapshot. We should try to restore from the backup at least once to confirm it works.

When do I need a connection pool?

ERROR 53300: Too many connections

I start to have many API calls and each API creates a new connection to the database, and eventually all the connection slots are exhausted. This error may have many root causes:

• Connections don't close properly

• Long running transactions or queries hold the connection

• max_connections param set too low

• Too many connections to the database (obviously)

Why is Postgres so sensitive about this? Because Postgres forks a whole OS process for every connection. Each connection eats several MB of RAM and adds overhead to internal bookkeeping, even when it's idle. So "just raise max_connections" only delay the problem.

When the app and traffic are still small, the app's builtin connection pool like Node.js pg-pool or Go go-pg is usually enough. But once we scale the number of app instances or serverless functions all connecting to Postgres, the database starts to struggle with thousands of short lived connections. This is where we need a dedicated connection pooler like pgBouncer.

So each app instance already has its local pool, then all the app instances connect to one or more pgBouncer instances. Each pgBouncer instance has a predefined, stable pool of connections to Postgres and multiplexes client requests onto them. That way, Postgres spends its valuable time running queries instead of forking and managing processes.

One thing to understand before deploying pgBouncer: the pool mode.

• Session mode: a client keeps its server connection until it disconnects. Safe, but we don't gain much multiplexing.

• Transaction mode: a server connection is only borrowed for the duration of one transaction. This is where the big win is, but it breaks session-level features: SET commands, advisory locks, LISTEN/NOTIFY, and prepared statements (on older pgBouncer versions). We need to check our app and drivers against this list before rolling it out to production.

Introducing pgBouncer is often the first scaling step for Postgres, long before we talk about sharding or more complex solutions.

How do I know I need indexes?

Answer: we need to measure.

Postgres constantly collects statistics about itself and exposes them as regular views we can query with plain SQL. No extra tool, no dashboard to install, we just connect with psql and SELECT from them. The database observes itself, we only need to ask.

The one to start with is pg_stat_statements. It's an extension that tracks every query shape that runs: how many times it was called, total time, average time, rows returned. "Shape" means parameters are normalized, so WHERE id = 42 and WHERE id = 99 are counted as one entry: WHERE id = $1. It ships with Postgres but is off by default (managed Postgres usually preloads it, then we just run CREATE EXTENSION pg_stat_statements;). Once it's on:

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Note the ORDER BY. There are two different questions hiding here:

• ORDER BY mean_exec_time answers "which queries are slow?"

• ORDER BY total_exec_time answers "which queries hurt the database the most?" A 5ms query called 50,000 times per minute does far more damage than one slow 2-second query. Chasing total time instead of the slowest query is one of those small habits that separates guessing from measuring. This practice work on other database as well, not just Postgres.

pg_stat_statements has siblings, all queried the same way:

• pg_stat_activity: what is running right now. First thing to check when "the database is stuck": long-running queries, or connections sitting in idle in transaction (bug: transaction opened, never committed).

• pg_stat_user_tables: per-table stats. A big table with a high seq_scan count is being full-scanned repeatedly, that's the index candidate.

• pg_stat_user_indexes: the opposite direction. An index with idx_scan = 0 after weeks in production does nothing except slow down our writes. We should drop it.

Then when a specific query is suspicious, prefix it with EXPLAIN ANALYZE and Postgres shows we the execution plan: sequential scan or index scan, and where the time actually went. So the troubleshooting loop looks like: pg_stat_activity (what's stuck now) -> pg_stat_statements (what costs the most) -> EXPLAIN ANALYZE (why this one is slow) -> fix -> measure again.

That said, indexes are one of the first things we should think about when designing tables. A good index can turn a painful full table scan into a quick lookup, and in practice it fixes most "my query is slow" issues, at least until the data grows big enough. Even when the table is still small, adding the right indexes early saves we from surprises later.

The trade off is always write performance. Every extra index makes inserts and updates a bit more expensive. For unique indexes it's even worse, because the database has to both find a place for the new data and check that the value is not already there.

Depending on the data and the query pattern, sometimes we don't need to index every row. We can use partial indexes with a condition. For example, in an emails table with status = draft | live | editing and category = newsletter | transactional | notification, maybe only non-draft emails are ever queried by users. In that case we can create an index only where status != 'draft'. Reads are faster because the index is smaller, and writes to draft emails stay cheap because they don't touch the index at all. One catch: the planner only uses a partial index when the query's WHERE clause matches the index condition, so WHERE status = 'live' works, but a query without a status filter won't.

If we really cannot avoid adding more indexes, that's usually a hint to also look at other options: improve the queries, archive old data out of the hot table, or later on use partitioning so each index only has to deal with a smaller slice of data.

If we use a managed Postgres, the service will usually surface slow queries and full‑table scans in its monitoring UI, but it won’t go as far as MongoDB Atlas and auto‑suggest exact indexes. In the Postgres world, index advice tends to live in separate tools (pganalyze, Datadog, pgHero...), while the core database only exposes the raw stats (pg_stat_statements, pg_stat_user_tables, pg_stat_user_indexes) and leaves the decision to us.

What is vertical scaling?

That is simply adding more CPU, RAM and Disk to the primary node until the curve between resources and performance doesn't align anymore.

This is why cloud providers make it easy to click and upgrade to a bigger instance. We just go from 4 vCPU / 16 GB RAM to 16 vCPU / 64 GB RAM and suddenly the system is faster. We can buy some time until we need another scaling strategy.

The trade offs:

• The pricing of the bigger instance, but we use this method for a while since hardware is cheap compared to engineering time. Note that we need at least one or two replicas (more on those later), so the price also multiplies.

• The blast radius gets bigger: a larger backup takes longer to restore, a failover moves more traffic at once, and any mistake affects more data.

• At some point, doubling CPU and RAM does not give us the same win anymore. I haven't operated at that ceiling myself, but the consensus is that vertical scaling alone eventually stalls, and we have to combine it with the other tools in this post: better indexes, partitioning, replicas, and in the extreme case, sharding.

What about caching?

Before adding more database machinery, there is a cheaper question: does this read need to hit the database at all?

A cache like Redis (or even an in-process cache) in front of Postgres can absorb a lot of repetitive read traffic: rendered pages, user sessions, computed aggregates, reference data that changes once a day. The common pattern is cache-aside: try the cache, on miss read from Postgres and write back to the cache with a TTL.

The trade‑off is the famous hard problem: cache invalidation. From my experience, caches solve some problems but introduce others.

Once, we hit a serious production incident where data from one tenant was returned to another. All evidence pointed to Redis: the application logs and keys were correct, but the value that came back belonged to a different tenant. After restarting Redis the issue vanished and we were never able to reproduce it. My best guess is a hardware or memory corruption issue, but we never got a definitive root cause. I mention this not to blame Redis, but to show how caching can create subtle, high‑impact failures even when we think we are using it carefully.

Generally, anything with strong consistency requirements (like show the order user just created) should keep reading from the database. When it works, caching is often the cheapest way to buy time: it cuts read load enough so we don't need replicas or a bigger instance yet. But it is another moving part with its own consistency model, so it deserves as much design thinking as the database itself.

Do I need partitioning?

Before jumping to sharding, there is a practice we can apply first: partitioning. In Postgres, partitioning means one "logical" table is actually split into many smaller physical tables behind the scenes, but they all stay on the same server.

Take an example: I have a table events where the data naturally groups by created_at. I can create a partition:

CREATE TABLE events_2026_07 PARTITION OF events
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

From the application, I don't care about events_2026_07 or events_2026_08 at all:

INSERT INTO events (user_id, created_at, payload)
VALUES (123, now(), '{ "action": "click" }');

SELECT * FROM events
WHERE created_at > now() - interval '7 days'
  AND user_id = 123;

Postgres will:

• Route the INSERT into the right partition based on created_at.

• For the SELECT, only touch the partitions that overlap "last 7 days" (this is called partition pruning).

So yes, it's still one server, but it can be faster because Postgres skips work:

• It does not scan a huge single index; it works with smaller per-partition indexes.

• VACUUM / ANALYZE runs on smaller chunks.

• When I want to delete old data, I don't run a giant DELETE, I can just: ALTER TABLE events DETACH PARTITION events_2026_01; DROP TABLE events_2026_01;

Typical patterns are:

• Time-based: one partition per day / week / month.

• Tenant-based: one partition per customer or group of customers.

It's all still one Postgres cluster, one primary, one connection string. Partitioning adds a bit of schema complexity, but compared to full sharding across many databases, it's a much smaller step and already helps with performance and data lifecycle.

But partitioning comes with trade offs: we are adding schema and operational complexity. In more detail:

• Partitions need to be created over time. But we should not do this manually, let's use an extension like pg_partman to automate it.

• Migrations and tooling need to be partition-aware.

• We need to pick a good partition key, and Postgres adds one extra rule: the partition key must be part of every primary key and unique constraint on the table.

  In practice, that means we should not partition directly on a high cardinality column like created_at timestamptz and then use (id, created_at) as the primary key. It works, but it's awkward to query and doesn't give us a clean "id is unique" guarantee anymore.

  A more practical pattern is to add a coarser key and partition on that:

  CREATE TABLE events (
    id          bigint,
    user_id     bigint,
    bucket_date date NOT NULL,
    created_at  timestamptz NOT NULL,
    payload     jsonb
  ) PARTITION BY RANGE (bucket_date);
  
  ALTER TABLE events
    ADD PRIMARY KEY (id, bucket_date);
  

  Now bucket_date is the partition key, and (id, bucket_date) is what Postgres uses to enforce global uniqueness across partitions. If the application really wants to treat plain id as the identifier and query with WHERE id = ..., we either add a separate unique/index on id, or accept that "id is globally unique" is enforced by our code, not by the database.

Partitioning becomes interesting when we deal with data on a hot disk (NVMe) and cold disk (SSD, HDD): the latest partition stays on the hot disk for frequent queries, the older partitions move to the cold disk to save cost, but all the data stays in the same database for the occasional query. In practice it looks like this:

1. Mount different disks on the machine, for example:

   1. /mnt/nvme_hot -> fast, expensive

   2. /mnt/ssd_cold -> slower, cheaper

2. Tell Postgres "this folder = this tablespace":

   CREATE TABLESPACE hot_ts LOCATION '/mnt/nvme_hot';
   CREATE TABLESPACE cold_ts LOCATION '/mnt/ssd_cold';
   

3. When creating a partition, put it on the right tablespace:

   CREATE TABLE events_2026_07 PARTITION OF events
   FOR VALUES
   FROM ('2026-07-01') TO ('2026-08-01') TABLESPACE hot_ts;
   

4. Now all the files for events_2026_07 live physically on the NVMe disk. When that partition becomes "cold", move it: ALTER TABLE events_2026_07 SET TABLESPACE cold_ts; Postgres will copy the table's files from /mnt/nvme_hot to /mnt/ssd_cold under the hood.

Two honest caveats on this practice: it only works on self-hosted Postgres, managed services like RDS or Cloud SQL don't let we mount disks or define custom tablespaces. And SET TABLESPACE takes an ACCESS EXCLUSIVE lock for the whole copy, so on a big partition that is real downtime.

That's enough about partitioning. In practice, most teams try everything inside a single Postgres cluster before jumping to sharding. Partitioning is already a noticeable step up in complexity, but it still keeps one primary, one backup story, one connection string.

When do I need replicas?

The first obvious reason to add replicas is to reduce read load on the primary. At some point one node is doing too much: it has to handle every write and also every read (dashboards, exports, background jobs, admin tools). With one or more replicas we can send some of the read traffic away from the primary and keep it focused on writes and the most critical queries.

But replicas are not only about load, they are also a basic high availability (HA) strategy. If the primary dies, requests to that node will fail or stall until something promotes a replica and points traffic there. In practice we use extra tools (Patroni, Stolon, cloud control planes) that run on top of Postgres and use a consensus store like etcd or Consul (which internally run algorithms like Raft) to decide which replica becomes the new primary, and to deal with the old primary when it comes back up. This is a sophisticated problem and it isn't worth solving ourselves when the industry already has the standard, or we simply use managed Postgres from a cloud provider.

Of course, replicas come with trade offs. Replication is asynchronous by default, so there is always some lag. If a user writes data and then we immediately read from a replica, they might not see their change yet. So we need to be careful which queries are allowed to hit replicas

• Strong consistency paths (like "show me the order I just created") should read from the primary

• Things that can be a few seconds behind (reports, analytics pages, background jobs) can go to replicas. This is the "read your own writes" problem.

This is where the OLTP / OLAP split appears. OLTP is the normal product traffic: small, focused reads and writes on fresh data. OLAP is the heavy stuff: big scans and aggregations. If we run OLAP queries directly on the primary, they will compete with OLTP and affect the performance. A common pattern is: primary for OLTP, one small replica ready for failover, and maybe one "analytics" replica that serve the requests from dashboards or batch jobs. Even then, replicas still share the same schema and indexes as the primary, so if the analytics workload grows too much we usually push it into a separate OLAP store via CDC.

When do I consider sharding?

If the system is multi-tenant or the data can be grouped by some clear key, we can start thinking of sharding. Sharding basically means instead of one big Postgres cluster, we split the data across multiple databases, usually by a shard key like tenant_id or region.

A simple example: each tenant (or group of tenants) goes to its own database. All the tables look the same, but tenant A lives on shard 1, tenant B lives on shard 2, and so on. From the app side, we either have a small routing layer, or the app itself decides which connection string to use based on the shard key. The main win is isolation: a noisy tenant or a huge customer only overloads its own shard, not everybody else. Storage and write load are also spread across machines instead of hammering a single primary.

One warning on picking the shard key: it should spread the writes. Sharding by time (one shard per day or month) sounds tempting but it's an anti-pattern for write scaling, all new writes hammer the newest shard while the old shards sit idle. Shard by tenant or a hash of some ID, and partition by time within each shard if needed.

Of course, the trade offs are much bigger than partitioning. Now we don't just manage one Postgres cluster, we manage many: more backups, more replicas, more monitoring, more things to upgrade. Queries that need data from multiple shards become more annoying: either we avoid them, or we have to fan out and aggregate in the app or some separate job. Moving tenants between shards and keeping them balanced is also extra work. We trade operational risk for performance.

Because of that, we should only start to seriously consider it when:

• A single well-tuned Postgres cluster (with connection pooling, good indexes, partitioning, and vertical scaling) is still not enough for the write/load pattern, or

• We have a couple of big tenants that clearly deserve their own hardware so they don't hurt everyone else.

When we reach that stage, we should be ready to live with the operational cost of many Postgres clusters.

CDC & ETL to OLAP / other stores

Postgres records every change in its write-ahead log (WAL) before applying it. Physical replication (the replicas above) works by shipping this WAL to standby servers. Change data capture (CDC) reads the same WAL but through a different mechanism, logical decoding, which turns the low-level changes back into a stream of row-level insert/update/delete events. Once people had that stream, they found many uses for it: event driven architecture, the outbox pattern, notifications, and feeding OLAP stores.

We just subscribe to the change log. Tools like Debezium or cloud-native services read the WAL from Postgres and turn it into an event stream of inserts/updates/deletes.

Once we have that stream, we can build an ETL pipeline:

• Extract: listen to the change events coming from Postgres (for example via Debezium into Kafka).

• Transform: clean up or enrich the events in a stream processor or simple workers (cast types, denormalise some fields, join with reference data).

• Load: write the transformed data into a store that is better for analytics: a columnar warehouse, a time-series database, a search index, etc.

The OLTP database stays focused on small, transactional queries. The OLAP side (warehouse, time-series, whatever we pick) gets a copy of the data that is usually a few seconds behind, but is shaped and indexed for heavy reads.

CDC isn't free, the trade offs:

• We add more moving parts. Instead of "app → Postgres → dashboards", now we have app → Postgres → WAL → CDC connector → message bus (Kafka, Kinesis) → ETL jobs → warehouse / OLAP store → dashboard. Every box can break, lag, or need upgrades. Operating this pipeline is a real ongoing cost.

• One subtle risk with CDC is how it uses replication slots. A logical consumer (like Debezium) tells Postgres "keep WAL from this position onward for me", and Postgres will not discard any WAL segments that this slot has not acknowledged yet. If the consumer falls behind badly or is quietly shut down, that slot never advances, so every new write generates more WAL that cannot be cleaned up. Over time those files pile up on disk. In the worst case they fill the volume and the primary cannot write new WAL or commit transactions, effectively taking it down. The practical fix is operational: monitor replication slot lag as a first‑class metric (for example, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) from pg_replication_slots), alert when a slot stops moving, and drop abandoned slots so Postgres can safely recycle old WAL.

• Our system turns from consistent to eventually consistent.

• Schema changes become harder to manage: one change in the Postgres schema needs updates through the whole pipeline.

• We increase the cost of every write: replicating data, writing logs, computation in the ETL pipeline.

• We face the general problems of distributed systems: handling duplicates, retries, backfills, idempotent workflow design, message ordering, reprocessing events after fixing a bug in the ETL (the event sourcing problem).

What about operational concerns?

All of the scaling practices above only work if we can see what is going on in the database and change things safely. The basic concerns are:

Observability

We need at least charts, logs, and alerts around some key metrics: connections, IOPS, CPU, RAM, disk usage, replication lag, and slow queries. With a bare metal Postgres setup, the loop is: running Postgres -> exporter process -> expose HTTP endpoint -> Prometheus periodically calls that endpoint -> stores time series data -> shows charts. Then we set up alert rules like:

• CPU > 80% for 5 minutes, fire alert.

• Free disk < 20%, fire alert.

• Replication lag > 30 seconds for 2 minutes, fire alert...

Managed Postgres does the same thing under the hood, it just hides the exporter + Prometheus bits and gives you "CPU graph + alarms" as a service.

By sending alerts to Slack or PagerDuty, we can detect issues early and start thinking about the scaling practices.

Observability brings up capacity planning. Instead of waiting for "database is down", we watch leading indicators: CPU trend, connection count, table and index size, replication lag, storage usage. When the curves start to bend, we plan changes (new indexes, bigger boxes, replicas, partitioning) before we hit the wall.

Autovacuum and bloat

This deserves its own mention because it's one of the most common ways a real Postgres install falls over at scale.

Postgres uses MVCC (Multi-Version Concurrency Control): an UPDATE or DELETE doesn't remove the old row, it marks it dead and leaves it in place. The dead rows (bloat) are cleaned up later by autovacuum. On a quiet database the defaults are fine. On a write-heavy table, autovacuum can fall behind: tables and indexes grow with dead space, scans get slower, and everything looks like a mystery performance problem even though the indexes are "right".

Two things to do:

• Watch bloat and autovacuum activity (pg_stat_user_tables shows dead tuples and last vacuum times). Tune autovacuum to be more aggressive on hot tables, the default settings are conservative.

• Know that transaction ID wraparound exists: if vacuum is prevented from running long enough (for example by a forgotten long-running transaction or an abandoned replication slot), Postgres will eventually force a shutdown to protect the data. It's rare, but we should monitor for it: watch n_dead_tup in pg_stat_user_tables, and never let transactions or replication slots sit idle for days.

Safe schema changes

A schema change on production can require long locks and downtime, we can't just simply ALTER TABLE. We need a better way: add columns in a compatible way -> backfill in small batches -> only drop old columns when nothing reads them anymore.

We also need to propagate the change through the ETL pipeline if any, which means it isn't just a technical change: we need to align the schema change with other teams, make sure all of them follow the new schema, then drop the old one. This sounds obvious, but in a big company, the communication issue usually leads to more problems than the technical one.

Backup and restore tests

You may have heard the stories of big companies that did backups frequently but never tried to restore, and when they had to, it didn't work and became a disaster. Recently I learned about 2 numbers that indicate a good backup strategy: RPO and RTO.

• RPO (Recovery Point Objective): How much data can we afford to lose? For example 15 minutes, then we need a base backup and WAL archives to have point in time recovery.

  • Point-in-time recovery is about being able to restore your database to an exact moment in the past, not just to when a backup file was taken. In Postgres terms, it works like this in practice:

    • You take a base backup (a full snapshot of the database at some time, e.g. 02:00).

    • You also keep all WAL segments that record every change after that backup.

    • If something bad happens at 10:37 (buggy deploy, DELETE FROM users, disk crash), you:

    • Restore the base backup from 02:00,

    • Then replay WAL forward until just before the bad thing happened, for example 10:36:55. That's called Point-In-Time Recovery (PITR): "give me the database as it looked at 2026-07-08 10:36:55".

• RTO (Recovery Time Objective): How long can we afford to be down while restoring the data? For example, if our RTO is 30 minutes, but a full restore from S3 to a fresh VM already takes 45 minutes, then we need to review our backup plan. One way to know is to actually do a restore from time to time:

  • Take one of our real backups.

  • Spin up a new Postgres instance in a separate environment.

  • Restore the backup and replay WAL to some point in time.

  • Measure how long it takes until the database is ready to accept connections.

That test tells us the real RTO number. If the number is too big, we either change the backup strategy (more frequent base backups, faster storage) or we accept that our RTO is worse than we wanted. For small side projects a daily snapshot is probably fine. For serious systems we almost always need continuous archiving and regular restore drills.

The iterative mindset

Scaling is not a one time project. We measure, notice the potential problem, make the change, measure again and keep going.

Answering a scaling problem requires understanding the problem first. Let me play out how this looks in an Q&A style conversation, because that back-and-forth of questions is the method.

Question: We need to write a billion data points and adding an index will slow the writes. We need to keep the writes scalable, what should we do?

Before answering, I need to question back. What is the time range for that billion data points, per day, per hour, per minute? How big is a record and what is in it? What is the current schema and the existing indexes? And why do we need the new index?

Q: Say it's a billion per day.

That's around 12K writes per second on average. A well-tuned single Postgres with vertical scaling and batched inserts can likely handle it. No exotic architecture needed yet.

Q: What if it's closer to a billion per hour?

Then the next question is: do we need ACID on this data, or can we accept eventual consistency? If we can accept it, put a high throughput message queue like Kafka in front to buffer the spikes, and insert into the database gradually through the queue. If we truly need transactional consistency at that rate, we shard, and the shard key must spread the writes: shard by tenant or a hash of some ID, never by time. Time-based sharding means every new write hammers the newest shard while the others sit idle. Partition by time within each shard instead.

Q: And the index we wanted to add?

What does it's for, OLTP or OLAP? If it's a non-negotiable OLTP index, consider a partial index to reduce the number of rows it covers, and archive or partition old, cold data so the index only covers the current working set. If it's for OLAP queries, we don't optimize heavy aggregations on the OLTP tables, we can use CDC / ETL to push the data into a warehouse or OLAP-friendly store and index it there.

Q: Does the shape of the data change the answer?

Yes. If the data is mostly unstructured documents, we can store it as JSONB in Postgres, but that should be treated as a convenience feature, not a full replacement for a document database. For heavy document workloads, purpose-built engines like MongoDB or Couchbase are usually a better fit.

If the data is structured, ask how it is queried and how many fields are repetitive versus unique per row. A high ratio of repeating values plus heavy analytical queries points to a columnar or warehouse-style store such as ClickHouse, Amazon Redshift, or Google BigQuery, which can compress and scan those columns much more efficiently. Postgres is a great fit for structured, transactional data where we care about ACID; for large unstructured or purely analytical workloads, a document or columnar database will usually be the better primary home.