Scaling Postgres

This week we’ll be looking at something that everyone working in SaaS will deal with sooner or later: Scaling Postgres.

Observability

When people want to scale postgres it’s usually because they’re experiencing some kind of problem. Too much CPU usage, too much RAM usage, disk contention, or slow queries. Before anything you should first determine what problem you’re actually trying to solve. The first thing you’re going to want is to set up observability so you can see what queries are being run, how often they’re being run, and their duration. This can be done either by instrumenting your code with prometheus counters on each query or via OpenTracing traces, or by using a postgres extension like pg_stat_statements. If you’re experiencing CPU problems or slow queries, the first place to look is your queries with the highest total execution time, and see if there’s anything you can do to optimize them. If you’ve got write contention on disk, look at queries that write data ordered by count, see if there’s anything you can do to reduce how much data is being written or how often you write.

Indexes are Good and Bad

Indexes can make or break your database performance, a good index may make it so you don’t even have to look up the full row, while a bad index can be a write amplification attack against yourself and make things run slower than no indexes at all. Always run EXPLAIN against costly queries to know what postgres is using and why.

Rule 1: Index Order Matters

The first mistake I frequently see people making is just putting an index on every field and calling it a day. Indexes are only useful if your application actually uses them, otherwise they’re just an extra thing to update on every write, and unused space in memory. Also, indexes don’t combine (ok, they do, but you generally never want them to, relying on that is a mistake), so indexes on single fields are generally not what you want.

In your standard B2B SaaS, indexes will generally start with organization_id as the first field, as you’ll always be filtering by that field anyway, so having that first limits the section of the index you’re looking at to just the data inside of the relevant customer. This means that large customers won’t be noisy to smaller customers and their query response time is based only on the data inside their own org.

An example of good indexes on users inside of an organization:

(organization_id, created_at, id)
(organization_id, updated_at, id)
(organization_id, name, id)

Having organization_id first filters the btree down to just the users inside of this org, then you have the field you’re sorting on or potentially filtering on, then id as a tie breaker, so if you’re returning data in pages and two users have the same created_at or name, you can always have the same order. This gets harder in B2C SaaS as you don’t generally have an organization to immediately cut data down to a fraction of the database, but even without a top level entry you can generally have something from the domain to filter on (post_id on the comments table, author_id on the posts table).

Rule 2: The Database Should Never Sort

Anything you allow sorting on via ORDER BY should have an associated index. A problem I often see in software is people wanting to allow users to sort by “anything”. This is easy to implement, but impossible to scale. If ORDER BY can iterate over an index just build a buffer of records already in order to return it will only hit the records relevant to the query and return. If it doesn’t it will have to load every record in the entire table that matches the query into memory so it can sort them. Because of this it’s almost always better to have indexes based on your allowed ORDER BYs than your common filters.

Rule 3: Always Use Cursor Pagination

OFFSET-LIMIT based pagination is almost always a mistake in SaaS. The way OFFSET-LIMIT works under the hood is that it will iterate through all records of previous pages until it reaches OFFSET, then it will start collecting records to build the new page. This means that while early pages are cheap, loading page 100 is 100 times as expensive as loading the first page, and 50 times as expensive as loading the second page. If you have millions or billions of records and a user skips to page 10,000 your database will slow to a crawl doing throw away work to find the records for that page.

The actual answer is to do cursor based pagination, which has become much more popular with the rise of “infinite-scroll” style social media. Rather than having individual number pages you return a “cursor” that the user can use to get the next page. As an example for how this works, let’s take a users table in an organization again:

1. You query http://example.com/api/users?sort=name.

This gets the first page of users. The server then takes the last user in the page, converts the name and id to a json string:

{"name":"Steve Steverson","id":"47"}

Then base64 encodes it into eyJuYW1lIjoiU3RldmUgU3RldmVyc29uIiwiaWQiOiI0NyJ9. Then the API returns the result to user:

{
    "data": [...],
    "next": "eyJuYW1lIjoiU3RldmUgU3RldmVyc29uIiwiaWQiOiI0NyJ9",
    "prev": null
}

1. You query the next page http://example.com/api/users?sort=name&after=eyJuYW1lIjoiU3RldmUgU3RldmVyc29uIiwiaWQiOiI0NyJ9.

The server can then decode the cursor and add WHERE name > "Steve Steverson" or (name = "Steve Steverson" AND id > 47) to the query. This allows postgres to go directly to Steve’s entry in the btree index and continue from where it left off, building the next page without iterate through every record of the previous pages.

