QueryGuard: What a Postgres Proxy Has to Learn

I wanted to stop one tenant on a shared Postgres from slowing down everyone else. I thought the hard part would be the scheduling: budgets, queues, and deciding who goes next. I was wrong. The scheduler is a small part of the code. The hard part was everything a proxy has to understand before it is allowed to touch a statement at all. It has to know what Postgres means by "the same query", "the same transaction" and "cancel this". It even has to know where a string of SQL ends.

The problem is easy to state. On a shared Postgres, one tenant's expensive query slows down everyone else. Examples are a full scan of a ten million row table, a cross join, or a migration that holds a lock. The database does not know that the query belongs to "the noisy customer". It just runs it. Connection poolers limit how many connections you get. They do not look at what the connections are doing.

QueryGuard is my attempt to build that missing piece. It is a proxy written in Go that speaks the Postgres wire protocol. Your app connects to QueryGuard instead of Postgres. For each statement, QueryGuard decides whether it may run, when it runs, and at what price.

Here is the whole idea in 15 seconds:

Does it work?#

The test I care about most has a rogue (badly behaved) tenant. Three innocent tenants do indexed lookups on six connections. At the same time, the rogue runs full reads of a ten million row table on six more connections, for 15 seconds. Through QueryGuard, the rogue has a budget of 50,000 cost units a second, plus 200,000 of burst. Each of its full reads costs about 169,000.

I ran it three times on each of PostgreSQL 16, 17 and 18. These are the ranges over all nine runs:

When clients connect straight to Postgres, the rogue runs 23 to 45 full reads, and everyone else's slowest queries get six to ten times slower. Through QueryGuard, the rogue runs 5 to 8, and the innocent p99 stays between 1.1 and 3.5 ms. That is about what its budget pays for: 200,000 units of burst plus 15 seconds at 50,000 a second buys five and a half reads at their planned price. Chapter 8 has the full tables, and the four runs that missed my own target by under a millisecond.

These are laptop numbers, so treat them as a rough guide. It took eight milestones to get to them. Each milestone was less about Go and more about some part of Postgres. Before this project, I had only used those parts from the outside.

Seven things a proxy has to learn#

1. Speak Postgres without changing a byte (M1): pass TLS, logins and cancel requests through without touching them.
2. Know where Postgres is in the conversation (M2): read every statement, and block the dangerous ones without breaking the rest of the session.
3. Ask the planner before a statement runs (M3): use Postgres's own planner to put a price on a statement.
4. Share the server fairly (M4): give every tenant a budget, a fair turn and time limits.
5. Assume the estimate is wrong (M5): compare what the planner predicted with what really happened.
6. One statement is not the whole server (M6): watch the server, change the limits as its load changes, and share the limits across proxies.
7. If you can't see it, you can't run it (M7): numbers on every statement, anomalies, and switches to use during an incident.
8. Prove it, then ship it (M8): real load, an audit of the whole code, the full benchmark, and v1.0.

Each chapter starts with the thing in that milestone that surprised me. It ends with the lesson I learned. A status box shows what is actually proven: built, tested against real Postgres, measured under load, or run in production. Nothing has run in production yet, and the boxes say so. The longer protocol details are folded into detail blocks. You can skip them, and the story still makes sense. Every figure is animated. You can pause it or step through it. From chapter 6 on, you can also control it yourself.

One idea connects all eight chapters. The proxy works only as long as it copies Postgres's own behaviour, instead of inventing a model of its own.

1. Speak Postgres without changing a byte#

The first milestone, M1, was a transparent proxy. It blocked nothing and changed nothing. I expected simple plumbing work. Most of it was simple, thanks to one good decision in the protocol's design. The surprise was the cancel request, because it does not arrive inside an existing session.

Every message is framed the same way#

A Postgres connection is a TCP stream of messages. After the first message, every message in both directions has the same shape. First comes one byte that says what kind of message it is. Then come four bytes of length, and then the body. The length counts its own four bytes and the body, but not the type byte.

That shape is the most useful fact in the whole protocol. A proxy can always find the end of a message without understanding it. So it can pass along messages it has never seen before. QueryGuard only decodes the messages it needs to act on. It streams everything else through.

Passing authentication through#

QueryGuard does not check passwords. It forwards the startup message to Postgres. Then it passes the authentication messages back and forth until Postgres says AuthenticationOk (or sends an error). Postgres stays the one that decides who may log in. The proxy never sees or stores a password.

At the end of login, Postgres sends a few ParameterStatus messages. These carry the server version, client encoding, time zone and so on. Next it sends a BackendKeyData message, and finally ReadyForQuery. From then on, the session is open.

Cancel requests need keys of their own#

When you press Ctrl-C in psql, it does not send anything on the connection that is busy running your query. Instead, it opens a new connection and sends a CancelRequest. The CancelRequest carries two numbers that the client got at login in BackendKeyData: the backend's process id and a secret key. Postgres finds the backend with that pid and checks the secret. Then it interrupts the query, which fails with SQLSTATE 57014.

A proxy cannot just pass the real key through. First, the cancel arrives on a new connection with no session attached. So QueryGuard needs a way to find which upstream backend the cancel is for. Second, if clients held the real keys, anyone who could reach Postgres directly could cancel queries without going through the proxy. So QueryGuard replaces BackendKeyData with a key of its own. It keeps a map from its key to the real one. A cancel with an unknown key is ignored.

Clients that vanish mid-query#

If a client dies while its query runs, Postgres does not notice by default. It keeps running the query until it next tries to send something. For a long aggregate, that might be minutes later. Postgres 14 added client_connection_check_interval. While a query runs, the backend checks from time to time whether the socket is still alive. If it is not, the backend stops the query. QueryGuard sends client_connection_check_interval=2000 (two seconds) in every startup message, unless the client already set it. QueryGuard closes its upstream connection as soon as the client goes away. Because of this, Postgres sees the closed socket and stops the orphaned query.

That handles clients that close their socket.

2. Know where Postgres is in the conversation#

M1 only copied bytes. In M2, QueryGuard has to block statements. To do that, it has to know where statements are and what they say. It also has to know which answers belong to which statement. That meant learning the two query protocols properly. The surprise came at the end. The proxy's parser and the server's parser are the same code, but they can still read the same bytes differently.

Simple Query and the extended protocol#

The simple query protocol uses one message: Query. It carries SQL text with the values written into it. Postgres answers with a row description, the rows, a CommandComplete per statement, and finally ReadyForQuery. A single Query message may contain several statements separated by semicolons.

The extended protocol splits the same work into steps:

• Parse sends SQL with placeholders like $1 and creates a prepared statement, named or unnamed.
• Bind gives the values and creates a portal (a statement ready to run).
• Describe asks what the result will look like.
• Execute runs the portal.
• Sync ends the batch.

Drivers like pgx, psycopg and JDBC use it for every query with parameters. The reason is that values travel separately from the SQL, so they cannot be confused with it.

Three rules of the extended protocol shaped everything that followed:

1. Nothing is finished until Sync. Everything between two Syncs runs in one implicit transaction, unless the client opened an explicit one. If the third statement of a batch fails, the first two are rolled back.
2. After an error, Postgres skips to Sync. It ignores every message until the next Sync. It answers that Sync with ReadyForQuery, and only then does it listen again.
3. Postgres may hold its answers. It buffers output. It sends the output at Sync, when the client sends Flush, or when the buffer fills. Suppose a client sends Parse and then waits for ParseComplete without sending a Flush or Sync. That client will wait forever.

Pipelining just means sending many of these messages without waiting for answers. It is fast. It also means that at any moment, several messages can be in flight.

ReadyForQuery tells you where you are#

Every ReadyForQuery carries one status byte: I for idle, T for inside a transaction block, E for inside a failed transaction. That byte turned out to be the most important input to M2. The reason is that the safe way to reject a statement depends on it.

Matching every answer to its message#

To act on answers, the proxy has to know which message each answer belongs to. QueryGuard keeps a pending queue of every message sent to Postgres, oldest first. Each answer type finishes exactly one kind of message:

