Row-level security is free until a 0.1% tenant reads 40 million rows

A Postgres policy that narrows a 40-million-row ledger to one merchant’s 40,000 rows can read all 40 million to do it. The policy is correct either way. The difference is one missing word in a function definition, and that word costs you twice.

Row-level security is cheap while the policy expression can still be an index condition. A helper left at the default VOLATILE ends that. The policy becomes a per-row filter, so a tenant holding 0.1% of a 40,000,000-row table reads all 40,000,000, and the planner’s estimate for that tenant stops reading the column’s distribution at the same moment.

When does PostgreSQL row-level security stop scaling?

Row-level security stops scaling at the moment the policy expression stops being usable as an index condition, after which every tenant reads the whole table however little of it belongs to them. Before that, a policy is a cheap extra comparison on an index scan the query was running anyway. After it, the policy is a filter, and a filter runs against rows already pulled off disk.

Section 5.9 of the PostgreSQL 18 manual, Row Security Policies, is plain about the shape of the work: “This expression will be evaluated for each row prior to any conditions or functions coming from the user’s query.” Correctness is not in question anywhere below. The cost is.

The two pages everybody opens first say little about that cost. Section 5.9 gives one sentence of performance advice about the shape of a policy, which is that policies looking only at the current row are “the simplest and best-performing case”; its other mention of performance is row share locks under concurrent updates, a different problem. The caveats at the foot of CREATE POLICY carry none at all. Index condition: locate the tenant’s rows, then read them. Filter: read the table, then keep the tenant’s rows.

Index condition or filter, and one word decides which.

A policy that calls a VOLATILE function cannot use an index

Section 36.7 of the PostgreSQL 18 manual, Function Volatility Categories, rules it out inside the definition of STABLE: “(Since an index scan will evaluate the comparison value only once, not once at each row, it is not valid to use a VOLATILE function in an index scan condition.)” And the default volatility is the one nobody types: the CREATE FUNCTION reference says “If none of these appear, VOLATILE is the default assumption.”

Two definitions, one marker apart. The first can sit inside an index condition on merchant_id:

-- STABLE: evaluated once for the scan, so it can be an index condition.
CREATE FUNCTION current_merchant() RETURNS bigint
  LANGUAGE sql STABLE
  RETURN current_setting('ferrystone.merchant_id')::bigint;

CREATE POLICY merchant_isolation ON ledger_entries
  USING (merchant_id = current_merchant());

The second cannot, and the only difference is the word that isn’t there:

-- No marker, so VOLATILE, so never an index condition.
CREATE FUNCTION merchant_for_session() RETURNS bigint
  LANGUAGE sql
  RETURN (SELECT merchant_id FROM session_map
          WHERE session_key = current_setting('ferrystone.session'));

CREATE POLICY merchant_isolation ON ledger_entries
  USING (merchant_id = merchant_for_session());

STABLE is the right marking here, since CREATE FUNCTION names it the choice “for functions whose results depend on database lookups, parameter variables”. Drop the marker and the comparison becomes a filter. Section 14.1, Using EXPLAIN, says what a filter does: “the plan node checks the condition for each row it scans, and outputs only the ones that pass the condition”, which arrives in a plan as Rows Removed by Filter.

None of that is a discovery and I won’t pretend otherwise. The trap is documented and has been written up more than once, and the usual treatment stops at the sequential scan. The second bill turns up three sections down, in the row estimate.

We don’t take multi-tenant Postgres work where the policy expression calls a function the team can’t mark STABLE. The index condition is the whole engagement; without it there is nothing left to tune.

Rows read against rows returned at 10%, 1% and 0.1% tenant share

A tenant holding 0.1% of a 40,000,000-row ledger reads all 40,000,000 rows to return 40,000 of them, which is 1,000 rows read for every row returned. Ferrystone is an invented system, not a client: the payments ledger this site argues about whenever money has to survive a retry, here carrying one shared ledger_entries table for 800 merchants. Every figure below is chosen so the division can be rechecked, not measured on anybody’s database, and the ledger has its own article on writing payment code.

