DEV Community: PlanetScale

Patterns for Postgres Traffic Control

Meg528 — Mon, 13 Apr 2026 16:44:20 +0000

By Josh Brown

Last month we introduced Database Traffic Control™. Traffic Control lets you attach resource budgets to slices of your Postgres traffic, like keeping your checkout flow running while a runaway analytics query gets shed instead. We have already discussed some scenarios where you should use Traffic Control, along with how to define resource limits, so now let's dig into what Traffic Control looks like in your codebase.

This post walks through some practical patterns in Go. Each pattern targets a different failure mode, architecture, or foot gun. Most of them layer on top of one another too, so you can adopt them individually or combine them for extra peace of mind. Keep in mind the general concepts here are applicable to whatever language your application is written in.

Setup

Most of the patterns here rely on custom tags attached to your queries. Traffic Control reads these using the SQLCommenter format: a SQL comment appended to each query with URL-encoded key=value pairs.

SELECT * FROM orders 
  WHERE user_id = $1
 /*route='checkout',feature='new_order_flow'*/;

These tags are then available for new Traffic Control rules.

Here's a minimal Go helper that appends tags in this format:

import (
    "fmt"
    "net/url"
    "sort"
    "strings"
)

// appendTags appends SQLCommenter-format tags to a SQL query.
func appendTags(query string, tags map[string]string) string {
    if len(tags) == 0 {
        return query
    }
    parts := make([]string, 0, len(tags))
    for k, v := range tags {
        parts = append(parts, fmt.Sprintf("%s='%s'", k, url.QueryEscape(v)))
    }
    sort.Strings(parts) // deterministic order
    return query + " /*" + strings.Join(parts, ",") + "*/"
}

You'll also want a way to thread tags through your call stack without touching every function signature. A context key works well for this:

type contextKey string

const sqlTagsKey contextKey = "sql_tags"

func tagsFromContext(ctx context.Context) map[string]string {
    if tags, ok := ctx.Value(sqlTagsKey).(map[string]string); ok {
        // return a copy so callers can't mutate shared state
        out := make(map[string]string, len(tags))
        for k, v := range tags {
            out[k] = v
        }
        return out
    }
    return make(map[string]string)
}

func contextWithTags(ctx context.Context, tags map[string]string) context.Context {
    return context.WithValue(ctx, sqlTagsKey, tags)
}

With these two helpers in place, the patterns below mostly just set keys and values in context. Tagging happens automatically when the query executes.

Per-service isolation via Roles

In a microservice architecture, a single misbehaving service should not be able to degrade every other service sharing the same database. The simplest way to isolate a service is to create a Traffic Control rule based on a unique connection string for the given service, or via application name.

A budget on username='pscale_api_123abc' will isolate all traffic from that role. This also helps in incident response: you can immediately cap a service's resource share without redeploying anything.

Note that the username is the internal Postgres username of the role, not the dashboard role name. You can also target custom roles created by CREATE ROLE if your microservices have strict security over table permissions.

You can also use the application_name by appending it to your connection strings such as postgresql://other@localhost/otherdb?application_name=myapp.

Route-level tagging in an HTTP service

When you're running a monolith or a large API service, the problem isn't usually the whole service, it's specific routes. The /api/export endpoint that generates CSV reports should not be able to kill the /api/checkout flow.

An HTTP middleware can inject the route into context at runtime before any handler runs:

// Any route using SQLTagMiddleware will have the pattern injected into its context
// dynamically at runtime
func SQLTagMiddleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        tags := tagsFromContext(r.Context())
        route := strings.ReplaceAll(strings.ReplaceAll(r.Pattern, "{", ":"), "}", ":") // Removes "{}" characters from route
        tags["route"] = route
        tags["app"] = "web"
        ctx := contextWithTags(r.Context(), tags)
        next.ServeHTTP(w, r.WithContext(ctx))
    })
}

Wrap your database calls to pick up the tags automatically:

// QueryContext for SELECT statements
func (db *DB) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error) {
    return db.sql.QueryContext(ctx, appendTags(query, tagsFromContext(ctx)), args...)
}

// ExecContext for INSERT/UPDATE statements
func (db *DB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error) {
    return db.sql.ExecContext(ctx, appendTags(query, tagsFromContext(ctx)), args...)
}

Now every query carries the route it came from. You can create a Traffic Control budget targeting route='/api-export' and give it a conservative CPU limit.

This also makes it easy to set up broad budgets during incidents. If you suddenly see a spike and don't know which route is responsible, the violation graph in Traffic Control will show you exactly which route tag is hitting limits.

Feature flags and new deployments

Shipping a new feature to production always carries risk. Maybe the new query pattern is fine under your test load but becomes expensive at scale. Traffic Control gives you a way to cap the blast radius before it becomes an incident.

The simplest version sets a tag from an environment variable at startup:

var deploymentTag = os.Getenv("DEPLOYMENT_TAG") // e.g. "new_checkout_v2" or git sha "96e350426"

func tagWithDeployment(ctx context.Context) context.Context {
    if deploymentTag == "" {
        return ctx
    }
    tags := tagsFromContext(ctx)
    tags["feature"] = deploymentTag
    return contextWithTags(ctx, tags)
}