• ParseComplete finishes a Parse.
• BindComplete finishes a Bind.
• CommandComplete (or an empty result) finishes an Execute.
• ReadyForQuery finishes a Sync or a Query.

Notices, notifications and ParameterStatus can arrive at any time. They do not belong to any message.

The tricky part is rule 2 above. Suppose an error answers a message in the middle of a batch. Postgres will then skip the rest of the batch, so the proxy has to drop those entries from its queue too. If it does not, every later answer is matched to the wrong message. The session state then slowly turns into nonsense. At first, one of my tests did not catch this. So I wrote a test that sends messages while Postgres is skipping. It checks that the proxy forgets those messages as well.

Parsing SQL with Postgres's own parser#

To decide whether a statement is allowed, QueryGuard has to understand it. Writing my own SQL parser was never an option. pg_query is the actual Postgres parser. It is taken from the Postgres source code and packaged as a library. It returns the same parse tree that Postgres itself builds. The Go binding uses cgo and currently ships the Postgres 17 grammar.

pg_query also gives two things that turned out to be essential:

• A fingerprint: a short hash of the statement's shape that ignores constants. select * from orders where id = 1 and ... where id = 2 have the same fingerprint. Logs use it to group statements. M3 uses it as a cache key.
• Normalized text: the statement with every constant replaced by $1, $2 and so on. QueryGuard logs that instead of the real SQL, so customer data never ends up in log files.

QueryGuard visits every node of the tree, not just the top statement. So a DELETE still counts when it is inside a CTE, under EXPLAIN ANALYZE, or inside a PREPARE.

Four rules#

M2 shipped four rules. Each rule runs in enforce mode (block) or warn mode (log what would have been blocked). You can also put a whole tenant in warn mode. That is how you start using new rules safely.

• deny_ddl blocks schema changes. I did not invent my own list of "DDL". QueryGuard uses Postgres's own list: the statements that log_statement = 'ddl' logs. This list comes from GetCommandLogLevel in Postgres's utility.c. A test compares that list with every statement type the parser knows. So a parser upgrade cannot silently change what counts as DDL. SELECT INTO (which creates a table) and DO blocks (which can run anything) are blocked as well.
• require_where blocks UPDATE and DELETE without a WHERE clause. It also blocks TRUNCATE. If you really mean every row, write WHERE true to say so on purpose.
• index_concurrently blocks CREATE INDEX, DROP INDEX and REINDEX without CONCURRENTLY. The reason is that the plain versions block writes to the table for as long as they run. CREATE INDEX ON ONLY is allowed. It is the first step of indexing a partitioned table without blocking anything, and CONCURRENTLY is not even possible there.
• schema_allowlist blocks statements that name any schema outside a list. pg_catalog, information_schema and the session's own temporary schema are always allowed, because every driver reads the catalogs.

The schema rule taught me how many ways there are to change search_path. This setting is the list of schemas that Postgres searches for names without a schema prefix (unqualified names). QueryGuard has to see every one of these ways.

Rejecting a statement without breaking anything#

Blocking a statement sounds simple: send an error instead of forwarding the statement. The problem is everything else that is happening at the same time. There are two paths.

Suppose the session is idle (I), nothing is in flight, and no extended batch is open. Then the proxy can answer by itself, with an ErrorResponse followed by ReadyForQuery. Postgres never sees the statement. The client cannot tell the difference from a real error.

In every other case, it gets tricky. Suppose the blocked statement is the third one in a pipeline. The first two may already have run inside the batch's implicit transaction. If the proxy just makes up an error, Postgres commits them at Sync. A failed batch should not do that. Inside an explicit transaction, there is another problem. The next statement should fail with 25P02 ("current transaction is aborted"), and COMMIT should turn into a rollback. The only way to get exactly Postgres's behaviour is to make Postgres raise the error itself.

So QueryGuard sends a DO block in place of the statement. This is a tiny anonymous PL/pgSQL block. It does nothing except RAISE EXCEPTION with the rejection's error code, message and hint. Postgres then fails the transaction, the batch or the pipeline, exactly as it would for any other error. The DO block contains only fixed text and the statement's hex fingerprint. It never contains anything from the client's SQL, so there is nothing to inject. After a rejected Parse, the proxy also drops the client's next messages until Sync. It does this because Postgres would have skipped them.

When the parser and Postgres read different things#

While reviewing M2, I found the most interesting kind of bug in the project. The parser in the proxy and the parser in the server are the same code. But they are not given the same settings.

Two session settings change how Postgres turns bytes into tokens:

• standard_conforming_strings. When it is on (the default since Postgres 9.1), a backslash inside a normal '...' string is just a backslash. When a session turns it off, a backslash becomes an escape character, as in old Postgres. Then a string in the same text can end at a different place.
• client_encoding. Postgres converts the client's bytes to the server's encoding before parsing. Some client encodings, such as SJIS, BIG5 and GBK, have two byte characters. The second byte of such a character can be an ordinary ASCII byte, like a backslash or a letter. If you read the same bytes as UTF-8, they split differently.

pg_query always reads text with the default settings. So if either setting is changed, the proxy and the server can disagree about where a string ends. Then they also disagree about how many statements there are, and what they do.

The fix is not to teach the proxy's parser every setting. The fix is to know when the proxy cannot be sure. Postgres reports both settings with ParameterStatus at login. It reports them again whenever they change, just before the next ReadyForQuery. QueryGuard tracks the settings from those messages. A statement is treated as unchecked in two cases:

• It has a backslash in a plain string, and standard_conforming_strings is not known to be on.
• It has non-ASCII bytes, and client_encoding is not known to be UTF8 (or SQL_ASCII).

"Not known" also includes the moment when an earlier statement is still in flight. That earlier statement might be the one that changes the setting.

Unchecked statements also include SQL that the parser cannot read, for example syntax newer than Postgres 17. Statements over 16 MB are unchecked too. By default, all of them are rejected. You can set "unchecked": "allow" to let them run with a log line. But then anyone can get around any rule by writing something the parser does not understand. Failing closed (rejecting when unsure) is the only honest default.

Connection caps, counted after login#

M2 also added connection caps: a total cap, a cap per role, and overrides per tenant. My first version counted a session as soon as the startup message arrived. But the startup message is just the client saying who it claims to be. Anyone could open connections as alice and leave them waiting at the password prompt. That would use up all of alice's slots without knowing her password.

Postgres checks its own connection limits after authentication, so QueryGuard does the same. The session is counted when Postgres sends AuthenticationOk. A login over the cap gets AuthenticationOk followed by FATAL 53300. That is exactly the order Postgres uses.

Guard rails, not a security boundary#

I want to be clear about this. These rules catch mistakes: a forgotten WHERE, an index build that locks a busy table, or a query against the wrong schema. They are not a security boundary. A function, a view or a trigger can run SQL that the proxy never sees, and an allowed statement can call it. Database privileges are still the boundary. The README says so too.

3. Ask the planner before a statement runs#

Rules look at what a statement says. M3 is about what it will cost. The planner already knows the cost, so QueryGuard asks the planner. The surprise was where to ask. It has to be on the client's own connection, in the middle of whatever the client is doing. If you ask anywhere else, the answer is about a different statement.

What a plan is#

EXPLAIN shows the plan Postgres would use for a statement, without running it. A plan is a tree of nodes. Scans are at the leaves (Seq Scan, Index Scan, Bitmap Heap Scan and so on). Joins, sorts and aggregates sit above them.

Each node has an estimated cost, in the planner's own units. One unit is roughly the cost of reading one page from disk in order (sequentially). Settings like random_page_cost and cpu_tuple_cost set the price of everything else compared to that unit. Costs are not milliseconds. You also cannot compare them across servers with different settings. But on one server, they rank statements well.

QueryGuard runs EXPLAIN (FORMAT JSON, VERBOSE). JSON is easy to read safely. VERBOSE adds the schema of every table, so public.orders and archive.orders are not confused. From the plan, QueryGuard keeps two things:

• the total cost of the root node
• the set of tables read in full by a Seq Scan (a parallel scan is also a Seq Scan node, under a Gather)

