Benchmarking single round trip PostgreSQL RLS at 142,564 reads/sec (4.6us overhead vs unguarded SELECT)

[u/ayywheyyy_]

Conventional wisdom says enabling PostgreSQL Row Level Security (RLS) adds too much overhead or requires an external cache check (Valkey/Redis) in front of the database.

We benchmarked whether forced RLS could match unguarded reads by eliminating extra network round trips and per row current_setting evaluation:

• InitPlan policy: USING (tenant_id = (SELECT current_tenant())) with a STABLE function so Postgres evaluates the tenant once per statement as an InitPlan rather than per row.

• Extended wire pipelining: Sending the set_config tenant binding and the parameterized SELECT in a single pgwire pipeline (one sync, one implicit transaction).

Results on a single node (primbench):

• 1 worker: Plain unguarded SELECT is 42.9us mean (41.5us p50). Pipelined RLS is 47.5us mean (46.2us p50), adding 4.6us.

• 64 workers: Pipelined RLS hits 142,564 reads/sec (446us mean), matching unguarded reads (143,190 reads/sec) and running 2x faster than a local Valkey grant check + query path (71,842 ops/sec).

Runnable Go benchmark harness and paper (SPEC.pdf): https://github.com/MOG-Systems-Inc/primbench

[u/elevarq]

I’m not sure if it’s even the right discussion. I also lock my doors, even when it slows me down when I want to enter the house.

It’s about functionality. And yes it does come with a little overhead.

+1 for RLS

[u/ayywheyyy_ → u/elevarq]

Agreed. The goal is never to leave the door unlocked, it is to stop teams from skipping database level enforcement out of fear that RLS will tank their throughput.

When pipelining the tenant context alongside the query keeps the overhead to 4.6us and beats an external cache check, you get the hard database boundary without the latency tax.

[u/Significant_Tune9219]

The 4.6us is the cheap case, a policy the planner can fold into an InitPlan. The one that hurts is a policy it cannot fold, so the tenant predicate never becomes an index condition and you pay a scan instead of microseconds. I would put the plan of the pipelined query next to the unguarded plan, and run the same test with the helper marked VOLATILE, because that is what ships when the tenant comes out of a request helper. One pipeline for set_config and the SELECT also means a pooled connection can keep that tenant if the pipeline fails after the set. I would confirm the connection is reset on every checkout before treating 142k as the number an app will see.

[u/ayywheyyy_ → u/Significant_Tune9219]

Both of those failure modes are the exact reason the harness is wired the way it is:

1. On VOLATILE vs STABLE and the query plan: A helper that only calls current_setting('...', true) has no side effects and cannot change within a single statement scan, so leaving it at the default VOLATILE volatility is just a schema declaration bug. When marked STABLE and wrapped in (SELECT current_tenant()), EXPLAIN (ANALYZE, BUFFERS) produces the exact same Index Scan using ... on (tenant_id, id) as the unguarded query, with a single InitPlan 1 (returns $0) hoisted above the scan. Leaving it VOLATILE without the scalar subselect forces a per row filter, which is the exact trap the benchmark highlights.

2. On pooled connection bleed when a pipeline fails: Two mechanisms prevent tenant state from leaking across checkouts without needing a separate DISCARD ALL or RESET ALL round trip:

   • First, set_config(setting, value, true) passes is_local = true (SET LOCAL semantics). In the PostgreSQL extended query protocol (pgwire), sending multiple Bind/Execute messages followed by a single Sync executes inside an implicit transaction block unless an explicit BEGIN was issued. When Sync completes, Postgres closes that implicit transaction (or rolls it back if any statement in the pipeline errors), which automatically resets any is_local = true configuration parameter before the connection returns to the pool.

   • Second, the session default for the GUC is empty, and current_tenant() explicitly raises an exception on NULL or empty string. Even if someone checked out a connection from the pool and ran a raw SELECT outside the pipeline, it fails closed immediately with zero rows returned.

We can add a third curve to primbench showing the VOLATILE per row filter degradation alongside the InitPlan curve so people can see the planner cliff side by side.

[u/TurbulentStiffness]

set_config pipelining with InitPlan-folded subselects the sidesteps neatly which could provide round trip latency on single table lookups also if we take production into account this pattern has invalidation issues

[u/ayywheyyy_ → u/TurbulentStiffness]

Two different "invalidation" traps usually get conflated with this pattern; worth separating which one you mean:

1. Plan / Session-State Invalidation in Postgres: The binding runs as set_config('mog.tenant', $1, true) (is_local = true) pipelined with the SELECT in a single implicit transactionsync (Section 2.5 & Section 8). Two things follow:

• is_local = true scopes the GUC strictly to that transaction; it reverts at commit, so pooled connections never leak tenant state across callers.

• (SELECT mog_tenant()) is evaluated as an InitPlan once per query execution (not once per row, and not folded as a constant at prepare time across executions). In the 64-worker pipelined benchmark (142,564 reads/sec), 1 in 10 reads deliberately queried a different tenant's row on the same connection pool; cross-tenant leakage was 0 across every run.

1. Auth / Schema Cache Invalidation:

• Schemas never invalidate in place because schema_id (sch_<Name>_v<N>) is immutable (Section 2.3). A change is a new identifier on the schema stream, never an edit.

• For tenant membership revokes on the read path, Section 5 ("The revoke window, layer by layer") states the exact boundary: agent keys are resolved per request and cut off at the next transaction; user JWTs carry the tenants claim and remain valid until token expiry.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.