Blog — PlanetScalePosts about the PlanetScale platform, MySQL, PostgreSQL, databases, and more.https://siftrss.com/f/y6MV6p117oA2026-09-01T00:00:00.000ZHow one connection kills a databasehttps://planetscale.com/blog/debugging-live-database-connections2026-08-31T00:00:00.000Z2026-08-31T00:00:00.000ZSimeon GriggsThink you could land a DBA job at GitHub?
You've just been paged. Every query is failing to execute. Find out why.
Years ago at GitHub, this was a question Sam Lambert would ask in interviews.
The solution involved a downed database that started with a MySQL schema change needing an exclusive lock on a table, but it couldn't get one. Another session had already touched that table and never committed. That open transaction blocked the schema change.
Once the schema change was waiting, new queries on the same table queued up behind it. Nothing was deadlocked. Nothing would time out on its own. The whole pile-up sat there until someone found the connection at the top of the chain and killed it.
(By the way, you'd avoid this MySQL lock on PlanetScale Vitess since ALTER would have been an online schema change, copying in the background and only taking a brief lock at cutover.)
This isn't rare, and it isn't exclusive to MySQL. You can reproduce the same pile-up on Postgres just as easily.
You won't need elevated permissions. A normal application connection is enough.
All it takes is an unhandled exception and a poorly timed migration.
Say you have a Postgres database with a hot table named orders.
On one connection, a transaction begins a SELECTquery, but the app throws an exception.
-- Connection A: Ordinary application userBEGIN;SELECT * FROM orders WHERE id = 123;-- ...then the app throws an exception 💀
The SELECTquery finished, but Postgres keeps holding the connection, waiting for the transaction to end with COMMIT or ROLLBACK, which it never does because of the app's thrown exception.
While Postgres waits on that connection, another connection attempts to run a migration.
-- Connection B: Migration role waiting on lockALTER TABLE orders ADD COLUMN foo integer;
The SELECT query only needed a read lock (ACCESS SHARE). The migration needs a lock that blocks all other access (ACCESS EXCLUSIVE), so it waits.
Postgres will not let later queries jump the queue. Every new query on orders waits behind the already-stuck migration, which is itself stuck behind the initial SELECT connection that never closed.
-- Connections C, D, E, etc: Waiting on the lockSELECT *FROM ordersWHERE customer_id = ?;
If the app doesn't handle errors gracefully and depends on quick responses from orders, you've got downtime.
Not because of a hack, but because one unhandled exception prevented a COMMIT, kept a connection open, blocked a migration, and that migration blocked everything else.
By the way, this is preventable. It happens because the default for idle_in_transaction_session_timeout is disabled. Give it a value and Postgres times out transactions that haven't closed.
It also depends on your database having spare connection slots. The worst-case scenario is your database is overwhelmed with connections that haven't been closed, meaning even you can't connect to debug and kill them.
Because this experience is so terrible, we've added the ability to view and kill connections in Postgres and MySQL databases on PlanetScale.
PlanetScale's connections tooling uses a reserved administrative connection so it can still show you a list of active connections and processes even when connections are exhausted and your application can't connect.
In the PlanetScale dashboard, you can now click the Connections tab to view a live list of connections. You'll see a bunch of useful columns like Process ID, State, and Duration, but most importantly, you will see a column for Blocked Queries.
From here, it's easy to see which connections are currently blocking others from proceeding. You can click into any connection to see more details, such as the query currently being performed by that connection.
Kill process gives you three options, from least to most disruptive:
Cancel query stops the running statement and leaves the connection open, so the app can send another query
Terminate transaction rolls back the open transaction and closes the connection, but only if that same transaction is still running
Terminate connection drops the backend entirely, whether it is idle or mid-query
Thinking back to the downtime example, the idle SELECT had already finished, so canceling the query will not release the lock. In that scenario you would need to terminate the transaction or the connection.
Over on Vitess, each process has two options: cancel the query or terminate the connection. In a sharded database, you can view the process list per keyspace and shard.
This opens an interactive live view that refreshes about once every second. The most important connections are sorted toward the top. You can navigate through the view using keyboard shortcuts and open any connection to see more details.
If your agents prefer the current state of connections in JSON, use this one-shot command instead of the live view:
pscale branch connections show <database> <branch> --format json
That output includes the IDs needed to cancel a query or terminate a connection, which you can still require confirmation for. See the CLI reference for the kill commands that go with it.
The correct answer to land your dream job today is "Use PlanetScale."
Next time you're paged because everything is erroring, I hope you've already migrated over. The interview answer hasn't changed, but connection management on PlanetScale has made it easier than ever before.
]]>Problems with large tables in Postgreshttps://planetscale.com/blog/dealing-with-large-tables-in-postgres2026-08-25T00:00:00.000Z2026-08-25T00:00:00.000ZSimeon GriggsTell me to stop when I name the largest table in your database: logs, ledger, notifications, feed, events, chonk ... admit it, I got it, right?
Product success leads to lots of data, lots of data leads to big tables, but big tables lead to predictable problems.
A table can be large (many rows), wide (many columns), or "fat" (oversized values). Any of these can cause you problems.
A customer had two tables, A and B. Table B is large and unpartitioned. It has a foreign key to A with cascade delete turned on. A single delete on A has to delete every child row in B. That can be 100s or 1000s of rows in B for every one in A.
In this customer's case, deletions from A ran in a background job and attempted to remove around 100,000 rows from A. The deletes took so long that they timed out. The job retried. Each attempt generated a pile of WAL (the write-ahead log Postgres uses to record every change), which saturated the network and CPU between the primary and its replicas. This caused the replicas to lag.
The application checked replica LSN (a replica's position in that WAL stream) before sending a read to a secondary. The secondaries were not caught up, so every query went to the primary.
The primary was already busy writing WAL. It also had to respond to all of the reads. And so a simple cascade delete on a large table quickly became an outage.
The problems covered in this post are common to all Postgres databases but are made worse by those containing large tables. You might reach for one of a few solutions, which we'll examine throughout.
Partitioning splits a large table based on a key value. For example, if your large table stores rows with a specific date, such as when an event occurred, cutting that table into partitions of months or years creates more, smaller, and more manageable tables.
This can be useful for some large-table problems.
Vertical scaling refers to adding more resources to make more space or brute-forcing your way out of performance bottlenecks such as increasing CPU, RAM, or buying larger and faster disks.
It's almost always a band-aid solution that only hides the real problem.
Sharding (horizontal scaling) splits the large table, or the entire database, into separate database clusters connected by a router to act as one. Just as Vitess is sharded MySQL, Neki is sharded Postgres.
With more, smaller, and isolated database clusters, almost all of your expected large table problems are resolved. More on that later in the post.
You may already know that deleted rows in Postgres aren't automatically removed; they are marked for deletion. These marked rows hold space until the vacuum process reclaims it. Read Every UPDATE leaves a ghost to learn more.
What you might not realize is that vacuum runs per table.
Vacuuming a single large table can tie up a worker for a long time. Vacuum uses a ring buffer so it does not evict the rest of shared_buffers (Postgres's shared page cache), but on a table larger than RAM it still competes with user queries for the same disks.
Large tables take longer to vacuum, and they also wait longer to start.
On Postgres 17, the default trigger is 20% of the table. A table with ~500 million rows needs ~100 million dead tuples before autovacuum begins. You can modify this value cluster-wide or per table by changing autovacuum_vacuum_scale_factor.
Postgres 18 added autovacuum_vacuum_max_threshold with a default value of 100,000,000. So a billion-row table triggered autovacuum at 100 million (still a high number) instead of 200 million. Below 500 million rows, the old scale factor was still in play.
Postgres 19 adds parallel autovacuum, off by default. Extra workers can finish one large table faster, but that still doesn't change when vacuum starts.
Autovacuum also runs ANALYZE, which has the same default-doesn't-scale problem. It samples a fixed number of rows no matter how large the table is. So the planner's estimates potentially get worse as the table grows. Raise the target with ALTER TABLE ... SET STATISTICS on the columns the planner actually needs to get right.
Vacuum on large tables is late to start, slow to finish, with more dead rows while it runs. Each cycle is worse than the last, even when vacuum reports success.
Partitioning can be a good first move to split your large table into more reasonable-size chunks, if it has a suitable key.
Partitioning also helps avoid autovacuum's late start. Each partition is its own smaller heap, so the 20% trigger fires after far fewer dead tuples. Workers vacuum partitions in parallel and ANALYZE samples a smaller relation.
Shape
Rows the formula sees
Default trigger (50 + 0.2 × rows)
One heap, 500 million rows
500,000,000
~100,000,050 dead tuples
Same data as 12 monthly partitions
~41.7 million each
~8.3 million dead tuples per partition
Disks and shared_buffers are still shared with the rest of the cluster, so it's not a perfect solution.
Vertical scaling can help more workers finish sooner with faster disks. That does not change the trigger on one huge heap, and the table may still not fit in RAM.
Sharding (horizontal scaling) gives you the best of both worlds. Smaller tables on smaller databases, each with their own isolated resources.
Sharding also solves foundational parts of Postgres that can't be resolved by more resources.
Every write transaction gets a 32-bit ID, and vacuum uses these IDs (xmin) to decide which row versions are still visible. These IDs are cluster-wide, not per-table. Vacuum freezes old tuples so XIDs can be reused; wraparound is the age of the oldest unfrozen XID (relfrozenxid in pg_class), not how many XIDs the database has ever issued. If that age reaches around 2.1 billion, Postgres goes into read-only mode. Postgres backups under the hood covers transaction wraparound in more detail.
Since each shard is its own cluster, a large table separated across shards is less likely to put a cluster into read-only mode.
Additionally with isolated I/O, any work vacuum does on one shard does not contend with queries on another. ANALYZE becomes a per-shard operation.
Vacuum can be prevented from reclaiming space even if it finishes successfully.
Vacuum can't remove a dead tuple while any transaction in the cluster still needs it.
But a slow vacuum on a large table may have wasted its time to completion. Many of the rows it found to remove may still be required by transactions that started before vacuum finished and haven't completed yet. Vacuum will need to walk the entire table again later.
This can be an issue on tables of any size, but is more annoying on large tables as they take longer to complete.
Free space that was successfully reclaimed can be reused for new rows, but the table file usually does not shrink. That leftover file size is bloat.
Should your table get bloated, PlanetScale Insights scans once a day and opens a recommendation when estimated bloat is over 25% and 100MB.
To compact a bloated table while reads and writes continue, enable pg_squeeze on the database's Clusters page (that requires a restart), then run a one-time squeeze or register the table for regular cleanup.
Be aware that pg_squeeze has no throttle. Squeezing a large table can saturate I/O and CPU and cause an outage. Only run it when the cluster has spare capacity.
Partitioning creates more, smaller heaps. So a wasted pass is cheaper and can be retried sooner. Squeeze is per partition, so the spare disk you need to run squeeze is the size of a partition, not the whole table.
Splitting the table does not split the snapshot horizon. xmin is still cluster-wide, so a long query anywhere on the cluster still pins every partition.
You can drop old or bloated partitions instead of compacting them.
Vertical scaling adds more spare disk space, which may be the only reason squeeze can run at all and/or finish sooner. That lets you compact the same large file faster.
Sharding creates distinct Postgres database clusters, so a long query or dump on one shard doesn't pin vacuum on any other. Squeeze runs only on the copy in its shard, not the entire large table. Each cluster tracks its own xmin.
In a sharded database, wasted vacuum passes are cheaper and less likely to happen.
It is impossible to write a Postgres article about performance and not mention that Postgres has a connection-per-process architecture. Connection hygiene matters even more when your database has a large table.
Sequential scans (reading large segments of the table from start to finish), big sorts, and heavy joins against a large table take longer, so they hold connections longer.
Raising max_connections feels like a logical solution to avoid "too many clients already," but this only lets more concurrent connections perform these slow queries. At best, this starves other workloads of new connections. At worst, a traffic spike on your large table is more likely to trigger an out-of-memory (OOM) event.
A large table will not stay in shared_buffers. More processes scanning it means more cache eviction, more disk usage, slower queries, and longer-held connections.
Partitioning splits your large table, which can help queries finish faster (and release connections sooner) so long as they access only one or a few partitions. But cross-partition queries can get worse.
Buy all the RAM and CPU you like with vertical scaling, but you're still not putting a hundreds-of-GB table into cache. You cannot spend your way out of inefficient connection handling.
Sharding spreads your backends across multiple clusters, where each shard-distinct query is shorter and more likely cached.
A connection held by a large table is one the rest of the product cannot use. With workloads spread across shards there may be less overlap.
A scatter-gather query with no shard key can hold more connections, not fewer. Selecting the right key to shard on is critical to seeing performance benefits from sharding.
Postgres gives you three ways to take a backup. A large table complicates each one in a unique way.
A logical dump with pg_dump writes live rows, so dead tuples stay out of the output. The dump still walks the bloated heap, and it holds a transaction snapshot the whole time. On a small table, that pin is brief. On a large table, the backup is a long-running transaction against the cluster.
A file system backup copies the data directory, bloat and all. Faster than dumping rows. A consistent copy usually means shutting the database down, or relying on atomic snapshots from the file system. You might be okay with a maintenance window for a database of small tables, but a single large table could add hours just to copy one relation.
Continuous archiving copies the data files while Postgres is still running, then keeps the WAL so you can replay through the modified bits and land on a consistent copy. No dump pin, no shutdown. You still copy the large table, bloat, and its indexes. What grows with the table is restore time. How long an incident waits on this heap plus the WAL written while the copy ran.
That third option is how PlanetScale backs up Postgres. We copy the data files and continuously archive WAL on a throwaway node rather than on your primary, so the backup doesn't compete with production queries for disk and doesn't pin the primary.
We can't skip the size of those files. A large table that doesn't fit in memory, plus its indexes, plus its bloat, is what has to land in object storage.
Partitioning rearranges files but doesn't shrink the copy. A pin held during dump is still cluster-wide. A physical backup includes the same bloat even if it is split across smaller tables.
Vertical scaling means faster copies through more resources. But the files aren't any smaller, so restore is only quicker because the disks are faster.
But neither partitioning nor vertical scaling changes the size of the data.
Sharding breaks down backups and restores into smaller, distinct units of work, allowing them to complete much faster. Recovery time depends on the slowest shard instead of the largest table.
A large table isn't viable to scan from start to finish and is likely queried in many different ways.
To keep queries fast, you keep adding indexes to the table. While indexes make queries fast, they aren't free. They take disk, get vacuumed, get backed up, and make every write touch more files.
There are no good options here. Prune indexes and your queries get slower. Accumulate indexes and pay for it with an enlarged heap, while also vacuuming, packing, and copying many access paths.
The indexes that make a large table usable are part of what makes it too large to keep indexing.
Indexes on a partitioned table are created on the logical table but stored and updated on each partition. Those smaller indexes are faster to build and cheaper to REINDEX.
Vertical scaling buys you more space to store more indexes ... but that's like fixing traffic congestion by adding another lane to the freeway.
Sharding creates more, smaller databases, so having more indexes is less of a problem. You can afford to create many more indexes than if your large table was on a single cluster since a write only updates indexes on the shard that owns that row. Sharding is the good option for balancing a lot of data and many indexes.
A table is not only large because it has many rows; it might be that the rows are wide with many columns, or each row contains a large amount of data.
If a row exceeds ~2KB, oversized values go to TOAST, a separate table and indexes next to the heap you are already struggling to vacuum.
Each toasted value gets a 32-bit OID, about 4 billion per table. Updates of toasted values take a new OID. High-churn wide tables fill that space, and inserts slow down as Postgres hunts for a free one before the hard stop.
Whatever is left still has to fit into a single 8KB page, as Postgres will not split a row across pages. If a tuple cannot fit in the free space on any existing page, it gets a new page.
For a read-heavy table, you want pages packed tight (a high fillfactor), so scans walk as few pages as possible. For an update-heavy table, you want the opposite. Lower the fillfactor so an update can often stay on the same page instead of allocating a new one.
On a small table, the wrong fillfactor is a few wasted pages. On a large, wide table it could be terabytes of sparse pages, or a constant stream of new ones that vacuum, WAL, and backups all have to follow.
A large table is a heap, plus indexes, plus TOAST, and some operations still walk those files one at a time. pg_database_size() is one example. It stats every file in the database serially. On a large, wide table, that can pin a core.
Partitioning is a genuinely good solution for this class of large-table problems. Fillfactor can differ per-partition. Each partition gets its own TOAST table, so OID space is per slice.
However, with more files in one cluster, pg_database_size() can get worse.
Vertical scaling can hide a bad fillfactor for a while with extra disk space. Faster disks make TOAST vacuum and file stat()s cheaper. But you cannot buy greater limits than 8KB pages, the 2KB TOAST threshold, or the 4 billion OIDs.
Sharding creates individual Postgres instances, so TOAST OID space resets per cluster. A bad fillfactor hurts less because the heap is smaller. File walks are per shard.
Sharding does not, by itself, let hot and cold rows use different fillfactors. Partitioning still wins there.
Just as partitioning breaks up a single table into many small pieces, sharding distributes the workload of your reads and writes across many database clusters.
Let's revisit the cascade delete story.
When replicas lagged, every read went to the primary. On a sharded database, each shard is its own cluster, with its own WAL and replicas. If the affected rows of A and B lived on one shard, that shard could still lag, and its reads pile onto its primary, but that could not fail the LSN check for the rest of the product. If those 100,000 deletes were spread across shards, the WAL burst would be split. No replica stream would have to replay the whole thing.
Your single-cluster large table likely processes a high throughput of traffic. Vertical scaling can help, but sharding does it better. With more clusters receiving and processing writes, a single table doesn't strain the entire cluster.
Additionally, cluster limits that cannot be solved by partitioning or vertical scaling are resolved. Postgres' hard-coded ceilings of 32-bit XIDs, TOAST OID and more become per-shard limits instead.
Sharded database backups are individually smaller and can be massively parallelized, making database restoration faster as well. Operations finish at the time of the slowest shard, not all at once waiting on large, bloated tables.
Cache eviction is a problem with large tables and their working sets. While more resources won't fit a large table into RAM, you often can per shard. Shard-local queries become shorter and faster.
Neki, sharded Postgres, lets your application write queries as if it were a single database, while splitting a large table into smaller, automatically distributed parts.
It contains the logic for where to send reads and writes. You still have to determine how that data is spread. That fan-out is covered in our post on data topologies.
Sharding shares the large table load across multiple clusters. If that's a problem you need solved, request access to Neki.
]]>The history of Postgres shardinghttps://planetscale.com/blog/the-history-of-postgres-sharding2026-08-24T00:00:00.000Z2026-08-24T00:00:00.000ZJosh BrownOver the past 20 years, many companies have achieved such a scale that their database could no longer support the workload assigned to it. The solution is almost universally to shard the database, but only when absolutely necessary due to the added complexity.
MySQL was a much more popular choice for relational databases in the 2010s and because of this, the tooling around things like online schema changes and sharding progressed much quicker. Tools like gh-ost, Vitess, and more were built around this ecosystem.
Postgres, on the other hand, had one-offs, then Citus, then proxy-like solutions. Now, in 2026, Neki builds on the lessons learned from the incredible engineers that have built not only Vitess, but all the other sharding solutions that came before it.
To fully understand Neki, and why we built it, it's important to have an understanding of how we got here.
One of the more prominent theories is that the term "Shard" originated, of all places, from a video game. Ultima Online is an MMORPG created in 1997. The developers quickly realized they needed to host players on multiple servers to sustain growth.
To support this, they invented lore that would make the idea of multiple servers make sense in the fictional universe. After digging through heaps of lore, "Shards" seemed like the best fit.
At the end of Ultima I, an evil wizard is defeated who had tried to trap the world into a crystal. When the wizard is defeated, his crystal breaks into "shards", each holding a copy of the world within it.
And thus, the term "sharding" was used to denote parallel, independently evolving servers. This term slowly made its way through the industry, narrowing its meaning to database sharding in particular.
The LAMP stack was all the rage in the early 2010s. This gave the sharding story for MySQL a head start, as most of the successful companies had started in an era where MySQL was the popular choice.
Facebook was one of the first companies to truly hit a global scale, serving over 100m active users in 2008 on roughly 1,800 MySQL nodes. Initially, sharding was done at the application level. Each user was assigned an ID that could be used to determine which shard their data lives on.
Eventually, as Facebook grew, their need for a specialized layer on top of MySQL became apparent. Facebook created TAO to accommodate the scale and access patterns that fit their needs.
YouTube, similarly, started as a single primary node. When the load grew too high, they added replicas. When the primary could no longer handle write pressure, they began the monumental task of sharding.
Originally, their solution was also baked into the application layer itself. YouTube's backend would determine which database server a user request should go to. Around 2010, they pulled out the routing layer into a standalone project, and thus Vitess was born.
Many companies that began scaling their LAMP-based stacks slowly consolidated on Vitess as their sharding solution.
It took Postgres much longer to get a similar solution. Many companies that would later hit Postgres scaling limits built in-house solutions. Most of these were never separated into standalone software, often due to how fine tuned they were for each company's needs.
Early Postgres sharding was frequently built as custom, in-house solutions. If a single Postgres node couldn't handle your scale, start spinning up more nodes, create a router, and a team of engineers to manage it.
For companies successful enough to need sharding, dedicating a team to solving database scaling was not an impossible ask. If the database can't scale up, the application will start failing, and growth stops.
In 2007 Skype announced PL/Proxy to the Postgres mailing list. The announced version was Skype's second iteration (v2) of PL/Proxy that ran as an extension on a Postgres Proxy database.
PL/Proxy was one of the earliest sharding solutions, allowing operators to route queries to different shards by defining SQL functions in the proxy database. The downside to this approach was that every operation that would result in a sharded query would need a defined function to route to the proper shard, defined by the user.
For instance, if you wanted to shard a users table, you would need to create PL/Proxy functions to insert, update, and read. For each new query pattern, you would need to create a new function and call it from the application layer.
Although this drastically improved scalability by removing the need for the application to keep track of each downstream shard, the application and database operators would need to work in tandem to ensure every query pattern is defined in the proxy and routed correctly.
Instagram is another early pioneer of Postgres sharding. In 2012, Instagram's ~30 million users could no longer fit onto a single EC2 instance running Postgres.
The solution for Instagram was to do logical sharding at the application layer. The application would map thousands of logical schemas down to a few physical shards. Any given physical database could have multiple logical schemas (collections of tables) within it.
When a given schema grew too large, it could be migrated to a new physical database, and the application would re-map that logical schema to the new database address. This was an elegant solution for their use case, since it required low overhead to manage. This system helped Instagram scale for years.
Instagram had debated going with PL/Proxy, but decided to hand-roll their own solution to keep with a more minimalistic design, specifically not needing to deal with the SQL function overhead, and for reduced network latency. Their application would talk directly to each shard without a proxy.
The downside to this approach, however, is that cross shard queries must be managed in the application layer, and moving data between databases requires careful coordination between the app's mapping of schemas and physical shards. Sharding was also limited to their custom integer ID approach, so every table that needed sharding would also need an ID column.
Citus was the first open-source sharding solution for Postgres that was designed for wider adoption. Designed originally in 2011, Citus started as a fork of Postgres.
Later on in 2016, Citus was refactored into a pure extension of Postgres, rather than a fork. Citus shards data by defining nodes as either coordinators or workers.
Every node is a standalone Postgres instance running the Citus extension. The coordinator node holds the routing map, routes queries as needed, and keeps track of all downstream worker nodes. Worker nodes store the sharded data, and execute the queries passed on to them from the coordinator.
Citus improves on earlier Postgres sharding solutions by keeping the sharding metadata and routing logic within one central node. This adds some undesirable side effects, however. First, the coordinator becomes the bottleneck. Although some queries in Citus 11+ can go through worker nodes, your application either needs to send all requests to the coordinator node, or keep track of multiple connection strings to each worker it wants to distribute load onto.
Another downside is that shard management and backups are a semi-manual process. Adding a shard with more resources for a noisy tenant, or many small shards for a wide shard space requires substantial manual configuration of not only the database servers themselves, but wiring them up together with Citus. Managing backups is also external to Citus, so operators still need to build the proper infrastructure to accommodate disaster recovery and healing nodes as needed.
Other similar attempts have been made to bring sharding to Postgres such as PgDog and Aurora Limitless. PgDog is a spiritual successor to PgCat, both of which improve on Citus's architecture substantially.
PgDog operates as a pure proxy layer in front of Postgres clusters. Although PgDog supports sharding, managing schema changes and resharding operations is still a very involved and manual task. Managing backups and disaster recovery of the Postgres nodes is also up to the operator.
Aurora operates in a similar fashion. Routers front Postgres nodes backed by EBS, with support for sharding.
Other solutions to scaling Postgres resulted in not using real Postgres at all, opting for a Postgres-compatible approach. Google published the Spanner research paper in 2012 while using it internally, later releasing it as a public service in 2017.
Spanner's approach hides sharding from the user and operator altogether, opting for automatic distribution of data across nodes. Instead of defining shard keys or indexes, Spanner splits and moves tables automatically as table size grows.
This is done by storing the data as key-value pairs. These key-value pairs are grouped into "splits" and replicated across the storage layer, using the Paxos consensus algorithm to maintain consistency.
CockroachDB and Yugabyte are similar distributed Postgres-compatible database solutions. Note that they are Postgres-compatible, not actual Postgres solutions. Inevitably this results in lower fine-tune control over the cluster compared to the large amount of settings and flags available for actual Postgres clusters.
Note
Yugabyte and CockroachDB are open source projects. However, Spanner is closed source.
All of these solutions share the same pitfalls. Performance can be disjoint from expected results since the underlying data access layer is not truly Postgres.
Extension support also becomes limited, along with some Postgres native SQL features and settings.
Data placement also becomes hard to predict. Related rows can easily drift across multiple storage locations causing drastically increased latency.
Explicit sharding makes it easy for engineers to understand and plan around cross-shard latency penalties. Automatic sharding blurs these lines, making it impossible to determine when or how a table will shard, and how drastically performance will drop because of it.
The amount of engineering work that went into many of these systems cannot be overstated. As we have seen however, that does not make them perfect. Neki builds on the lessons and foundations from all of its predecessors.
Neki's goal is to raise the bar for scaling Postgres.
While Spanner and CockroachDB shard data automatically, Neki requires explicit sharding. Developers have complete control over exactly how data is distributed across clusters.
Neki also uses true Postgres clusters under the hood. This means support for Postgres extensions, stronger SQL compatibility, and predictable performance.
Compared to PL/Proxy or Citus, sharding is defined by a routing file called the data topology, allowing database operators to define and evolve sharding schemes over time. Since each Neki router only holds a cache of the topology, routers can be horizontally scaled with ease.
One connection string can go to any router, letting your application treat 1,000 shards as one unified database. No single router becomes the bottleneck.
Each Neki shard has its own primary and replicas. If a primary dies, Neki can bring the cluster back to a healthy state. Routers sit in front, connecting all the shards together.
Neki also allows each group of shards to be independently sizeable and scalable, making it possible to precisely control resources for every part of your cluster.
Unlike PgDog or Citus, Neki handles backups, restores, node health, Insights, connection pooling, schema changes, and much more. By being more than just an extension or pure proxy, Neki can manage every part of the database life cycle.
Sharding used to be one of the scariest things a growing team could face. Neki aims to change that, making it easier than ever for teams to reach truly planet-scale with as little friction as possible, the familiarity of real Postgres, and incredible performance. Sign up today for early access at neki.dev.
]]>What is a data topology?https://planetscale.com/blog/what-is-a-data-topology2026-08-17T00:00:00.000Z2026-08-17T00:00:00.000ZHarshit GangalAhmed Darwich“Vitess for PostgreSQL” is a useful shorthand for the ambition behind Neki, but it significantly understates the architecture we have built. Neki carries forward what we learned from Vitess in a new sharding architecture designed around PostgreSQL’s internals and semantics from the start.
PlanetScale has spent years building and operating Vitess at scale. That experience has shaped how we approach query routing, data placement, and the challenge of making many database servers behave like one.
One of Neki’s core design goals is flexibility in data placement. There are many ways engineers need to map their logical database schemas onto physical shards in order to optimize for performance and scalability.
That configuration is expressed as a data topology.
Sharding spreads data across multiple servers, but it also creates a unique routing problem. How does each query find the shard or shards that hold the data it needs?
Without a routing layer, the application would need to keep track of where its data lives and choose the correct shard for each query.
A data topology is how Neki answers that question. It is a JSON configuration that describes a PostgreSQL sharding scheme by mapping logical PostgreSQL tables to groups of physical shards and giving routers the information they need to route queries.
A data topology has three building blocks:
Shard indexes specify the column or expression Neki uses to route a row and how its value is transformed for routing. The selected column or expression is called the shard key, and its transformed value is the routing key that Neki uses to select a shard.
Shard groups define a set of physical shards and assign a range of routing keys to each one. They also specify the default shard index for tables in the group. Related tables that use the same shard key can be colocated, allowing joins and transactions between them to stay local.
Databases bind tables to shard groups, following the PostgreSQL hierarchy of databases, schemas, and tables. A table can name its shard group directly or inherit a default from its schema, its database, or the cluster.
Together, these building blocks tell Neki how to route queries.
The data topology does not provision or manage physical shards. Those shards are provisioned separately, and the topology refers to each one by a stable ID. In Neki, a shard consists of a PostgreSQL primary and its replicas.
The topology names this shard index customer_id_xxhash. It tells Neki to convert each customer_id into a routing key using xxhash.
Neki supports three shard-index strategies:
Hash-based indexes use a hash function to map a value into the routing space. Neki currently uses xxhash.
Modulo-based indexes divide an integer by a configured modulus and use the remainder.
Range-based indexes use an integer value directly, without hashing it.
Each strategy takes a different path from customer_id to a physical shard:
In this example, customer_id is the shard key, and its value is 42. Each strategy transforms its value into a different routing key. xxhash produces 0x2e4fe982b68910ac, which falls in the range assigned to customer-shard-1. modulo calculates 42 mod 4, producing 2, which falls in the range assigned to customer-shard-2. range uses 42 directly, which falls in the range assigned to customer-shard-1. In each case, the shard group selects the physical shard whose range contains the routing key.
The customer_data shard group divides the possible xxhash results between two shards. This example focuses on hash-based routing, but shard groups work similarly with modulo-based and range-based strategies. Key-range boundaries are written as hexadecimal prefixes, so 80 expands to 0x8000000000000000, the midpoint of the routing space:
customer-shard-1: 0x0000000000000000 through 0x7fffffffffffffffcustomer-shard-2: 0x8000000000000000 through 0xffffffffffffffff
For example, these two customer IDs land on different shards because their hashes fall in different ranges.
Neki first hashes the raw customer ID, then finds the range containing the resulting routing key.
Every data topology also names an authoritative group, a single-shard home for database-wide metadata such as sequences and schema information. Queries against customers still use customer_data.
The databases section follows the familiar PostgreSQL hierarchy of databases, schemas, and tables.
Most tables do not need to appear in the data topology. An unlisted table inherits the default shard group from its schema, then its database, then the cluster, allowing a topology to shard an entire database with one default and list only the exceptions.
Put together, the explicit entry for store.public.customers maps it to customer_data, where it uses the customer_id_xxhash shard index and the group’s key ranges.
On one side of a Neki router is the user's application, speaking the PostgreSQL wire protocol. On the other, the application's data can be spread across an ever-growing number of physical shards.
The data topology connects the two, informing the router how data is divided and which shard or shards should receive a query. The result is a live configuration that maps logical databases to physical locations. Neki updates it during resharding, table moves, and imports, all while the application continues serving traffic.
Running PostgreSQL at scale? Request access to Neki.
]]>Concurrency vs. Throughput: why more parallelism can make databases slowerhttps://planetscale.com/blog/concurrency-vs-throughput-vitess-mysql2026-08-07T00:00:00.000Z2026-08-07T00:00:00.000ZLiz van DijkNot long ago we watched a production MySQL database melt down for sixteen minutes.
The errors started as a trickle, a handful per minute, then fed on themselves:
The trigger was fairly mundane: A batch job opened a transaction against a hot table, took row locks, and then held them for fifteen minutes without committing. A common application bug, the kind that eventually sneaks into many large codebases.
What happened around this long transaction is the interesting bit! The queries piling up behind that transaction were mostly not blocked on its locks at all. They were simple reads, where InnoDB didn't need to wait for row locks; it reads a consistent snapshot instead. Building that snapshot means walking back through the version history of every row the open transaction had touched, and that history grew for fifteen straight minutes.
This caused reads that normally took milliseconds to start blowing through their 90-second execution ceilings. The application retried them in a tight loop. Within minutes, more than ten thousand requests were piled up inside the storage engine. Processing each required reconstructing ever-longer version chains, and the resulting page reads outpaced the buffer pool's ability to free memory.
Now requests that had nothing to do with the locked rows, that touched entirely different tables, began failing too. The otherwise correctly sized buffer pool suddenly became too small to serve the crowd of queries.
At PlanetScale, we run our MySQL databases with Vitess, whose configured transaction timeout eventually killed the long-running transaction. Its locks were released, and the sixteen-minute backlog drained in about thirty seconds.
One slow transaction should not take down a database, and much of Vitess's plumbing is built around avoiding exactly this situation. So what went wrong? This was caused by the combination of the single long-running query and the ten thousand requests allowed in after.
Some context on how a seemingly healthy database could end up like this in the first place.
This workload had recently been migrated onto PlanetScale from Cloud SQL, which ran Managed Connection Pooling, a thread-pool-style layer, in front of the database. A thread pool caps how many statements execute at once, commonly around a thousand, and queues the rest. A client arriving when the pool is full waits for a slot, usually for milliseconds, occasionally for a few hundred milliseconds. This cap can protect InnoDB's internals even under extreme load.
On PlanetScale, every MySQL shard sits behind a Vitess proxy called vttablet. These have a transaction pool, which caps how many transactions can be open against MySQL at once. Unlike a thread pool, when this pool fills, requests wait a set amount of time and then fail with an error.
For this workload, that small difference made the issue way worse. Errors propagated up an application stack that had never needed to handle them (since the Cloud SQL thread pool queued requests, pool-full errors effectively didn't exist there).
The solution that was initially tried was to raise the transaction pool cap and increase the timeout.
The pool was increased to ten thousand, a number chosen not by sizing but because it made the errors stop. However, this now limited Vitess's ability to control the backpressure between the application and the database storage engine.
Picture database transactions as items flowing down a conveyor belt (Factorio, anyone?). If they never interacted, throughput would scale in a straight line: twice the items in flight, twice the work done. That is the dream of a perfectly shared-nothing system, but rarely is that achieved. Somewhere on the belt there is a junction where multiple conveyor belts meet. In MySQL, this is the equivalent of a hot row, a latch, a CPU run queue, etc.
Below is a simple playground to visualize how request rate and queuing impact latency. Adjust the ARRIVALS / S slider to see the impact.
Little's Law describes this well. In steady state, N = X * W. Work-in-flight equals throughput times the time each request spends inside the database. Rearranged, X = N / W.
Adding in-flight requests raises throughput only as long as query execution time holds steady. If each new arrival stretches the execution/wait time of all the other queries within the database, the denominator now grows along with the numerator.
γ is the ideal single-request throughput: the linear scaling coefficient if requests never interacted.
α (contention) is the cost of taking turns for a shared resource.
β (coherency) is the cost of requests making each other slower. Put differently, the work a database must do to keep shared state consistent across everything in flight.
Note the multiplier N(N−1), which grows with the number of pairs of requests. When N doubles, this coherency cost roughly quadruples.
Past a critical point, N_max = √((1−α)/β), β dominates, and total throughput actually starts to reverse. Gunther calls it retrograde scaling: beyond the peak, every request you admit requires coordination overhead and makes everything slower.
Let's look at how the problem from earlier maps to this equation. The row locks were α. The transactions that wanted those rows had to take turns, and no amount of concurrency changed the queue's drain rate. β, the real issue, was InnoDB trying to process so many queries at once: ten thousand concurrent snapshot reads, each made more expensive by the version history every other in-flight transaction was generating. The per-request cost rose with the increase in in-flight requests.
This is why focusing on "lock contention" does not explain the issue. The lock is just one of many potential junctions. It is the damage around the junction, the mutual slowdown of everything admitted past it, that scales with the square of concurrency and turns a slow batch job into a wider outage.
To solve this, we implemented the reverse of the change that set this up, in both dimensions at once:
Reduce the Vitess transaction pool size from ten thousand to roughly the thousand the old thread pool used. We now did this consciously and with evidence-backed proof: that workload had run successfully for years with a pool of that size.
Instead of erroring after waiting, a transaction arriving at a full pool now queues for a slot with a longer timeout.
In other words, we configured Vitess's vttablet layer to mimic the old thread pool's behavior.
For this to be safe, two things must be true. The clients have to tolerate periodic waiting during bursts and the wait itself has to be bounded, so that a truly undersized system announces itself with timeouts at the queue rather than accumulating latency forever. Both are true for this workload.
The morning after rolling out this new configuration, it met its first test. A traffic burst arrived on a different Vitess shard of the same database, one that handles an order of magnitude more steady traffic. At peak, the pool handled around twenty-five thousand transaction pool slot requests per second against its thousand-odd available slots. Here are the results, in the same view we opened with:
During the earlier incident, errors climbed to 1,400 a minute and throughput fell to a tenth of what the workload requested.
With the new and improved configuration, the application experienced no disruption and QPS didn't drop.
Over the entire day, database health was much better:
One rejected transaction. Despite slot requests/s at times jumping as high as 40,000, the queue absorbed effectively everything. Waits sat comfortably within the timeout.
Fewer than two hundred statements executing inside MySQL at any instant. InnoDB managed the bursts well. Sixty thousand queries a second through fewer than two hundred concurrent slots is Little's Law again: about three milliseconds apiece.
Lock contention didn't change. The hot-row pattern was just a minor contributor here. We were able to solve the problem without changing the lock contention dynamics.
This allowed the database to do less work at once, allowing it to do more work in total.
Below is another playground to visualize the difference between an open request flow and a gated one. Send bursts of traffic, simulate a locking event, and see the difference between a gated and ungated solution with the buttons below.
This is not a universal recommendation to tightly limit concurrency. Its applicability must be understood in the context of your workload.
Workloads built on pessimistic locking and contended shared state.
Hot rows updated by many workers.
SELECT ... FOR UPDATE on popular keys.
Long transactions holding locks while they do unrelated work.
Counters, balances, job queues.
If that describes your workload, added concurrency might just decrease your throughput!
The principles described here are not MySQL-specific. Postgres can have similar problems, but also a similar solution: PgBouncer's transaction pooling exists to allow for more client concurrency than server concurrency. This is why our Postgres offering comes with a local PgBouncer, and can be configured with dedicated primary and replica PgBouncer instances too.
Backpressure can't always be avoided, so make conscious choices about the expected behavior. The answer is almost never to simply raise a limit out of reach, but to fix what happens when that limit is reached.
Sometimes the fastest thing you can do for a busy system is to simply let less happen at once.
Every 12 hours, a backup system must turn the entire state of a busy database into a consistent, encrypted snapshot, with no impact to production queries.
Such backups are crucially important and simultaneously something that most engineers would rather never have to think about.
Just make the backup work.
Our goal at PlanetScale is to make taking, scheduling, managing, and restoring Postgres and MySQL backups effortless.
Though this is what our customers experience from the outside, achieving this internally requires careful orchestration of cloud infrastructure and DBMS tooling.
It's especially interesting to look at backups for sharded databases, which requires spinning up backup-specific nodes, pulling data from object storage, and WAL replay, all with massive parallelism. These techniques allow for petabyte-scale databases to be backed up in hours, at rates over 50 GB/s.
Here we take a behind-the-curtain look at how to effectively back up a sharded database with massive parallelism.
Here is an example of a Neki (sharded Postgres) database with 8 shards, happily handling hundreds of thousands of queries per second of production traffic.
If sharding is a new concept to you, check out our recent post Making 768 servers look like 1 on how it all works. The first step in taking a backup depends on whether this is the very first backup or if we've taken one previously. We'll start with the steady-state case, which assumes a prior healthy backup already captured and stored in Amazon S3 (or similar object storage in other clouds).
Since a sharded Postgres database is many individual primary Postgres servers working together, we use regular Postgres backups as the building block for large-scale backups with Neki.
There are three ways to take backups in Postgres, which we've written about in detail previously. The best of the three, and the one that Neki uses, is combining filesystem backups with the replay of archived Write-Ahead Log (WAL). To summarize, the steps for this are:
Begin a full backup of the on-disk Postgres database files at time T1
The backup completes at time T2; between T1 and T2, rows on disk may have been mutated
Replay the write-ahead log modifications between T1 and T2 to correct mutated data
Store the final result in a distinct storage location like Amazon S3
We could complete these steps directly on the primary, or perhaps one of the traffic-serving replicas. The problem is that this task utilizes a significant amount of IOPS and compute, especially for a large database. Our goal should be to minimize the impact a backup will have on production query serving.
Because of this, we take a different approach. We spin up a brand new set of EC2 instances, one per shard, to manage the backups on.
These new instances will be responsible for the majority of the work in the backup. Because we operate these sharded databases in clouds like AWS and GCP, dynamically spinning up tens or hundreds of instances for short time slices to complete backups is achievable. It adds a small amount of cost, but is worth it to minimize negative production impact.
The next step is restoring the most recent backup to each shard. Prior backups are stored in object storage. Here we will use Amazon S3 as the example, but the same applies to other clouds (like GCS in Google Cloud). These are streamed directly from here.
This approach requires temporary compute and transfers each shard's data out of and back into object storage. We accept that cost for two important reasons:
Only recent WAL comes from the primary, minimizing production impact
Every cycle proves the previous backup can be restored and replayed
Once all the copying completes, these 8 servers have the exact state of each of the 8 shards from the previous backup, 12 hours ago.
We now must catch up each shard's old backup to match the present state of the database. This requires replaying all changes from the Postgres Write-Ahead Log between 12 hours ago and now.
A naïve approach would be to pull the WAL directly from the primary. This is problematic for several reasons:
This would have nontrivial production impact. Replaying 12 hours of WAL on a high-churn database could take tens of minutes, or even an hour+.
Because we don't want too much server storage consumed by WAL, we continuously archive it to S3. Thus, we likely don't even have the full past 12 hours of WAL resident on the primary node.
On PlanetScale, all Postgres databases use wal-g to continuously archive their write-ahead logs. A sharded Neki database is similar, except each shard has a distinct WAL archive stream.
What if we streamed from there instead?
This almost solves the problem. The remaining problem is that Postgres archives WAL only after a segment is complete. If write traffic doesn't fill a segment sooner, our five-minute archive_timeout setting forces a segment switch so it can be archived. This means the newest changes may not have reached S3 yet. Thus, we take a hybrid approach. The system uses S3 for the majority of the replay, then streams the last ~few minutes of changes directly from the primary. Ideally, this last step takes on the order of seconds, not minutes or hours.
Putting this step together, back in our sharded database:
When every node has caught up replication to time T, where T is the timestamp we will log for the backup time, we stop WAL replication, freezing the point-in-time of the backup. Time T is saved to ensure we know the precise time, down to the second, included in this backup. The full backup is now consistent, with no smeared data. The final step is to encrypt and send these backups off to a new S3 bucket for safe keeping.
When complete, the backup nodes have served their purpose, and are decommissioned.
The cycle just described assumed we had a good backup from 12 hours prior to start with. In the steady-state this is true, but not for a brand new database.
Within 12 hours of a new database creation, pg_basebackup is used to seed a backup node for each shard.
pg_basebackup is a built-in PostgreSQL client utility that takes a physical backup of the entire database cluster: the data directory, tablespaces, and configuration needed for recovery.
Why not upload the pg_basebackup output directly? We use it only to seed the temporary backup nodes. The durable backup is created with wal-g, keeping the format and restore process identical for initial and steady-state backups.
The steps for this first one are similar, but slightly different than the steady-state:
Spin up one new EC2 instance per shard.
Run pg_basebackup on each instance to copy data from its primary.
Configure each instance to replicate from its primary.
Replay WAL until every instance catches up.
Stop replication at time T.
Use wal-g to format, encrypt, and upload each backup to S3.
Once this exists, all future backups happen with the restore -> catch up -> save flow.
Part of the reason we do so much parallelism is due to the nature of sharding. Whether it's 4 shards or 400, since each shard contains its own Postgres primary, we may as well take a rock-solid system (regular Postgres backups) and do it over and over on each shard.
A side-effect of this is that backups are VERY fast, even for large databases.
For every backup, the data transfer steps are:
Restore old backup from S3
Catch up the backup using a hybrid of S3 + the primary
Send the new file back to S3
Consider what it would take to complete this backup cycle on an unsharded 32 terabyte database. Backups on PlanetScale are compressed and stored with encryption at rest in S3. We'll assume that backups for this 32 terabyte database compress down to 20 terabytes. Therefore, we have
20,000 GB transfer from S3
Catch up (Ex: 20 GB, compressed to 10 GB)
20,010 GB back to S3
This equates to a total transfer volume of ~40,030 GB. If our various nodes and interconnects can sustain a 500 MBps transfer rate, this means the backup would take ~22 hours. Crucially, this means that if we want twice-daily backups, multiple backups would overlap. This would prevent us from meeting our recovery point objective (RPO).
This same database in Neki, spread over 8 shards storing ~4 TB each, performs quite differently. In total, we need to transfer, catch up, and store the same 40,030 GB. But if we can do so across 8 distinct backup nodes in parallel, each capable of 500 MBps, we reduce the total time to ~2.8 hours.
This gets better the more shards you add. The same data spread across 32 shards would back up in 42 minutes. Backup speed scales well. 100 terabytes on 100 shards backs up at ~the same speed as 1 terabyte on a single shard.
One reason to take backups is data safety. In the case of accidental data deletion or a one-in-a-million database disaster, backups (and the WAL) are an essential fallback.
But at PlanetScale, backups are also a core part of everyday database operations. For Metal databases specifically (ones with Local NVMe), backups are also used every time the database is resized.
Resizing a Metal database requires:
For every existing node in the sharded database, spin up a brand-new EC2 instance at the new size. In a database with 8 shards each with a primary and 2 replicas, we may currently be running it on 8 x 3 = 24i8g.xlarge nodes. While running, we initialize 24 additional i8g.2xlarges to double the compute capacity.
Each of the 24 new nodes pulls down a copy of the most recent backup from S3, and begins catching up to the present state of the database via the archived WAL.
All nodes are initialized as standbys of the original primary, and complete final replication catch-up.
When all nodes are synchronized with the primary, a switchover is made from the old i8g.xlarge primary to a selected new i8g.2xlarge primary.
The smaller nodes are decommissioned, leaving only the larger primary and replicas.
We've now completed a full resize across all shards. Backups are used to facilitate the creation / catch-up of the new nodes. This process can take anywhere from minutes to hours, depending on how large the backups are and how much WAL there is to replay.
Backups are also used when a node needs to be replaced due to unexpected failure. If your average server lifetime is 5 years, you'll rarely notice a failure on a small database with a single primary. For a database with hundreds of shards, each with multiple replicas, the statistical likelihood for node failure in a given week or month increases significantly. When we see an individual node fail (say, a single replica in a shard), the process for replacement looks like:
Initialize a new cloud instance of the same size as the previously failed one.
Use the process described earlier to restore a recent back-up to it, catch up the WAL.
Initialize as a follower of the original primary, synchronize data.
This new node can now actively serve read queries and/or serve as a new primary during a switchover or failover operation.
Everything here has been about how we complete sharded backups and resizes on Postgres databases, powered by Neki. PlanetScale also operates large-scale sharded MySQL databases, powered by Vitess as our query routing / sharding layer.
The process for backing up and resizing these databases is quite similar! We have a whole separate blog on how this works, but the main difference is that we use VTBackup instead of the Postgres backup builtins, we use the MySQL binary log replication instead of WAL, and replication catchup is done from the primary instead of a hybrid between S3 and primary.
Ultimately, we want all this to be transparent to you. Taking a backup should be as easy as an automated schedule, or clicking a button. Resizing a database should be a single click or API call.
However, we also have a deep appreciation for the finer details of database operations. By taking a journey through the lifecycle of backups, we hope you have a new-found appreciation for how incredible database orchestration is.
]]>Postgres backups under the hoodhttps://planetscale.com/blog/postgres-backups-under-the-hood2026-07-24T00:00:00.000Z2026-07-24T00:00:00.000ZJosh BrownData integrity is one of the most important pillars for a database system. If your database goes down, backups are how you stay safe and recover in case of disaster.
Backups should be taken often, and recovery needs to stay fast. With backups being such a vital part of keeping your data safe, you should know how they work.
pg_dump is a purely logical backup that exports a consistent snapshot of the database, and is the simplest way to back up a Postgres database. At its core, pg_dump reads the current state of the database and writes the data to a given output.
pg_dump can create dumps in either plain text as SQL statements (with SQL queries such as COPY or INSERT), or custom pg_dump formats designed for use with pg_restore.
Regardless of the format used, the data pg_dump outputs is instructions on how to rebuild the data in the database.
-- An abbreviated snippet of output from pg_dump in plain formatCREATE TABLE public.customers ( id bigint NOT NULL, name text NOT NULL, email text NOT NULL, country text NOT NULL, created_at timestamp with time zone NOT NULL);ALTER TABLE public.customers OWNER TO postgres;COPY public.customers (id, name, email, country, created_at) FROM stdin;1 Customer 1 user1@example.com UK 2020-01-02 00:00:00+002 Customer 2 user2@example.com DE 2020-01-03 00:00:00+00...
Reading, formatting, and compressing a few gigabytes is trivial, but in the terabytes+ this becomes increasingly unfeasible. Even if your system can take a successful pg_dump backup at 30TB, it consumes a lot of resources. User queries must contend with pg_dump for CPU and IO, and in worst case scenarios this brings the database to a crawl.
Note
Logical backups lack the ability to do point in time recovery (PITR) since the backup is a static snapshot of data at a given time. However, they are particularly useful for facilitating migrations.
pg_dump connects to the database through a standard SQL connection, only saving the rows and schema inside the database. It does not save physical page layouts, dead tuples, stored index bytes, or cluster-wide roles/tablespaces. This generally keeps outputs from pg_dump smaller compared to other backup methods.
Logical backups leverage Postgres's MVCC support in order to take a consistent backup while the database is online. If you're not familiar with MVCC, read our blog on Postgres MVCC. The section on transaction horizons is particularly important here. When pg_dump starts, it opens a transaction in one of two ways:
-- pg_dump with the default settingsBEGIN;SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY;...-- pg_dump --serializable-deferrable -- Ensures transactions are serializable while the backup is being taken.-- Generally not recommended for backups intended for disaster recovery.-- see the --serializable-deferrable flag for pg_dump-- https://www.postgresql.org/docs/current/app-pgdump.htmlBEGIN;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE; ...
Every transaction that modifies data gets assigned an internal id, referred to as its xid. Rows in the database contain a hidden variable named xmin. xmin informs Postgres of the xid for the transaction that modified that row, while xmax is which transaction deleted, updated or locked that row.
Although pg_dump may not get its own xid, it pins a transaction snapshot of the active xids at that moment, giving the dump a consistent view of the database for its entire duration.
Note
txid is the 8 byte ID used for tracking transactions that modify data. xid is the lower 4 bytes of txid and is stored in tuples on disk as xmin, xmax, or in other variables.
Remember that xids are 32-bit integers used to track the historical version of rows (or tuples, technically) across transactions. Tuples are the underlying representation of a row on disk. 32 bits can only represent a maximum of ~4 billion numbers, so what happens after we have more than 4 billion transactions?
Postgres treats xids as a circular range. Quoting from depesz's article on transaction wraparound:
Let's explain this bit on smaller numbers. Let's assume that we have the whole range of 0 to 10. Only 11 xids. If my current xid is 5, then xids 0..4 are in the past, and 6..10 are in the future. And if current xid is 3? Future is simple: 4..8. But past now contains two ranges: 0..2, and 9..10. Similarly, if current xid is 9, then past xids will be 4..8, and future would be 10, 0..3.
To keep the range circular and xids reusable, Postgres uses a freeze bit. This bit marks a tuple as older than any running transaction or snapshot, and should always be read as an "old" tuple.
When autovacuum runs, it internally calls an internal Postgres function GetOldestNonRemovableTransactionId(). This finds the oldest xid that is assigned to an open transaction, otherwise known as the transaction horizon, and autovacuum will go and freeze tuples "older" than that given xid.
A unique situation arises when the following criteria are met:
A long running open transaction pins the transaction horizon
More than ~2 billion new transactions have started after the long running transaction
If both of these points are met, new transactions start seeing old unfrozen tuples as if they were written by future transactions. This is called a transaction wraparound. At this point, Postgres will refuse new commands that would create xids, triggering read-only mode to ensure no data loss actually occurs.
Since pg_dump holds its snapshot open for the entire duration of the backup, it prevents the transaction horizon from advancing until the backup finishes. On a large database with sufficiently high throughput, pg_dump may inadvertently trigger this phenomenon bringing the cluster into read-only mode.
Above, we can see this in action. Each transaction has its own "future" and "past" based off its own xid. Every time autovacuum runs, it advances the oldest unfrozen xid. The blue section represents active xids that have not yet been frozen.
When the cluster goes into read-only mode in our visual, new transactions would potentially cause data loss. A new transaction would see unfrozen tuples created by the first transaction after pg_dump ran as if they were created by a future transaction that has not yet run.
On very large clusters (upwards of 30TB), this is a valid concern depending on the write throughput to the database. This isn't to say pg_dump is bad by any means. pg_dump is an extremely useful tool. The point here is demonstrating that for taking scheduled backups, pg_dump is most likely not the correct tool.
Another approach is to perform a full file system backup. This is substantially faster than pg_dump for saving and restoring a backup.
A file system backup bypasses the database and directly copies the underlying file system.
In order for file system backups to be usable however, the database must be completely shut down. If the database is online during a backup, rows could be changed after the backup process has started, but before they have been copied, leaving the database in an inconsistent state.
Note
It is possible to take a file system backup online, only if the underlying file system supports atomic file system snapshots. See the Postgres docs on file system backups for more details.
This is explained in more detail in the next section, as this is what continuous archiving solves.
The other drawback to this type of backup (and logical backups) is that changes to data after a backup has been taken are not included in the backup. If data from between "now" and the most recent backup needs to be recovered, it isn't there.
The solution to this is to save the WAL, along with the file system, which is exactly what continuous archiving does.
Offline file system backups are generally a non-starter for production workloads, as no one wants to take down their database for hours to perform a daily backup.
The most powerful (and complicated) backup method is continuous archiving. Continuous archiving combines a file system backup with the write-ahead log (WAL).
This process can be done without downtime and generally lower impact on the CPU of the current system. This is what PlanetScale uses to facilitate Postgres and Neki backups.
The first step of taking a continuous archive backup has already started long before you take your first backup: archiving the WAL.
The WAL is like your database's logbook. Every change, addition, or deletion of the data in your database is written down in the WAL.
Before Postgres makes any changes to data on disk, it first records the changes in the WAL. This ensures that upon a system crash, Postgres can restore to a consistent state by replaying the data mutations saved in its WAL. See the ARIES Wikipedia for more information on the algorithm and reasons behind WAL.
The WAL is by default saved in 16MB segments as binary files, however the size is configurable per cluster. These can be fed to tools such as pg_waldump yielding human readable output:
There is a lot of information here, and remember this is only what pg_waldump is showing us. For understanding how continuous archiving works, there are a few parts of WAL we need to understand.
len (rec/tot): rec is the size in bytes of the specific record data, while tot is the total bytes saved in WAL for this entry.
lsn: The log sequence number (LSN) is the byte address of the given WAL line.
FPW: Full page write (FPW) marks where a full page was written to the WAL. This is what causes rec != tot in the len field.
rel: Tablespace / db / filenode, or more simply put, the location of a table file in a specific database. Tables are represented as filenodes, which are made up of pages, and saved as files on disk. See our MVCC blog for more detail.
blk: The specific page of the filenode that is being updated or saved.
desc: What action is happening. HOT_UPDATE, for example, is when an updated tuple with an associated index (in this case from an UPDATE SQL query) is saved to the same page it was originally on.
By default, Postgres enables a setting called full_page_writes. This setting is necessary for continuous archiving, and is forced on during a file system backup (also known as a base backup). This instructs Postgres to save an entire page to WAL the first time the page is updated after a checkpoint.
If the base backup is taken from a replica or standby node, then the operator must ensure full_page_writes is turned on for the primary. When the backup is taken directly on the primary, pg_basebackup (the common CLI tool for taking continuous archives) will force it on.
Note
Pages are chunks of data within table or index files on disk, usually 8KB per page. Pages contain the underlying tuples and pointers that materialize into rows.
Every WAL line above containing FPW marks a point where Postgres saved the entire page (blk) within a given filenode (rel) to the WAL.
If you look closely at the rel, blk, and FPW on each line, you can see each first update to a given page (rel/blk) after a checkpoint triggers a new full page write. This is the mechanism that makes continuous archiving possible, as we will see later on.
Saving WAL segments often means streaming them to external storage systems such as S3. This can be done via tools such as wal-g, which are built for this exact purpose.
For any Postgres cluster (including Neki shards) running on PlanetScale, every primary has a wal-g sidecar streaming its Postgres WAL to S3.
When we start a backup, we begin by taking an online file system backup. Interestingly, we don't care if writes or mutations cause the file system to be in an inconsistent state, or even corrupted.
The data files we copy over can be thought of as "smeared" data on disk since they have potentially been "smeared" by queries that happened during our backup. We will take a look at exactly why we don't need to worry about smeared data with an example.
The start time is based on a LSN in the log, known as the backup's start_lsn, while the end_lsn is the point in the WAL at which the backup completed.
Say we start taking a backup at some time stamp. As the backup is copying over files, the database receives a SQL statement:
UPDATE users SET email_confirmed=true WHERE id = 314159
where email_confirmed was previously false. At this point, any one of three things could happen after the query executes.
The backup process has already copied the file containing the page for that row where email_confirmed was set to false. This is the best case; our backup is unaffected, and still in a consistent state. We have already copied the file, so we do not care if it is modified after the fact.
The backup has not yet read the page where that row lives, and our backup is now out of sync. The backup started at a time where email_confirmed was false, but it will save that data page while it is true!
The page is backed up at the same instant it was written to, and is now corrupted. The page now contains mangled bits, and later on we will be unable to read its contents properly.
How does Postgres know how to un-smear these data files?
This is where the full_page_writes capability of the WAL comes into play. After we have copied over the smeared file system backup from S3 to a new container for restoring, Postgres looks over the WAL reading every change between start_lsn and end_lsn.
Since full_page_writes was on when Postgres received the UPDATE users ... query mentioned above, it created a new heap tuple on disk as usual, and additionally logged a full image of the entire 8KB page where that row lives to the WAL.
Regardless of the state of that page from the file system backup, whether it is corrupted, in an inconsistent state, or perfectly healthy, we have enough information within the WAL to reconstruct it. The full page image in the WAL can replace the same page that was originally backed up if the ordinary WAL records are not enough to repair it.
Notice that the actual restored backup is not a snapshot of the database when the backup was started (like how pg_dump works), but a consistent version of the database when it finished backing up.
If we tried to restore the database to a point in-between start_lsn and end_lsn we would potentially be left with smeared data. The restore process must read through the full WAL from start to end before the restore can be marked as healthy.
The backups system described above is also what facilitates Postgres' ability to do point-in-time restores.
This is where the "Continuous" part of "Continuous Archiving" comes into play. Since the WAL is continuously saved to S3, we can restore any backup and continuously replay the WAL past our end_lsn up to any arbitrary point in time.
Replaying the WAL is generally more expensive than copying over the file system, however. This is why backups need to happen often, and is why at PlanetScale we back up every cluster every 12 hours by default. This keeps the WAL replay within a reasonable bound (half of a day) and keeps restores fast.
Continuously saving WAL is what also powers cluster resizes. When you resize your cluster on PlanetScale, the following is happening under the hood:
We bring up a new node with the desired specs
Copy over the file system backup (or mount an EBS volume if we are on an EBS backed node)
Replay WAL as close to the current time as possible
Stream remaining changes from the primary
Cutover to new node
In fact, this is the same process we use to take backups in the first place! We use the latest backup taken (usually around 12 hours ago), bring it online, catch it up to the primary, and finally save its file system as a new backup. This is all done on a throwaway node only used to take a new backup, meaning the entire process never contends with the primary for CPU, IO, or network bandwidth.
Even with these techniques in place, extremely large databases can end up with backups that take 12+ hours to complete, resulting in ever growing windows between restore points.
When restore is needed, it could take days to get the new node back online. You can attempt a PITR from your most recent backup, but that could mean waiting substantially longer for WAL to not only stream over, but replay onto a new node.
We wrote previously on how Vitess handles backups at this scale, but what about Postgres?
Find out next week where we discuss distributed Postgres, and how splitting your cluster into shards with Neki can scale your backup process to petabytes and beyond.
]]>Making 768 servers look like 1https://planetscale.com/blog/making-768-servers-look-like-12026-07-15T00:00:00.000Z2026-07-15T00:00:00.000ZBen Dicken
This is 768 servers.
To some, that looks like a lot of computers. To those managing the infrastructure for apps with millions of customers, executing millions of queries per second, pretty normal. Products at this scale frequently require thousands of servers working in unison.
The most difficult infrastructure component to scale is almost always the database. A single database server cannot handle such demand, so we must spread the queries and data out across many servers with database sharding.
Database sharding is the best way to scale a Postgres or MySQL database for anything beyond a few terabytes of data. Let's look at how we go from a small single-node database, to one with a few terabytes spread across four shards, all the way up to one that is sharded across 768 servers and storing a petabyte of data.
To understand why sharding is a necessary part of scaling relational databases, we must understand the bottlenecks of less scalable approaches.
Consider first a simple application architecture.
Most applications you've ever used function in this way, or at least did early in their existence. The software running on a client device connects to an app server over the internet. This app server lives in a data center and handles authentication, page loads, and all the server-side logic for how your application behaves. All the persisted data like user accounts, posts, settings, and messages get stored in and retrieved from the database server (where "database server" is typically Postgres or MySQL, though the focus of this article is Postgres).
Even with a large database servers (10s of CPU cores, 100s of gigabytes of RAM) bottlenecks arise pretty quickly. Typically, it is either CPU constraints due to high query volume, or I/O constraints (IOPS) due to a high volume of reads and writes.
This is summed up nicely by the Universal Scalability Law:
In short, the USL states that resource contention causes scalability to grow sub-linearly with increasing resources, and at a certain point, incoherence causes performance degradation. This is true for Postgres, as with any software system attempting to scale out across many threads or processes on a larger server.
One way to solve this, at least in the short term, is leveraging read-replicas.
In this configuration, you maintain the original server as a primary and add additional replicas as shown above.
The primary sends a continuous stream of messages to every replica to ensure they stay up-to-date with the data changes on the primary. Writes (INSERT, UPDATE, DELETE) can only go to the primary. If writes were allowed to any server, we could end up with conflicting data. Solving this requires complex and slow consensus algorithms, which is possible, but in most cases not ideal for optimal performance.
However, app servers can send read (SELECT) queries to the replicas. Since most apps have a much higher percent of reads compared to writes, this provides a lot more scalability. (Replicas are also necessary for high availability and data durability, even if query traffic does not require them).
It turns out, scaling servers vertically (increasing CPU / RAM) and adding replicas can only take you so far. There are several bottlenecks that cannot be solved in this way
With high enough write volume, no amount of additional read-only replicas will alleviate an issue. Before Postgres can acknowledge a committed write, it must record the change in its write-ahead log (WAL) and flush that log to durable storage. The WAL is a shared resource amongst all connections on the primary. This is essentially a single write bottleneck across your entire database, even if you have tens of replicas.
A replica is a full copy of the primary's data, including all indexes. Adding replicas gives us more places to run reads, but it does not distribute the data.
Backups are an important part of data durability and RPO / RTO guarantees. Taking a backup of a large, monolithic database to object storage can take hours or even days due to the bandwidth limitations of node-to-storage communication. This is unacceptably long for many organizations that rely on frequent and validated backups.
Sharding solves these three bottlenecks by distributing the data and queries across many distinct primaries. For data, it is useful because a single node can only store so much and is limited on write throughput. For queries, this is useful because the network interconnects and CPUs can only process so many queries at a time.
Sharding is useful at all scales past a few terabytes of data. For example, with 2 terabytes of data, we may choose a setup with four shards, each storing 500 gigabytes and handling 1/4th of the total query traffic. When we needed to store a petabyte of data (one million gigabytes), we'd need many more shards. In this case, we can use 256 shards, each with a primary + 2 replicas, and each responsible for storing ~4 terabytes. This requires 256 * 3 = 768 servers!
Without a good system in place, this adds significant complexity to our app's backend. With so much going on, how does the system...
Decide which data goes to which server?
Decide which queries go to which server?
Handle queries that need to talk to multiple shards simultaneously?
Take backups across this spread-out database?
Monitor system-wide health?
Respond to a failing server?
There's a lot that could be said in addressing each one of those concerns. But the question to address here in this article is the following:
How can these 768 servers look like 1 cohesive database to our apps?
We want to allow the application servers to go from interacting with a complex system, like this:
To instead interacting with it over a single connection string, making it appear as if it's interfacing with one large, scalable database:
While in reality, utilizing tens or hundreds of shards. Neki for Postgres and Vitess for MySQL solve this. Let's see how.
The most important amongst several critical pieces here is the proxy layer.
Proxies are middleware servers that sit between two services. In our case, these two services are the application servers and database servers.
Proxies are frequently used with Postgres databases. Even when there's no sharding, they are useful for connection pooling and request queuing. For regular (unsharded) Postgres, PgBouncer is a popular proxy that people use to multiplex 1000s of app connections across fewer direct Postgres connections.
PgBouncer has a simple goal. It's built to accept a large number of connections from many clients and route them through a smaller pool of connections that it continually maintains with Postgres. The query queuing is useful for traffic surges and during database failover, so requests can resume when the new primary comes online. We have a whole blog on PgBouncer if you want to learn more.
Sharding Postgres requires an even more sophisticated proxy. The biggest difference is that, in addition to multiplexing and buffering, the proxy must understand how data is distributed across servers and route SQL queries to the correct shards. Because of this, we refer to it as a router.
When inserting data, the router must be aware of how data is to be distributed. This is known as the sharding strategy.
A common approach is to shard incoming rows based on a hash of an id column. When inserting row like this into the database:
Each of the four shards is assigned a range of IDs that it's responsible for storing, and the router sends the inserts to the correct shard. The insertions first get sent to the router, where it computes a hash of each ID, then forwards it along to the correct shard.
When it comes to reads, some queries are simple enough such that the router passes them along to a single shard.
SELECT email from user where id = 4;
In this case, all the router needs to do is have an internal mapping of which user IDs live in which servers, and forward that query on. Based on the example above, this would be the first (top) shard.
Some cases are more complex.
SELECT email FROM user WHERE id BETWEEN 3 AND 5;
Users with this range of IDs are spread out across several shards. The router must understand the data topology, create a plan for distributing the query to all shards that may contain matching results, aggregate the results back at the router, and send the full result set to the client.
Ultimately, this means the router itself must have a full query parser and routing planner built in.
The router must be able to perform query parsing, planning, connection pooling, and buffering, all within a single system. Complex software is hard to get right.
Every database is unique, with its own schema, tables, and query patterns. How then can a router generically know which data, and which queries, go where?
In both Neki and Vitess, these are specified via JSON files representing the data topology of the system. Vitess' VSchema and Neki's data topology give engineers a ton of flexibility to describe precisely how tables and queries should be distributed. Below is a simplified example of how we would specify a sharding scheme for a user table:
This metadata is stored in the router, and tells it that the user table is sharded on its id column using the user_hash shard index. This user_hash shard index uses the router's built-in value hashing. For each incoming row, it hashed the ID, and uses this to send it to the correct shard to be stored.
Since this is all communicated to the router via text and JSON, AI agents are great for configuration and optimization here.
At a scale of 256 shards spanning 768 servers and millions of queries per second, we cannot route all of this traffic through a single proxy. We need many! Perhaps 10, perhaps 100, depending on the shape of the traffic.
We'd still like our apps to think of this as a single server. This is where a Network Load Balancer (NLB) helps.
NLBs have a simple job: Allow connections via a single host/IP, and assign each connection to one of many destinations. This is how traffic is distributed across the routers. Once assigned, a connection remains with the same proxy for its lifetime.
In some cases, an NLB is not necessary. Eliminating an NLB adds slightly more complexity to the app server's connection logic, as it will have to be aware of each router's host, but eliminates a network hop, keeping round-trip latency to a minimum.
Now all the pieces are in place to make 768 servers storing 1,000 terabytes of data appear as a single, monolithic database to our apps.
An app server is told "connect to the database at mydb.pscale.com"
A DNS lookup is performed, returning the NLB's IP address: 123.152.100.4
The app requests to connect to the database at 123.152.100.4
This routes the connection first through the NLB, then to one of the N proxies
The app begins sending database queries, which go app -> NLB (optional) -> proxy -> shards. The complex routing logic is hidden from the application. (NLB not pictured below, for simplicity)
This example shows scaling up to 1 petabyte, but sharding should begin long before this scale. The precise recommendations depend on each database's size, schema, and QPS, but we recommend sharding Postgres and MySQL for anything beyond a few terabytes of data. That's the point where you typically begin hitting the bottlenecks described earlier: long backups, write bottlenecks, etc. If you are facing challenges scaling relational databases, Neki and Vitess are the solutions.
Vitess for MySQL has been used for over a decade to scale the world's biggest relational databases. We have years of experience operating large, sharded databases for our customers, and are the core maintainers of the Vitess project. Neki was developed by the same expert maintainers of Vitess, bringing an even more powerful sharding system to Postgres.
We've only scratched the surface of everything sharding systems like Neki and Vitess provide. There are so many other interesting details. What's the best way to shard data? How do sharded databases handle failures? How do you change the number of shards? How do you take backups across 256 shards at the same time?
Stay tuned for more here. Follow our RSS feed or on X to stay in the loop.
Happy sharding.
]]>The feedback loops behind Kuberneteshttps://planetscale.com/blog/the-feedback-loops-behind-kubernetes2026-06-16T00:00:00.000Z2026-06-16T00:00:00.000ZFatih ArslanFor the last decade, Kubernetes has been the backdrop to most of my work: operating clusters, helping build hosted Kubernetes, and writing Kubernetes operators. At PlanetScale, that now means running stateful systems like Postgres and MySQL in production. Kubernetes has many faces, but here I want to talk about one face only: why it is so good at running workloads at scale.
People ask me what an operator actually does. The canonical answer is: "it reconciles desired state." This is correct, but it also tells you almost nothing.
An operator is a feedback controller. It's the same closed loop that runs a thermostat or keeps your car at a fixed speed on cruise control. In our case, the thing being controlled is a database. I have been building these loops for years, and the best way I know to make them click is to ignore Kubernetes at the beginning. Kubernetes is full of control theory, even if we don't call it that in the day-to-day.
Before we look at a single line of Kubernetes, we're going to run a production database by hand and slowly let the feedback loop appear on its own. Then we'll map that loop to Kubernetes, with the pieces production needs: a store, watches, queues, retries, and more. At the end, we'll look at what one of these loops looks like in a real operator.
Note
A working understanding of containers and kubectl helps, but you don't need to be a Kubernetes expert. I'll use terms like idempotent, fan-in, and eventual consistency, and introduce the parts that matter as we go.
We're going to start slow and gradually ramp things up. Each part builds on the previous.
Let's start from scratch. I want to run Postgres on a Linux box, and I need it inside a container. To start it, we run:
docker run -d --name pg \ -e POSTGRES_PASSWORD=secret \ postgres:18
That's it. Postgres is running. My app connects to it, writes some rows, and everything works fine. But then the machine goes away: the cloud provider reclaims the instance (hardware fails, or a spot instance gets taken back), or I ship a new version of my setup, which means stopping the old container and starting a fresh one in its place. Either way, the container is replaced, and my data is gone. The container storage was ephemeral, and I did not attach any persistent volume to it.
There is already a gap between what I want (Postgres, running, with my data) and what I have (a container whose storage disappears when the container or node goes away). The rest of this post is about that gap and the machinery we build to close it.
Imagine we have hundreds of nodes (servers) we can use. I already have other workloads running on them. I need to decide which one runs this database. So I ssh into the box that looks the least busy and start the container there.
ssh node-07 'docker run -d --name pg ... postgres:18'
I picked node-07 because it looked idle enough. I start keeping track of it, save it in some sort of config file, and push it to some repo.
Container storage is ephemeral, so I have to attach a real block device. In the cloud this is an EBS volume (e.g. on AWS); on bare metal it's a physical disk. Assuming it's a block device, this is what we usually do: provision the volume, attach it to the node, format it, mount it, and point Postgres' data directory at the mount.
# provision + attach first with cloud CLI, then on the node:mkfs.ext4 /dev/nvme1n1mkdir -p /var/lib/pg-datamount /dev/nvme1n1 /var/lib/pg-datadocker run -d --name pg \ -e POSTGRES_PASSWORD=secret \ -v /var/lib/pg-data:/var/lib/postgresql \ postgres:18
These are a lot of steps, and each one can fail halfway. And if the disk fills up later, Postgres stops accepting writes and we have to resize the volume by hand: first through the cloud provider, then again inside the filesystem.
A single Postgres instance is a single point of failure. We want high availability: one primary and two replicas. These need to be on three different machines, with streaming replication between them. So we do the same steps again, three times, on node-07, node-12, and node-19. I also wire up replication by hand: primary_conninfo, replication slots, all of it.
Now we have three nodes with three Postgres instances. One of the instances is the primary (here it's node-07). But this raises new problems, like what to do if the primary's node dies?
Here is another thing we have to solve. The replicas need to reach the primary, and the primary needs to accept their connections. And every one of these addresses is an IP that changes when a container restarts.
The first thing I do is hard-code the IPs. I write node-07's address into the replicas' config, I list the replicas' addresses in the primary's pg_hba.conf, and I keep a small /etc/hosts table and save it somewhere.
# on each replica's postgresql.auto.conf, until the primary is recreated with a new IPprimary_conninfo = 'host=10.4.7.21 port=5432 user=replicator ...'
But we still have a problem: the first time the primary is recreated with a different IP, the whole cluster falls apart.
Now, this is where we start thinking about how to solve these issues. Everything described so far can break, and will continue to break even if I fix it:
A replica process dies and doesn't come back.
A disk gets full.
The primary fails and a replica has to be promoted.
A config I changed on two nodes but forgot on the third one. They are now out of sync.
Let's assume we've set up a simple uptime monitor and we're going to get paged for all these cases. To avoid getting paged at night, we do the sensible thing: write a script. So we decide to write a loop that wakes up every few seconds, looks at each node, and fixes whatever's wrong.
while true; do for node in node-07 node-12 node-19; do if ! ssh "$node" 'pg_isready -q'; then ssh "$node" 'docker start pg' # it died, bring it back fi usage=$(ssh "$node" "df --output=pcent /var/lib/pg-data | tail -1 | tr -dc 0-9") if [ "$usage" -gt 80 ]; then grow_volume "$node" # disk filling, make it bigger fi done sleep 5done
It's written in Bash, and probably has tons of bugs. You notice something here? The loop doesn't care how the database got into a bad state. Every five seconds it looks at the current state of the world and asks this question: does reality match what I want?
If a process is down, start it. If a disk is filling, grow it. Run the loop once or run it a thousand times and the result is the same, because each action is conditional on the current state. The script is idempotent.
Let's make things a little more complex. I need to raise max_connections from 100 to 500. This one is not a reload-only change. PostgreSQL says it can only be set at server start, so the manual version is to ssh into each box, edit postgresql.conf, restart Postgres, and check that it took on all three.
Because I know that ssh'ing into the nodes manually isn't a thing I want anymore, I do the same thing we did previously: I write the desired value down in one place and teach the loop to enforce it.
WANT_MAX_CONNECTIONS=500for node in node-07 node-12 node-19; do have=$(ssh "$node" "psql -tAc 'show max_connections'") if [ "$have" != "$WANT_MAX_CONNECTIONS" ]; then ssh "$node" "sed -i 's/^max_connections.*/max_connections = $WANT_MAX_CONNECTIONS/' /var/lib/pg-data/postgresql.conf" ssh "$node" "docker restart pg" fidone
This is the same idea as before. I read what I want (a variable). Observe what I have (a query). If they differ, I take an action to close the difference. Again, I don't track whether I changed it last time. All I do is compare and converge, every loop.
I started with a desired state that was written down in one place: three instances, this disk size, max_connections = 500. Every few seconds I observe the actual state of the system. I compute the difference. I take whatever action closes that difference. Then I do it again, forever.
That's a closed feedback loop. The word "closed" matters. It means the output of the system is fed back into the next decision. I don't run docker start and assume the database is fine. I check the database again. If it is still wrong, I act again. If it is already correct, I do nothing.
The nice part is that the same loop works for different problems. It can restart a dead process, grow a disk, or push max_connections = 500. The action changes, but the shape stays the same: read what I want, observe what I have, compare them, act, repeat. If I draw the same thing as a block diagram, with the control theory names added, it would look like this:
Here is how the vocabulary from control theory maps cleanly onto my shell script:
The setpoint is my desired state, the variables at the top of the script (disk size, max_connections and so on).
The measured output is what I observe: pg_isready, df, show max_connections.
The error (e) is the difference between them.
The controller is the body of the loop, the if statements that decide what to do. It is not the whole script.
The actuator is what carries out the action: ssh plus docker start.
The plant is the system being controlled, Postgres and its disk.
That also gives us a nice way to understand open-loop control. My very first attempt, ssh in, run the command, and walk away, was open-loop: fire an action and assume it worked. The Bash script is closed-loop because it keeps feeding the measured state back into the next decision.
A Bash loop is not a production control plane. Just to name a few issues with it:
It has no concurrency control, so two copies of the script can race each other. Imagine both deciding to promote a different replica.
It keeps its only real state, "am I mid-failover?", in a shell variable that could die with the process.
It polls every node every five seconds whether anything changed or not, which is fine for three nodes, but too expensive for three thousand nodes.
It has no idea what to do when the ssh itself times out.
And the moment I want a second kind of resource, a connection pooler, a backup job, a read replica in another region, I'm copy-pasting this whole structure.
What if the script also fails? Who runs it then? We could keep hardening this script, but look at where it goes: we would need a real store for the desired state, watches instead of polling, a work queue, retries, leader election. We would be rebuilding Kubernetes. The real platform already exists, and it's Kubernetes.
Let's go through some of the pieces we built by hand before the watchdog loop. You already know these components by name. What you might not have noticed is that they also work like controllers.
Spinning up the container: the kubelet. First, a quick definition: a Pod is the smallest thing Kubernetes runs, one or more containers scheduled together on a node and sharing its network. For us it's the Postgres container. On every node runs an agent called the kubelet. Its desired state is the set of Pods assigned to its node, which it learns from the API server. Its observed state is the set of containers actually running, which it gets from the container runtime. When they differ, it starts the missing container, kills the extra one, or restarts the crashed one. My if ! pg_isready; then docker start; fi is the kubelet's job, just done properly. The kubelet doesn't shell into anything; it talks to containerd over a gRPC socket, which talks to runc.
Picking a node: the scheduler. Remember me choosing node-07? That's the scheduler's whole reason to exist. It watches for Pods with no node assigned, filters out the nodes that can't work, scores the rest, and writes the decision to one field: pod.Spec.NodeName. The scheduler doesn't start the container; it records the placement and lets the kubelet pick it up. You will realize that most things in Kubernetes are decoupled like this.
Attaching the disk: CSI and the PV/PVC sync. My multi-step mkfs and mount script becomes a PersistentVolumeClaim, which is a declarative request for storage. The Container Storage Interface (CSI) driver turns that request into a real volume. CSI itself is a set of controllers and sidecars: one provisions, one attaches, one resizes, and so on, while the kubelet calls the driver's node plugin to do the actual mount. It's a family of controllers. If there is a PVC but no disk behind it, one controller creates the disk. If the PVC size increases, another controller calls the provider API (e.g., AWS ModifyVolume). Again, I write intent, and a controller does the actual work. (note: I wrote one of the early production CSI drivers, csi-digitalocean, and a long post about building one.)
Making them find each other: the CNI and Services. The /etc/hosts problem is solved at a layer we no longer have to think about. A CNI plugin gives Pods their network identity; Cilium, for example, does this with eBPF instead of a pile of iptables rules. For stateful workloads, a StatefulSet plus a headless Service gives each replica its own stable DNS name, which is exactly what a Postgres replica needs. The hard-coded IP that broke our cluster becomes a name that keeps working. DNS is only one tool here; other service discovery systems like etcd, ZooKeeper, and Consul solve similar problems.
As you see, all the problems we solved with ssh and various scripts are replaced by Kubernetes components and drivers. And these are just a few of them:
All of this so far is useful context, but the part we care about is our watchdog loop, because that's the one we get to write ourselves.
In Kubernetes, our watchdog script is a controller, and the standard way to write one in Go is a library called controller-runtime. At its heart, it's a function with a basic signature:
func (r *Reconciler) Reconcile(ctx context.Context, req reconcile.Request) (reconcile.Result, error) { // req contains a namespace/name. That's it. That's the whole input.}
Notice what is missing here: the function isn't told what changed. There is no diff. It isn't handed the old object and the new object. It isn't given an event type. It gets a key, a namespace and a name, and nothing else. It's minimal by design, because it has to work for many different controllers. The function's job is to fetch the object with that namespace/name, look at the world, and converge to the desired state.
There are two ways to build any closed feedback loop:
Edge-triggered: act on transitions, on events. "The disk crossed 80%." "The Pod was deleted." "The number of replicas increased by 2."
Level-triggered: act on the current state, regardless of how you got there. "The disk is at 85%." "The Pod is missing." "The number of replicas is 3."
My first mental model of controllers, and probably yours at some point, was edge-triggered: listen to a stream of changes, and for each change, try to converge.
The problem is that this is very fragile. In distributed systems, if one component is fragile, the fragility spreads to the rest of the system. Why is edge-triggering fragile? Say your controller is down for thirty seconds. It misses the events from those thirty seconds, and its view of the world is now permanently wrong. If two events arrive out of order, you process them out of order. If an event is delivered twice, you act twice. You're rebuilding your state from a stream of events, and you've inherited all of event sourcing's hard problems.
Here is a very concrete example. Assume you have 1 replica, and you increase it to 3 replicas. Because you have only subscribed to changes, either:
You miss the event (maybe the queue dropped it, or the consumer, your app, dropped it due to a crash or a full buffer).
You receive it twice.
In the first case, you won't be able to self-correct. In the second case, if your handler blindly applies the delta again, you'll end up with 5 replicas (you overshoot), instead of 3.
The level-triggered model fixes all of that. Remember, our shell script never asked "what changed?" It asked "what is true right now?", every five seconds, from scratch. Miss a loop, and the next one catches up. Run the loop twice, and you get the same result. The current state of the world is the only input that matters, and it's always available to read. So in the level-triggered case, our example above becomes this: you read replicas=3, you check the current number of replicas, which is 1, and you increase by 2.
If you miss the event, no one cares. In the next reconcile loop you'll catch it. If your app crashes, it comes back, reads again and detects that it did not increase it yet, increases it.
Events (the edges) are only a hint that it's worth looking again. They tell you when to reconcile, never what to do. The reconcile itself is level-based: it reads the current state (e.g., replicas=3) and drives toward the desired state (e.g., create 2 replicas), ignoring the triggering event completely. That's whyReconcile only gets a key. The framework makes it hard to write edge-triggered logic, on purpose. Edge-triggered logic is how you get a controller that's fragile and permanently wrong after its first hiccup.
Our bash script stumbled into this property by accident, at least for the sake of the example. But the controller-runtime framework gives it to you on purpose. It's why a Kubernetes controller can crash, get restarted ten minutes later, and converge correctly with no special recovery code. There is no recovery code. There is just the loop. The controller can reconstruct the world from scratch.
So where do the edges come from? And what stops a controller from DDoSing the API server by listing everything every five seconds like my script did?
The answer is the informer. An informer opens a single watch against the API server for a given resource type, streams every add, update, and delete, and keeps a complete in-memory cache of the objects we're interested in. Two things matter here:
First, the informer turns each watch event into a key and puts it on a work queue. The queue does a lot of work for you.
It coalesces: if the same object is updated five times before you get to it, you reconcile it once, against the latest state (level-triggered again).
It rate-limits: an object that keeps erroring backs off exponentially instead of spinning. This is damping, the same reason a crash-looping container backs off instead of restarting hot.
It lets you run a pool of workers pulling keys in parallel, which is your fan-out. Events fan in from the watch, collapse in the queue, and fan out to the workers. This is something you need to tune. The higher you set the pool, the more pressure you put on the system: more writes, more API calls, more load on the provider, and more CPU usage in the operator.
Second, and this is a detail that bites people a lot: your reads and your writes in Kubernetes don't go to the same place.
In controller-runtime, the client you're handed reads from the informer's local cache. Cache reads are cheap, they don't touch the API server, and that's how a controller reconciles thousands of objects without falling over. But your writes go straight to the API server. The cache only learns about your write when the resulting watch event comes back around, a moment later.
Because of that, a read can be stale. You need to be prepared for this.
If you write a field of an object and then read the same object again from the cache, the reconciler might think it's not updated yet. You write again, and you get a Conflict error. Retrying with a fresh read can be fine, but blindly retrying against the same stale cached view just spins.
Most of the time, what you want is to drop the call and requeue. In the next reconcile, the GET will see the updated object, and your write will never happen. That's how everything self-converges.
Here is another edge case. Picture this sequence inside a reconcile:
// I want N replicas. I see fewer, so I create the missing ones.existing, _ := r.listChildPods(ctx) // reads the CACHEfor i := len(existing); i < desired; i++ { r.client.Create(ctx, newPod(i)) // writes the API SERVER}
Now an event fires again a second later, before the cache has caught up with the Pods you just created. You list from the cache, and the new Pods aren't there yet. Your code decides it still needs to create them, and you create duplicates. This is the classic stale-cache double-create, and it's nasty because it only shows up under timing you can't reproduce on your laptop.
There are two ways out. The correct one is the expectations pattern, the same trick the built-in ReplicaSet controller uses: you record that you expect to see N creations in memory, and you don't act again until the cache has caught up to your own writes. It works, but it's not easy to implement and it's a fair amount of machinery. Read more on Ahmet's blog.
The pragmatic one, which a lot of people use, is to bypass the cache for the reads where a stale view would cause a double-create or double-delete, and go straight to the API server:
// The cached client can be stale right after our own writes, which// would make us miscount and create duplicates. For this one read,// go direct to the API server instead of the cache. Slower,// but consistent for this decision.err := r.apiReader.List(ctx, &instances, client.InNamespace(ns), labelSelector)
This is not only a Kubernetes issue. In any system with a read cache and a write-through path, read-after-write is not consistent unless you make it so. Most of the time the cache is exactly what you want: cheap, local, and eventually consistent. Eventual consistency is fine because the loop runs again. But the moment a decision would be destructive or non-idempotent if you acted on a stale read, you need to know which path you're on. Kubernetes solves many hard problems, but it also gives you a few new ones.
Back to the control diagram. My script kept its setpoint in shell variables and its measured state in the output of df and psql. Kubernetes gives both a permanent home, on the object itself.
.spec is the setpoint, the desired state. It's owned by whoever created the object (a human, or another controller), and the reconciler treats it as read-only intent. It's an anti-pattern to write to the .spec from inside the controller. If you do it, stop reading, go and fix your codebase. There are only a handful of exceptions, but a controller should generally never set its own setpoint.
.status is the measured variable, the observed state. It's owned by the controller, written through a separate status subresource, and it's where you record what's actually true. The better the status, the better the controller can decide. A good .status field is what makes a controller pleasant to operate. The word observability comes from control theory; Kalman coined it around 1960 to ask whether you can infer a system's internal state from its outputs. .status is also your response to any third-party system. If someone wants to learn the outcome of your actions, .status is the place to look at.
That split is the whole declarative model in two fields. It comes with a piece of bookkeeping that's pure control theory: .metadata.generation increments when desired state changes, and by convention the controller writes back .status.observedGeneration to say "the state I'm reporting reflects this version of your intent."
When observedGeneration < generation, the status you're looking at does not reflect the latest setpoint yet. That one comparison is how you tell "converged" from "still working on it."
This is why the reconcile is stateless, and why that matters. Our shell script kept "am I mid-failover?" in a variable that died with the process. A Kubernetes controller keeps nothing important in memory. Every fact it needs is on an API object: the spec it's driving toward, the status it last observed, the conditions describing where things stand. Kill the controller, restart it on another node, and it picks up exactly where it left off, not because it saved its progress, but because there was never any in-memory progress to lose. The state lives in the cluster (API server, etcd is what holds the state). The controller is just the loop that reads it.
When a controller creates a child object (a Pod, a PVC), it stamps an ownerReference on the child pointing back at the parent. That reference does two things. It sets up garbage collection: delete the parent, and Kubernetes can cascade the delete to its children. And it gives the controller a way to map child changes back to the parent: "when any object I own changes, enqueue my parent for a reconcile." ownerReference allows you to link controllers to each other and create chains. If done right, all your controllers and systems fit together.
Here is an example. Follow the loop:
A node dies and takes a Pod with it.
The Pod's deletion is a watch event, an edge.
Through the ownership link, that edge becomes a reconcile request for the parent.
The parent reconciles, observes its children (level-triggered), sees one is missing and the count is below the setpoint, and creates a replacement.
The replacement is an unscheduled Pod, an edge for the scheduler.
The scheduler detects the unscheduled Pod, assigns a node.
The kubelet gets triggered because that's an edge for that node's kubelet and it starts the container.
That's multiple feedback loops, each watching the layer below, each reacting to an edge and converging to its own level, chained together through the API server with nobody orchestrating the whole thing.
Control theory has a name for loops stacked like this: cascade control. The output of an outer loop becomes the setpoint of an inner loop. A controller never writes its own .spec, but it writes other objects' .spec all the time. My operator writes the PVC's spec, and that spec is the setpoint the CSI controllers converge to. Each loop worries only about its own layer and trusts the loop below.
So we wrote if ! pg_isready; then docker start; fi and maybe thought we're good. Kubernetes turns that one line into several independent controllers that have never heard of each other, but still cooperate because they share the API server and watch each other's objects. I like this part a lot. Nobody calls a central orchestrator. Nobody passes a private message. The system heals itself.
Up to here I've been a little vague about the "measure" step, because in the examples the measured state is just "list the child Pods." But in a real database operator it's a lot more than that.
When an operator I work on reconciles a single Postgres instance, the first thing it does, before it decides anything, is build a snapshot of reality from every source that knows something true about that instance. Not just Kubernetes. Kubernetes barely knows anything about whether Postgres is actually healthy.
The sources gathered at the top of every reconcile:
The Kubernetes cache: the Pod, its PVC, the PV behind it, the Node it's on, the ConfigMap holding its config. These are the cheap local reads, the stuff we already talked about.
The database's effective configuration. Not what we last wrote down, but what the server has actually loaded, so we can compare the two and detect drift. Other entities can rewrite or reload the config on disk without us knowing, so the only honest source of truth is the running server itself, never our last write.
The database's own view of its health. Its role, whether it's healthy, how far behind its followers are, whether it's currently accepting writes. Some of this comes from the agents that sit next to the database and manage it; some we get by opening a connection and asking the database directly. These calls carry a tight timeout and are allowed to fail, more on that below.
A background collector. Some signals are too expensive or too rate-limited to fetch on every reconcile: disk usage, or whether a volume operation we kicked off earlier is still in flight and where it sits in its cooldown window. A separate collector, often a background goroutine, gathers these on a slow cadence and keeps the last value per volume in memory. The reconcile reads that value instantly, without blocking on anything. Think of these as custom workqueues you implement.
In code, the snapshot is just a struct, and the reconcile's first move is to populate it. This is simplified, but faithful to the real shape:
// The observation snapshot: everything we know about this instance, right now.type reconcileHandler struct { // The object (.spec = setpoint, .status = measured). instance *v1.PostgresInstance // Kubernetes objects. pod *corev1.Pod pvc *corev1.PersistentVolumeClaim node *corev1.Node // Database state. dbState DatabaseState // What Postgres actually loaded, not what we last wrote. effectiveConfig map[string]string // Collected out-of-band. diskUsage *resource.Quantity // The volume operation already in flight, if any. storageOp *StorageOperation}func (r *Reconciler) newReconcileHandler( ctx context.Context, inst *v1.PostgresInstance,) (*reconcileHandler, error) { h := &reconcileHandler{instance: inst} // Cheap local reads. h.pod, h.pvc, h.node = r.fetchKubeObjects(ctx, inst) // Active database calls. h.dbState = r.queryDatabase(ctx, h.pod) h.effectiveConfig = r.readEffectiveConfig(ctx, h.pod) // Values from the collector/metric. h.diskUsage = r.collector.Usage(h.pvc) h.storageOp = r.collector.InFlightOp(h.pvc) return h, nil}
A few things about this are deliberate, and only look obvious after you've been burned once or twice.
Gather once, at the top. Every sub-decision in the reconcile reads from this one snapshot. We don't re-query the database in the middle of the loop, or read the disk usage again three functions deep. If we did, different parts of the same reconcile could see different versions of reality. This sounds like a small detail, but it changes the whole design.
For example, the database might be the leader when we check at the top and a replica by the time another helper checks again. Then you get decisions that are individually reasonable, but wrong together. We have a rule in the codebase against stashing state back onto this handler mid-reconcile to pass between steps, because it reintroduces exactly the inconsistency we gathered the snapshot to avoid. Making the reconcileHandler immutable is one way to enforce that rule in the type system instead of relying on code review.
Partial failures are tolerated. Reaching the database can fail while the Kubernetes reads succeed. That's not always an error that aborts the reconcile. It's a measured fact: "Postgres is currently unreachable." That itself is something to record in status. A control loop that gives up entirely whenever one sensor is unavailable is a control loop that's down a lot. We degrade instead. Think of a car. If the rain sensor for the wipers is broken, the whole car doesn't stop. You can still drive, but you need to turn on a few things yourself.
Once the data snapshot exists, the reconcile is a sequence of small, idempotent steps, each comparing one slice of desired against observed and acting to close the gap:
func (r *reconcileHandler) reconcile(ctx context.Context) (reconcile.Result, error) { var rb results.Builder rb.Merge(r.reconcileConfigMap(ctx)) // push desired config rb.Merge(r.reconcileDatabase(ctx)) // reload/restart if params drifted rb.Merge(r.reconcilePVC(ctx)) // grow the disk if needed rb.Merge(r.reconcilePod(ctx)) // create/replace the Pod rb.Merge(r.reconcileStatus(ctx)) // always last: write what we observed return rb.Result()}
Status is written last on purpose, because it's the measured variable: you record what's true after you've taken your actions and observed the result. Again, in our operators, it's not possible to write the status mid-reconcile.
Each step is independently idempotent. Each returns a result, either "I'm done" or "requeue me in 30 seconds, I'm waiting on something," and the results merge. It reads almost exactly like the body of my shell loop. The difference is that "observe the state" grew from df and pg_isready into a fan-in across multiple systems, and "take an action" grew from ssh into typed, conflict-aware API writes.
This is the operator. The kubelet, the scheduler, CSI, and CNI are infrastructure we get by using Kubernetes. This loop, with its messy real-world observe step, is the part we actually write and deal with. Because we know how the underlying system works, we can design it without treating Kubernetes like a black box.
There's one more piece, and it lets me close a loop from Part 1 that I left deliberately: the disk-usage check.
My shell script polled df on every node every five seconds. For three nodes, fine. For thousands of databases, you can't reconcile every one of them every few seconds just to check a number that rarely changes; you'd spend all your CPU re-deriving "still at 40%, still at 40%, still at 40%." This is the level-triggered model's one real cost: re-checking everything is correct, but it isn't free.
The fix is to add a sensor that emits its own edges. A background collector polls our metrics pipeline for disk usage on a slow cadence, keeps the last value per volume in memory, and only emits an event when usage crosses a threshold, not while it sits above or below one:
// Edge detection. We fire only on the transition across the threshold,// not every cycle we happen to be above it. Hovering at 81% is silent;// crossing 80% upward is an event.crossedUp := previousUsage < pvc.GrowThreshold && usage >= pvc.GrowThresholdif crossedUp { // -> generic event -> work queue -> reconcile relay.Send(Event{Key: pvc.Key})}
That event goes into the same work queue as the API watch events and triggers a normal reconcile of the affected instance. Same rule as before: the event wakes us up, the reconcile decides from the current state.
For example, say we have a 10GiB disk and it's using 8GiB. The collector saw it cross the threshold, so it wakes the reconciler. The reconciler reads the current usage, sees that it crossed the 80% threshold, and sets a new size on the PVC. After that, CSI handles the rest.
And because edges can be missed (the collector could be down, an event could be dropped from a full channel), there's a resyncer: a periodic timer that enqueues every object for reconcile every minute or so, regardless of events. It's the safety net. It's our sleep 5 loop. There's also RequeueAfter, which a reconcile returns to say "wake me again in 30 seconds," the controller's way of polling a slow external operation without holding a worker.
There are two more questions: how often should the loop run, and who is allowed to run it? Control theory calls the first one the sampling interval. The rule of thumb: act faster than the thing you're tracking changes, but not faster than it can respond. Reconciling a disk that fills over hours every few milliseconds just burns CPU to learn the same thing again.
So the operator puts boundaries around it.
A coalescing delay handles noisy edge events: a burst of events for one object becomes one reconcile (think of it like a fan-in), not a thousand.
The resyncer is the safety net: every object gets looked at once in a while, even when nothing fires.
And leader election answers the who: only one copy of the operator runs the loop at a time. Two controllers writing to the same database object is not "more reliable." Even with idempotent controllers, they'll be requeueing due to conflicts and consuming unnecessary compute. In theory, a perfectly written controller should tolerate this. In practice, software is rarely perfect, and the safer boundary is worth it.
To close out Part 2, let me redraw the control loop again. The diagram in Part 1 had a few basic boxes. Now, the same loop represents a closed feedback loop more realistically:
There is one new arrow in this diagram: disturbances. A controller has two jobs. The first is setpoint tracking: someone edits the .spec, and the loop chases the new intent. The second is disturbance rejection: the world changes on its own. A node dies, a customer starts a bulk import, someone deletes a Pod by hand. The level-triggered reconcile treats both the same way: it only sees the gap.
Our controller doesn't always touch Postgres directly. Sometimes it writes a PVC and lets CSI do the storage work. Sometimes it creates a Pod and lets the scheduler and kubelet do their part. This is what a production operator looks like: one loop we write, surrounded by other loops we don't write.
Notice that every decision in this loop has been binary: start the Pod or don't, grow the disk or don't, rewrite the config or don't. That's an on/off controller, and it covers most of what an operator does. But not every question is yes/no; once the answer becomes how much rather than whether, you need a controller with memory and a sense of trend: how long you've been off, and how fast it's changing. That's a separate post.
All of this works, and most of the time it runs without anyone watching it. But the abstractions still leak, and they usually leak at a bad time.
Eventual consistency and the split between cache reads and API writes mean that a freshly-created object might not be visible to the thing that just created it. When something goes wrong, we're debugging Kubernetes objects, database state, metrics, volume operations, and sometimes the cloud provider at the same time. The bug is usually not in one clean place.
The declarative model is wonderful until it meets an operation that's inherently imperative and stateful, like a failover, a major-version upgrade, or a data migration. Then you have to turn a blocking, non-idempotent action into an idempotent one. That's a whole other blog post.
That complexity is easy to underestimate. If you're not dealing with sophisticated systems, if you can sacrifice availability, or if you don't care about scalability, maybe all this machinery isn't needed at all. Operators do not remove complexity. They move it into code someone has to understand.
I still think it's worth it. For running thousands of databases that have to heal themselves without anyone watching, I don't know a better alternative. The hard parts are hard because the problem is hard, not because Kubernetes made it hard.
Kubernetes is not only a container runtime. It's not only a YAML processor, or an orchestrator, or whatever word we use that year. For me, the useful way to read Kubernetes is this: Kubernetes is a framework for feedback controllers, plus a consistent store to hold their setpoints and a shared event bus to wake them up.
Once you see that, the rest fits together. The kubelet, the scheduler, CSI, and your operator all read and write facts onto shared objects, and each one tries to move its own small part of the system toward the desired state. The core idea is still the same one we started with: write down what you want, look at what exists, make the next change, and repeat. Events wake the loop up, but the current state decides what happens.
Kubernetes didn't invent these ideas; a thermostat had them long before us. The mapping to control theory is not perfect, and some boundaries are fuzzy. But the core idea holds. We are writing feedback loops in Go and applying them to databases. Mechanical and electrical engineers figured out how to build stable, long-running systems before us. Software engineering is still catching up, and Kubernetes gives us a practical way to use those ideas in production.
]]>See what your database is doing right now with Connectionshttps://planetscale.com/blog/see-what-your-database-is-doing-right-now2026-06-15T00:00:00.000Z2026-06-15T00:00:00.000ZBrett WarminskiMuch of database debugging eventually turns into carefully inspecting what each connection is doing. In Postgres, this means watching pg_stat_activity in a loop. In Vitess, it means watching SHOW FULL PROCESSLIST the same way.
Tools like Query Insights are useful for exploring the recent history of queries. They can tell you what was slow, what's consuming resources, and where to spend tuning effort.
But during an active incident, the questions are more immediate. What's happening this second? Did the last thing I changed fix it?
Here's a manual version of this workflow in Postgres:
SELECT pid, state, wait_event_type, wait_event, now() - xact_start AS tx_age, pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS queryFROM pg_stat_activityWHERE state <> 'idle'ORDER BY tx_age DESC;
Run it over and over again in a terminal and it's a pretty effective view of the database.
It's also a rough interface.
You're scanning rows as they move around, trying to reconstruct what's blocking progress, and hunting for the one detail that actually matters for the fix.
The worst version of this problem is when you can't connect at all because the database has exhausted all of its connections. You can't fix what you can't connect to.
That workflow shaped the design of Connections, a new feature of the pscale CLI available today for PlanetScale Postgres and Vitess (MySQL) databases.
Here's that same debugging flow using the new pscale branch connections top functionality with a Postgres database, instead of pasting that pg_stat_activity query in a loop and comparing output:
pscale branch connections top <database> <branch>
Connections opens an interactive live view that refreshes about once a second and sorts the sessions most likely to matter toward the top. There are keyboard shortcuts to navigate the list of connections and inspect each one in more detail.
Columns in the list include the Process ID (PID), status, number of blocked queries, why they're waiting, and more.
Say your writes are backing up and the app is timing out. In this example, an idle transaction from checkout-api is holding up three other writes. Open the row, and the blocker tree shows the queue behind it:
From there you can decide whether the right fix is to cancel a query or terminate the connection. You no longer need to remember the syntax of pg_stat_activity, retrace the blocker chain by hand or copy and paste PIDs around.
Another problem with running that query in a loop is that the interesting moment flies by. Connections keeps a recent rolling history, so you can pause, step forward and backward with [ and ], and see how the state has changed.
You can also capture a session to a file. You can record everything you see in Connections by pressing C. This includes the recent history already buffered in memory and keeps appending from there. Perfect for handing off logs to agents to assist with debugging.
That also makes it easier to write a postmortem, share what happened with a teammate, or replay the same view later instead of describing it from memory.
The PlanetScale CLI's new Connections feature also works with Vitess databases (MySQL). In this case, the live view is the PlanetScale version of watching SHOW FULL PROCESSLIST, with the ability to cancel the current query or terminate the connection from this unified interface.
The main difference is scope. Vitess connections are shown for one keyspace (and one shard) at a time. If a branch has multiple keyspaces, or a sharded keyspace, pass --keyspace and --shard to choose the tablet:
pscale branch connections top <database> <branch> --keyspace <keyspace> --shard <shard>
The same live monitoring, pause, history, capture, and replay workflow applies. The actions are MySQL-specific: canceling a query runs KILL QUERY, and terminating a connection runs KILL. See the Inspect live Vitess connections guide for the full command behavior.
Try it next time you need to troubleshoot active database connections.
]]>Egress problems and where to find themhttps://planetscale.com/blog/database-egress2026-05-14T00:00:00.000Z2026-05-14T00:00:00.000ZSimeon GriggsName something in recent history that got better and cheaper (other than the TVs at the entrance of Costco). I'll wait.
Better performance and lower costs rarely come together, but optimizing your queries to reduce egress gives you both.
So once you hit scale, or ideally before scale bites you, improving the efficiency of your queries by making the responses smaller and their frequency lower can pull off a rare double: make your application faster and cheaper.
Egress: Data transferred out from your database over the public internet. Most cloud providers bill for this, so it's something we want to minimize.
Ingress: Data transferred into your database over the public internet. Most cloud providers either do not bill for this, or do so only in specific scenarios.
PlanetScale includes 100GB of egress on High Availability (HA) plans. Non-HA $5/month Postgres includes 10GB of egress. Usage is metered beyond those allowances, so it's worth knowing about and minimizing where possible.
This post focuses largely on Postgres, but the general principles apply to all databases across all the major cloud providers.
If your egress numbers are approaching the included quota, or exceeding it by more than you’d like, your problems likely stem from two things: you're either fetching too much, too often, or both.
Consider the case of a content-heavy application. The database is full of documents made of rich text and block content. That content is stored in a JSONB column using the Portable Text specification.
CREATE TABLE posts ( id integer NOT NULL DEFAULT nextval('posts_id_seq'::regclass), title text NOT NULL, slug text NOT NULL, content jsonb NOT NULL DEFAULT '[]'::jsonb, created_at timestamp with time zone NOT NULL DEFAULT now(), updated_at timestamp with time zone NOT NULL DEFAULT now(), CONSTRAINT posts_pkey PRIMARY KEY (id), CONSTRAINT posts_slug_unique UNIQUE (slug));
Fetching too much is easily done. Performing a SELECT * query will return every value from every column in every matching result and will return more data as more columns are added. Likewise, "unbounded queries," that is, a query without a limit, will linearly return more data as more matching data exists.
-- ❌ returns unlimited columns and rowsSELECT * FROM posts;-- ✅ returns limited columns and rowsSELECT id, title FROM posts LIMIT 10;
Selecting specific columns has the added benefit of making your code more declarative about the data your application requires. While PlanetScale measures the data transfer size of your queries, it can't make assumptions about how much of that query response was used. The more specific your queries are, the simpler the debugging process becomes.
For a JSONB column, you may also consider using Postgres' built-in syntax to extract specific values from the data if not all values are required.
For example, perhaps you want to build a table of contents from level 2 and 3 headings from our Portable Text column. An unspecific query would just return the entire content column.
SELECT content FROM posts WHERE id = 1
Instead, we can use the jsonb_agg() function in Postgres to filter the array of objects down to just the headings we're looking for.
SELECT jsonb_agg(block) AS headings FROM posts, jsonb_array_elements(content) AS block WHERE id = 1 AND block->>'_type' = 'block' AND block->>'style' IN ('h2', 'h3');
Including JSON filtering will introduce some CPU overhead, so it's a tradeoff. Monitor resource usage and see if the reduced egress is worth it.
Fetch only the rows, columns, and data from those columns that your application requires.
Pagination also bounds how much data leaves your database per request. Without it, a growing dataset means ever-larger responses. Two common approaches:
Offset/limit skips a number of rows and returns a fixed page size. Simple to implement, but the database still scans all skipped rows, so deeper pages cost more.
SELECT id, title FROM posts ORDER BY id LIMIT 10 OFFSET 0; -- page 1SELECT id, title FROM posts ORDER BY id LIMIT 10 OFFSET 10; -- page 2
Cursor pagination uses the last value from the previous page as the starting point. It performs consistently regardless of depth.
SELECT id, title FROM posts ORDER BY id LIMIT 10; -- page 1SELECT id, title FROM posts WHERE id > 10 ORDER BY id LIMIT 10; -- page 2
While most cloud providers do not typically charge for ingress, there are instances where your ingress operations quietly result in egress.
ORMs can have this happen by default when returning data from an insert operation. Here is an example insertion operation using Drizzle.
const post = await db .insert(posts) .values({ title: 'Hello world', slug: 'hello-world' }) // ❌ returns everything with no parameters .returning()
The function call above would result in an SQL query like this.
INSERT INTO posts (title, slug)VALUES ('Hello world', 'hello-world')RETURNING *;
In this particular instance, we're only writing the title and slug, so the response is relatively small in terms of bytes transferred. It's worth noting, however, that more columns were returned than were written.
+----+-------------+-------------+---------+-------------------------------+-------------------------------+| id | title | slug | content | created_at | updated_at ||----+-------------+-------------+---------+-------------------------------+-------------------------------|| 5 | Hello world | hello-world | [] | 2026-05-11 16:04:03.049885+01 | 2026-05-11 16:04:03.049885+01 |+----+-------------+-------------+---------+-------------------------------+-------------------------------+
The content column is small for now, but if we were writing an UPDATE to an existing and very large document, it would be returned with every operation.
Now imagine our content editor upserts changes to an edited document every second. This could be a massive payload of our Portable Text JSON, with every insert operation returning the full body of the inserted item, essentially doubling the operation's egress.
const post = await db .insert(posts) .values({ title: 'Hello world', slug: 'hello-world' }) // ✅ returns only the id column .returning({ id: posts.id })
If every user of your application requesting the same data results in a fresh request to your database, you're wasting your egress quota.
In the simplified diagram below, that means trying to avoid every user request from triggering fresh egress to generate a response.
Caching and Content Delivery Networks (CDNs) exist largely to improve performance. One way they achieve this is by reducing data transfer. By loading a local copy of the data your application needs instead of fetching it fresh from the database.
An application-level cache (like Redis) between your database and application, or a network-level cache (like a CDN) between your application and a user, can help reduce the frequency of requests to your database.
Preventing unnecessary work in your database is increasingly important as your dataset grows and the frequency of requests increases. A single JSONB column of Portable Text, for example, could get into megabytes in size, and you won't want it requested from the database with each page load, should your article hit the front page of Hacker News.
Egress is typically charged when data travels over the public Internet. PlanetScale supports AWS PrivateLink and GCP Private Service Connect to improve security and reduce egress costs (another win-win combo).
If your application is hosted within the same infrastructure as your database (and it should be), you may be able to use either of these private connections to skip this public internet hop.
PlanetScale charges much lower rates for data transferred over these private connections, however, both ingress and egress are billed. See the documentation for more pricing details and to see if this is an option for you.
PlanetScale Postgres offers us ways to measure the bytes returned by individual queries, but not to observe egress bytes usage patterns over time. Let's look first at what it takes to measure a query.
If we prepend EXPLAIN to the same unbounded, unspecific query as before, we're shown the query plan for the response.
> EXPLAIN SELECT * FROM posts;+----------------------------------------------------------+| QUERY PLAN ||----------------------------------------------------------|| Seq Scan on posts (cost=0.00..15.60 rows=560 width=116) |+----------------------------------------------------------+
The query plan shows us rows=560, an estimate of the number of rows returned, and width=116, an estimate of the size of each row. These estimates are based on averages and won't reflect the size of any particular row, especially for variable-length columns like JSONB.
The only way to accurately measure the transfer size of a query is to run it. Let's measure the difference between querying the full content column of a post compared to just extracting the headings.
We could use pg_column_size() to measure the size of the content column, but it would return the TOAST-compressed size, not the size of the data being sent over the wire. octet_length() will return a closer approximation of the relative size.
Note
Postgres uses TOAST (The Oversized-Attribute Storage Technique) to compress and store large values, such as our JSONB column, so its on-disk size is dramatically smaller than its measured egress size. TOAST-compressed data is decompressed and serialized before being sent over the wire.
-- Full content columnSELECT pg_size_pretty(octet_length(content::text)::bigint)FROM posts WHERE id = 1;+----------------+| pg_size_pretty ||----------------|| 37 kB |+----------------+-- Just the headingsSELECT pg_size_pretty(octet_length(jsonb_agg(block)::text)::bigint)FROM posts, jsonb_array_elements(content) AS blockWHERE id = 1 AND block->>'_type' = 'block' AND block->>'style' IN ('h2', 'h3');+----------------+| pg_size_pretty ||----------------|| 5127 bytes |+----------------+
Less data is smaller, big surprise!
This is useful information for this specific query, but measuring queries individually is tedious. Ideally, we want to monitor the size of every query generated by our application and see usage patterns over their lifetime. Fortunately, Insights does this for us.
PlanetScale Insights monitors the queries performed in your database. These statistics can be viewed in the dashboard and are made available to agents via the PlanetScale MCP server.
Often, developers use Insights to measure query latency to improve performance, but it also provides many other statistics, such as bytes returned.
Open Insights and from the query list select the "Data" tab. These tabs contain preset columns relevant to debugging specific scenarios. Here we've sorted by "Bytes returned per query" and can see the largest transfer size of all queries in the currently selected time period.
Consider a query that returns 37 KB per call. Run 100 times, it transfers less than 4 MB and is probably not worth optimizing. Run 100,000 times, it transfers nearly 4 GB. Sort by the queries with the highest total bytes returned to find improvements which may have the most impact.
Look for frequently run, large-byte-transferred queries to identify opportunities for improvement.
For PlanetScale Postgres databases, the overall volume of egress and ingress can also be measured in the Metrics tab. At the bottom of this tab are graphs for ingress and egress.
From here, you can look for spikes that correlate with queries run at particular times to find any outliers.
Additionally, on PlanetScale Postgres, if you know your application contains several related queries you'd like to monitor collectively for egress or performance, query tagging is a way to link them.
Query tags are added using the SQL Commenter format. By adding tags, for example, we can tag every query that requests or updates a row with portable-text so that we can measure all these queries together.
Don't wait until things start getting expensive before thinking about egress. Optimizing early can result in more declarative queries, cleaner code, faster responses, and lower resource demands on your database.
Connect your agent to the PlanetScale MCP server and prompt your agent to find opportunities to improve your application's database egress usage.
From the point of view of an application developer that understands efficient database usage patterns, interrogate our code base for examples where we are querying for columns of data that the application is not using, returning data from updates or inserts that we do not need, or improvements to reduce the frequency or quantity of queries performed for the same data. Read https://planetscale.com/blog/database-egress for more details.
]]>Problem solving with PlanetScale Insightshttps://planetscale.com/blog/problem-solving-with-insights2026-05-07T00:00:00.000Z2026-05-07T00:00:00.000ZSimeon GriggsThere are so many ways your database can disappoint you. It'll make your application perform in ways you don't expect and upset your users.
In a sufficiently complex application, finding and eliminating performance problems can be difficult. Fortunately, PlanetScale gives you the tools to isolate the problem. PlanetScale Insights, available in the dashboard and through the MCP server, provides accurate, up-to-date information on how the queries in your codebase perform in production.
But with so many different metrics available, how do you differentiate good numbers from bad, signal from noise, or know what the most likely fix is once you've pinned down the problem?
For this post, I'll walk through exploring Query Insights for a demo e-commerce app connected to a PlanetScale Postgres PS-10 database with a few million rows of data. I set up a flow of constant, regular traffic along with a few "unexpected" spikes.
PlanetScale Insights also works for PlanetScale Vitess/MySQL databases and has many of the same features. This post focuses only on PlanetScale Postgres.
The default view of the PlanetScale Insights dashboard shows query performance, counts, and row reads and writes for the past 24 hours. You can navigate through up to seven days' worth of traffic data.
Query latency is the best starting point for isolating query performance issues. You can toggle trend lines in the graph on and off; the query list below aligns with the same timeline.
On this page, latency percentiles are computed from all query pattern executions performed within the observable time window. How fast most runs are versus the slow tail. That is how you differentiate the median run (p50) versus the worst few percent (p99 and above).
p50: Half of this query's executions complete faster than this value, half slower. This is the median latency for that pattern.
p95: 95% of executions complete faster than this; only 1 in 20 are slower. This filter identifies patterns that occasionally misbehave, but tuning them often will not move overall database latency (for example, workload p50) very much.
p99: 99% of executions complete faster than this; only 1 in 100 are slower. This is where gains can be made for that pattern's worst runs.
p99.9: Only 1 in 1,000 executions are slower. These are usually extreme outliers for that pattern: lock contention, cold caches, missing indexes, table scans, and similar.
Max: The single slowest execution of this pattern in the time window. Useful for spotting worst-case scenarios, but a single anomaly can skew this number and may be related to an almost random event that never reoccurs. Always compare it against the percentiles above.
Given this screenshot of Insights from my example application, the tabs at the top show that at 12:05 GMT+1 the p50 is 1.4ms and the p99 is 2s.
Point-in-time performance numbers can be useful, but execution trends over time matter much more to find real, unexpected outliers.
From the graph we can see the p99 is consistently far higher than the p50 and p95, with one huge spike where it got as high as 12s.
Generally, you may think that if "only" 1/100 queries are slow this latency may have a limited blast radius. But if a page load in your application triggers 10s or 100+ queries to your database the impact could be widespread and affect more users than you think.
These slower p99 queries we need to find and resolve. Let's find the guilty parties.
Below the latency graph, filtered to the same timeline, is a list of queries. From here, you can investigate the performance of each individual query that was run on your database at the same time. There are many columns of data you can read to investigate query performance. Which data is useful to you will depend on what you're debugging.
If you're not sure which numbers to look for, the tabs on the top right have preconfigured columns.
For example, if your database consistently shows high CPU usage, click the "Resources" tab to view CPU usage metrics. You can click any column to sort by that metric.
Since we're looking to fix query latency, we'll click the Performance preset and sort queries by p99 latency (ms).
Note that the Performance preset also includes the "Rows read/returned" column; this is often the simplest identifier of slow queries. It contrasts rows the engine had to read with rows actually returned—when reads are high but returns are low, the database is doing a lot of work per useful row, often because of missing or unsuitable indexes. Most often, these queries can be fixed with an index.
Solving the response time issues for some of these queries will be simpler than for others. A number of these queries have a little (i) information icon beside them showing that the queries are being performed without an index and may benefit from one.
(It's also worth noting that some of these queries are slow because they're deliberately bad queries. I needed an exceptionally unoptimized application for this blog post. So, for example, we're not going to "fix" a query for a random product ID.)
The search box above the query list lets you perform targeted searches for specific queries. You may write part of an SQL query in this box, but there are additional search syntaxes to query by feature, latency, tag, or more. Examples include:
indexed:false — find all queries not using an index
index:table_name.index_name — find all queries using a specific index
p50:>250 — filter by latency threshold
query_count:>1000 — filter by execution count
tag:key:value — filter by tag
Clicking the SYNTAX button on the right side of the search box reveals the full set of filters you can use to narrow down your query filtering.
Along with Query latency there are graphs to show other activity trends in your database.
The Queries tab shows total queries per second over time. If latency rises at the same time as query volume, you may be looking at a traffic spike rather than a single query pattern getting worse.
The Rows read tab shows how many rows the database reads per second. High rows read, especially compared to rows returned in the query list, can indicate that the database is reading unnecessary rows and may benefit from a better index.
The Rows written tab shows rows written per second over the selected time period. It gives you a separate view of write volume alongside query latency, query count, and rows read.
Let's click in to look at an individual query and what Insights can tell us.
If your application writes raw, sensible SQL, your query might look as simple as this:
If you're using an ORM, your query could be incomprehensible at first glance. Fortunately, the "Summarize query" button runs the query pattern through an LLM to describe its purpose in plain English.
You may also notice the query has been anonymized. Because parameters in a query may contain sensitive information, they're replaced with placeholders when logged in to Insights. In this instance, the search term %turbo% is rendered as the parameter $1, but it is not visible in Insights.
The page of a query pattern also contains a table of notable queries, individual executions that took longer than 1 second, read more than 10,000 rows, or produced an error. This could help determine whether your query is not always slow and perhaps reveal a common time when it runs slower than normal.
On this page, you can see the same performance graphs as the query list page, but isolated to just this one query. In this screenshot, we see a recommendation from Insights to add an index if the performance is poor. A lot of the time, this is a great idea.
Unfortunately, because this query is using a wildcard search, a BTREE index won't help.
If the query were simpler, like below, an index on the name column would greatly improve performance.
select count(*) as count from products where name = 'turbo';
Instead, we may be better off with a GIN trigram index, as they are better designed for wildcard searches. Fortunately, pg_trgm is a supported extension in PlanetScale Postgres, so I was able to experiment with it. It improved query performance, but only slightly.
Often, an index can fix a slow query. Other times, slow queries reveal bad schema or application design. Both are important to resolve; the latter is just a little more complicated, as you may need to rip and replace the query in order to improve application performance.
The most common mistake of a smart engineer is to optimize a thing that should not exist
If the selected query is using indexes, statistics are shown below the latency graphs, along with tags attached to that query (more in the section below).
Fortunately, there's never been a better time to fix complex problems.
The PlanetScale MCP server has access to the same data you're able to browse in the dashboard. This means you can task an agent with finding and suggesting fixes for slow queries within your codebase. With your application as its context and real-world production data available via tool calls to Insights, you no longer have excuses for slow database queries.
At PlanetScale, we have workflows configured to do this daily. See the video below for more details.
In the case of our slow query that can't be fixed with an index, this is a great job for an agent. It can not only read Insights data but also perform queries on its own. While experimenting with indexes on this database, I observed the agent reading the output of EXPLAIN ANALYZE to ensure the index was being used and to report the impact on results.
Consider a prompt something like:
I need you to help resolve a slow database query in this application. Make suggestions on whether we can resolve this by adding an index. If so, let's test the results before and after. Additionally, we may need to rethink the query and consider whether there are more efficient ways to obtain the same data to improve application performance.
To help keep your agent focused, include the details of the PlanetScale database in your application's AGENTS.md, for example:
## PlanetScale- Organization: ready-set-go- Database: tutorial-insights- Branch: main
Note
MCP permissions are set when you authenticate the server. It is not advised to give an agent write access to your production database.
So far, we've looked at identifying queries by grouping together the slow ones. There are other reasons to group queries together, though, which can help with debugging as well as improve performance.
On the Tags page, we can see queries grouped by metadata related to them. There are built-in key-value pairs, such as the application name and remote address of the connection that ran the query.
Custom metadata can be included with queries as SQLCommenter comments. Not all ORMs support comments; check your documentation if you are not writing raw SQL.
select count(*) as count from products where name ilike '%turbo%'/* application='store', action='search' */;
These comments are then logged as key-value pairs as queries are performed, allowing you to investigate the performance of a specific subset of queries based on their application, intention, and more.
So if you're not debugging "why is this query slow," but instead "why is this section of the application slow," you might benefit from grouping that section's queries with the same tag.
Some slow queries are unavoidable. We've already determined that our application has a slow query that can't be easily fixed with an index. One option is to remove it entirely in favor of something else. An alluring third option is to put controls on how many resources the query can actually use.
Traffic Control allows you to do just that. Where timeouts in Postgres can be used as a blunt instrument to stop queries running over a certain time, Traffic Control gives you fine-grained control over how many resources a query can consume, as well as controls over concurrency and more. Perhaps our slow search query actually only runs from an admin panel.
So it's less of a concern that a single query is slow, but more of a concern if multiple administrators run it concurrently and bring down the database's performance.
The same tags we applied to observe a category of query behavior can have "resource budgets" applied to them to limit the amount of resources they are permitted to consume.
Insights now identifies slow queries, recommends improvements, and controls whether they can run at all.
So far, we've covered manual performance investigation. You and your agent are digging through Insights for improvements. As your application runs, Insights also gathers its own data on anomalous behavior and preemptively suggests upgrades.
The Anomalies page highlights when database performance is well outside the expected range. This can reveal unexpected query patterns, traffic spikes, or other problems with your database.
If you have an anomaly in your Insights dashboard, you can click in to see more details about the time of the anomaly and which queries contributed to it. Match this timeframe against any other application logging platforms you have to identify the root cause. It could be an unexpected one-time outlier, or it could be the result of recently updated application code, and is likely to repeat.
Insights also monitors traffic to regularly produce schema recommendations. These may include the index suggestion we saw earlier, or other helpful tips to potentially improve the health of your database.
Recommendations typically include SQL statements you can run in your database to take action.
Both the Anomalies and Recommendations data found within Insights are available from the PlanetScale MCP server if you would like an Agent to help you decide whether to take action on the database.
Slow queries aren't the only problem Insights can surface. The Errors page captures every database error from the past 24 hours and plots them on a timeline, letting you spot patterns you'd otherwise miss in application logs.
In my demo store, I simulated a retry storm during checkout — a flaky network that caused the same order to be submitted multiple times with the same idempotency key. The errors tab immediately surfaced the duplicate key value violates unique constraint message on the orders_idempotency_key_key index. Clicking into it revealed each occurrence: the exact query, when it ran, how long it took, and the tags I'd attached to identify the checkout action. From there, I could see the errors clustered in tight bursts, a telltale sign of retries hitting the same unique constraint rather than a systemic problem.
This is the kind of issue that often goes unnoticed. The application catches the exception, retries successfully, and the user never sees a failure — but the database is doing unnecessary work. The Errors page makes these invisible problems visible.
PlanetScale Insights is the best way to see how your database actually performs in production, providing you and your agents with the metrics that matter to improve your database schema, queries, or completely change access patterns.
In a future article, we'll look at how to inspect common database problems by viewing specific metrics in Insights. If there's an issue with your queries you can't yet get to the bottom of, let us know!
]]>On benchmarkinghttps://planetscale.com/blog/on-benchmarking2026-05-05T00:00:00.000Z2026-05-05T00:00:00.000ZBen DickenBenchmarking is hard. There are many ways to do it wrong and few to do it right.
But zooming out from any single system or harness, there are broad principles that should be applied to all benchmarking. Using these correctly makes it difficult to produce biased results.
Am I the world's best benchmarker? Certainly not. I invented the language balls, after all. But correctness and precision are important parts of PlanetScale's culture. We've spent considerable time learning the art of benchmarking, and are here to share best-practices.
Here, we're focusing primarily on benchmarking databases, but these principles apply to many domains.
Databases typically operate in a client-server model. The database server is started, accepts connections from clients, executes queries, and returns results.
To benchmark, we need a client that establishes the connections, generates queries, and takes measurements. Since both sides consume resources and we want to give the database its full share of the host server, it's common to set up a distinct server for benchmark execution.
As usual, there's a catch. This introduces latency between the two machines.
How much this skews the results of the benchmark depends quite a bit on how "far apart" the benchmark server and database server are (network latency) and how long the queries / transactions take on the database (execution latency).
Let's consider a scenario where each query takes ~10ms to execute on the database. If the network round-trip time is 2.5 milliseconds, then we can execute approximately 80 queries per second over a single connection. On the other hand, what if the round-trip is 15 milliseconds? We've now cut our single-threaded QPS capability in ~half, resulting in 40 QPS.
Same database. Same benchmark client. The only difference is the speed at which bytes can go over the wire between the two.
This latency variation will always have an impact on latency measurements.
It can also impact throughput. We often don't run benchmarks on a single connection. We'll do 10, 50, or 100 simultaneous connections to best utilize the parallelism of the machine and database. But if we have a fixed connection count, and are not making it dynamic to account for round-trip latency, we can end up allowing the elevated latency to hurt throughput.
Finally, you should double-check that the client server is not a bottleneck. While benchmarking, ensure that CPU and network utilization are well under their capacity. We want to be straining the database server, not the client.
It's easy to make one database look better than another with an imbalance of resources. Postgres running on a 16-core server will almost always perform better than on an 8-core server.
An important prerequisite to proper benchmarking is setting up the compute, storage, and networking resources to allow for a fair fight.
This isn't as easy as it sounds, especially when we're talking about running things in the hyperscaler clouds like AWS and GCP. For example, the Geekbench results for an AWS r7g.2xlarge are ~15% lower than the results for an r8g.2xlarge. Both have 8 vCPUs and 64 GB RAM. But move one generation newer, and there's a ~15% CPU improvement.
You might then be tempted to just use the same instance for everything, but this breaks down too. The availability of instance types varies over time, region, and database provider. In some cases, it's not possible to match.
In an ideal world, we'd run everything on the exact same instance. In reality, we sometimes have to settle for matching CPUs and RAM as best we can, and living with the differences. However, you must give this your best effort. Purposefully choosing to benchmark your product on 2025-gen CPU and then comparing to a competitor's product on a 2022 CPU, when the alternate was readily available, is intentionally misleading.
Even once we know that our infrastructure is set up sanely, there's a lot to consider for the workload we run.
The easiest way to think about this is in terms of traffic ratios.
How many queries are hitting RAM vs disk?
What % of the data is hot (frequently queried) vs cold (rarely queried)?
What's the ratio of reads to writes?
All of these impact performance, especially when combined with the variations of underlying hardware.
Queries executed on a relational database often require some amount of I/O work. Writing data must always be persisted to disk. Reading data can come from the in-memory cache, or disk on cache misses.
Some databases operate on local SSDs, while others use network-attached storage like AWS EBS or Google Persistent Disk. Some even take a hybrid approach. Either way, the percent of read traffic hitting RAM vs disk impacts performance due to I/O wait times.
Consider a benchmark like sysbench OLTP read-only. This is a simple, read-only benchmark that runs a handful of select query patterns repeatedly. As benchmarks often do, the data size is configurable in the preparation phase. If we run this benchmark on a server with 64 GB of RAM and a 32 GB data size, the entire data set will fit in RAM after warming. The same benchmark run with a 320 GB data size will generate significant I/O and inevitably run slower.
This is related to, but not the same as, data distribution.
Even for a fixed data size, access patterns can vary widely. The simplest examples are uniform and Zipfian.
A uniform access pattern gives every row the same chance of being queried on each request. If we have 100 rows, each has a 1% chance of being read for each operation.
A Zipfian access pattern is skewed: the k-th most popular key is accessed roughly proportional to 1/k. A small number of hot rows receive a large share of requests, while most rows are accessed rarely.
These are only simple models. Real workloads often have messier shapes: recently inserted rows might be hotter than old rows, one tenant might dominate traffic, or a small working set might receive most reads for a period of time.
Which pattern the benchmark operates with significantly impacts performance, because it in turn impacts how frequently we need to access disk vs RAM and the amount of cache churn.
There are two types of benchmark workload shapes: open and closed loops.
In a closed-loop benchmark, the client sends requests and then waits for a response before sending the next.
while True: # wait for response response = send_bench_request() # then send next process(response)
We may do this in parallel across many connections, but each individual connection sends a controlled sequence of queries. A closed loop can also hide a failure mode called coordinated omission: when the database stalls, the client stops issuing new requests too, so the benchmark only records the stalled request and omits the work that would have queued behind it. This is especially misleading for tail latency, where the missing queued requests are exactly the ones that would have made p95/p99 look worse (more on latency and percentiles soon).
Open loop on the other hand has a fixed pace of sending requests, regardless of how quickly the database responds.
while True: # fire and forget send_bench_request() # fixed pace time.sleep(0.1)
This can be fixed throughout the entire benchmark duration, or vary in a controlled way:
Open-loop benchmarks tend to be more realistic. In production systems, database load is applied at the rate that the clients demand, regardless of how well the database is keeping up.
Closed-loop benchmarks are more commonly seen in academic and performance comparisons, as they offer a more controlled environment for comparing things like QPS across a fixed amount of concurrency.
Both are beneficial, but they are useful for different things. Important to decide up front what the purpose of a benchmark is, then choose the type accordingly.
Broadly, there are two things we like to measure when benchmarking: throughput and latency. Any good database benchmark will report on both of these things.
Throughput is the amount of work completed in a slice of time. In databases, the most common measures are Queries Per Second (QPS) or Transactions Per Second (TPS). For many popular benchmarks like TPC-C and TPC-H, TPS < QPS because there are typically multiple queries within single transactions. Either works fine as a measure.
To measure throughput, choose a workload, a period of time to run it for (say, 5 minutes / 300 seconds), and then execute with TPS / QPS sampling. As a benchmark runs, samples are taken of how many queries or transactions complete each second. We then display this as a graph, showing every collected data point:
A more compact way of displaying this is via a bar chart with error bars.
This communicates similar information in a more compact way, but it's ideal to show a full line graph, as that also better visualizes inconsistencies or spikiness of performance throughout a benchmark run. More on this later.
Error bars are only one way to summarize variance. Coefficient of variation, interquartile range, and histograms are different lenses on the same samples, each helping show whether a benchmark was stable, noisy, or hiding outliers. It's helpful to include these or provide the data so readers can compute them themselves.
Latency is the amount of time it takes to complete an operation, query, or transaction. We can look at individual latencies ("How long did this particular SELECT * FROM... take?"), but more often we assess latencies in aggregate.
The standard language for communicating about latencies in distributed systems is with percentiles over some span of time (1 second, 1 minute, etc.). For example:
p50 - The median latency. During this time period, half of the requests executed faster than this, the other half slower.
p90 - The 90th percentile. During this time period, 9 out of 10 requests executed faster, 1 out of 10 slower.
p99 - The 99th percentile. During this time period, 99 out of 100 requests executed faster, 1 out of 100 slower.
We can measure any latency percentile we want, but these are the most common, along with p95 and p99.9. When benchmarking, we typically measure one or more of these in a series of small windows over the entire benchmark period. Say, sample p50, p90, and p99 once per second over a 5-minute (300-second) execution. Then, we plot the results.
In some cases, the line graphs are overkill. As with throughput, the visual can be compressed using a bar chart showing the median (or mean), with error bars.
We now have a way of communicating both how much work we accomplished and how quickly each unit of work was completed.
We've now settled the prep work and know what we should be measuring. Now let's get tactical. How do we ensure that we are fair when running the benchmark? There's a lot to consider for the executions themselves.
A big one is cache warmup. If we've recently booted up our database, the various caches are not full of pages (buffer_cache in Postgres, buffer_pool in MySQL). These require time and query load to warm, during which time latency and throughput will slowly be brought up to full potential.
We typically run databases without measurement for a few minutes to ensure all caches are warmed before starting benchmark measurement. This ensures non-full caches and other startup costs don't impact the numbers.
Even when warm, there are a number of configuration options that impact performance over long stretches of time. Though there are many, a good example of this is checkpoint_timeout in Postgres.
This and max_wal_size determine how frequently we need to flush table / index changes to disk (I/O checkpointing). If we set these to low / aggressive values, we may trigger it once every minute, causing regular performance dips. If we set it lax to only trigger once every ten minutes, we may not even notice it in the results of a 5-minute benchmark execution.
We can end up with graphs like this in these cases. But run for another 10 minutes, and we'd likely see a large performance dip on the green line.
Background jobs, I/O checkpointing, autovacuum, and other work can impact the throughput, skewing the benchmark results.
It's important to consider the impact database configurations have on performance. An identical benchmark on the same hardware can perform very differently with different tunings. DBMSs give us these tunings so we can trade off things like performance, durability, data size, and resource consumption on a case-by-case basis. It's generally best to either (a) ensure all configuration options are aligned or (b) for pre-tuned situations (like most database-as-a-service providers) leave things at the pre-tuned defaults.
Another important consideration, especially in the cloud, is (in)consistency. Even with the same benchmark instance and same client machine, latency and throughput can vary from run to run. This can be due to contention on the network or noisy neighbors that are co-occupying the same hardware you are running on.
It's advisable to do multiple runs to measure consistency.
The best benchmarks are the ones that compare apples-to-apples. In other words, ones that create data-driven comparisons between products that have the same or very similar characteristics and feature sets.
Examples of this are:
Comparing 4 different Postgres configurations to determine workload suitability
Comparing 3 different cloud MySQL platforms to determine which is most performant
Comparing MySQL and Postgres on an identical workload (different databases, but same stated purpose)
People sometimes draw comparisons between vastly different database engines, resulting in wild claims. Things like:
Analytics queries run 100x faster on Apache Pinot than Postgres
Achieve 100x higher QPS on a purpose-built realtime database compared to a Postgres relational database
SQLite latency is 80% lower than MySQL
These are comparing databases that were distinctly optimized for different purposes. It's easy to make one look better than the other, especially when cherry-picking the workload.
Don't do this. Ensure comparisons are between comparable technologies and workloads that fit the DBMS's stated purpose. The one exception may be as an internal test to determine which technology, amongst ones with vastly different goals, is best-suited for a system.
Good benchmarks should be reproducible. Document the client and target setups as exhaustively as possible: hardware (or cloud instance type), OS, software versions, build flags, configurations, benchmark tool, exact command line, etc. After looking at the results of a benchmark, an engineer should be able to reproduce the results.
As you can see, there's a lot to good benchmarking. Missing any one of these steps leads to bias. Some of the most common mistakes:
Reporting only averages, without percentiles, variance, or the full time-series
Leaving out hardware, instance type, etc.
Measuring before the system reaches steady state
Reporting a percentage difference without the surrounding variance
Forgetting to check whether the benchmark client is the bottleneck
That last one is easy to miss!
If the client machine has maxed out on CPU or network connections, the graph may look like the database has plateaued. But all you've really measured is the limit of the load generator.
You now have an elementary understanding of database benchmarking.
When presenting results, don't stop at the numbers. If two runs differ meaningfully, offer a hypothesis for why: hardware, configuration, workload shape, cache behavior, network latency, or something else. The reader should not have to invent the causal story themselves.
Apply all these to your next round of benchmarks, and you're less likely to veer off-course.