The query being costed carries no tenant predicate of its own: SELECT sum(amount) FROM ledger_entries; and nothing more, because narrowing to the merchant is the policy’s job. Put WHERE merchant_id = 42 in the application’s SQL and that literal is a constant the index can use whatever the policy function is marked, and none of what follows applies.

Walk the bytes. Ferrystone’s ledger is 40,000,000 rows at a mean width of 120 bytes (chosen for legibility), so 40,000,000 × 120 = 4,800,000,000 bytes. That is 4,800 MB, or 4.8 GB. At a sequential read rate of 800 MB/s — also chosen, and kind to a cloud volume — 4,800 MB ÷ 800 MB/s is 6 seconds of scan. The 0.1% tenant owns 40,000 rows, which at 120 bytes is 4,800,000 bytes, or 4.8 MB. Fetched by an index condition, 4.8 MB ÷ 800 MB/s is 0.006 seconds, or 6 ms. Six milliseconds against six seconds, for the same answer.

Rows read and rows returned for that balance query on the filter path, by the merchant’s share of Ferrystone’s 40,000,000-row ledger:

Two routes to that multiplier, and only one is a second route. The byte ladder is the row count in other units: 4,800 MB ÷ 4.8 MB is (40,000,000 × 120) ÷ (40,000 × 120), and the width cancels. A real second route never touches a row count. A filter plan reads 100% of the table whatever it returns, the merchant’s share is 0.1% by definition, and 100 ÷ 0.1 = 1,000. On a live plan you read it off the counter: Rows Removed by Filter at 40,000,000 − 40,000 = 39,960,000, against 40,000 returned.

Scale that to the fleet. 800 merchants loading a balance once an hour is 800 × 6 s = 4,800 seconds of scanning inside every 3,600 of wall clock, or 1.33 volumes of sequential throughput committed before anybody opens a report; on the index path those same 800 read the table once between them, so 6 seconds covers the hour.

Check the volatility first, and the LEAKPROOF marking second

Volatility decides whether the policy expression can be an index condition, and leakproofness decides something else entirely, which is the reverse of the advice you will meet most often. Marking a helper LEAKPROOF governs whether the planner may run your qualifiers on rows the policy would hide — an ordering question inside the security barrier, with no bearing on whether merchant_id = current_merchant() can be an index condition. Section 36.7 settles the index question on its own, and the word leakproof does not appear on that page once.

Two columns of pg_proc answer both questions at once. Section 52.39 of the PostgreSQL 18 manual, pg_proc, says provolatile is “v for ‘volatile’ functions, whose results might change at any time”.

SELECT proname, provolatile, proleakproof
FROM pg_proc
WHERE proname IN ('current_merchant', 'merchant_for_session');

The CREATE FUNCTION entry defines the second column: “LEAKPROOF indicates that the function has no side effects. It reveals no information about its arguments other than by its return value. For example, a function which throws an error message for some argument values but not others, or which includes the argument values in any error message, is not leakproof.” Then the entry’s closing sentence, which settles it for most readers: “This option can only be set by the superuser.” On most managed Postgres, where the administrative role is not a true superuser, you can’t mark your own tenant-lookup function leakproof at all. Operators are a separate question. The one-line check for those is \do+ in psql, collected below.

I wouldn’t mark one LEAKPROOF even with the role to do it: a function that reads a setting and casts it can throw on a malformed value, which is just the case the manual rules out.

Why does the planner estimate the same row count for every small tenant?

Because the planner has to know the comparison value before it can look anything up, and which fallback it lands on depends on that marker. Mark the helper STABLE and the call is folded to a constant first: the header comment on estimate_expression_value in the PostgreSQL 18.0 source file src/backend/optimizer/util/clauses.c lists three liberties estimation takes over ordinary constant folding, and the second is “Fold stable, as well as immutable, functions to constants.” A constant is what puts you on the documented formula.

