DEV Community: PlanetScale

The feedback loops behind Kubernetes

Meg528 — Mon, 27 Jul 2026 17:24:18 +0000

Written by Fatih Arslan

For 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.

Part 1: running Postgres by hand

One container, one machine

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.

Pick a node, by hand

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.

It needs a real disk

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/nvme1n1
mkdir -p /var/lib/pg-data
mount /dev/nvme1n1 /var/lib/pg-data
docker 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.

One isn't enough

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?

They have to find each other

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 IP
primary_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.

The watchdog script

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 5
done

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.

Changing a parameter

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=500

for 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"
  fi
done

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.

What we actually built

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.

Part 2: how we reinvented Kubernetes

Now we can map what we hand-rolled in Part 1 to Kubernetes. Almost all of it already exists there. The operator is the part we care about.

The other loops

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.

That's the operator.

The for-loop translated to Kubernetes

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.

Edge-triggered notifications, level-triggered logic

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.

Kubernetes controllers combine both: edge-triggered notifications, level-triggered logic.

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 why Reconcile 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.

Informers, the work queue, and a cache

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 CACHE
for 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.

Setpoint and measured variable: spec and status

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.

Self-healing by design

This is the part I like most.

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.

What "observe" actually means in a real operator

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.

Not every edge comes from the API server

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.GrowThreshold
if 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.

Conclusion

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 Connections

Meg528 — Mon, 20 Jul 2026 16:21:39 +0000

Written by Brett Warminski

Much 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?

Manual monitoring

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 query
FROM pg_stat_activity
WHERE 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.

Simpler debugging with Connections

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.

Keep enough history to see the pattern

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.

Available even when connections are exhausted

The stress of debugging an active incident is worse when you can't even connect to the database yourself.

Connections uses a reserved administrative connection, so the inspection path still works when regular application connections are exhausted.

Managed databases should remove the need to SSH into a box, not remove your ability to debug an incident.

You can still get in, see what is running, and act from there.

For Postgres and Vitess

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 today

Connections is available for PlanetScale Postgres and Vitess. Update to the latest version of pscale and run:

pscale branch connections top <database> <branch>

See the CLI reference, the Inspect live Postgres connections guide, and the Inspect live Vitess connections guide for more details.

Try it next time you need to troubleshoot active database connections.

Problem solving with PlanetScale Insights

Meg528 — Mon, 13 Jul 2026 18:53:51 +0000

Written by Simeon Griggs

There 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.

Latency timeline graph

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.

For a deeper dive on understanding latency percentiles, watch Ben's video.

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.

Query list

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.)

Searching for queries

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.

Other graphs

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.

Query details

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.

Taking action on a query

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).

Insights MCP

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.

Grouping queries with tags

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.

For more on tags, see Enhanced tagging in Postgres Query Insights.

Tags are also the backbone of Traffic Control, the killer app of PlanetScale Postgres.

Traffic Control

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.

See more in the Traffic Control documentation.

Continual improvement

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.

Learn more in the Anomalies documentation.

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.

Error tracking

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.

Conclusion

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!

Transparency in benchmarking

Meg528 — Mon, 06 Jul 2026 16:56:24 +0000

Written by Ben Dicken

Database benchmarks are imperfect. They are also useful.

No benchmark can tell you exactly how a database will perform for your application. Workload shape, data size, region placement, storage, configuration, and cost all matter. But fair benchmarks help customers understand tradeoffs, compare options, and ask better questions before choosing infrastructure.

The DeWitt clause

Many cloud vendors include language in their terms that restricts comparative benchmarking. These restrictions are called "DeWitt clauses", named after database researcher David DeWitt. That is a strange legacy for someone whose work helped move the database industry forward by measuring real systems and publishing results.

Previously, PlanetScale also included a DeWitt clause in our Acceptable Use Policy (AUP). Recently, we removed this in favor of a more open "Benchmarking" section in our AUP.

The new section reads:

You may perform benchmark tests (“Benchmark”) of the Services, provided that the Benchmark is conducted in good faith and uses a fair and transparent methodology. Please refer to PlanetScale's published benchmarking best practices. Except with respect to Beta Features, you may disclose the results of the Benchmark. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of the Services, you (i) will include in such disclosure, and will disclose to PlanetScale, all information necessary to replicate such Benchmark, and (ii) agree that PlanetScale may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products and services.

Any Benchmark must be conducted in accordance with the Agreement, including this Acceptable Use Policy. The Benchmark must not interfere with the Services or misrepresent the configuration, methodology, results, or cost of the Services or any compared service.

Benchmarks have gained a bad reputation because they are frequently conducted poorly. Sometimes this is done with malicious intent, often referred to as "benchmarketing." At other times it is done out of ignorance. Many engineers are not trained in all aspects of fair benchmarking.

A new standard

Anyone benchmarking PlanetScale should follow the best practices outlined in our benchmarking guide. These practices come from our deep experience benchmarking databases in the cloud where topology, server location, region, workload, and instance type differences materially impact the result.

We encourage other vendors, analysts, and practitioners to use the same standard. Benchmarks should be deep, thorough, technically sound, and transparent enough for others to understand and reproduce.

Our ask

We invite other vendors to adopt this same language and standard in their own AUPs. Allow public benchmarking, remove DeWitt clauses, and hold benchmarks to clear expectations for fairness and transparency.

Customers should be able to compare the systems they rely on.

Egress problems and where to find them

Meg528 — Mon, 29 Jun 2026 16:16:49 +0000

Written by Simeon Griggs

Name 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.

Definitions

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.

Common culprits

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)
);

Too much out

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 rows
SELECT * FROM posts;

-- ✅ returns limited columns and rows
SELECT 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 1
SELECT 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 1
SELECT id, title FROM posts WHERE id > 10 ORDER BY id LIMIT 10; -- page 2

For more detail on each approach to pagination, see Offset limit pagination and Cursor pagination in the MySQL for Developers course.

Too much in

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 })

Too often

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.

Too internet

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.

Identifying egress usage

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.

With EXPLAIN

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 column
SELECT pg_size_pretty(octet_length(content::text)::bigint)
FROM posts WHERE id = 1;

+----------------+
| pg_size_pretty |
|----------------|
| 37 kB          |
+----------------+

-- Just the headings
SELECT pg_size_pretty(octet_length(jsonb_agg(block)::text)::bigint)
FROM posts,
  jsonb_array_elements(content) AS block
WHERE 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.

With Insights

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.

Egress and ingress metrics

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.

Tagging classes of queries

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.

SELECT
    id, content
FROM
    posts
LIMIT
    10
/* returns='portable-text' */;

From the "Tags" page in the dashboard, we can now view queries with just this tag and measure their transfer sizes more cleanly.

Conclusion

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.

The only scalable delete in Postgres is DROP TABLE

Meg528 — Mon, 22 Jun 2026 14:06:03 +0000

Written by Tom Pang

Counterintuitively, large DELETEs add work to the database.

From experience we can plainly claim the following: the most scalable Postgres data-deletion strategies revolve around deleting entire tables.

Individual row DELETE is fine at a small scale. However, big batch DELETE operations don't immediately free up physical disk space, add write and replication overhead, and are ultimately not good for large scale row cleanup.