Two cost rules#

M3 added two rules to the same policy file. They have the same warn and enforce modes:

{
  "rules": [
    { "check": "max_cost", "cost": 100000 },
    { "check": "max_scan_rows", "rows": 1000000 }
  ]
}

max_cost blocks a statement whose planned total cost is over the limit.

max_scan_rows is about table size. The reason it exists says a lot about Postgres. A Seq Scan is not a bad plan in itself. For a small table, or for a query that reads most of a table anyway, reading the table from start to end is the fastest way.

The real problem is a full read of a big table, hidden behind a query that looks cheap. An example is a WHERE on a column with no index. So the rule never judges by the node type alone. It adds up the row counts of all the tables the plan reads in full, and compares the total with the limit.

Both rules reject with 54000. The error detail gives only numbers: the planned cost and the limit, or the rows read and the limit. Table names go to the log, never into the error. The reason is the same reason the DO block never carries client text.

Explaining on the client's own connection#

Where to run the EXPLAIN mattered a lot. A separate connection would be simpler, but it would plan a different query:

• a different role, so different row-level security policies
• a different search_path, so maybe different tables
• no access to the session's temporary tables
• different settings

So QueryGuard runs the EXPLAIN on the client's own connection, as part of the client's session.

For a simple Query, this works in three steps. QueryGuard sends EXPLAIN (FORMAT JSON, VERBOSE) followed by the client's SQL. It waits for the result. Only then does it decide whether to send the real statement. The EXPLAIN's answers are captured: they go to the waiting check, not to the client.

Sometimes Postgres refuses the EXPLAIN, for example because a column does not exist. In that case, the real statement would have failed in exactly the same way. So the client gets that error as the answer to its statement.

Explaining a prepared statement at Bind#

With the extended protocol, Parse carries the SQL but not the values. And the values change the plan. A query for your biggest customer and the same query for a tiny one can need completely different plans. So QueryGuard checks a prepared statement at Bind, when the values arrive. It explains the statement with exactly those values. This is a custom plan, the kind Postgres itself makes for specific values.

Doing this in the middle of a client's batch needs three things:

• a statement and portal that belong to the proxy itself
• a Flush at the end of the EXPLAIN, so Postgres answers now and not at the client's Sync
• holding the client's Bind until the plan is judged

The plan cache#

An EXPLAIN is an extra round trip. This roughly doubles the latency of a fast query. Most applications run the same few hundred statements again and again, with different values. So QueryGuard caches plans. The cache key has four parts:

• the database
• the role (because of row-level security)
• the fingerprint
• whether the plan is generic

The fingerprint ignores constants. So when a statement runs a second time with new values, it is a cache hit. Each plan stays in the cache for one minute. At most 10,000 plans are kept. Failed EXPLAINs are never cached.

The M3 milestone also asked for the numbers to be visible. Every minute, the proxy logs a line with hits, misses, the hit rate, and the average and slowest EXPLAIN time. The EXPLAIN time is the latency that the cost check adds, because a cache hit sends nothing over the network.

4. Share the server fairly#

Rules and cost limits judge one statement at a time. They can't stop a tenant who sends a thousand reasonable statements a second. They also can't stop a tenant who sends ten expensive ones that each stay just under the limit.

M4 looks at the tenants instead: who is asking, how much they have used recently, and how long they may take. I expected this scheduling to be the hard part. Its worst bug was a race between two lines of code that looked like one step.

Who is the tenant?#

The simplest answer is the database role. If every customer logs in with their own role, Postgres has already checked who they are. Then QueryGuard can use the role as the tenant.

Many SaaS apps don't work that way. They log in with one role, say app, for every customer. They keep the customer's id inside the application. For these apps, QueryGuard reads a sqlcommenter tag.

sqlcommenter is a convention from Google. Frameworks add a comment to each statement, such as /*tenant='acme',route='/orders'*/. The keys and values are quoted and URL-encoded. QueryGuard reads the statement's last comment with pg_query's scanner. This is the same tokenizer Postgres uses. So text inside a string literal that only looks like a comment is never taken for a real comment.

There is a problem: the client writes the comment. If any role could name its own tenant, a noisy customer could simply claim to be a quiet one. So a tag only counts if it comes from a role listed in trusted_roles. This is usually the app's service role, which fills in the tag from its own session data. A tag from any other role is ignored, and logged once per session.

Budgets in cost units#

What should a budget count? Statements per second treats select 1 and a full scan of ten million rows the same. Time is only known after the statement has run. That is too late to decide whether it may start. The planner's cost from M3 is known before the statement runs, and on one server it ranks statements well. So budgets are counted in cost units.

Each tenant gets a token bucket. The bucket fills at rate units a second, up to burst. A statement may start while its tenant owes nothing. Then its whole cost is taken from the bucket, even if that leaves the balance below zero. This rule looks strange at first, but both alternatives are worse:

• Asking for the full cost up front would starve any statement bigger than the burst. Such a statement could never start.
• Charging only what is in the bucket would let a tenant run one huge statement after another, and pay only the burst for each one.

Letting tenants owe units fixes both problems. The big statement runs once. Then its tenant waits until the debt is paid back at rate. Over any long period, the tenant gets exactly its rate. A min_charge makes even the cheapest statement cost something. So a tight loop of select 1 still counts.

What happens to a statement whose tenant still owes units depends on the config:

A statement that fails this way gets SQLSTATE 53000, insufficient_resources. This is the code Postgres itself uses when it runs out of something. Some statements can't be planned by EXPLAIN, like BEGIN or a string of several statements. These are charged min_charge.

The gate, and a race the benchmark found#

Every statement now passes through a gate just before it goes to Postgres. The order of the steps matters.

The budget is checked twice. Reserve is a cheap early check. If the tenant already owes units, there is no point paying for an EXPLAIN round trip. Spend is the real decision, made once the cost is known.

My first version split Spend into two separate steps: check the balance, then charge it. The rogue tenant benchmark found the problem right away. Six connections from one tenant sent full scans at the same moment. All six checked a balance that nobody had charged yet. All six were allowed, and the tenant spent six times its budget at once. Now checking and charging happen under one lock. So the second statement sees what the first one spent.

Slots, lanes, and the least served tenant#

Budgets limit how much a tenant does over time. Slots limit how much runs at the same time. max_active sets how many statements may run together in the fast lane. There is also a slow lane with its own limit, which is usually small.

Statements that are over budget with when_over: slow go to the slow lane. So do the plan flips of M5. Expensive work then waits behind other expensive work, not in front of everyone.

When a slot becomes free, it does not go to whoever asked first. A first in, first out queue gives the server to whoever sends the most statements. That is exactly the tenant we are trying to limit.

Instead, every waiting tenant gets a score: its recent use (the cost it was charged), divided by its share. The tenant with the lowest score goes first. Use fades with a 10 second half-life, so a burst from some time ago stops counting. CockroachDB uses the same idea for its admission control.

When should a slot be freed? My first thought was "when the statement finishes". But that is not what the server actually spends. A Postgres session is one backend process, and it runs its statements one after another. A client that pipelines ten statements keeps one backend busy until the last answer. The later statements are already on their way to Postgres, whatever the proxy decides.

So a session takes one slot for as long as Postgres has anything left to answer for it. It frees the slot at the ReadyForQuery that leaves nothing pending. A whole pipeline needs one slot. If a statement finds no slot within the queue timeout, it gets 53000. The cost it was charged is refunded, because it never ran.

Time limits a client can't switch off#

Postgres has its own statement_timeout and idle_in_transaction_session_timeout. They are good settings. But any client can turn them off with SET, and the tenant you most want to limit is often the one that does this. So QueryGuard keeps its own clocks, per tenant.

• Statement timeout. When a statement runs too long, QueryGuard cancels it the same way a client would. It sends a CancelRequest on a new connection, with the server's real cancel key. The proxy has kept this key since M1. Postgres fails the statement with 57014 and the message "canceling statement due to user request". That message would confuse anyone who reads it, because no user asked. So QueryGuard rewrites it to say that it was the statement timeout.