Set DEPLOYMENT_TAG=new_checkout_v2 when rolling out new pods and leave it unset on the old pods. Traffic Control can then have a budget on feature='new_checkout_v2' in Warn mode from day one, so you see exactly how the new code behaves before it causes problems. When you're confident, either remove the budget or switch it to Enforce as a safety net.

For feature flags controlled at runtime, the same approach works but driven by your flag evaluation:

func (h *OrderHandler) ServeHTTP(w http.ResponseWriter, r *http.Request) {
    ctx := r.Context()
    if flags.Enabled(ctx, "new_order_flow") {
        tags := tagsFromContext(ctx)
        tags["feature"] = "new_order_flow"
        ctx = contextWithTags(ctx, tags)
    }
    h.processOrder(ctx, w, r)
}

Tier-based limits in multi-tenant apps

In a SaaS application, free-tier users should not be able to degrade the experience for enterprise customers. Traffic Control lets you enforce this at the database level rather than just at the application layer.

Inject the user's subscription tier into the SQL tags early in your request handling — ideally right after you've resolved the authenticated user:

type Tier string

const (
    TierFree       Tier = "FREE"
    TierPro        Tier = "PRO"
    TierEnterprise Tier = "ENTERPRISE"
)

func WithUserTier(ctx context.Context, tier Tier) context.Context {
    tags := tagsFromContext(ctx)
    tags["tier"] = string(tier)
    return contextWithTags(ctx, tags)
}

In your authentication middleware:

func AuthMiddleware(users *UserService, next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        user, err := users.Authenticate(r)
        if err != nil {
            http.Error(w, "unauthorized", http.StatusUnauthorized)
            return
        }
        ctx := WithUserTier(r.Context(), user.Tier)
        next.ServeHTTP(w, r.WithContext(ctx))
    })
}

With this in place, create two Traffic Control budgets:

tier='free' — conservative limits on server share and max concurrent queries
tier='pro' — moderate limits

Leave enterprise traffic unbudgeted or give it a high budget as a ceiling. When a free-tier user runs an expensive dashboard or triggers a slow query, the budget sheds that traffic before it touches enterprise workloads.

You can combine this with the route tag from Pattern 2. A budget matching tier='free' AND route='api-export' can be stricter than a budget on tier='free' alone. Enterprise export requests get more headroom than free-tier export requests.

Background jobs and scripts

Background jobs are a common cause of database incidents. A migration script, a nightly sync, or a one-off data backfill can all accidentally saturate your database if they run faster than expected. Traffic Control is a clean way to give these jobs a resource ceiling without having to tune query-level timeouts throughout your codebase.

For long-running background workers, use a dedicated connection pool with a distinct application_name:

func newJobDB(dsn string) (*sql.DB, error) {
    jobDSN, err := url.Parse(dsn) // your connection string
    if err != nil {
        return nil, err
    }
    q := jobDSN.Query()
    // This sets the application name in code instead of in the connection string env variable.
    q.Set("application_name", "background-jobs")
    jobDSN.RawQuery = q.Encode()

    db, err := sql.Open("pgx", jobDSN.String()) // connects to Postgres
    if err != nil {
        return nil, err
    }
    db.SetMaxOpenConns(4) // Jobs don't need high concurrency
    return db, nil
}

newJobDB takes the DSN of your database and sets application_name to background-jobs before connecting. Once connected we set the max connections to 4 to make sure our background job isn't taking up more workers than it should, and finally we return it so that the calling function can now query the database.

Setting application_name on the connection string level in code ensures that it is always set for this service, no matter the query or connection string given. You can pair this with SQL comments as described above for even more fine-grained control and insights into your queries.

For one-off scripts and migrations we can do something similar. Here we encode the script's identity directly in the connection string so it shows up clearly in Traffic Control and Insights:

// Returns a database instance with the `application_name` set to `script-[scriptName]`
// for use in scripts
func scriptDB(dsn, scriptName string) (*sql.DB, error) {
    u, _ := url.Parse(dsn)
    q := u.Query()
    q.Set("application_name", "script-"+scriptName) // e.g. "script-backfill-order-totals"
    u.RawQuery = q.Encode()
    return sql.Open("pgx", u.String())
}

Create a Traffic Control budget for application_name='background-jobs' in Warn mode before you run this job next. Observe how much of the database's resources your background work typically consumes. Then switch to Enforce to cap it at a level where it can't crowd out interactive traffic even if a job goes sideways.

Handling blocked queries

When Traffic Control is in Enforce mode and a query exceeds its budget, Postgres returns SQLSTATE 53000 with an error message prefixed with [PGINSIGHTS] Traffic Control:. Your application needs to handle this without crashing.

With pgx/v5:

import (
    "errors"
    "github.com/jackc/pgx/v5/pgconn"
)

const sqlstateTrafficControl = "53000"

func isTrafficControlError(err error) bool {
    var pgErr *pgconn.PgError
    return errors.As(err, &pgErr) && pgErr.Code == sqlstateTrafficControl
}