If your application needs to delete large amounts of data, even very rarely, we recommend moving towards schema designs that let you express that as a DROP TABLE or a TRUNCATE.

Let's study why this is by looking at how DELETE works in Postgres.

Deletes hurt

When rows mutate, Postgres can maintain multiple versions of the same row, so that different transactions can see row values as of the time they were queried. This is Postgres' implementation of "Multi-Version Concurrency Control" (MVCC) and a core principle of its design.

Postgres makes an intentional tradeoff here. It stores modified and deleted rows alongside current ones, relying on transaction IDs and visibility maps to skip over "dead tuples." Later on, a vacuum process comes along and says, "Hey, these bytes in this heap page are now free, you can overwrite them."

Deletes also need to be fully replicated; they are still a work of writes, which means large-scale DELETEs can impact other writers to your application and cause them to wait for the DELETE replication to finish (under synchronous and semi-synchronous replication).

It's worth noting here that DELETE or even autovacuum doesn't typically return data to the operating system; they only say "the space in those pages can be written over." This is an intentional choice by Postgres. It optimizes for the case where DELETE workloads are mixed with INSERT ones, and releasing space to the operating system and then asking for it back is relatively expensive and should be avoided. VACUUM FULL allows for this, but takes an expensive lock for a long time.

Another related tradeoff Postgres makes is that index data is not touched at all when issuing a DELETE; instead, readers reading the index have to resolve "is this tuple dead." There's also a best-effort optimization where an index scan that finds a dead row can mark the entry as dead itself.

Overall, DELETE is really "work added," not "work done." If you want more details on Postgres MVCC, see Keeping a Postgres queue healthy.

If you're running a DELETE over a large amount of data, you can imagine how it adds work to every read query and autovacuum. Be aware that using foreign keys and CASCADE for deletions can cause a single row delete to delete gigabytes of data, resulting in the same set of problems.

Drop DELETE for DROP

In contrast, DROP TABLE and TRUNCATE require a heavyweight AccessExclusiveLock on the table, but are loosely independent of data size. At the physical layer they remove files from the operating system directly, plus sweep the Postgres buffer cache to remove pages related to the table.

That sweep can be less trivial on databases with large shared buffers, but it is only a metadata sweep. Postgres keeps a small fixed-size header (a BufferDesc, padded to 64 bytes) for every 8KB buffer, and dropping a table scans those headers, not the pages themselves. At 64 bytes per 8KB page, that's 1/128th of the cache size: with 128GB of shared buffers, you are sweeping only ~1GB of memory, sequentially, which is very fast on modern hardware.

DROP TABLE and TRUNCATE scale much better than DELETE. They produce zero dead tuples, zero vacuum debt, zero work for readers. They immediately free up space for the operating system.

A performant one-off delete

One common case where folks need to delete large amounts of data is "my table is full of junk due to a bug." We encountered this recently in an internal observability tool. A bug caused the tool to write millions of rows that we wanted to delete from the database. The bad rows had an old updated_at timestamp; anything with a recent one was designed to be kept. There were only a few hundred thousand rows to keep; most of the data was junk.

For this case, especially because "lock the database for minutes" was not an issue at all, we performed some surgery, leaning on Postgres' transactional DDL:

BEGIN
Explicit LOCK TABLE ... IN ACCESS EXCLUSIVE MODE on the table in question; this prevents other transactions from reading or writing the table, so we get consistent data.
Create a temporary table to hold just the kept data, like so:

CREATE TEMP TABLE temp_keep_big_table AS
  SELECT * FROM big_table
  WHERE updated_at >= '2026-04-01';

TRUNCATE big_table;
INSERT INTO big_table SELECT * FROM temp_keep_big_table;. In our example, this took a handful of minutes to process on a very small instance with hundreds of thousands of rows.

This worked very well for a one-off; the only data written to the Write Ahead Log (WAL) are the reinserted rows in the big_table.

If holding an AccessExclusiveLock on the table for minutes during TRUNCATE is unacceptable, use a trigger-based approach instead: mirror writes to a new table, then swap with an atomic rename.

You should also know that this more advanced maneuver is roughly what the Postgres extension pg_squeeze (a more modern version of pg_repack) does. pg_squeeze is for optimizing tables that already have significant bloat. This blog post is really about preventing bloat in the first place. By structuring your schema to avoid large bulk DELETE, pg_squeeze becomes less necessary.

In cases where the data to keep is much larger than the data to discard, but the data to discard is still substantial, the typical approach is to perform many isolated batched deletes in a loop, e.g., 10,000 rows at a time. This keeps transactions short, avoids lock pileups, and lets you pace things so that autovacuum keeps up.

Postgres partitions for ongoing deletes

Since version 10, Postgres has had great partitioning support. A "parent" table can have "child" tables, and queries can be automatically routed to them. Postgres supports a variety of partitioning schemes; one that is extremely useful is date-based partitioning, but many others are available.

Partitioning can transform a workload that does "lots of DELETE" into a workload that does "occasional DROP TABLE." For example, if you have historical data that needs to be aged out, you can have a child partition per day, and a periodic process that deletes older child partitions (or use the pg_partman extension).

You can go further still. Partitioning in Postgres is recursive, so you could partition the top level by LIST (e.g., a "visible" rows partition), then partition the "not visible" child table by RANGE to age out old data.

Go forth and DROP

Structuring your schema and application so that large-scale DELETE becomes DROP or TRUNCATE can dramatically improve your database. It helps reduce read query latency in some cases, mitigates replication lag spikes, and overall improves database health.

On benchmarking

Meg528 — Tue, 16 Jun 2026 15:56:24 +0000

Written by Ben Dicken

Benchmarking 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.

Client-server architecture

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.

Choosing resources

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.

Workload

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.

Closed and open loop

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.

What to measure?

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

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.

Throughput only tells half the story.

Latency

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.

Warmup

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.

Configuration

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.

(In)consistency

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.

Apples to apples to oranges

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.

Document everything

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.

Benchmark crimes

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.

Go forth and benchmark

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.

Approaches to tenancy in Postgres

Meg528 — Mon, 18 May 2026 18:05:36 +0000

By Simeon Griggs

Multi-tenancy is a term used across various kinds of technical infrastructure, including application hosting, compute, databases, and more.

For example, you may purchase cloud services from a provider, but your account is one of many that draws from a common pool of resources. Your account is one "tenant" in a multi-tenant infrastructure.

In this article, we're focusing on using a single Postgres database cluster to serve an application with many tenants—you are our customer, and your customers are tenants in that cluster.

Note: PlanetScale databases are, by default, multi-tenant within our infrastructure. Single-tenant resources are available on Enterprise. See the deployment options documentation for more details.

Given the many approaches to multi-tenancy within a Postgres database, it is worth clarifying the recommended best practices and the data models you should avoid. These recommendations are informed by years of seeing multi-tenant applications, both good and bad, succeed and fail at scale.

Definitions

The term "database" is overloaded and can refer to different things:

A Database Cluster refers to the entire database server instance – the running Postgres process, its storage and any replicas.
A Logical Database is an isolated namespace within a database cluster that contains its own schemas, tables, and data.