The rogue tenant#

This is the milestone that the benchmark at the top of the post tests. Straight to Postgres, the rogue ran 52 full reads in 15 seconds. It pushed the innocent p99 to 4.18 ms. With a budget per tenant, it ran 6. The innocent p99 stayed at 1.10 ms with a role per tenant, or 1.06 ms with one shared role and trusted tags.

5. Assume the estimate is wrong#

M4 trusts the planner's cost completely. But the cost is only an estimate, and estimates can be wrong. M5 checks them against reality. QueryGuard times every statement it explained, and it learns from the difference. The surprise was that the timing itself can be wrong. Postgres may hold back one statement's answer until the next statement has run.

Why cost units don't map to time evenly#

The planner gives each plan a cost using a few settings:

• reading a page in order costs seq_page_cost (1),
• reading a page out of order costs random_page_cost (4),
• handling a row costs cpu_tuple_cost (0.01),
• and so on.

These numbers are averages for an imagined server. The real time also depends on things the planner can't see:

• whether the pages are already in memory, in Postgres's shared buffers or the operating system's cache,
• how rows are laid out on disk, which decides how random an index scan's reads really are,
• how fast this particular machine is,
• and whether the statement had to wait for a lock held by someone else.

So two plans with the same cost can take very different times. Also, a plan's cost can stay the same while its real time grows. PlanetScale's Traffic Control handles this by learning a multiplier for each query pattern. The multiplier is the ratio of two moving averages: time spent and planner cost. QueryGuard does something similar.

Timing from the outside#

A proxy can't see a backend's CPU time. It can only see wall-clock time: the time from when a statement goes to Postgres until Postgres has answered it. This time includes the network round trip. It also includes the time the client takes to read its rows. That is fine, as long as you know it is there.

There was one trap here. A code review found it, not me. In M2 I wrote that Postgres may hold its answers until Sync. This matters a lot for timing.

Say a pipeline runs two statements before one Sync. Then the first statement's answer reaches the proxy only after the second statement has run too. If I timed the first statement from its own send to its own answer, it would be charged for both. So statements that share a Sync are now not timed at all. DECLARE is not timed either. It opens a cursor and runs nothing until the FETCHes that follow.

Calibrated costs#

For each plan, QueryGuard keeps the average of ln(seconds ÷ cost) over its last 50 or so runs. Because the average uses the logarithm, one run that took a hundred times longer moves the average only a little. It does not take over the whole average. The server keeps the same kind of average over all plans, weighted by cost. So a cheap statement that sat waiting on a lock barely counts.

A plan's factor is its own time per unit divided by the server's time per unit. The charge to the budget is then the planned cost times that factor. A plan that keeps taking longer than its cost suggests is charged more. A plan that runs from memory is charged less.

The first benchmark run with calibration went badly. It taught me something about measuring from outside. The rogue ran 13 full reads instead of 6. The innocent lookups take about 0.3 ms. Nearly all of that time is the network round trip and the fixed work every statement does. Very little of it comes from their plan. There were thousands of these lookups a second, and they set the server's "normal" time per unit. Next to that, the rogue's full reads looked cheap per unit. So they were charged less than their planned cost. Now only runs of 5 ms or more are used to learn costs. Below that, the time says almost nothing about the plan. Also, min_charge already covers the fixed cost of a statement. With that change, the rogue was back to 6.

The factor is kept between 1/100 and 100. The max_cost rule from M3 still judges the planner's own cost, which is the number you see in EXPLAIN.

Plan shapes#

To learn for each plan, QueryGuard needs to tell plans apart. The fingerprint from M2 identifies the statement, not the plan. The same statement can get an index scan today and a full scan tomorrow. So M5 gives every plan a shape. A shape is a hash of what each node does (Index Scan, Hash Join, the join type, parallel or not) and which table and index it uses. It leaves out every estimate. So the same plan with a cheaper estimate has the same shape. A different strategy has a different shape.

Plan flips#

A plan flip is when a statement that ran fine yesterday suddenly gets a much worse plan. In Postgres that happens for a few reasons:

• an index the plan relied on is dropped, or a migration replaces it,
• ANALYZE gathers new statistics and the planner picks a different plan,
• a table grows past the point where the planner thinks a full read is cheaper,
• or a prepared statement switches to a generic plan.

QueryGuard catches two kinds of flip. The first kind is visible in the plan. The new plan reads a table in full, but the statement's usual plan read it through an index. The usual plan is the plan with the most recent runs, among the plans that have run at least 5 times.

A flipped statement runs in the slow lane, where it can only hurt other slow work. The flip is logged once. Then QueryGuard has to decide when the new plan is no longer new. Each plan's past runs fade with a half-life. The half-life is the quarantine setting, 10 minutes by default. If the index never comes back, the full read builds up weight while the index scan's weight fades. After about one quarantine, the full read is simply the usual plan.

One case looks like a flip but isn't. In skewed data, a common value correctly gets a full read, while rare values use the index. Both plans keep running, side by side. So take a plan that has run 5 times while the usual plan also kept running. That plan is treated as the plan for other values, not as a flip.

Generic plans, caught by the clock#

The second kind of flip can't be seen in any EXPLAIN the proxy runs. For the first five runs of a prepared statement, Postgres plans it for its actual values. Then it compares. A generic plan is a plan made for any value. If a generic plan costs about the same as those custom plans, Postgres switches to the generic plan from then on and saves the planning time. The plan_cache_mode setting can also force either kind. A generic plan can't use what it knows about the value. So for skewed data, it can be far worse than the custom plans were.

QueryGuard explains a prepared statement with its bound values. So its EXPLAIN always shows the custom plan. Only the time shows that the switch happened.

So the second kind of flip is a run that is more than 10 times slower than the plan usually takes, and at least 100 ms long. After such a run, the statement's cached plan is dropped, so the statement is explained again. The next run goes to the slow lane. A normal run clears the flag. The 100 ms minimum is another lesson from the benchmark. When the minimum was 20 ms, ordinary lookups that were briefly slow under the rogue's load were taken for flips. They were sent to the slow lane. In a small lane, they could have waited behind seconds of full reads.

Six bugs I didn't find myself#

Before I called M5 done, I had a separate review check the change, looking only for bugs. It found:

• the pipeline timing problem above,
• the data skew case,
• DECLARE being timed,
• plans being forgotten before a commit,
• a first catalog load that could block a session while it held a lock,
• a run being credited to the wrong portal when a client interleaved two portals.

Every one was real, and every one now has a test. That review was worth more than any amount of rereading my own code.

Catching a flip within one run#

The milestone's test was simple to state. A dropped index and a forced generic plan must each be caught within one execution and quarantined. Against real Postgres 16, 17 and 18, a dropped index is caught before the statement's next run. This is because the DDL clears the plan cache once it is committed, and the next EXPLAIN shows the full read. A forced generic plan is caught after its first slow run. Both then run in the slow lane. The rogue benchmark above was also run with calibration on. It still holds the rogue to its six full reads, and no innocent lookup is taken for a flip.

6. One statement is not the whole server#

Everything up to M5 judged one statement at a time: its text, its plan, its tenant's budget and its own run times. Most of what goes wrong on a busy Postgres can't be seen from one statement. For example:

• A migration waits on a lock, and every query on the table waits behind it.
• A report holds a snapshot open for an hour, and vacuum stops cleaning up.
• A standby falls behind.

The whole server slows down, but a fixed max_active keeps letting in as much work as before.

In short: a statement can be fine on its own while the server as a whole is in trouble.

M6 adds two loops that run next to the statement path:

• Once a second, a monitor asks Postgres what every session is doing.
• Once a second, a fleet loop shares the limits with any other QueryGuard instances in front of the same database.

Both loops only change the scheduler's settings. So a statement's own path is as short as it was before.

Asking Postgres what everyone is doing#

Postgres describes itself in a set of views. Four of them answer most questions about a server under load:

A limit that follows the server#