The right response depends on the query's role in your application:

func (s *OrderService) GetUserOrders(ctx context.Context, userID int64) ([]Order, error) {
    rows, err := s.db.QueryContext(ctx, `SELECT id, total FROM orders WHERE user_id = $1`, userID)
    if err != nil {
        if isTrafficControlError(err) {
            // Return a degraded response rather than a 500
            return nil, ErrServiceUnavailable
        }
        return nil, err
    }
    defer rows.Close()
    // ...
}

For non-critical workloads like analytics or reporting, returning a 503 Service Unavailable or a cached result is most likely the right behavior. That's exactly the controlled failure mode Traffic Control is designed to create. For more critical paths, you may want a short retry with backoff:

func queryWithBackoff(ctx context.Context, db *sql.DB, query string, args ...any) (*sql.Rows, error) {
    const maxRetries = 3
    backoff := 100 * time.Millisecond

    for attempt := range maxRetries {
        rows, err := db.QueryContext(ctx, query, args...)
        if err == nil {
            return rows, nil
        }
        if !isTrafficControlError(err) || attempt == maxRetries-1 {
            return nil, err
        }
        select {
        case <-time.After(backoff):
            backoff *= 2
        case <-ctx.Done():
            return nil, ctx.Err()
        }
    }
    return nil, errors.New("overloaded")
}

Observing warn-mode notices

Before switching a budget to Enforce, you'll run it in Warn mode. In Warn mode, queries succeed but the driver receives a Postgres notice containing [PGINSIGHTS] Traffic Control:. With pgx/v5 you can log these notices to build an accurate picture of what would be blocked:

import "github.com/jackc/pgx/v5"

config, err := pgx.ParseConfig(dsn)
if err != nil {
    log.Fatal(err)
}

config.OnNotice = func(c *pgconn.PgConn, notice *pgconn.Notice) {
    if strings.Contains(notice.Message, "[PGINSIGHTS] Traffic Control:") {
        log.Printf("traffic control warning: %s", notice.Message)
        // Increment a metric, write to a structured log, etc.
    }
}

Collect these logs for a few hours of representative traffic before switching to Enforce. The pattern of which rules fire and how often tells you whether your limits need adjustment.

Putting it together

These patterns compose. A real application might layer several of them:

func (s *Server) setupMiddleware() http.Handler {
    mux := http.NewServeMux()
    // register routes...

    var handler http.Handler = mux
    handler = SQLTagMiddleware(handler)   // Pattern 2: route tags
    handler = AuthMiddleware(s.users, handler) // Pattern 4: tier tags
    return handler
}

// At startup, the job worker uses Pattern 5: Background jobs
jobDB, _ := newJobDB(dsn)

// New features use Pattern 3:
// DEPLOYMENT_TAG=new_checkout_v2 set in the deployment manifest

Traffic Control sees all of this as a combination of tags. A budget on tier='free' covers all free-tier traffic regardless of route. A budget on route='api-export' AND tier='free' covers a specific combination. Multiple matching budgets all apply simultaneously and queries must satisfy every budget they match. You can build layered policies without complicated rule logic.

Start in Warn mode, observe which budgets would fire during normal load, tighten the limits until only pathological cases trigger violations, then switch to Enforce. The getting started guide walks through this rollout process in detail.

The difference between a database outage and a degraded experience often comes down to whether you've decided in advance which traffic to shed. Traffic Control makes that decision explicit and configurable instead of leaving it to whichever query happens to win a resource race.

Scaling Postgres Connections With PgBouncer

Meg528 — Mon, 06 Apr 2026 16:59:32 +0000

By Ben Dicken

The Postgres process-per-connection architecture has an elegant simplicity, but hinders performance when tons of clients need to connect simultaneously.

The near-universal choice for solving this problem is PgBouncer. Though there are upcoming systems like Neki which will solve this problem in a more robust way, PgBouncer has proven itself an excellent connection pooler for Postgres.

PlanetScale gives you local PgBouncers by default, and makes it incredibly easy to add dedicated ones when needed. The challenge comes in determining the optimal configuration for your app, which is highly use-case dependent.

My aim with this article is to make every engineer well-equipped to tune PgBouncer with confidence.

Why PgBouncer?

PgBouncer is a lightweight connection pooler that sits between your application and Postgres.

PgBouncer is totally transparent, speaking the PostgreSQL wire protocol. From an app's perspective, it's just talking to a Postgres server, PgBouncer acting as a lightweight middleman. It can multiplex thousands of client connections onto tens of Postgres connections.

But why not just make 1000s of connections directly to Postgres? Unfortunately, the Postgres process-per-connection architecture doesn't scale well. Every connection forks a dedicated OS process consuming 5+ MB of RAM and adding context-switching overhead. PgBouncer solves this by maintaining a pool of reusable server connections, reducing resource consumption and letting PostgreSQL handle far more concurrent clients than its native max_connections would otherwise allow.

It's best-practice to keep the count of direct connections to Postgres small. Tens of connections for smaller instances. Hundreds for larger servers.

This is too restrictive for the way modern apps are built. We frequently want thousands of simultaneous connections to the database. PgBouncer gives Postgres that capability while keeping the total number of forked processes low.