When you generate credentials to connect to a database, you're connecting to the database cluster. The queries you perform will target a single logical database within it. On PlanetScale Postgres, the default logical database name is postgres.

In short: one database cluster can contain many logical databases.

When modeling data in a relational database:

A Tenant refers to a single entity that accesses their own subset of data in your application.
Single-tenancy refers to giving each tenant their own isolated schema, logical database, or database cluster.
Multi-tenancy refers to using a consistent schema (set of tables and relationships) for all of the users of your application within a single database cluster.

Three approaches to tenant isolation

There are three common approaches to separating tenant data within a single database cluster:

Row-level isolation where each tenant's data is isolated by a column value such as tenant_id
Schema-per-tenant where each tenant has its own schema and tables
Database-per-tenant where each tenant has its own logical database, schema, and tables

Of the three approaches, row-level isolation is the most common and is our recommended approach.

Row-level isolation is also the only true method of "multi-tenancy" in a relational database. Schema-per-tenant and database-per-tenant within the same database cluster do not share tables, but they do share resources.

Finally, you may already be running a database using one schema-per-tenant. You may be able to migrate to a recommended approach to improve the performance of your application and workloads. See Migrating to row-level multi-tenancy.

Good examples for multi-tenancy

Good examples of multi-tenancy include SaaS applications that need to isolate data for each customer but have so many customers that it would be impractical to assign each customer to an individual database cluster. Or multi-national applications that need to isolate data for each country, market, or region.

These are good use cases for multi-tenancy because only the data is different between tenants. The schema, tables, relationships, application code and access patterns are uniform across all tenants.

With any multi-tenancy approach, your goal should be for data belonging to each tenant to be consumed by the same applications, with care to ensure that one tenant cannot query another tenant's data nor that their behavior in your application could jeopardize the experience of another tenant.

Note: These recommendations assume all tenants share the same schema. If tenants genuinely need different schema structures, schema-per-tenant or database-per-tenant is the better fit.

Row-level isolation

Recommended. This is the most common, general-purpose method for combining tenants in a single database.

All data is stored in a single database cluster
All tenants share the same schema and tables
Each tenant's data is isolated with a column such as tenant_id

With row-level isolation, each tenant shares the same schema and tables, but has its own data.

This is the simplest model conceptually and the most scalable approach to multi-tenancy.

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    customer_name TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    total NUMERIC
);

-- tenant_id should lead most indexes
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at DESC);

-- Insert data for different tenants into the same table
INSERT INTO orders (tenant_id, customer_name, total) VALUES (1, 'Alice', 49.99);
INSERT INTO orders (tenant_id, customer_name, total) VALUES (2, 'Hans', 59.99);

-- Every query must filter by tenant
SELECT * FROM orders WHERE tenant_id = 1;

Depending on the size of your tables, row-level isolation can easily scale to many thousands of tenants. Migrations and schema changes need only be applied to a single table to update all tenants. Querying across tenants is simple and efficient.

Modeling tenants

In most multi-tenant applications, tenants have metadata beyond just an ID — a name, a region, etc. A dedicated tenants table gives you a place to store this and lets the tenant_id column across your schema remain a compact, performant BIGINT foreign key.

CREATE TABLE tenants (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code VARCHAR(2) UNIQUE NOT NULL, -- 'uk', 'de'
    name TEXT NOT NULL               -- 'United Kingdom', 'Germany'
);

Using a BIGINT for tenant_id is preferred over text-based identifiers. A BIGINT is faster to compare than a string and is a stable identifier that won't need to change if a tenant rebrands or a region code is restructured.

The column name tenant_id is a common one, but not a required naming convention. For example, a social media application may use the column user_id for the same purpose.

Enforcing tenant filtering

The inherent risk of row-level isolation is that every query must include WHERE tenant_id = ?. Rather than relying on each query to add this manually, use ORM global scopes, middleware, or a shared data access layer to inject the tenant filter automatically.

Postgres also offers Row-Level Security (RLS) as an optional, additional layer of defense. RLS automatically appends a filter to every query on a table based on a session variable. In the example below, RLS ensures that queries are scoped to the current tenant without relying on the application to include the filter.

-- Create a non-superuser role for the application
CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;

-- Enable RLS and define the policy
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::BIGINT);

-- At runtime, your app sets the tenant context per request
BEGIN;
SET LOCAL app.current_tenant = '1';
-- SET LOCAL ensures the setting is scoped to this transaction
-- which is important when using connection pooling.
-- Only returns orders for tenant_id = 1

SELECT * FROM orders;
COMMIT;

We generally don't recommend relying on RLS. It shifts security logic into the database, where policy misconfiguration, silent failures, and connection pooling interactions are difficult to debug. Keep tenant isolation enforced in your application code.

Partitioning

With all data stored in a single table, as your database scales and your tenant count grows, row-level isolation can be further optimized by partitioning the table. The tenant_id column, which is used to partition the data, is an ideal partition key.

Partitioning is a Postgres feature that splits a single logical table into multiple sub-tables based on a column value. Your application queries don't need to target a specific partition, as Postgres will automatically route the query to the correct one.

In practice, you would only partition tables that grow large enough to benefit from it. A messages table with billions of rows is a strong candidate for partitioning by tenant, but not a small reference table like office_locations with only thousands of rows.

Note that Postgres requires the partition key to be part of the primary key on partitioned tables.

-- Create a partitioned table
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    tenant_id BIGINT NOT NULL,
    customer_name TEXT,
    total NUMERIC,
    PRIMARY KEY (tenant_id, id)
) PARTITION BY LIST (tenant_id);

-- Create a partition for each tenant
-- All rows with tenant_id=1 (UK) go into 'orders_tenant_1'
CREATE TABLE orders_tenant_1 PARTITION OF orders FOR VALUES IN (1);
-- All rows with tenant_id=2 (DE) go into 'orders_tenant_2'
CREATE TABLE orders_tenant_2 PARTITION OF orders FOR VALUES IN (2);

-- Your application doesn't know or care about partitions
INSERT INTO orders (tenant_id, customer_name, total) VALUES (1, 'Alice', 49.99);
-- Postgres automatically routes this to orders_tenant_1

SELECT * FROM orders WHERE tenant_id = 1;
-- Postgres only scans orders_tenant_1 (partition pruning)

Partitioning can greatly improve performance and scalability by reducing the amount of data that needs to be scanned and the size of indexes. Internal processes such as vacuuming and index maintenance are also performed on a per-partition basis.

This adds to operational overhead, as you will need to create a new partition for each tenant.

Row-level isolation with partitioning offers some of the benefits of database-per-tenant multi-tenancy with lower operational overhead.

Tenant data lifecycle

With partitioning, onboarding each new tenant requires creating a new partition.

Partitioning simplifies offboarding tenants: you can drop the partition, and all data for that tenant is deleted.

-- Wrap in a transaction in case the DROP fails
BEGIN;
ALTER TABLE orders DETACH PARTITION orders_tenant_1;
DROP TABLE orders_tenant_1;
COMMIT;

Without partitioning, a new tenant's data can be inserted into a table with no schema changes or migrations.

However, removing tenants requires doing table-level delete operations, which can generate a significant number of dead tuples and increase vacuum pressure.