A fixed max_active is a guess about the server, made once. The right number changes with the workload. Thirty cheap lookups can run side by side, but four big joins can't. TCP solved the same problem for networks decades ago. A sender doesn't know how much the network can carry, so it tests it step by step. While things go well, it grows its window by one packet per round trip. When a packet is lost, it halves the window. This is called AIMD, additive increase and multiplicative decrease. It settles near the limit of what the path can carry.

With adaptive on, max_active becomes a ceiling (an upper limit). The fast lane's real limit then moves in the following way, every second:

• Overload: this means one of two things. Either the statements that finished in the last second ran, on average, more than twice as long as they usually take. Or more than a quarter of the limit waits on locks. The limit is multiplied by 0.9, and it drops by at least one.
• Calm and full: every slot was taken at some point and statements finished, but none of the overload signs happened. The limit grows by one.
• In any other case, the limit stays where it is. So on a quiet server, the limit doesn't slowly climb back to the ceiling before there is load that needs it.

"Usually take" comes from M5's plan history: a plan's average over its last 50 runs, once it has run five times. So the signal is not "this statement is slow". An analytics report is always slow. The signal is "statements are slower than their own past". The average over a second is geometric. So one statement that is a hundred times slower can't outweigh everything else.

Who goes first when the server is drowning#

Each tenant has a priority: critical, normal or best_effort. A statement can lower its own priority with a /*priority='best_effort'*/ tag. Only a trusted role may raise one. A free slot goes to the waiting statement with the highest priority. Among statements with equal priority, M4's fair share decides. While the limit is going down, best-effort statements are shed. The ones that are waiting fail at once with 53000. New ones run only if a slot is free.

The order of the queue changes too. Facebook's Fail at Scale describes two observations from running services under overload:

• A queue that never empties is a standing queue. It adds delay, but it does not add work done. This is the idea behind the network algorithm CoDel.
• In a standing queue, the oldest requests are the ones whose clients have most likely given up. So if the server serves them first, it wastes its work on answers nobody reads.

Their answer is adaptive LIFO. Once the queue has stood for a while, serve the newest request first. Also, drop requests that waited too long.

QueryGuard's fast lane does the same. While the queue keeps emptying, it is first in, first out. Once it hasn't been empty for a second, the newest statement goes first. Statements that waited more than 500 ms are turned away with 53000 when a slot frees. The slow lane is meant for long waits. It always stays first in, first out.

What statement_timeout really promises#

statement_timeout is a Postgres setting. A statement that runs longer than this is cancelled with 57014. Its clock starts when the server begins the statement. So time spent in a proxy's queue isn't counted on the server's clock. But it is counted on the client's clock. A client that set five seconds expects an answer, of some kind, within about five seconds.

So QueryGuard treats the client's limit as a deadline, counted from when a statement is sent to run. The deadline is the earlier of two values: the statement_timeout set at login, and a /*deadline='250ms'*/ tag. If a statement's plan has a usual time, the statement must also start early enough to finish in time. Waits for a budget or a slot end at the deadline minus that usual time. A statement that couldn't finish even with no wait at all gets 57014 at once. It does not run for an answer nobody will read.

How Postgres queues locks#

Every statement takes locks on the tables it uses, in one of eight modes. A SELECT takes ACCESS SHARE, the weakest mode. It conflicts with only one mode: ACCESS EXCLUSIVE. Most forms of ALTER TABLE need that mode. So reads and a migration can't run at the same time on the same table.

The part that causes outages is how waiting works. Lock requests on a table form a queue. A new request waits for the locks that are held. It also waits for the requests ahead of it that it conflicts with. This stops a stream of readers from starving a writer forever. But it also means this:

1. A report opens a transaction and reads orders. It holds ACCESS SHARE until it ends.
2. A migration runs ALTER TABLE orders. It wants ACCESS EXCLUSIVE. This conflicts with the report's lock, so it waits.
3. Every new read of orders wants ACCESS SHARE. That lock doesn't conflict with the report at all. But it does conflict with the waiting ALTER, so the read queues behind it.

So one forgotten transaction and one migration are enough. Every query on the table stops until the report ends.

Postgres has a setting for exactly this: lock_timeout. The usual advice is to set it before every migration. A proxy can't set it for the client without changing the client's session. So the DDL guard does the same job from outside. Sometimes the reading shows one of QueryGuard's own sessions running DDL that waits on a lock. If other sessions queue behind it, or if it has waited more than two seconds, the guard cancels the DDL. It uses 55P03, lock_not_available. This is the error of lock_timeout itself, and migration tools already know to retry it.

Who should pay for a lock wait?#

M4 charges each tenant for what its statements cost. A lock wait breaks this. The waiting tenant uses up its slots and its time, and the tenant holding the lock pays nothing. Every reading includes pg_blocking_pids, so QueryGuard knows who waits on whom. So the rule is: the blocker pays. Each second of waiting is moved from the waiter's budget to the holders' budgets. When there are several holders, it is split evenly. The price of a second comes from the plan history: what a second of running usually costs on this server.

Snapshots, xmin and vacuum#

Postgres never updates a row in place. An UPDATE writes a new version of the row and marks the old one as ended. Each version carries the IDs of two transactions: the one that created it (xmin) and the one that ended it (xmax). A transaction sees the versions that were live when its snapshot was taken. This is called MVCC. It is the reason readers never block writers.

VACUUM has to remove the old versions, but only once no snapshot could still see them. The oldest snapshot anywhere on the server is the horizon. It is the smallest backend_xmin in pg_stat_activity. Nothing that changed after the horizon can be cleaned up, on any table. One report left open for an hour holds the horizon in place for an hour.

The table that suffers first is a busy job queue. Every job is inserted, updated a few times and deleted. So it leaves dead versions behind, and every poll for the next job reads through all of them. Now say mvcc_horizon is set, and two things are true:

• the backend holding the oldest snapshot has held it longer than max_age,
• the watched tables' dead rows have grown by more than max_dead_tuples since then.

Then that backend's tenant runs one statement at a time until the backend lets go of the snapshot. This stops the tenant's statements from overlapping and holding the horizon in place between them. It doesn't cancel the report.

A slot that waits on a lock#

While I was thinking about the horizon cap, I found a problem that has nothing to do with it. Since M4, a session gives its slot back while it sits idle inside a transaction. So a client that is thinking between statements doesn't hold a slot. But it can still hold row locks. Take a lane with one slot and two workers on the same row:

A holds the row and needs the slot. B holds the slot and needs the row. Nothing moves until A's statement times out in the queue. This needs nothing unusual, only a small lane and a hot row. So it would have happened in production.

The fix uses the reading again. If other sessions are waiting on a session, that session is let past every limit, budget and hold, within about a second. It is still charged for what it runs. BEGIN, COMMIT, ROLLBACK and SAVEPOINT never wait for a slot at all. They do no work of their own, and the statement holding the slot may be waiting for exactly that COMMIT. Without that rule, the horizon cap could even refuse the snapshot holder its own COMMIT. But that COMMIT is the one statement the cap is there to make happen.

One limit, several proxies: Doorman#

One QueryGuard is a single point of failure. So in production, there are several behind a load balancer. But then a tenant's budget of 1,000 units a second becomes 1,000 per instance, and four instances let in 4,000. Splitting the budget into four fixed quarters is also wrong. A balancer doesn't spread one tenant's connections evenly. And if an instance dies, its quarter is lost with it.

The design I followed is Doorman, the system YouTube built to share rate limits across many clients. It is based on three ideas:

• Ask for capacity, not for permission. A client never asks about a single request. Every few seconds, it tells a central server how much it wants. The server gives it a share.
• Shares are leases. A share is valid until a set time. Until then, the client uses it on its own. To keep it, the client has to renew it. A client that crashes gives its share back just by not renewing it. It does not need to tell anyone.
• Fair share. When there is enough for everyone, each client gets what it asks for. When there isn't, the capacity is split in proportion to what each one asks.

QueryGuard's version keeps these ideas and fits them to what was already there. The store is three small tables in a database set aside for it. So there is no new dependency. Every change to these tables happens one instance at a time, under a Postgres advisory lock. Every second, each instance reports how much of each budget and each lane it used. It then gets a ten second lease. The lease is one of these:

• its demand plus an even part of what is spare,
• or a part in proportion to its demand, when the instances ask for more than there is.

The store never grants what the other instances may still be using.

The hard part is an instance that can't reach the store. Doorman lets a client fall back to a safe share. QueryGuard does this too, and the store does the same arithmetic on its side:

So losing the store or an instance never lets in more than the total. Grants carry sequence numbers, and the instance acknowledges them. So if the reply with a lease is lost on the way back, that lease is still counted. I tested this with a simulation of four instances. It covered store outages, cut links, slow and lost replies, and instances dying. It ran twenty seeds of three thousand steps. Every 50 ms, it checked that the shares never add up to more than the capacity. It found two bugs before any real Postgres was involved:

• a sequence number that was reused after an instance rejoined,
• a fallback share that could be larger than the lease it fell back from.

Twelve bugs at the edges#

As with M5, a separate review read the whole change, looking only for bugs. It found twelve, and every one was real.

The worst one was in the deadlines. I set the deadline when the statement was checked. For a prepared statement, that happens at Parse. But the gate runs again at every Bind. So a client with a statement cache and a five second statement_timeout got 57014 on every run that came more than five seconds after the Parse. In a long-lived app, that is every run. The deadline now starts at each run.

The fleet's bugs were all at the edges. One example: I had put the store in UNLOGGED tables to save WAL. A crash empties those tables, so the store's sequence numbers started again at zero. Those numbers were lower than the numbers the instances had already seen. So every new lease looked older than the last one, and it wasn't counted. The tables are normal logged tables now. They also survive a failover.

The slot and lock knot above was not on the list. I found it after the list was fixed, when I asked what else could go wrong.

What held up against real Postgres#

The milestone's tests, against real Postgres 16, 17 and 18:

• An ALTER TABLE behind a long transaction gets 55P03 within about a second, and the read queued behind it runs.
• Three instances behind a round-robin balancer hold one tenant to one rate, within 10%.
• With Toxiproxy faults on the store's connection, no phase lets in more than the rate. Admission stops at the end of the fallback minute.
• A statement queued past its statement_timeout never runs.
• Ctrl-C through another instance cancels the query.
• The slot and lock knot is undone in about a second. Before, it took the full queue timeout.

7. If you can't see it, you can't run it#

Up to M6, QueryGuard decided and then forgot. It knew a statement's cost before it ran and its time after. It used both, but it kept nothing an operator could read. When something went wrong, I had questions with no answer. Which statements got slow? For which tenant? Since when? And what was QueryGuard doing about it?

M7 keeps numbers on every statement that passes through. It watches those numbers for minutes that don't look like the server's normal behaviour. It also adds the tools an operator needs during an incident:

• a console
• a kill switch
• a list of the statements a role may run
• a way to test a config on yesterday's traffic before it goes live

The surprise: the code that measures had as many bugs as the code that decides. One of them could cancel the wrong statement.

Counting statements the way the client sees them#

Postgres already counts statements, in the pg_stat_statements extension. It counts by role, database and statement. Its time is the time the backend spent executing. QueryGuard wants two things that it can't give:

• The tenant. Postgres knows nothing about tenants.
• The time as the client sees it. This is from when the statement went to Postgres until its answer came back, including network and lock waits.

So QueryGuard keeps its own table. Each row is one statement fingerprint, run by one tenant through one role in one database. A fingerprint is a hash of the statement's parse tree with the constants left out. This is the same idea behind pg_stat_statements's queryid. So WHERE id = 7 and WHERE id = 8 go to the same row. A row holds:

• calls
• rows returned or changed
• latency percentiles
• Postgres's errors by SQLSTATE
• QueryGuard's own rejections, kept separate from Postgres's errors

Percentiles without keeping every run#

A p99 needs the shape of the whole distribution. Keeping every run's time for five thousand rows uses too much memory. Fixed buckets, as in a Prometheus histogram, are cheap. But they are only as precise as their bounds. With buckets ending at 100 ms and 250 ms, a p99 of 130 ms and a p99 of 240 ms look the same.

QueryGuard uses the idea behind DDSketch: bucket bounds that grow by a fixed ratio, here 2%. Every bucket is 2% wider than the one before it. So when QueryGuard reports a bucket's middle, the error is a share of the value. That error is under 1%, whether the run took a microsecond or an hour. A row stores only the buckets that its runs fell in.

What pg_stat_statements still knows better#

Some things only Postgres can see:

• whether a statement found its pages in shared buffers or had to read them in
• whether a sort or a hash spilled to temporary files because it didn't fit in work_mem
• how much WAL a write produced

pg_stat_statements has all three (shared_blks_hit, shared_blks_read, temp_blks_written, wal_bytes). So every 10 seconds, QueryGuard reads it and adds these numbers to its own rows.

Charging for what the plan leaves out#

Since M3, a statement is charged what its plan costs. But two costs that the plan doesn't see turned out to matter.

A plan's cost ends at its top node. Sending the rows to the client also costs the server something. A SELECT * that returns 100 MB costs more than its plan says. QueryGuard now charges returned_mb units, 128 by default, for each MB a statement returns. The number is not a random choice. By definition, a cost unit is what reading one 8 kB page in order costs (seq_page_cost = 1). And a MB is 128 pages.

A write's plan leaves out the triggers it fires, the foreign keys it checks and the indexes it updates. But the WAL it writes includes all of them. pg_stat_statements knows each statement's average wal_bytes. So a write is charged wal_mb units for each MB of WAL it usually writes, at the same 128.

Telling a client when to come back#

A rejection with 53000 used to say only no. Now it also says when to come back:

• A refusal over budget carries a hint: how long until the tenant owes nothing at its rate.
• A refusal because no slot was free says about a second.

The obvious version has a problem. Say twenty clients are turned away at the same moment. They are all told "2 seconds". They all come back at the same moment, and they find room for only one. This is called the thundering herd. The fix is randomness, as AWS explains in Exponential Backoff And Jitter. Each hint gets a random extra amount, up to half of the hint again:

When is a minute unusual?#

With every statement counted, QueryGuard can notice when the server stops behaving like itself. Each minute with at least 20 statements is judged on three signals:

• its p99
• the share of statements that failed
• the share that ran over ten times their own statement's median, and for at least 100 ms

The last signal tells the most. A minute's p99 rises whenever a tenant runs its nightly report. But a statement that takes ten times its own usual time means something has changed.

Each signal has a baseline: a moving mean and mean deviation over about the last 30 minutes. A minute is anomalous when its value is:

• more than three mean deviations above the mean,
• more than twice the mean, and
• over a floor (50 ms, 2% or 5%).

It must pass all three tests. That way, a quiet server with a tiny baseline doesn't raise an alarm over 3 ms. Two anomalous minutes in a row start an anomaly, and two normal ones end it.

Two choices in it are not obvious.

First, an anomalous minute doesn't join the baseline. Otherwise, an incident that lasts an hour would become the new normal halfway through.

Second, a minute with too few statements neither starts nor ends anything. The review pointed out that this lets an anomaly last through a quiet night. I kept it anyway. If a quiet minute counted as calm, then a very slow server would look calm too: one so slow that it finishes only ten statements a minute. The anomaly would then end at the worst moment.

When an anomaly starts, the log says what is behind it. It uses what M5 and M6 already gather:

• the statements with the most slow runs or errors that minute
• the plans that flipped
• the most sessions waiting on locks at once

level=WARN msg=anomaly signal=p99 value=2.1 baseline=0.012 statements="[select * from orders where note like $1]" flips=[] lock_waits=7

Cutting a read short#

statement_timeout limits how long a statement runs. But a fast statement can still do harm by returning too much. For example, take a SELECT with no LIMIT on a table that grew. The client and every proxy on the way buffer a million rows.

A tenant can now have max_rows and max_bytes. QueryGuard counts the DataRow messages as they pass. Once a read is over a cap, it sends a cancel. The client keeps the rows it already has. After them, it gets 54000, program_limit_exceeded, with a message saying which cap it broke.