At PlanetScale, we recommend using PgBouncer for all application traffic, only resorting to direct connections for administrative tasks and a few other narrow cases.

How to use PgBouncer

PgBouncer maintains a pool of pre-established Postgres server connections. When an app / client needs a database connection, it connects to PgBouncer, and then PgBouncer uses one of the pre-existing pooled connections to pass along the message. When the client is done, the connection returns to the pool for reuse. A single pooled Postgres connection can serve hundreds or thousands of client PgBouncer connections over its lifetime.

When all pool connections are in use, PgBouncer queues the client until one becomes available rather than rejecting it. If the wait exceeds query_wait_timeout (default: 120 seconds), the client is disconnected with an error.

Whereas the Postgres default port is 5432, PgBouncer defaults to 6432. Typically, switching from a direct connection to a PgBouncer connection is as simple as switching the port in your client connection string.

This is true on PlanetScale, with a twist: We give you three options for using PgBouncer:

Local PgBouncer

Every Postgres database includes a local PgBouncer running on the same server as the primary. Connect using the same credentials as usual, just swap the port to 6432.

Dedicated primary PgBouncer

A dedicated primary PgBouncer runs on separate nodes from Postgres, making for better HA characteristics. It connects to the local PgBouncer first, which then connects to Postgres. Client connections persist through resizes, upgrades, and most failovers. Connect by appending |your-pgbouncer-name to your username on port 6432.

Dedicated Replica PgBouncer