DELETE FROM orders WHERE tenant_id = 1;

Schema-per-tenant

Generally not recommended. Schema-per-tenant has a few benefits but does not work well at scale.

All data is stored in a single database cluster
Each tenant has its own schema and tables
Each tenant's schema and data are isolated by the schema name as a prefix to the table name

The appeal of this approach is greater isolation, since your queries do not need to filter on a specified tenant_id column. Instead, your application can reuse the same queries but with a different search_path to target the correct tenant's data.

-- Create the schemas
CREATE SCHEMA uk;
CREATE SCHEMA de;

-- Each gets identical tables
CREATE TABLE uk.orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);
CREATE TABLE de.orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);

-- At runtime, your app sets the search path per request
BEGIN;
SET LOCAL search_path TO uk;
SELECT * FROM orders;  -- returns uk.orders data
COMMIT;

BEGIN;
SET LOCAL search_path TO de;
SELECT * FROM orders;  -- now returns de.orders data
COMMIT;

There are performance benefits to using a schema-per-tenant. With each table containing fewer rows, indexes are smaller and more likely to fit in the buffer cache. One tenant's update/delete churn will not increase another tenant's bloat or vacuum workload.

However, the operational overhead of maintaining a schema-per-tenant outweighs the performance benefits. It increases schema migration complexity because they need to be applied to each tenant's schema. Should you need to query across tenants, complex cross-schema joins will be required.

While this approach works, it likely won't scale beyond a few hundred tenants. Every table, index, constraint, and sequence across all schemas lives in shared system catalogs. With hundreds of schemas, each containing even a modest number of tables and their indexes, these catalogs grow into millions of rows. This slows the query planner as it consults the catalog on every query. Migrations slow down as the catalog size increases.

Safety concerns of `SET search_path`

There is no database-level enforcement of preventing access to the wrong schema. Schema-per-tenant feels like greater separation of data, but it does not meaningfully impact data isolation from a security perspective. You may also need to create a separate database user and set up precise schema-level permissions for better security.

Tenant data lifecycle

Onboarding new tenants requires creating a new schema for the tenant and performing a migration.

Removing tenants from a schema-per-tenant configuration may be one of the few operational advantages of this approach to multi-tenancy, as it is a single, simple operation.

DROP SCHEMA uk CASCADE;

Database-per-tenant

Generally not recommended. Database-per-tenant has a few benefits but is at odds with the connection model of Postgres.

All data is stored in a single database cluster
Each tenant has its own logical database, schema, and tables
Each tenant's data is isolated by the logical database name

Within a PlanetScale Postgres database, you have the option to run CREATE DATABASE to create many logical databases within a single database cluster.

The appeal of using logical databases per tenant is increased isolation: you do not need to filter by a column or modify search_path; instead, you can modify the connection string to connect to the correct database. This makes working with the data and schema of an individual tenant much simpler.

-- Create separate databases
CREATE DATABASE uk_store;
CREATE DATABASE de_store;

-- Connect to the UK database and create tables there
\c uk_store
CREATE TABLE orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);

-- Connect to the German database and do the same
\c de_store
CREATE TABLE orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);

There are notable performance benefits to using a database-per-tenant. With each table in each database containing fewer rows, indexes are smaller and more likely to fit in the buffer cache. One tenant's update/delete churn will not increase another tenant's bloat or vacuum workload.

Database-per-tenant is better for performance than a schema-per-tenant, as each database contains its own catalog of tables, indexes, constraints, and sequences.

However, these performance benefits are still outweighed by the drawbacks of increased operational complexity. Critically, connection pooling becomes a problem immediately, as PgBouncer pools are calculated per-database and will quickly exceed your max_connections limit. Connection limits are the primary issue with database-per-tenant multi-tenancy.

Additionally, each CREATE DATABASE copies Postgres's template database, consuming roughly 8 MB. Unlike schema-per-tenant, where all schemas share a single set of system catalogs, every logical database carries its own, multiplying storage and catalog maintenance overhead with each new tenant.

While all the isolation and performance benefits of a database-per-tenant are compelling, it conflicts with Postgres's connection model.

Additionally, if you need to query across tenants, there is no way to do so in Postgres. You would need to use an external data warehouse or a custom application layer to join the data together.

While this approach works, it likely won't scale beyond a few hundred tenants.

Security considerations

Of all the multi-tenancy approaches, the database-per-tenant approach is the most isolated from a security perspective. Each tenant has its own logical database, schema, and tables. Each tenant's data can be accessed only by a user with privileges to that database and schema.

Even so, the limitations on connectivity and the operational complexity of this model make it difficult to recommend.

Tenant data lifecycle

Every new tenant requires a new logical database to be created and a migration to set up its tables.

Removing tenants from a database-per-tenant configuration may be one of the few operational advantages of this approach to multi-tenancy, as it is a single, simple operation with no side effects.

DROP DATABASE uk_store;

Protecting tenants from each other

In all three approaches to multi-tenancy, tenants must be protected from one another, both in terms of data access and resource contention.

Our recommended approach, row-level isolation, is the most exposed because tables and indexes are shared. Care must be taken here to keep things safely isolated. Schema- and database-per-tenant approaches are more isolated at the relation level, but all three compete for CPU, memory, disk I/O, and connections.

One tenant running an expensive query degrades performance for all other tenants, commonly referred to as a "noisy neighbor" problem. Within your database, you can add some protection by setting statement_timeout and idle_in_transaction_session_timeout appropriately. Your application should also be aware of potential rate limits, which could allow one tenant to disrupt another tenant's experience.

PlanetScale Query Insights can help you identify and troubleshoot performance issues within your database, which you can debug manually or with an Agent using the PlanetScale MCP server.

Migrating to row-level multi-tenancy

Should your application already be configured for schema, database, or some other kind of multi-tenancy, you may be able to migrate to row-level multi-tenancy by adding a tenant_id column to your tables and updating your application to filter by this column.

If you are not yet on PlanetScale, we have successfully migrated large, multi-tenant workloads that were experiencing operational, performance, or scaling issues. We offer hands-on assistance on a case-by-case basis.

Reach out to discuss your current situation.

Other examples for multi-tenancy

For needs that are less than mission critical, such as internal applications and side projects, you may diverge from the recommendations in this post. For example, you might like to run distinct applications from a single database cluster, as it seems cheaper or operationally advantageous.

If your multiple "tenants" are actually different applications with unique data structures running from a single database, we simply ask you to exercise caution.

If you can't behave, be careful.

RLS sounds great until it isn't

Meg528 — Mon, 11 May 2026 16:06:32 +0000

By Josh Brown

When you leave your house, go to sleep, or go do work in the yard, you lock your door. Maybe you have a gate or fence you lock too. Without these, anyone can waltz into your house and snoop around.

Row Level Security (RLS) can be attractive to developers for numerous reasons, but the foot-guns and gotchas in RLS often outweigh the benefits. You probably want to keep your doors locked.

Friends and family: Managing access

RLS for Postgres lets administrators define security policies in their database, instead of the application layer. Let's imagine your house is your database, and the rows, tables, and data are like the things inside.