Which statements may be cut is the interesting part. A cancel makes the statement fail. A statement that fails inside a transaction aborts the whole transaction. Think of a read inside BEGIN ... COMMIT that comes after an UPDATE. Cutting that read would undo the UPDATE too. So only reads outside a transaction are cut.

"Outside a transaction" has a tricky case. In the extended protocol, the statements between two Sync messages run in one implicit transaction, even without a BEGIN. For example, a DELETE and a SELECT pipelined together commit together, or not at all. ReadyForQuery reported the session idle before the batch. Even so, cancelling the SELECT would undo the DELETE. So a read that shares a Sync with an earlier Execute isn't cut either.

My first version had the opposite bug. It cut only a read with nothing else pending. But a prepared statement always has its Parse and Bind pending ahead of its Execute. So no prepared read was ever capped. pgx and most other drivers prepare everything, so in practice the cap applied to almost nothing. The tests had used simple queries.

A cancel can land on the wrong statement#

M1 covered how a cancel request travels: on a new connection to the postmaster, carrying the backend's PID and secret key. What I hadn't thought about was when it takes effect. The postmaster finds the backend and sends it a signal, SIGINT. The signal cancels whatever the backend is running when the signal arrives. It doesn't name a statement.

A client usually cancels the statement it is waiting on, so nothing can come in between. A proxy that cancels on its own is different:

1. The read it is cutting may end by itself while the cancel is on the way.
2. The client sees ReadyForQuery and sends its next statement.
3. If that statement reaches the backend before the signal does, the signal cancels it.

libpq already handles this for its own PQcancel. After sending the request, it waits for the postmaster to close the connection. The postmaster does that only after signalling the backend. QueryGuard now does the same. It holds the client's next message until its own cancel's connection has closed. A signal that reaches an idle backend is ignored. So whichever way the race goes, nothing else is cancelled.

That fix broke something else, and only the tests against real Postgres caught it. M6's DDL guard cancels DDL from the monitor's loop, and logs afterwards. Once a cancel waited for the postmaster, the loop waited on its own cancel, and the log came late. The cancel now goes out from a goroutine. The only thing that waits for it is the session's next message.

transaction_timeout before PostgreSQL 17#

PostgreSQL 17 added transaction_timeout. It ends a session whose transaction lasts too long, whether the transaction is idle or not. Before that, there was idle_in_transaction_session_timeout. It catches a client that forgot to commit. But it doesn't catch a client that keeps sending statements inside a transaction that never ends. Such a client holds its locks and its snapshot the whole time. M6 showed what an old snapshot costs.

QueryGuard does the same on every version, per tenant. When ReadyForQuery reports a transaction open, a timer starts. When it reports idle again, the timer stops. If the timer fires, QueryGuard sends FATAL 25P04, the code PostgreSQL 17 uses. Then it closes the server connection. This makes Postgres roll the transaction back and stop any statement still running in it.

The hard part is when the clock starts. It must start when the client sent the statements that opened the transaction, because that is when the client's own clock started. My first version started it from the oldest statement still waiting for an answer. With pipelining, that can be a different, earlier statement:

Timeouts learned from a statement's own past#

A tenant's statement_timeout must be long enough for its slowest legitimate statement. So it does nothing for the fast ones. A lookup that takes 20 ms can run for 30 seconds when its plan goes wrong, before anything stops it.

With learned_timeouts on, each statement gets its own timeout:

• It is ten times the statement's p99 from the query stats.
• It applies once the statement has run 100 times.
• It is never under a second.
• It is never over the tenant's statement_timeout.

The stats were built for operators to read. This is the first time QueryGuard uses them to decide something.

My first version had a bug here. It timed every run, including the runs it had cancelled. A cancelled run lasts exactly as long as its timeout, so those runs pulled the p99 up. The next timeout was ten times that higher p99, so it was longer again. The timeouts kept climbing. Now only runs that finish without an error are timed.

A statement cancelled for breaking its timeout or a row cap goes on a runaway watch, by fingerprint, for ten minutes. When it runs again, the policy decides what happens. It can log it, send it to M4's slow lane, or turn it away with 53000 and a hint saying when the watch ends. A statement cancelled for any other reason (such as the DDL guard's lock timeout) wasn't at fault, so it isn't watched.

Functions a SELECT can call#

M2's rules look at statement types, and a SELECT is a read. But a SELECT can call anything:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity;
SELECT pg_read_file('/etc/passwd');
SELECT set_config('statement_timeout', '0', false);

deny_functions blocks calls by name, with or without their schema, in any statement. If you don't give it a list of your own, it blocks the built-in functions whose effects reach beyond the statement:

• ending other sessions
• the server's files
• large objects
• other servers through dblink
• settings
• WAL and replication slots
• session advisory locks. These outlive the statement, and behind a pooler they outlive the client too.

A kill switch, and a console that is a database#

In an incident, the first thing an operator wants is to stop one tenant, or one statement. They want it at once, and without a deploy. KILL TENANT acme FOR '10m' refuses all of a tenant's statements with 53000 for ten minutes. KILL STATEMENT with a fingerprint refuses one statement for everyone.

Kills are checked each time a statement runs, not only when it is prepared. My first version checked them at Parse. So an app with a statement cache kept running the statement I had just killed. It used the copy it had prepared an hour before.

Where to type KILL was a design question. An HTTP endpoint means another port, another kind of auth and another client to install. Everyone who runs a Postgres database already has psql. So the console is a database. You connect to queryguard_admin, and QueryGuard answers the session itself instead of passing it on.

Logging in still goes through Postgres. QueryGuard hands the login to a real database (postgres by default) and lets Postgres check the password. So the console keeps no passwords of its own. After a login, Postgres sends ParameterStatus messages that describe the session. One of them is is_superuser. That and the role's name are all the console needs to decide who may use it: superusers, and the roles in admin_roles.

My first version passed on the startup message and then waited for Postgres's answer. But it didn't pass on the client's password in between. Every test used trust auth, so every test passed. And every real login with a password waited forever.

queryguard_admin=> SHOW STATS 3;
queryguard_admin=> KILL STATEMENT 8c6f2a0d4e1b9f37 FOR '10m';
queryguard_admin=> SHOW ANOMALIES;

The console also has SHOW TENANTS, SHOW FLIPS, SHOW WATCH and SHOW KILLS, plus RELOAD to read the config again. It accepts simple queries only, the way psql sends them. Kills, the watch list and the stats live in each instance's memory. So a fleet doesn't share them yet.

Statements a role may run#

Some roles should only ever run a known set of statements: an AI agent's, a dashboard's, a reporting tool's. The learned allowlist works like ProxySQL's firewall:

1. Run it in learn mode while the role does its usual work. Every fingerprint the role runs is recorded.
2. Switch to enforce. Now any statement whose fingerprint wasn't learned is refused with 42501, insufficient_privilege.

Constants don't change a fingerprint, so a learned statement runs with any values. But an injected OR 1 = 1 changes the statement's shape, so it is refused.

The list is a JSON file that maps role to fingerprint to the statement's text. So it can be reviewed and edited by hand. It stops learning at 10,000 statements. Without this limit, a role whose app builds statements with their values inlined would grow the list forever.

Failed logins#

Postgres has no lockout. A client can try passwords as fast as the server answers. The postmaster starts a backend process for every connection before checking anything, so each attempt costs a process.

Say an address fails to log in as one role 10 times in a minute, with 28P01 (a wrong password) or 28000 (refused by pg_hba.conf). Then that address is refused at the proxy for a minute with FATAL 28000. This happens before it reaches Postgres at all.

Trying a config on yesterday's traffic#

Changing a rule in production is a guess about traffic you haven't looked at. With -traffic-log, QueryGuard writes a line for every statement. The line has its time, database, role, tenant, fingerprint, text with the constants replaced, time taken, cost, rows and error.

Then queryguard simulate -config new.json replays a day of it against a new config. It prints what would change:

• the statements each rule would refuse, with examples
• what each tenant's budget would have refused or delayed
• how many statements the new config refuses that ran, and the other way round

A simulation is only honest if it says what it can't replay:

• Cost rules need each statement's plan, and the log doesn't have it.
• Slots and budgets by capacity need the live server.
• A rule that matches on application_name needs a field that the log doesn't keep.

When the config uses any of these, the report names it. It does not quietly report that nothing would change.

Twenty-five bugs, three reviews#

As with M5 and M6, the whole milestone was reviewed for bugs alone. This time there were three separate reviews. Together they found about twenty-five real problems. The worst ones are above:

• the cap that never touched a prepared statement
• the cancel that could land on the next statement
• the transaction clock started by the wrong batch
• kills that a statement cache got around
• the console that hung on any password
• the learned timeouts that kept pushing themselves higher

What is proven, and what isn't#

The milestone's tests against real Postgres 16, 17 and 18:

• Prepared statements, simple queries and errors through QueryGuard land on the right stats rows, by tenant. They land with their calls, rows, percentiles and SQLSTATEs.
• With pg_stat_statements loaded, a sort bigger than work_mem shows its temp file spill on QueryGuard's row for that statement.
• Everything from M1 to M6 still passes, including the DDL guard. That is how the side effect of the cancel fix was found.

The rest is tested against a fake Postgres that plays exact message sequences. This covers the caps, the cancel ordering, the transaction clock, the console and the throttle. A fake server is the only way to put a cancel between a ReadyForQuery and the next Query every time. What isn't proven yet: the anomaly thresholds have only ever seen made-up minutes.

8. Prove it, then ship it#

M1 to M7 were built one piece at a time, and each piece was tested on its own. M8 asked a different question. Does it all hold up together, under real load, on real Postgres? M8 also shipped v1.0.

The surprise: the bug that hurt the headline number most came from a fix.

Seven checks against real load#

Some of M6's and M7's claims had only been tested against a fake Postgres or a fake clock. So I ran each of them again against real PostgreSQL 16 to 19, under real load. Three of the seven checks found bugs:

• A lock pile-up raised two anomalies. Latency and errors both crossed their baselines, a minute apart, and each one started its own anomaly. Now one incident is one anomaly. It holds every signal that rose, and it names the statements that were blocked, not every statement that ran.
• The adaptive limit recovered while the overload went on. It compared each statement's time with its plan's usual time over the last 50 runs. Under steady overload, 50 slow runs become "usual" within seconds. The baseline now moves over about ten minutes, so the limit stays at its floor until the overload ends.
• The cap on a tenant holding back vacuum kept flapping. This is the MVCC horizon from chapter 6. The cap ended the moment the old snapshot did, and the tenant's next snapshot started it again a second later. Now it holds until the horizon has been clear for twice its limit.

An audit of the whole code#

With the features done, four reviewers read the whole code base, each taking one layer. They reported 34 issues. I checked each one against the code before changing anything, and every fix began with a test that failed. Two of them are corners of Postgres that are easy to miss.

The fast path. The protocol has a message, FunctionCall, that calls a function by its OID, with no SQL at all. libpq's large-object functions use it. It went straight past every check QueryGuard has. Now it counts as a statement QueryGuard can't read, so it is refused unless unchecked is allow.

Work that no one plan covers. Cost rules judged one statement's plan. But select 1; <huge join>, EXPLAIN ANALYZE <huge join>, COPY (<query>) TO STDOUT and EXECUTE of a prepared statement all ran without a cost check. Now QueryGuard judges the plan of the statement that does the work. A string with several statements that each do work is refused, because EXPLAIN plans only one at a time.

A fix that made things worse#

After the audit, I ran the benchmark again. Through QueryGuard, the rogue now ran twice as many full reads as before: 8 to 21, instead of 7 to 8. One of the audit's own fixes caused it.

Chapter 5 explained calibration. Each plan learns how long its cost units take, compared with the server as a whole. The server's number is an average over tenants, weighted by their mean cost. The audit found a hole in that. Before PostgreSQL 18, a plan that must use a disabled plan type gets 10 billion units added to its cost. One such plan would set the average for everyone. So the fix capped each tenant's weight at a mean cost of 1,000.

But next to a rogue, every innocent lookup waits its turn. With the cap, hundreds of these slow, cheap lookups weighed almost as much as a full read. Together they pulled the server's time per unit up. Next to that, a full read looked cheap, so it was charged about half its price. Half the price bought twice the reads.

The cap is gone. A plan whose cost carries the 10 billion is left out of the average instead, which was the only case the cap was meant for. A new test keeps the server's timing at the full read's, even with four tenants' waiting lookups next to it. Run again, the rogue was back to 5 to 8 full reads.

The unit tests had a rogue and one waiting lookup. The benchmark had a rogue and three busy tenants. Only the benchmark could see the bug.

The full benchmark#

make bench runs everything three times on each of PostgreSQL 16, 17 and 18. The versions take turns within each run, so a laptop that gets warmer or busier doesn't favour the first one. It all ran on an Apple M1, with Postgres in Docker.

First, what the proxy costs. QueryGuard adds 30 to 44 µs to select 1 at p50, and about 12% to a query that returns 1,000 rows. Most of that is the extra hop that any proxy adds. The client talks to QueryGuard, and QueryGuard talks to Postgres, so every round trip crosses two connections instead of one. select 1 is the worst case, because Postgres does almost nothing for it. A statement that takes milliseconds hardly notices.

Then the rogue. Innocent p99, as the range over all nine runs, with the rogue's full reads:

The test passes when the innocent p99 through QueryGuard stays within 1.5 times the same path's quiet p99, plus a millisecond. Four of the nine runs missed that in one scenario, by 0.1 to 0.8 ms. In those runs the rogue still ran only 5 to 8 reads, so it didn't get past its budget.

QueryGuard decides how often the rogue reads. It doesn't decide how heavy each read is. A full read that is let in still uses the CPU and the disk while it runs, and the lookups that arrive during it are the slowest 1%. That 1% is exactly what p99 measures. On a laptop, with Postgres in a VM, a result this close to its limit passes in one run and misses in the next.

v1.0#

QueryGuard v1.0.0 was tagged on 6 October 2026, and the repository is now public. A tag builds the Docker image for amd64 and arm64 on GitHub's runners, checks that each image reports the right version, and publishes it:

docker run --rm -p 6543:6543 ghcr.io/avik-creator/queryguard:v1.0.0 -listen :6543 -upstream your-db:5432

The code, the README and every number in this post are at github.com/Avik-creator/queryguard.

What building a Postgres proxy taught me#

Eight milestones, and the lessons that showed up in all of them:

• The wire protocol is simpler than it looks. Every message has the same frame, so the proxy can pass on what it doesn't understand. The hard part is never the bytes. It is time: what is in flight, what Postgres will skip after an error, and when an answer arrives.
• Transactions are where a proxy gets dangerous. The hidden transaction between two Syncs decided three things: how to reject a statement (chapter 2), which statements can be timed (chapter 5) and which reads may be cut short (chapter 7).
• Refuse when you might be wrong about Postgres. A proxy that reads a statement differently from the server is worse than no proxy.
• The planner's cost is good for ranking, bad for timing. Use it to decide before a run. Then correct it with the time the run really took.
• Fairness needs history. First in, first out rewards whoever sends the most. A fair turn needs to know who has already been served.
• Most bugs live where two things run at once. A pipeline and its Sync, a cancel and the next statement, a check at Parse and a run at Bind, a monitor loop and a session.
• A review that looks only for bugs finds what rereading doesn't. Every milestone from M5 on had one, and every one found real problems.
• Run the benchmark after every fix, not only the tests. One audit fix passed every unit test and doubled the rogue's reads.

The proxy works because it copies Postgres's own behaviour, and now also its own measurements. It does not make up a model of its own.

What comes next#

v1.0 is out. Next is v1.1 (M9): index suggestions tested with HypoPG, a report on unused indexes and bloat, OpenTelemetry spans, PostgreSQL 19's plan advice for plans that flip, CEL rules, budgets by time of day, usage export and webhook alerts.

The claim that matters most is still untested: someone other than me running make bench and getting the same shape. If you try it, I'd like to hear what you get.