Dedicated replica PgBouncers are similar to dedicated primary ones, but connect to the replicas instead (and don't route through the local bouncer).

We recommend this if your applications make heavy use of replicas for read queries.

The three pooling modes

PgBouncer operates in one of three modes.

Session pooling assigns a server connection for the lifetime of the client connection, releasing it only when the client disconnects. This means there's a 1:1 mapping between client and server connections. It's not incredibly useful, as it does little to reduce Postgres connection count. At times, it's helpful for limiting thundering herds of connections.

Statement pooling assigns a server connection for a single SQL statement and releases it immediately after. This means multi-statement transactions are disallowed entirely. Most apps need this, so not useful in 99% of cases!

Transaction pooling is the only sensible option. It assigns a server connection for the duration of a transaction, returning it to the pool the moment a COMMIT or ROLLBACK completes. This is great for most use cases, though there are a few unsupported features in this mode.

PlanetScale only supports Transaction pooling, given the clear weaknesses of the two. When you absolutely need one of those few unsupported features, keep them to a small number of direct-to-Postgres connections.

Knob all the things

PgBouncer's configuration centers on a hierarchy of connection limits. These control how many client connections are accepted, how many server connections are maintained per pool, and how those relate to PostgreSQL's own max_connections.

The connection chain works like this:

max_client_conn is the maximum number of application connections PgBouncer will accept. Because connections are lightweight in PgBouncer, this is frequently set in the 1000s.

default_pool_size controls the number of server connections per (user, database) pair that PgBouncer will make to Postgres. How to configure this depends quite a bit on your schema and access patterns. In an environment where you have a single server with many logical databases and many Postgres users, this will likely need to be set low, between 1-20. When you have a single logical database and a small number of Postgres roles, this can be set much higher.

The total potential PgBouncer ↔ Postgres connections equals num_pools × default_pool_size. With 4 users and 2 databases we get 4 x 2 = 8 pools. At a pool size of 20, PgBouncer could open up to 160 connections to PostgreSQL.

max_db_connections and max_user_connections are hard caps that span across all PgBouncer pools for a given database or user, respectively. They act as safety valves to prevent pool arithmetic from exceeding PostgreSQL limits. These default to 0 (no limit) but can be set in some scenarios for safety.

All the above are PgBouncer settings. The key setting on the Postgres side is max_connections. The total server connections must stay below this number. We should always keep a few available direct connections reserved for admin tasks and other emergency scenarios. We NEVER want PgBouncer to use all of the connections!

All of this can be summarized in a nice formula:

In Postgres, we can explicitly set superuser_reserved_connections, which is handy for ensuring some connections are reserved for the superuser.

Tuning examples

Thinking through some practical scenarios makes this easier to reason about.

Small server

First, let's think through having a PlanetScale PS-80 (1 vCPU, 8GB RAM per node), a single multi-tenant database, and 3 distinct Postgres users we use for clients connecting through PgBouncer: one for the app servers (app), one for an analytics service (analytics), and one for a data exporter (export).

We want to keep direct Postgres connections low, so we set the Postgres max_connections=50.

Though it's a small database, we sometimes have 100s of app servers making simultaneous connections during peak load. We set the PgBouncer max_client_conn=500.

The majority of these connections come from a single Postgres user + database pair (the app-server user connecting to the main logical database). Because of this, we set default_pool_size=30 but then also set max_user_connections=30 and max_db_connections=40. This prevents connections from the app user from utilizing all of the backend connections, ensuring some are always available for the other two. This also means PgBouncer can never hold more than 40 connections to Postgres in total, ensuring 10 are always available for other services or administrative tasks.

Large server

Now for the same scenario, but with much higher traffic, requiring an M-2650 (32 vCPU, 256GB RAM per node). We'll again have the same 3 distinct Postgres users.

Just because we now have 32x the CPU power, we don't want to increase direct Postgres connections by 32x. It's still wise to keep this on the lower side, so we will settle in at a max of max_connections=500.

We now sometimes have 1000s of app servers making simultaneous connections during peak load. We set the PgBouncer max_client_conn=10000.

Because of this, we set default_pool_size=200 but then also set max_user_connections=200 and max_db_connections=450 for similar reasons as the previous example. No one user can use more than 200 connections.

This also means PgBouncer can never hold more than 450 connections to Postgres, ensuring 50 remain available for other purposes, or if we add services requiring features of direct connections like session variables.

Single-tenant configuration

Though single-tenant architectures are generally discouraged, some organizations prefer this or have inherited such a structure. In this case, we'll assume there is a unique logical database co-located on the same Postgres server for every customer.

Say in this case we have a PlanetScale M-1280 (16 vCPUs, 128GB RAM per node), 200 distinct logical databases (for 200 tenants) and a unique Postgres role for each, for the sake of isolating permissions. There is a 1:1 mapping between each logical database and the Postgres user querying it.

This is a much different connection pattern than the previous example. We have 200 roles connecting to 200 logical databases all on the same host, and want to ensure we can scale to thousands of combined connections without hitting limits.

We'll center this around max_connections=400.

If any one tenant peaks at 20 connections, then we'll set PgBouncer's max_client_conn=5000 (includes a bit of buffer).

Recall that default_pool_size controls connections per (user, database) pool. Since each of the 200 users connects to exactly one database, there are 200 active pools. Even a modest default_pool_size results in a large number of server connections: for example, a default_pool_size of 10 would yield a theoretical max of 200 × 10 = 2,000 server connections, far exceeding max_connections=400.

We'll set default_pool_size=2 (at most 2 PgBouncer <-> Postgres connections per pool). Since we have a clean user-to-logical-database mapping, we also set max_db_connections=2 and max_user_connections=2 to enforce this per-pool cap. The maximum total PgBouncer server connections is 200 × 2 = 400, matching max_connections=400.

A single tenant can have 10s or even 100s of connections to PgBouncer, but all these will get multiplexed through at most 2 direct Postgres connections.

App-side PgBouncers

In some deployments, it also makes sense to layer PgBouncer. You can run one PgBouncer on the app or client side to funnel many worker or process connections into a smaller egress set, then run another PgBouncer near Postgres as the final funnel into a tightly controlled number of direct database connections.

This is especially useful when you need connection pooling both close to compute and close to the database.

Multiple PgBouncers

In large-scale deployments, setting up multiple PgBouncers is useful for traffic isolation. When your web app, background workers, and other consumers all share one pool, a spike from one class of traffic can saturate the PgBouncer and delay everything else.

Giving each major consumer its own PgBouncer creates independent funnels with their own limits, pool sizing, and failure domains. That makes it easier to protect latency-sensitive app traffic from bursty worker traffic and tune each workload separately.

For an additional layer of protection, Database Traffic Control™ lets you enforce resource budgets on query traffic by pattern, application name, Postgres user, or custom tags — without needing separate infrastructure. The two approaches complement each other well: PgBouncer manages connections, Traffic Control manages resource consumption.

The key concepts

PgBouncer solves a fundamental architectural constraint in PostgreSQL: the process-per-connection model that makes every connection expensive. When working with PgBouncer, there are a few fundamental things to keep in mind:

Transaction pooling is the mode that matters. Every transaction, be it a single query or many, gets a dedicated connection from PgBouncer <-> Postgres while executing. After this, the connection can be re-used for another transaction, maybe on the same client, and maybe for another.
Use PgBouncer as much as possible. If you absolutely need features that are incompatible with transaction pooling, like LISTEN, session-level SET/RESET, or SQL PREPARE/DEALLOCATE, use a direct connection. In all other cases, the small latency penalty of PgBouncer is well worth the scalability and connection safety.
The key configs to pay attention to are: max_connections (Postgres), plus max_client_conn, default_pool_size, max_db_connections, and max_user_connections (PgBouncer).
Ensure things are configured to allow for direct connections, even when all PgBouncer connections are in use.

Behind the Scenes: How Database Traffic Control Works

Meg528 — Wed, 01 Apr 2026 19:13:49 +0000

By Patrick Reynolds

In March, we released Database Traffic Control™, a feature for mitigating and preventing database overload due to unexpectedly expensive SQL queries. For an overview, read the blog post introducing the feature, and to get started using it, read the reference documentation. This post is a deep dive into how the feature works.

Background

If you already know how Postgres and Postgres extensions work internally, you can skip this section.

A single Postgres server is made up of many running processes. Each client connection to Postgres gets its own dedicated worker process, and all SQL queries from that client connection run, one at a time, in that worker process. When a client sends a SQL query, the worker process parses it, plans it, executes it, and sends any results back to the client. Planning is a key step, in which Postgres takes a parsed query and turns it into a step-by-step execution plan that specifies the indexes to use, the order to load rows from multiple tables, and the operators that will be used to filter, aggregate, and join those rows. Most queries can be run using several different plans, so it's the planner's job to estimate the cost of the possible plans and pick the cheapest one.

Every part of how Postgres handles queries can be modified by extensions. Extensions can add new functions, new data types, new storage systems, and new authentication methods, among other things. (They can also add new failure modes, but that's a topic for another day.) Extensions can also passively observe and report on traffic, like PlanetScale's own pginsights extension that powers Query Insights.

Much of what Postgres extensions can do, they do using hooks. A hook is a function that runs before, after, or instead of existing Postgres functionality. Want to observe or replace the planner? There's a hook for that. Want to examine queries as they execute? There are three hooks for that. As of this writing, there are 55 hooks available to anyone writing Postgres extensions.

PlanetScale's pginsights extension installs hooks for the ExecutorRun and ProcessUtility functions, among others, to run timers and measure resource consumption while SQL statements execute. Since each hook wraps the original Postgres functionality, that means pginsights sees each query just before it executes and again just after it completes. Any time that has elapsed and any resources the worker process has consumed are directly attributable to that query. The extension does some aggregation, sends aggregate data periodically to a data pipeline, and returns control to Postgres to accept the next query.

Insights, hooks, and blocking queries

When we first started planning for Traffic Control, we knew we would use a Postgres extension with a hook on ExecutorRun to decide whether or not each statement would be allowed to run. Initially, we wrote a new extension for this. We soon realized that there are two ways to choose which queries to block: based on static analysis of the individual query, or based on cumulative measurements of resource usage over time. We split the extension along those lines. Blocking based on static analysis got merged into the project that became pg_strict. Blocking based on cumulative resource usage became Traffic Control.

It turns out Traffic Control needed the same hook points and much of the same information that pginsights already had. So rather than duplicate all that code and impose the extra runtime overhead of another extension, we taught pginsights how to block queries.

If there are any Traffic Control rules configured, then at the beginning of each query execution, the extension does four things:

It identifies all of the rules that match the tags and other metadata of the query. Each rule identifies a budget; multiple rules can map to the same budget.
It checks to see if any of the applicable budgets has reached its concurrency limit.
It checks if the query's estimated cost is higher than any applicable budget's per-query limit.
It checks to see if every applicable budget has enough available capacity for the query to begin execution. In the documentation, these parameters are described as the burst limit and the server share. As we'll see below, those parameters combine over time to describe the behavior of a leaky-bucket rate limiter.

If any budget fails any of these checks, then the query is warned or blocked, based on how the budget is configured.

Blocking a query just before it begins execution means the server spends no resources on the query, beyond the cost of the planner and the decision to block it. That's an improvement over schedulers like Linux cgroups, which let every task begin and simply starve them of resources if higher priority tasks exist in the system. It's also an improvement over the Postgres statement_timeout setting, which allows any overly expensive query to consume resources until it times out. Traffic Control blocks expensive, low priority queries before they begin.

Cost prediction

I glossed over something important in the last section: cost. The concurrency check is easy, because it just counts worker processes already assigned to the queries associated with a Traffic Control budget. But the other two checks — per-query cost and cumulative cost — require us to know what resources the query will consume before it even begins execution. How do we do that? We trust, but also don't trust, the planner.

A SQL query planner takes a parsed SQL statement and selects what it hopes is the most efficient series of steps to execute that query. To evaluate all the possible plans, the planner has to estimate the cost of each one. When you run EXPLAIN on a SQL statement, Postgres's planner shows the cost of each step in the chosen plan, as well as the overall total cost. The cost is measured in dimensionless units and is based on configurable weights assigned to each step the plan will take. There are a lot of variables that go into the plan cost, most of which you can ignore for the purposes of understanding Traffic Control. Just remember these two things: plan costs are roughly linear (a plan with double the cost should take something like double the time and resources to execute), and the relationship between plan costs and real-world resources is heavily dependent on what query you're running, what server you run it on, and what else is happening on that server at the moment.

Traffic Control compensates for those dependencies. We assume that there is an unknown constant k that we can multiply the plan cost by, to get the actual wall-clock time it will take to execute that query. But that constant is different for each query pattern and for each host. The constant may also change over time as the workload mix on the server changes and as tables grow and change. So it's not exactly a constant!

Traffic Control implements a hash table on each host, mapping query patterns to two averages: CPU time and planner cost estimates. Both are exponential moving averages, heavily weighting recent queries. Every time a query completes, we update both of those averages. The magical not-quite-constant k is the ratio of the two.

Each time a query comes in, Traffic Control multiplies the planner's estimated cost by k to guess how much CPU and/or wall-clock time the query will take. Based on that estimate, Traffic Control decides if the query can be allowed to begin. If it does, then at the end of query execution, Traffic Control updates the two averages for that query pattern so the k value will be more recent and more precise for the next query that arrives.

Leaky buckets

Two of the checks that Traffic Control performs for each query are easy: if the query's estimated cost is too high, block it. If too many queries in the same budget are already running, block it. But the final check — is there enough capacity in the budget to proceed — is harder. It's important, though! Many executions of a moderately expensive query can be even more damaging than a single very expensive query, and managing a budget over time is the best way to block queries that are only expensive in aggregate. Traffic Control considers the cumulative cost of queries in each configured budget.

Each budget is modeled as a reverse leaky bucket. Here's how that works. Each query that executes accumulates debt in the bucket. Any query that would cause the bucket to overflow with debt is blocked. Debt drains out over time, until the bucket is empty. The bucket has two important parameters: its size and its drain rate. The size dictates the burst limit, or what total resources queries under a given budget can use in a short amount of time. The drain rate dictates the server share, or what fraction of overall resources queries under a given budget can use in the long term.

Traditionally, leaky buckets work the other way: they start out full, they fill (but never overflow) with credits at a configured rate, traffic consumes credits, and if a bucket is ever empty, traffic gets blocked. We inverted the model for a simple reason: an empty bucket doesn't need to be stored. Over time, we may need to store many buckets for changing rules and changing query metadata. We can drop buckets with a zero debt level, meaning that we only need to store recently active buckets, instead of every possible bucket. We store as many buckets as will fit in a configurable amount of shared memory, and we evict them implicitly when their debt falls to zero.

There is no periodic task that drains debt from all buckets. Instead, each bucket is updated only when read. There is also no periodic task to evict buckets with a debt level of zero. Instead, adding a new bucket to the table evicts any that have already emptied, or whichever bucket is expected to become empty soonest.

Rule sets

One important goal for Traffic Control is that it can efficiently decide when not to block a query. After all, Traffic Control has to make that decision before each query is even allowed to begin execution. So the budget here is measured in microseconds. But we also want developers and database administrators to be able to configure as many rules as it takes to manage traffic to their application. So it's crucial that we can evaluate many rules quickly. Enter rule sets: a data structure that allows evaluating n rules in O(1) time.

Each rule has the form <key, value>, and it matches any query that has that same value for that same key. It's complicated a bit by the fact that value can be an IP address with a CIDR mask.

A rule set maps each <key, value> pair to a rule. Now, when a query comes in with metadata like username=postgres, app=commerce, controller=api, the rule set can quickly identify the rule for each of those pairs. Hence, for this query, there are just three lookups in the rule set, regardless of how many rules are configured.

Note that a rule set only identifies rules to consider. Each rule's budget is only checked if all its conditions match the query. A rule set is all about checking as few rules as possible. So, the sequence is: the rule set identifies a list of rules, that list is narrowed down to just the rules that actually match, and then the budgets for all the matching rules get checked to see if the query can proceed.

There are three exceptions to the O(1) target for identifying rules:

Rules for the remote_address key check for a match for each mask length. So if you have rules for ten different mask lengths, the rule set has to do as many as ten lookups to find the rule with the longest matching prefix.
Any conjunction rule — that is, a rule with multiple <key, value> pairs ANDed together — may be identified as a candidate for queries that match any one of the <key, value> pairs in the rule. So if you have conjunction rules with overlapping <key, value> pairs, the rule set may identify several or all of them as candidates for each query.
It is possible to add multiple rules for the exact same <key, value> pair. If you do that, any query with that exact <key, value> pair will get checked against all of those rules.

Applying new rules

Traffic Control is meant to be used both proactively and during incident response. For incident response, it's important that rules take effect quickly. And they do! Rules created or modified in the UI generally take effect at all database replicas in just 1-2 seconds. How?

Rules and budgets are stored as objects in the PlanetScale app. Any change to Traffic Control rules made in the UI or the API gets stored as rows in the planetscale database. Then it's serialized as JSON in the traffic_control.rules and traffic_control.budgets parameters for Postgres. Some Postgres parameters require restarting the server, but those two don't. So they cut the line and get sent immediately to postgresql.conf files on each database replica. Postgres reads the new config, and each worker process parses it into a rule set as soon as it completes whatever query it's executing. The rule set is in place before the next query begins.

One big advantage of using Postgres configuration files, rather than sending configuration over SQL connections, is robustness on a busy server. You may want new Traffic Control rules most urgently when Postgres is using 100% of its available CPU, 100% of its worker processes, or both. Changing config files is possible even when opening a new SQL connection and issuing statements wouldn't be.

Wrap up

Traffic Control uses the hooks and the performance measurements that Query Insights already implemented, then bolts on a system for sorting query traffic into budgets and warning or blocking queries that exceed those budgets. Each query can be warned or blocked if it's individually too expensive, if too many other queries are already running under the same budget, or if recent and concurrent queries under the same budget have consumed too many resources in the aggregate. Traffic Control implements a dynamic model per query pattern that leverages the existing Postgres planner to estimate the real-world cost of a query before it begins to execute. Leaky buckets impose limits on both traffic bursts and the long-term average fraction of server resources assigned to any individual budget.

Taken as a whole, these elements implement Traffic Control, which gives developers and database administrators powerful new tools to identify, prioritize, and limit SQL traffic.

PlanetScale integrates with Arctype's MySQL GUI

etnoctua — Fri, 19 Nov 2021 15:47:58 +0000

Check out our exclusive integration with Arctype, our favorite database GUI for developers.

Arctype features a fast, modern interface with keyboard shortcuts, a command bar to quickly find objects, and the best support for JSON in a SQL client. It’s also free to use. You can already connect to the main branch of your PlanetScale database in Arctype, but today it’s possible to connect to development branches, switch between them, and even create deploy requests. The power of PlanetScale is experienced within a beautiful GUI!

For a full walk through, check out the demo video.

Prisma Data Platform's PlanetScale Integration

etnoctua — Thu, 18 Nov 2021 15:30:09 +0000

As developers, we often want to build faster, but that comes with tradeoffs that we have to deal with in the long run. At PlanetScale, we want to empower developers to be able to build without having to worry about issues of database scalability as their application grows. Similarly, Prisma wants to empower developers to efficiently work with data while making fewer errors.

PlanetScale and Prisma have partnered up to allow developers to create PlanetScale databases in the new Prisma Data Platform. You can have a starter database schema and a live PlanetScale database ready to accept thousands of new database connections with a few clicks.

The Prisma Data Platform provides you with application templates with Prisma data schemas, so you don't even have to think about a data model to get started. Once set up, you can deploy to Vercel immediately or use the Prisma Data Explorer and Query Builder to explore your PlanetScale database.

Try out creating your first PlanetScale database with one of the application templates and experience the power of Prisma with PlanetScale for yourself.

Zero downtime data migrations with PlanetScale Data Imports

etnoctua — Wed, 17 Nov 2021 17:15:25 +0000

PlanetScale now supports zero downtime data migrations from your existing MySQL database with our new Data Imports tool. We’re leveraging the power of Vitess to let you try us out as a replica and then easily switch over your database to PlanetScale completely. No dumping your data, no restoring from backup. Just give us a connection and let us handle the rest.

When you connect your existing MySQL database to PlanetScale, you instantly get to experience the power of PlanetScale’s platform, which includes:

Secure Passwords that scale to thousands of simultaneous connections.
Query Statistics start finding & optimizing slow queries.
Web Console to query your database directly from the PlanetScale web UI.

The tool will help you along the way to make your data import successful and alert you on any incompatibility issues we may encounter.

Try importing your existing MySQL Database on PlanetScale and let us know how we can make this better. We’d love to hear from you on any ideas you have to improve onboarding.

PlanetScale is now GA

etnoctua — Tue, 16 Nov 2021 14:07:48 +0000

It has been an incredible six months since we released our product in beta. From the outset we focused on creating a platform that would delight developers, built on top of the only open source database proven at hyperscale, Vitess.

We believe databases should be powerful, easy to use, and have impeccable DX. This is why we chose to build our serverless platform so that developers can be productive without ever having to worry about scale.

With PlanetScale you can:

Instantly create dev and staging environments with database branching
Deploy features fast and safer with non-blocking schema changes
Scale out with built-in horizontal sharding
Handle traffic with unlimited connections and connection pooling
Analyze slow queries using our Query Stats tool

Our database platform is now GA and ready for your production workloads.

Get started with PlanetScale for free and check out the new features that will change how you work with databases. Also, we're happy to answer any questions you might have and if check out this post if you're interested in how we used PlanetScale to build PlanetScale.

Building a Next.js app with Netlify, NextAuth.js, Prisma, and a PlanetScale serverless database

etnoctua — Mon, 15 Nov 2021 15:40:01 +0000

Git-centric workflows have fundamentally changed how we build for the web. As developers, we want isolated environments to develop and test our applications. As Netlify’s CEO Matt Biilmann mentioned in his recent Jamstack Conf keynote, our back-end data layer is still a single branch. It’s often talking to a long-running server or a production environment.

It doesn’t have to be this way. PlanetScale supports database schema branching, deploy requests, and non-blocking schema changes. Your staging environment’s database can be isolated from the production environment’s database.

We are excited to announce a new Next.js starter app that can be deployed to Netlify with the “Deploy to Netlify” button, uses NextAuth.js for built-in authentication, and Prisma to interact with your PlanetScale database.

The starter app is located today on the Netlify Jamstack Templates page. Want to get started right away? Click the button below!

Note: After you deploy to Netlify, you will need to follow the instructions in the documentation to get your PlanetScale database up and running.

Since this is just the first version of this starter app, we would love to hear your feedback!

The database for developers is here

etnoctua — Tue, 18 May 2021 17:18:51 +0000

Our team has worked at companies like YouTube, Amazon, Facebook, DigitalOcean, and GitHub, and we've always had to solve the same problem — database scaling.

The story is the same each time... The day 1 stack is chosen to optimize for developer velocity to get that MVP out the door. After some success, years 3 or 4 are spent paying down immense technical debt — mostly due to that early database choice.

Now with PlanetScale, there is a better way. Built on the Vitess engine that powers companies like YouTube, GitHub, and Square, our new database platform is fully integrated with the developer workflow and is set up to take you from idea to IPO.

Try out our free Developer plan today and check out the new functionality that will change how you work with your database, like live database branching and non blocking schema changes.