When your friends or family come over, you give them keys to every drawer they are allowed to have access to. Maybe everyone gets access to the silverware, but only the family can access your laundry room.

This is similar to how policies work in RLS. The rules for who gets which keys are your policies. If a user passes a policy rule (has the key) then they are allowed to access the data. At a very small scale, this can seem like a great idea. Anyone can access your database however they want and your policies ensure they aren't seeing things they shouldn't.

Testing and scaling these policies as your database grows becomes near impossible. For every new feature in your application, you must ensure your RLS policies are protecting the correct rows. Remembering to add these policies can be cumbersome, especially when they need to be manually synced to your codebase.

RLS fundamentally exists to protect your data. If you mess up even a single policy however, your data becomes exposed. Managing access in the same location your code lives is much easier than remembering to write a new policy every time a new table, column, or feature is added to your product.

The party: Managing connections

Postgres uses a process-per-connection architecture. Each new user connecting to your database directly with their role is like a new person coming into your house. At first it's fine, but once you have 100 people it gets crowded pretty quick.

PgBouncer is a connection pooler that reuses a small number of direct connections to your database while letting many clients connect to it. When using PgBouncer with RLS, you lose the upstream identity of the client.

The traditional way of solving this is using local variables instead of roles to define RLS policies. You define a policy that reads from a session-local variable instead of checking the Postgres role:

CREATE POLICY user_isolation ON orders
  FOR ALL USING (user_id = current_setting('app.tenant_id')::bigint);

Then wrap every transaction in your application to set that variable:

BEGIN;
SET LOCAL app.tenant_id = '1234';
SELECT * FROM orders;
COMMIT;

This requires a lot of extra application code to manage all the different local variables attached to each and every transaction (1). If SET LOCAL is omitted, current_setting() returns an empty string or throws an error depending on how your policy is written.

Annoying neighbor: Attack Surface

You go out to get your mail and you find your neighbor standing over your mailbox trying to open it over and over. You try to tell them that one is yours and to let you in, but they are having none of it. Now you have to sit and wait until they get bored and figure out they don't have the right key.

RLS acts like an extra WHERE clause appended to your queries. Unless the user lacks read permission on a table, their queries will still run even if no data is returned. On complex joins or queries lacking indexes, this can hurt database performance.

If a malicious user starts retrying a query over and over, RLS will make sure they don't see any data, but cannot stop them from running the query itself. Relying on RLS to completely protect your tables burns valuable CPU cycles and can potentially starve your other, honest users.

Any user of your application, particularly in situations where you do not have sufficient rate limiting in place, can DDoS your database simply by hitting an API endpoint. This is preventable by checking authentication to see if a user is allowed to run a query, without relying on RLS to manage your security for you.

A large keyring: Performance Implications

Every time your friend goes to get a Diet Coke, they need to find the fridge key on their very large key chain. This wastes valuable time sifting through all the different keys and trying each one, so instead they mark the key so it's easier to find next time they go to the fridge.

RLS policies are generally executed per row (2), meaning any function or complex logic will run for each row scanned. This can be solved by wrapping functions into subqueries. Setting up a simple benchmark, we can see the difference between RLS, RLS cached, and with RLS disabled. If you want to try it yourself, you can use this benchmark repository.

For this benchmark, we tested 5 different setups. Two different functions that are called from two different policies, and one without RLS at all.

RLS with a VOLATILE function
RLS with a STABLE function
RLS with a VOLATILE function + cache
RLS with a STABLE function + cache
No RLS

A volatile function is defined with the keyword VOLATILE that tells Postgres the function may modify data or return different values upon successive calls. This is the default mode for a new function in Postgres.

CREATE OR REPLACE FUNCTION get_current_role()
RETURNS TEXT
LANGUAGE SQL
VOLATILE
SECURITY DEFINER
AS $$
    ...
$$;

The other option is to use STABLE in our function definition. Stable functions cannot modify data, and are expected to return the same value for successive calls within the same transaction. When using RLS however, Postgres does not cache the value when evaluating the policy on each row during queries. In order to successfully cache the result across each policy evaluation, we need to trick Postgres.

When we wrap the function call in a SELECT, Postgres creates an InitPlan query node type. By default, anything after the USING keyword is executed as a SubPlan type, where Postgres expects that the outcome can change row to row. This is desired as that is what we are checking; for every row, should the user be allowed to fetch it.

An InitPlan is only run once per execution of the outer plan, and cached for reuse in later rows of the evaluation. Using EXPLAIN, we can see how the different policy definitions change the estimated cost.

-- RLS without subquery: no InitPlan, high cost
CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id')::bigint AND get_current_role() = 'admin');
EXPLAIN:
    Aggregate  (cost=34828.68..34828.69 rows=1 width=40)
      ->  Index Scan using orders_tenant_id_idx on orders  (cost=0.43..34826.20 rows=495 width=6)
            Index Cond: (tenant_id = (current_setting('app.tenant_id'::text))::bigint)
            Filter: (get_current_role() = 'admin'::text)

-- RLS with subquery: Initplan caches result, lower cost
CREATE POLICY tenant_isolation ON orders USING  (tenant_id = current_setting('app.tenant_id')::bigint AND (SELECT get_current_role()) = 'admin');
EXPLAIN:
    Aggregate  (cost=10095.69..10095.70 rows=1 width=40)
      InitPlan 1
        ->  Result  (cost=0.00..0.26 rows=1 width=32)
      ->  Index Scan using orders_tenant_id_idx on orders  (cost=0.43..10092.95 rows=495 width=6)
            Index Cond: (tenant_id = (current_setting('app.tenant_id'::text))::bigint)
            Filter: ((InitPlan 1).col1 = 'admin'::text)

The cost= in the explain rows is Postgres' guess at how expensive a query will be to run, in arbitrary units. The first number is the estimated startup cost; or how expensive it is to do the sorting and filtering of the query before returning rows to the user. The second number is the estimated total cost, including fetching all the rows. The rows= and width= are how many expected rows the query will return, and the width of those rows respectively.

When Postgres doesn't think it can cache the inner query, the cost is over 3x higher than if it would have been able to. In reality, the actual latency difference is much larger than 3x as seen in the chart above.

When Postgres doesn't cache expensive functions in your policy definitions, RLS becomes expensive overhead. RLS can be just as fast as if you weren't using it at all in some scenarios. The issue is that RLS becomes yet another layer of code that needs to continuously optimized, where small mistakes can cause large performance hits.

It's your house: Permission ownership

It's your house, you obviously have the keys to everything, but what if you weren't supposed to?

Every Postgres table has an owner. Normally you'd control table and row access on a per-Postgres-role basis, however when you connect to Postgres as the owning role of a table, none of its RLS policies apply. You must explicitly opt in:

ALTER TABLE users FORCE ROW LEVEL SECURITY;

Even this may not be sufficient if you are connected with the Postgres superuser role. Any roles that contain the SUPERUSER attribute will always bypass RLS. This is easy to miss and easy to test incorrectly. Your policy tests might pass under a non-owner role while production traffic runs as the owner.

Making a ham sandwich: Stricter patterns

