PostgreSQL Row-Level Security: Where the Performance Cost Hides

Benchmarks on a 2-million-row multi-tenant invoices table show that a simple row-level security policy with a matching index is effectively free — the same latency as an explicit WHERE clause. The real cost shows up in specific implementation choices.

Using a PL/pgSQL helper function for the tenant lookup without marking it STABLE prevents PostgreSQL from inlining it, forcing a sequential scan: a 0.2 ms count can balloon past 1.8 seconds. SQL functions stay fast and get inlined regardless of their volatility declaration — until SECURITY DEFINER or SET search_path is added, which breaks inlining and produces multi-second scans even for SQL functions.

Policies that check multi-tenant membership with IN (SELECT ...) add roughly 80 ms to every query because the planner can no longer reduce the check to a single index lookup; resolving the tenant once per request and passing a single value avoids this. Separately, non-leakproof functions like lower() or LIKE used inside a query force PostgreSQL to use the index only for tenant_id and then filter every row of that tenant afterward — turning sub-millisecond lookups into tens of milliseconds, fixable with generated columns or pattern-matching operators.

For engineers, the takeaways are concrete: declare tenant-lookup helper functions STABLE (and PARALLEL SAFE if large tenants matter), avoid SECURITY DEFINER or search_path hardening on functions used inside policies, resolve memberships once per request rather than inside the policy, and replace lower()/LIKE comparisons with indexable alternatives.