Rule 4: Partition Early, Partition Often

Sometimes your data won’t fit cleanly into the indexing rules above, but you have a good idea of how to tell the database where to look, this is where partitions come in. The best example I always point to of where partitions work is an events table - it has a large volume of writes, is highly ordered, and is only really queried by timestamp. I’ve had great success creating hourly partitions in an events table, you still get the benefits of indexing by organization first, but the database only needs to look at the relevant partition, greatly limiting the amount of data it has to search through, and the overall index size that needs to be kept in memory for recent partitions. Then when events roll off the back of the time window you can delete the entire partition without needing to rebuild indexes related to any more current data.

Async Results and Caching

A good question to ask of any problematic query is “does this actually need to be synchronous”. If it’s doing a direct update to a single record as the result of the user clicking a button, the answer is probably. Otherwise the answer is probably not, human reaction time is pretty slow by computer standards, and you propagate a result to a lot of places faster than a human could notice.

Rule 5: The Best Work Is the Work You Don’t Do

A problem I ran into years ago was a database where the number one query by volume was a simple auth query that happened on every request coming from a set of automated systems. Pulling the individual auth records each request was incredibly cheap, but we were doing it so often that it was the majority of all load going through the database. The actual solution was to move those requests out of postgres into a distributed redis instance. Any time postgres was updated we had change tracking setup to produce a kafka message that a worker would then use to update redis. This introduced about 100ms of latency where the auth data could be stale, but well below the level where it could cause problems, and instantly cut postgres load in half.

Anything you can move off of postgres by moving it to a cache, do it, that is just less work for postgres.

Read replicas are also just an automatic cache. They will be out of sync for however long it takes replication to work, but if you can put up with a few milliseconds of lag (faster than the user can reload the page), you can basically guarantee that your CPU constraint will only ever be write load on the authoritative instance.

Rule 6: Postgres Is Not for Full Text Search

Likewise, postgres supports full text search. You shouldn’t use it. Not because it’s poorly implemented or inefficient, but rather because there’s rarely a time where you’d need it to be in postgres vs having writes to postgres asynchronously update something like ElasticSearch that’s built for full text search, and that you can offload the load to entirely. There’s certainly an argument for keeping everything in postgres to keep your stack simple, I’ve seen people implement queuing systems entirely inside of postgres, but when you’re running into scale issues any work you can move out you do.

Rule 7: Synchronous Paths Should Have a Fixed Amount of Work

Another problem I’ve encountered is systems where an update cascades to a huge amount of work. This could be from a simple delete that cascades to tens of thousands of linked records, or because you have computed values that must be updated downstream. In one case I worked on a system where we had a series of nodes in an hierarchy of arbitrary depth. You could apply rules to nodes, and any rule that applied to a parent applied to its children as well. This meant that on any update, we had to compute every affected record and cascade the results to them, which could take minutes to process if a node near the root was affected. The solution to scale this was to make the whole process async. When a user updated an individual record, it would process the single update and return the response to the user. Change tracking would then see that a node was modified and kicked off a message to a queue. A worker would then pick up that message, query for all of that node’s children, then update them. Change tracking would see those changes and the process would continue, one layer at a time until all children were resolved. This allowed the user experience to be quick, and would turn everything into short transactions over time, rather than locking all of those rows for a long running transaction.

For bulk deletions, consider having the data model tolerate invalid foreign keys (either skip returning them from the API, or render them with an error message), then you can cascade the deletes as a background task. In the vast majority of cases where the deletes are quick users won’t be able to notice a difference, but when you encounter a massive delete, you won’t block the entire application on a single large transaction.

Limiting Transactions

Postgres can support long running transactions, and high numbers of concurrent transactions, but if you want it to scale, keep them short and don’t lock rows that you don’t need to.

Rule 8: Favor Optimistic Locks Where Possible

Postgres assumes that data contention is rare, and so uses a pessimistic locking model for transactions. This is entirely dependent on your workload, so you will likely encounter situations where holding database locks is less efficient than allowing failures but holding no locks. Take the example of a jobs table in a workflow system, that has an associated jobs_failed_today field in an overview table, where jobs_failed_today is a cached view of data counted from the jobs table. The usual way to update the column would be to, on a failed job:

1. Start a transaction
2. Query the current jobs_failed_today record to lock it from updates concurrent from other failed jobs.
3. Query the current number of failed jobs today.
4. Write the new value and commit.