Let's say your friend Andy wanted to make a ham sandwich. He had access to the fridge and utensils, but not your grocery list. When he made his sandwich, he used up all the mustard, and now you need to go get more. When using RLS, Andy's query can't touch our grocery list. We have to update that separately.

Without RLS this is easy. When using RLS, doing this type of query can add a lot of complexity. Getting the utensils, making the sandwich, and updating the grocery list might not share the same permissions. While rows in one table may be accessible to a user, updating rows in another may not be. Since we own the grocery list, we don't want anyone touching it except in well defined scenarios.

One way to solve this is by using multiple roles and multiple transactions, but this becomes overly cumbersome on our application layer. A better solution would be to add a SECURITY DEFINER function in our database that gives roles access to modify or view data in a well defined way:

CREATE FUNCTION use_ingredients(ingredients text[])
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER AS $$
BEGIN
  -- Runs as the function owner, bypassing Andy's RLS policies
  UPDATE grocery_list SET quantity = quantity - 1
  WHERE item = ANY(ingredients);
END;
$$;

SECURITY DEFINER causes the function to run as its owner's role, bypassing RLS entirely for that operation. Now you're back to managing security on both RLS and your application layer, ensuring only specific parameters are allowed to pass to this function.

Keeping database functions in version control also becomes difficult. Some migration tools include SQL functions and policies, but are another part of your schema migrations that can cause headaches down the road.

Your application layer also needs to stay in sync with every function it calls in your database. Changing function definitions, names, or return values may require a new database migration, or delicate surgery to ensure a stable update.

End of the day

Once we have managed locking everything under a different key inside your house, who has what keys, who is allowed in, and who is delegating access for who, we find our application code has almost as much logic as if it didn't have RLS at all.

RLS policies themselves are stored in pg_policies inside your database, not in your source code. Most standard migration tools don't track policy changes alongside schema changes. Policy migrations become a separate, manual process, and they drift. A schema change that adds a column or renames a table can silently break a policy that no one realizes is outdated until something breaks in our application, impacting users.

Each query to the database will already need some sort of modifier in your application code to add local variables for user identification when using PgBouncer. Misconfigured local variables could be just as damaging as if RLS wasn't there to begin with.

We still need to check early on if a user has permission to run a query, or else we risk allowing users to degrade our database performance with spam. If we are already checking permissions at the application layer, the benefits of RLS become harder to observe.

Optimizing queries also becomes much harder. Queries are artificially restricted to what they are allowed to see, and need bespoke functions and permissions to get access. This causes our management of source code and database logic to become even harder to manage, between policies, functions, and the mappings between them.

How to do it right

At PlanetScale, we typically recommend against relying on Postgres RLS. There may be occasional useful scenarios, but when implementing RLS correctly at scale, the benefits quickly turn into cons with a higher overhead not only to performance, but also developer experience and complexity.

Application-layer authorization like middleware, ORM-level scoping, or a dedicated permissions table keeps your logic visible, testable, and co-located with the code that uses it.

Your database is more like a warehouse. Don't treat it like your house.

Footnotes

Note that PgBouncer pool_mode must be in either session or transaction. statement mode won't work with SET LOCAL at all.
The Postgres query planner can sometimes determine that a policy is safe to cache across evaluations on its own. Doing this properly can be a tricky process. Even in our benchmark example, functions that are marked as stable still need to be wrapped in a subquery in order for Postgres to properly cache the result. Each policy is different, and determining the proper optimizations for each one is another layer of complexity in your codebase.

High Memory Usage in Postgres is Good, Actually

Meg528 — Mon, 04 May 2026 15:56:50 +0000

By Simeon Griggs

Houseplants often die from over-watering, not neglect. It is easy to project human needs onto them: "If I am thirsty, they must be thirsty too." But many indoor plants actually benefit from drying out between waterings.

Similarly, your empathy can lead to misinterpreting signals from your database. You don't like feeling overwhelmed, so you don't want your database overwhelmed either.

But not all usage is created equal, and memory in computers can be uniquely complex to understand.

A look at your PlanetScale dashboard might show memory usage sitting at 80%. That looks bad, but it could actually be representative of a healthy system.

To be clear, consistently high CPU usage is a problem. For as long as CPU stays high, queries wait longer, the slowest queries get slower, and you have less headroom for spikes.

Memory is different. The percentage shown in the cluster diagram on your PlanetScale dashboard is measuring the entire node your database runs on, not just Postgres. When most RAM is in use, it usually means the system is keeping data close to the CPU so it does not have to read from disk as often. Unlike sustained high CPU, high memory usage by itself does not mean performance is degraded or that you are at immediate risk of "running out" of memory.

Why Postgres wants your memory

Reading from disk is slower than reading from RAM, even with PlanetScale Metal's locally attached NVMe drives. Postgres is designed to take advantage of that gap by caching as much data in memory as it can.

There are two layers of caching at work, and both consume RAM.

shared_buffers is Postgres' own buffer pool. When a query needs data, Postgres first checks this pool for the relevant pages, the fixed-size (8 KB by default) chunks of table and index data it works with, before reading from disk. The more of your working data that fits here, the fewer disk reads Postgres needs to perform.

This parameter can be configured in the cluster configuration page of the PlanetScale dashboard. The default value should be sufficient for most workloads, and modifying it should not be your first step in troubleshooting memory usage.

The OS page cache is the second caching layer. Even when Postgres does go to disk, the operating system keeps a copy of the data it reads in RAM so the next access is faster. This is not a Postgres feature — it is standard Linux behavior. Postgres was designed with this in mind, and its own documentation notes that the operating system's cache is expected to handle data beyond what fits in shared_buffers.

Between these two layers, a healthy Postgres server will use most of the available RAM. That is the goal, not a side effect. For context, reading a page from RAM is roughly 1,000 times faster than reading it from even a fast NVMe drive. A database that keeps frequently accessed data in memory avoids that penalty on every query.

When caching is working well, the vast majority of page reads are served from memory without touching disk. If that ratio drops — because the working dataset has outgrown available memory, for example — queries slow down as Postgres waits on disk more often.

See our documentation on "Normal operating ranges" to sense-check what values you should be seeing in Cluster Metrics for CPU, memory, and more.

Memory usage compared to CPU usage

At a glance, CPU and memory usage numbers look comparable because they share a 0–100% scale, but they describe very different behavior.

CPU is work. Sustained high CPU means the database is spending time on work it cannot skip. When CPU is saturated, queries arrive faster than they can be processed. They queue, latency climbs, and connection timeouts can cascade into application-level failures. There is no "good" kind of sustained high CPU usage.

Memory is workspace. Postgres and the OS use spare RAM to avoid expensive disk reads. Higher use improves performance ... most of the time.

"Most of the time" because memory usage gets a little complicated.

Two kinds of memory usage

The single “memory usage” percentage number combines two different behaviors.

To explore that number in more detail, within the Cluster Metrics page of the PlanetScale dashboard, memory is shown as a stacked chart over time with four different categories: active cache, inactive cache, RSS, and memory mapped. These four categories can be grouped into two separate but equally important use-cases: cache and process memory.

1. Cache (active, inactive, and memory mapped)

Much of what looks like “used” memory on a healthy database host is cache: file data the operating system keeps in RAM after reads so the next access is cheap. You may see this referred to as "page cache" in other dashboards.

