Row-level security performance in PostgreSQL, measured
a day ago
- Simple row-level security (RLS) policies with direct column comparison (e.g., tenant_id = setting) incur no measurable performance cost when the query already filters by that column.
- RLS performance costs stem from three sources: how the tenant ID is retrieved, membership subqueries, and the use of non-leakproof functions in queries.
- PL/pgSQL functions declared VOLATILE cause row-by-row execution, resulting in sequential scans (up to ~1.9 seconds). Fix by declaring STABLE or wrapping the call in a (SELECT ...) subquery.
- SQL functions are inlined by default, but adding SECURITY DEFINER or SET search_path breaks inlining. Declaring them STABLE avoids this dependence.
- Functions are PARALLEL UNSAFE by default, which disables parallel plans. Declaring STABLE PARALLEL SAFE restores parallelism, but can cause plan caching issues in connection-pooled environments.
- Membership subqueries (IN (SELECT ...)) evaluate per row, adding ~80 ms overhead per query. Using = ANY (ARRAY(SELECT ...)) improves counts but may worsen queries that rely on index order.
- Best practice for multi-tenancy: resolve membership once before the query and pass the tenant ID via a setting, avoiding subqueries in the policy.
- Non-leakproof functions (e.g., lower(), LIKE) prevent index usage for the expression under RLS. The index is used only for the tenant column, then the filter is applied to all rows of that tenant, disproportionately slowing large tenants.
- Fixes for the leakproof issue include using a stored generated column for lower(), using leakproof operators (~>=~, ~<~ for text_pattern_ops), or marking the function as leakproof (not recommended).
- To diagnose RLS performance issues: check for VOLATILE functions in policies using the provided query, and run EXPLAIN as the application role to see if conditions become Filter instead of Index Cond.