This gives you a long running transaction, if the value you’re caching is difficult to compute or requires a roundtrip to another system, that row might be locked for human noticeable timeframes. An alternative would be to use optimistic locking. Add an updated_at field to the record in the overview table that contains the database timestamp of the last write. Now using an optimistic lock:

1. Query the current jobs_failed_today record and remember the updated_at timestamp.
2. Query the current number of failed jobs today.
3. Write the new record, with a WHERE updated_at = <original timestamp>.
4. If you wrote zero rows, a concurrent job must have updated it. The optimistic lock failed, try again. Otherwise, success!

This version doesn’t use transactions, or you could use them only for the last step when you already have the information you need and don’t have any expensive operations remaining. This allows you to move data contention off of postgres at the expense of worker CPU for failed jobs, and workers are generally much easier to scale.

Optimistic locks are also interesting in that all you need is an updated_at, version field, or a consistent hash of the original data, which means that you can propagate them down to users. If you’re using read replicas, or even if you just have a high number of concurrent users, you can pass the lock data back up to make sure that edits only ever happen against the latest version of data, which is good for correctness.

Shards are the Secret Ingredient in the Webscale Sauce

If you can avoid sharding and just scale vertically, do it. If your database cannot be measured in Terabytes or higher, you don’t need sharding. But you will eventually run into a database large enough that sharding isn’t optional.

Rule 9: Shard Horizontally

Easiest way to shard is to just declare ranges based on random ids, and send data to the relevant instances. If the shard key is truly random, hot-spots shouldn’t be a huge problem, or can be resolved by adjusting the ranges. The harder part is operational, you’ll need to make your clients aware of the ranges, then any time they’re adjusted you’ll need to do a migration and adjust your clients in sync which will likely be custom tooling.

This also doesn’t even help with most read load. It can help with getting by individual ids, but so can redis, for lists and more complicated queries, you’re potentially hitting every database then combining the results on the client, multiplying the load by the number of shards you’re looking at. If you’re using a large number of read replicas, this might be a fair trade off, because you’re distributing the write load which is often the real bottleneck.

Rule 10: (Don’t) Shard By Organization

Sharding by a root key like organization may be convenient in the short term as you can maintain foreign key relationships and noisy orgs won’t be problematic to other users, but it’s also choosing to intentionally hotspot, and if a single org grows beyond what one DB instance can hold, there isn’t a solution. Still, this can be useful as a strategy if done intentionally, and being able to toggle an entire org to read-only while moving it between shards makes migrations easy, but this would never be the first tool I reach for.

Query Planning and Operational Concerns

Even if you’ve optimized how you’re using postgres, it’s always worth looking at optimizing postgres itself. Postgres has a lot of settings to tune, so it’s worth taking time to understand how they relate to your use case.

Rule 11: The Query Planner Is Only for Queries You Don’t Care About

The postgres query planner is a miracle of modern software, and legitimately one of the most impressive pieces of software I interact with regularly. That having been said, if you’re building your data model, it’s not uncommon that you’ll know better than postgres how you want your data to be queried. As always this is a place where your observability matters, if a query isn’t unacceptably slow and isn’t contributing meaningfully to total query time, the query planner’s output is probably fine, but it’s always worth knowing how to adjust postgres query costs. I can’t give you any exact guidance on what to adjust because it’s going to be different for every use case, but know what output you want the EXPLAIN to have that it does not, and adjust costs until postgres is iterating over your indexes exactly how you it to. Then make sure you didn’t mess up other queries in the process.

Rule 12: Durability is Slow

Sometimes you’re using postgres as a cache because it supports rich queries that other caching systems do not. If that’s the case the easiest thing you can do for scaling is disabling durability if you can trade even a little data loss for speed, a little will buy you a lot. Choosing to queue writes rather than make durability guarantees was the secret sauce that made early MongoDB have such great benchmarks. It’s also the reason for the name of this blog.

Rule 13: Bigger Connection Pools Aren’t Faster

Another mistake I see frequently is when an application is running now, people will increase connection pool size like it’s the CPU or memory request. More is not better, and can often be worse, as you can run into connection limits on postgres and run into even bigger problems. My usual advice is that 10 is a reasonable number if you have no idea what to set it to, then from there, benchmark, benchmark, benchmark. This will generally only be your limit if you have long running queries or transactions, but that’s a problem of your queries, not your connection pool. You can always use something like PgBouncer to get around connection limits, but that should be an intentional choice because you’re using thousands of pods, not because you’re running a dozen pods configured poorly.