Section 69.1 of the PostgreSQL 18 manual, Row Estimation Examples, carries that formula three screens down the page in Part VII, Internals, and nothing in the row-security documentation links to it. It governs values outside the most-common-values list, and Section 14.2, Statistics Used by the Planner, is where that list’s cap is written down: the maximum number of entries in most_common_vals is set “globally by setting the default_statistics_target configuration variable”, and “The default limit is presently 100 entries.”

selectivity = (1 - sum(mcv_freqs))/(num_distinct - num_mcv) = (1 - (0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003))/(676 - 10) = 0.0014559

rows = 10000 * 0.0014559 = 15 (rounding off)

Apply it to Ferrystone, the invented ledger above. Its top 100 merchants hold 65% of the 40,000,000 rows (chosen, like every figure here), so the frequencies sum to 0.65, num_distinct on merchant_id is 800 and num_mcv is 100. Selectivity is (1 − 0.65) ÷ (800 − 100) = 0.35 ÷ 700 = 0.0005, and 40,000,000 × 0.0005 = 20,000 rows for each of the 700 outside the list. Those 700 hold 35% of 40,000,000 rows, which is 14,000,000, and 14,000,000 ÷ 700 = 20,000 again.

Real tenants don’t spread evenly. Ferrystone’s 700 small merchants run from 500 rows to 160,000, a 320:1 spread (160,000 ÷ 500 = 320) collapsed into a single number. At the bottom, 20,000 ÷ 500 = 40, a fortyfold over-estimate; at the top, 160,000 ÷ 20,000 = 8, an eightfold under-estimate. The 0.1% merchant owns 40,000 rows and is told 20,000, wrong by two. Two is survivable. Forty is how a plan that should seek turns into a plan that scans.

Leave the marker off and you never reach that formula at all. With no constant on the other side of the comparison, equality selectivity takes its non-constant branch, and the comment above the arithmetic in src/backend/utils/adt/selfuncs.c (line 507 of 8,786) says what happens instead: “Search is for a value that we do not know a priori, but we will assume it is not NULL. Estimate the selectivity as non-null fraction divided by number of distinct values, so that we get a result averaged over all possible values whether common or uncommon.” Distinct values on merchant_id is 800, so 1 ÷ 800 = 0.00125 and 40,000,000 × 0.00125 = 50,000 rows. The 10% merchant gets 50,000. The 0.1% merchant gets 50,000. All 800 get 50,000, and the most-common-values list, default_statistics_target and the 65% share play no part. One cross-check there does read the list — “selectivity should never be estimated as more than the most common value’s” — and it never bites, since the largest merchant holds 10% against an estimate of 0.125%.

So the missing word bills you twice: once for the scan, and once for an estimate no amount of ANALYZE will improve, since only the distinct count still gets read. Everything below assumes the marker is back.

Row-level security does not cost you the planner’s statistics

The rule people cite for that is Section 69.3 of the PostgreSQL 18 manual, Planner Statistics and Security, and its fall-back needs two conditions to fail together. Read the sentence whole: “the current user must either have SELECT privilege on the table or the involved columns, or the operator used must be LEAKPROOF”. A tenant subject to a policy holds SELECT on the table, which is what makes the policy apply at all.

I had this wrong, and said it to people who went and designed around it. Section 69.3 says that where both branches fail, “the selectivity estimator will behave as if no statistics are available” — five paragraphs that never mention row-level security, and whose worked case is a security barrier view over a table the reader cannot read. What gave it away was the section number: the same argument is Section 68.3 in the PostgreSQL 17 manual and 69.3 in 18, and I had been reading 68.3.

Section 69.3 also collects \do+ from earlier, in the paragraph that states the rule: “The psql program’s \do+ meta-command is useful to determine which operators are marked as leakproof.” Open the 17 page and that sentence is absent, and 17 even spells it “leak-proof” where 18 spells it “leakproof”. The reason sits in the PostgreSQL 18.0 release notes under psql: “Add function’s leakproof indicator to psql’s \df+, \do+, \dAo+, and \dC+ outputs (Yugo Nagata)”.