Active cache is data the OS recently touched and wants to keep around. Inactive cache hasn't been accessed lately. Memory-mapped pages are cached pages that are backed by real files on disk.

All three of these cache types are reclaimable by the operating system and can be dropped when something else needs RAM.

If total memory is high because cache is high, good! Frequently accessed data stays near the CPU for faster access.

2. Process memory (RSS)

Separately, Postgres holds memory for processes that are actually using it. You will see this referred to as RSS (Resident Set Size) in the PlanetScale dashboard.

This memory is not reclaimable by the operating system and is what increases out of memory (OOM) risk. High memory usage through high RSS leads to restarts and degraded behavior.

If total memory is high because RSS is high, that is referred to as memory pressure and is a problem.

What is Resident Set Size?

Roughly, RSS is the amount of private memory allocated to a process such as stack, heap, catalog/relcache caches, query execution memory like sorts and hash tables.

Given Postgres' process-per-connection architecture, each process requires some baseline amount of memory. Not every process will consume the same amount of memory.

Further, some memory use is shared across processes. So calculating RSS use is not as simple as adding up the memory usage of every process.

RSS increases for a number of reasons:

Postgres may grant multiple work_mem allocations within a single query; see below for more details.
Catalog bloat can spike RSS usage, common in multi-tenant schemas using a table-per-tenant pattern.
The operating system's memory allocator may not return memory efficiently.
Misbehaving or misconfigured extensions can increase RSS usage.
Cached plans and prepared statements accumulate per-session memory that is not released until the session ends or the statement is explicitly deallocated.

The work_mem parameter's default value is set relative to the amount of memory in your database cluster. It can be modified in the cluster configuration page of the PlanetScale dashboard.

Tuning work_mem might seem like an obvious lever — decrease it to reduce RSS, or increase it to prevent operations from spilling to disk. But the allocation is per-sort/hash-node, per-query, per-backend.

A single complex query can allocate work_mem multiple times, and that multiplies across every active connection. Setting it too low forces more disk I/O; setting it too high globally can cause total memory usage to spike unpredictably under load. Neither direction is a safe default change without first understanding your workload's concurrency and query complexity.

Efficient connection pooling can be the best way to reduce RSS usage. Fewer active connections result in fewer copies of all that per-process overhead.

PgBouncer on PlanetScale runs in transaction mode, where connections are returned to the pool after each transaction completes. See our blog post on Scaling Postgres connections with PgBouncer for more details.

Investigating memory usage while debugging performance

If you're experiencing degraded performance, the challenge is figuring out what drove the RSS growth.

Query Insights helps you investigate query performance through CPU time, I/O, and latency, but it does not show per-query memory. You may see OOM markers and slow-query signals, but not query-specific RSS usage.

RSS is a per-process metric, not a per-query metric. That means you cannot read “RSS per query” directly from EXPLAIN or Query Insights. Instead, you may need to gather multiple signals and triangulate:

Use Cluster Metrics to identify when RSS rises.
In Query Insights for that same window, look for expensive patterns (high runtime, CPU, I/O, rows/blocks read) and OOM-adjacent activity.
Re-run suspect queries with EXPLAIN (ANALYZE, BUFFERS, MEMORY) to inspect operator-level memory usage.
Check connection counts in the same window, because many concurrent connection processes can increase RSS even when a single query is moderate.

The out of memory documentation has more details on the likely causes of, and how to prevent, OOM events.

In summary

A lot of cached data in memory is a good thing. Ideally, your "hot dataset" fits in the page cache of your database cluster to maintain fast performance. Too little cached data can lead to increased CPU usage and degraded performance.

High memory usage is not automatically bad. If your high memory usage is due to cache, you typically have a healthy, performant database.
Memory pressure is bad. Rising RSS toward limits, OOM kills, unexplained restarts, and tail latency spiking together with heavy disk I/O when the working set is tight on RAM are the signals to act on.
Sustained high CPU is a problem. It means you are out of headroom. Tune the workload (see Query Insights) or upgrade.

If the dashboard shows a high “% memory used,” do not panic. Investigate the types of memory being used and check for OOM events before taking action.

Stripe Projects Partnership: Provision PlanetScale Postgres and MySQL Databases From the Stripe CLI

Meg528 — Mon, 27 Apr 2026 17:55:54 +0000

By Elom Gomez

Did you hear the news? PlanetScale is participating as a co-design and launch partner for Stripe Projects, a new developer preview from Stripe that centralizes dev tool provisioning and billing in one place.

What is Stripe Projects?

Stripe Projects is a new way for developers and coding agents to discover, provision, and pay for developer tools all from the Stripe CLI. Instead of jumping between dashboards, entering payment info, and copying credentials across services, everything lives in one centralized workflow.

This fragmented developer workflow has always existed, but AI agents have made the gap much more obvious. The ecosystem has been missing a standard way for provisioning and credential handoff to work reliably across providers. And we're excited to partner with Stripe to close this gap.

With PlanetScale as a launch partner, you can now spin up and pay for fully managed MySQL or Postgres databases directly from your terminal in seconds.

Try it out today

Stripe Projects is currently in developer preview. You can request early access here. Once you're in, follow these instructions to spin up a PlanetScale Postgres or MySQL database:

Install the Stripe CLI
Install the Projects plugin: stripe plugin install projects
Initialize Stripe Projects in your app stripe projects init
Add a PlanetScale database: stripe projects add planetscale/postgresql or stripe projects add planetscale/mysql
Go through the prompts to create your database: database name, cluster size, region, and number of replicas
Within seconds, your PlanetScale Postgres or MySQL database is provisioned without you ever leaving the terminal
Sync your database credentials to your .env file: stripe projects env --sync

Resources and feedback

You can start using PlanetScale with Stripe Projects in the Stripe Marketplace. Or, head to the Stripe Projects documentation to learn more.

We'd love to hear how you're using PlanetScale with Stripe Projects. Join our Discord to let us know or reach out to us on X!

Enhanced Tagging in Postgres Query Insights

Meg528 — Mon, 20 Apr 2026 16:14:33 +0000

By Rafer Hazen

As part of our Traffic Control launch, we made enhancements to the Insights query tagging feature for Postgres databases. Insights has supported query tags for some time, but they were previously only attached as metadata on individual notable query logs. With this release, tags are now present in aggregated query data, which enables powerful new capabilities. It's now possible to view the complete distribution of tags assigned to a query pattern, search queries by tag, and see a per-tag breakdown of database-level statistics. This blog post gives an overview of the feature, and digs into the details of how we implemented it.

Adding Tags

Query tags are string key-value pairs that are included in query SQL using specially formatted SQL comments. For example, the following query has the controller and action tags attached.

select * from users 
  where id = 1 
  /* controller='users',
     action='show' */;

Typically tags are specified at the application level and applied automatically to all queries issued by the database framework you're using. Common examples are controller, action, job, or source_location.

In addition to tags set by the database client, Insights automatically adds the following tags to all queries:

application_name - set by the Postgres driver
username - the Postgres user executing the query
remote_address - the remote IP address

Feature Overview

This feature introduces three new surfaces where tag information can be seen.

Query Pattern Tags

To see the set of tags associated with a given query pattern, click on a query pattern from the main Insights dashboard. This page lists the tags that have been submitted with a given query pattern over a particular time range, as well as the percentage of queries that included each tag value.

Database Tags

To see aggregate statistics for your entire database broken down by tag, go to the Tags section in the Insights sidebar and select the tag or set of tags that you want to view.

Query Filter

To see a list of query patterns that have a given tag value, go to the Insights dashboard and search for a particular tag with tag:MY_TAG:MY_VALUE. The returned query patterns and statistics are filtered to only queries with the specified tag pair.

Implementation

To understand how tagging works in Insights, it helps to understand the underlying data sources that power Insights. Query performance data is observed by the Insights Postgres extension, emitted to Kafka and written to ClickHouse. The extension publishes to two separate Kafka topics:

Individual queries - any query reading more than 10,000 rows, taking longer than 1 second, or resulting in an error. One message is sent per qualifying query. This powers the Notable queries feature.
Aggregate summaries - statistics like total query count, rows read, and cumulative query time. One message is sent for every query pattern every 15 seconds. This powers the majority of Insights including the query table, anomalies, and all query-related graphs.

Prior to this release, tag data was only attached to the individual query data stream. This adds important information to notable queries, but because the data wasn't present in the aggregate summaries, it wasn't possible to filter or group aggregate data by tag. Insights couldn't answer important questions like:

What queries has this user executed?
What percentage of my total query run time is coming from this controller?
Which background jobs are executing this query?

Our goal with this release was to associate all query data with the relevant tags to make it possible to answer this class of questions.

Sending Tags

To explore the various approaches for implementing tags, let's use the following query executions as an example:

select * from users where id = 1 /*controller='users'*/;
select * from users where id = 2 /*controller='sessions'*/;
select * from users where id = 3 /*controller='sessions'*/;

Since each of these queries has the same fingerprint (query with all literal values removed), without tags we would only need to send a single summary message. To include tags, we have several options. The first would be to continue sending only a single query summary event with a count of how many times each tag was observed. This would produce a summary message like the following (other stats fields are omitted):

{
    sql: "select * from users where id = ?",
    query_count: 3,
    total_time: "100ms",
    tags: {"controller=users": 1, "controller=sessions": 2}
}

This message tells us the given query was executed three times - twice from the sessions controller and once from the users controller - and had a cumulative execution time of 100ms.

At first glance, including tags in this manner is an attractive option. It's simple to implement - we just accumulate tags along with the other aggregate stats - and it doesn't increase the number of events that need to be emitted and stored. It has a serious shortcoming, however: it's not possible to attribute aggregated stats to any individual tag. For example, it's not possible to know the total time of queries emitted from just the users controller, because we can't tell what portion of the 100ms was associated with controller=users. The summary data for one tag is permanently combined with the data from all tags.

To overcome this limitation, we can instead emit a separate aggregate summary message for each set of unique tags. In our example this would mean we emit two separate messages to the insights pipeline:

{
    sql: "select * from users where id = ?",
    query_count: 1,
    total_time: "20ms",
    tags: {"controller": "users"}
}
{
    sql: "select * from users where id = ?",
    query_count: 2,
    total_time: "80ms",
    tags: {"controller": "sessions"}
}

This approach makes it possible to fully disambiguate aggregated statistics based on the attached tags. We can tell that the users controller was responsible for exactly 20ms of total execution time and the sessions controller was responsible for exactly 80ms.

This comes at a cost though: we have to emit a separate message for each unique tag combination. This can be problematic for high-cardinality tags (tags with a large number of distinct values). Consider a customer that has set a request_id tag on all of the queries issued from their web tier. Where we previously would be able to collapse 500 user-lookup queries into a single summary message, we now have to send 500 messages because they each have a unique request_id. In the worst case, this means that the summary data stream must send one summary message per query execution, and we've lost all of the scalability advantages of aggregating query statistics. For large clusters executing millions of queries per second, this would be prohibitively expensive to process and store, and would consume considerable resources on the database host where telemetry data is emitted.

To prevent this from overwhelming the pipeline, we implemented several strategies to dynamically reduce the cardinality of tags and therefore decrease the number of messages that must be handled by the Insights pipeline.

Cardinality Reduction

The core idea is simple: when a tag (or set of tags) would result in sending too much telemetry data, we collapse that tag by replacing specific values (like request_id="a" and request_id="b") with a value that indicates it has been removed: request_id=*. This lets us more aggressively merge aggregates and reduce the total number of messages sent, while ensuring that we're capturing 100% of the summary data.

We employed two separate approaches for tag collapsing.

Per-tag Limits

This mechanism tracks the number of unique values seen for each tag key, scoped per query pattern. If that count exceeds a predefined limit (currently 20), we proactively collapse that key for all queries for the next hour. This catches inherently high-cardinality tags like request_id or user_id.

An important part of this approach is that cardinality is monitored per query pattern and not globally. Consider the source_location tag that contains the file and line number showing where the query was initiated in the client app. Overall this tag is high-cardinality, because each query pattern likely has its own unique value for source_location, but it is highly correlated with the query pattern so it doesn't actually result in additional messages being sent to the pipeline - we are already sending a separate query summary message for each query pattern. Monitoring cardinality per-pattern allows high-cardinality tags that are highly correlated with query pattern to pass through without being collapsed.

Per-interval Limits

Within each 15-second interval, we track all aggregates keyed by their unique set of tag key-value pairs. Because we must emit a message for each unique combination of tags, even individually low-cardinality tags could produce an unacceptably large number of combinations of tags. For example, if a query pattern has 6 tag keys that each have 10 distinct values, there could be 10^6 individual tag combinations. To prevent an explosion in the number of messages that must be tracked, we perform dynamic cardinality reduction on a per-interval basis for any individual query pattern that has more than a fixed number of tag combinations.

To reduce the combined cardinality of a given set of aggregates, we find the highest cardinality tag and collapse it (replace all values with a single value). We successively perform this operation until the number of aggregates is beneath the fixed threshold (currently set to 50 in production).

To illustrate this operation, consider five executions of the same query pattern.

select * from users where id = ? /*controller='users',    host='app-1'*/
select * from users where id = ? /*controller='users',    host='app-2'*/
select * from users where id = ? /*controller='sessions', host='app-3'*/
select * from users where id = ? /*controller='sessions', host='app-4'*/
select * from users where id = ? /*controller='sessions', host='app-1'*/

Without any limits, this produces five separate aggregate messages. To reduce the aggregate message count, we identify that the host tag has the highest cardinality (4 unique values) and replace all of its values with a placeholder and merge the remaining results. This yields only two combinations that must be emitted to the pipeline, one for each of the two unique controller tag values.

Tracking Tag Collapsing

When a tag must be collapsed due to either of the cardinality limitation mechanisms, we record the fact that the key has been collapsed in the emitted aggregate message. This allows us to detect when collapsing has occurred and display a message noting the percentage of tag values where the value is unknown.

Conclusion

Query tagging is a powerful feature. Being able to slice your Insights data by arbitrary tags gives you a much clearer picture of your database performance. We're excited for you to try it.