Most of the row-level security I have read does nothing at all. Section 5.9 again: “Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE … FORCE ROW LEVEL SECURITY.” Applications almost always connect as the role that created the tables, so until somebody runs that statement the policies are decoration, and one query settles it: relrowsecurity and relforcerowsecurity in pg_class. And the setting named for switching row security off doesn’t: Section 19.11.1 says that with row_security set to off, “queries fail which would otherwise apply at least one policy”.

One thing I can’t tell you. A policy written as IN (SELECT ...) might be pulled up into a semi-join or might come out as a per-row SubPlan; Section 5.9 allows sub-SELECTs and warns about race conditions, and says nothing either way. I have only ever seen a hashed SubPlan. Run EXPLAIN before you design around it.

Multi-tenant RLS or schema-per-tenant: the tenant count that decides it

The line is the statistics target on the tenant column: under 100 tenants the default is enough, between 100 and 10,000 you buy per-tenant estimates with ALTER TABLE ... SET STATISTICS and pay in ANALYZE time, and above 10,000 there is nothing left to buy. Call it the statistics-target line. The ceiling here is the statistics target, not catalog capacity: the limit usually quoted for this decision is the catalog, and that one lands lower.

The number comes from a tolerance, not from taste. We accept a row estimate wrong by at most tenfold for any tenant on the bill (chosen, and arguable). Ferrystone’s 800 merchants put 65% of the rows in the top 100 and spread the other 700 over 500 to 160,000 rows each, so the shared estimate is the 20,000 derived above and the worst case is the fortyfold error at the small end. 40 beats 10. One statement fixes it:

ALTER TABLE ledger_entries ALTER COLUMN merchant_id SET STATISTICS 800;
ANALYZE ledger_entries;

The ALTER TABLE reference gives the range as “0 to 10000”. 800 against a default of 100 is eight times the target, which buys room in the list for all 800 merchants rather than an entry each: the target caps the length of most_common_vals, and ANALYZE enters a value only where its sample supports one. The planner settings page says only that “Larger values increase the time needed to do ANALYZE”, a direction with no multiple attached, so time it on your own table.

Schema-per-tenant is the honest alternative and the case for it is not weak: the planner reads statistics describing exactly one tenant, and a tenant that writes a billion rows bloats its own tables and nobody else’s. Then cost it past the ceiling, at 20,000 tenants. 12 tables each is 240,000 tables, and a migration adding a column to all twelve is 12 × 20,000 = 240,000 statements at 40 ms apiece (chosen, and generous for DDL taking ACCESS EXCLUSIVE on its table), so 9,600,000 ms = 9,600 s = 160 minutes of locking per release. Derate the input least worth trusting, the 40 ms, to 10 ms: 2,400,000 ms = 2,400 s = 40 minutes. The ranking holds. A fourfold cut in the cost of the rejected option does not move it.

The obvious estimate stops at migration minutes, and the hidden factor is the shared lock table. Section 19.12 of the PostgreSQL 18 manual, Lock Management, puts max_locks_per_transaction at a default of 64 objects per server process, then adds that “individual transactions can lock more objects as long as the locks of all transactions fit in the lock table”. So 64 is an average and the real bound is the table: at the typical default of 100 connections in Section 19.3, that is 64 × 100 = 6,400 entries for the instance. A settlement report touching all 240,000 tables in one transaction wants 240,000 ÷ 6,400 = 37.5 times that allowance, and raising it is a restart rather than a reload. The cross-tenant query stops being one query, which is also what a shared database does to a service split.

Halve the tenant count and the answer flips. At 10,000 tenants the migration is 120,000 statements, so 120,000 × 40 ms = 4,800,000 ms = 4,800 s = 80 minutes, no better per table; but 10,000 is exactly the top of the documented range, so SET STATISTICS 10000 is the last rung on the ladder and one table holds. Schema-per-tenant wins past 10,000 tenants, or where a regulator wants physical separation, and nowhere in between. Below roughly 100 tenants — the default statistics target, and the number isn’t a coincidence — one table with a policy and a btree index on the tenant column is simply right, and we wouldn’t price the alternative. A week of reading query plans is the shape most software development work on multi-tenant Postgres actually takes.