💡 Does PostgreSQL RLS Policy Design Impact Performance? Benchmarks Reveal the Truth

✨ Introduction

Row Level Security (RLS) is a powerful PostgreSQL feature that helps enforce data isolation directly at the database layer.

When implementing RLS, one common question often arises that should we use a single policy with multiple conditions, or split the logic into multiple policies? While both approaches are functionally correct, their performance characteristics are rarely discussed in official documentation. This raises curiosity, does policy structure actually matter?

In this blog, I will go with real world scenaio and performance tests in both cases.

🧩 Begin with Real Scenario

I have a scenario where I have a multi-client database. Current situation is that all client data sits in one database with RLS enabled by against client id. This will restrict a client data to others.

Every table has a client_id column as reference, secured by RLS policies. Branches table and bills table also has client_id with RLS. Now there is a new requirement comes from the client is that the branch manager can view their branch bills only.

Client expectation is simple is that they want a screen that should have the way to assign the managers to branch. By this way, their branches bills only should be shown to the managers. On backend, how we are going to acheive this in DB level, the same way how we are already restricting the client wise data.

PostgreSQL has the policy, as we know already, that will make our effort as simpler. Introducing one more policy against bills table and that should restrict other branches data. Branches will be filtered on policy, based on who logged in. Now bills table should have the following policies:

One for client restriction
One for branch restriction

Question: Already there is a policy running against bills table. Now, Do I want to create a one more policy or should alter the existing policy which includes branch restrictions? Which way to choose? Does PostgreSQL doc is detailing about this? No. Doc is silent on which one to choose to get better performance and maintainability.

🕵️ Benchmark Experiment

I was impressed to find out the performance difference between those two way of policies. Hence, I choose the Benchmark Dotnet to make the comparison.

Database Setup:

The benchmark database contains three schemas with identical structure and data. Row Level Security (RLS) is enabled in all schemas. Only the policy structure differs between them.

Current Schema (Existing Implementation)

RLS is applied using a single condition per table.

Clients Table

ALTER TABLE current.clients ENABLE ROW LEVEL SECURITY;

CREATE POLICY current_clients_isolation_policy
    ON current.clients
    USING (id = current_clientid);

Branches Table

ALTER TABLE current.branches ENABLE ROW LEVEL SECURITY;

CREATE POLICY current_branches_isolation_policy
    ON current.branches
    USING (client_id = current_clientid);

Bills Table

ALTER TABLE current.bills ENABLE ROW LEVEL SECURITY;

CREATE POLICY current_bills_isolation_policy
    ON current.bills
    USING (client_id = current_clientid);

Approach 1 (Multiple Policies)

Bills table uses multiple policies to apply client and branch isolation separately.

Clients Table

ALTER TABLE approach1.clients ENABLE ROW LEVEL SECURITY;

CREATE POLICY approach1_clients_isolation_policy
    ON approach1.clients
    USING (id = current_clientid);

Branches Table

ALTER TABLE approach1.branches ENABLE ROW LEVEL SECURITY;

CREATE POLICY approach1_branches_isolation_policy
    ON approach1.branches
    USING (client_id = current_clientid);

Bills Table

ALTER TABLE approach1.bills ENABLE ROW LEVEL SECURITY;

CREATE POLICY approach1_bills_client_policy
    ON approach1.bills
    USING (client_id = current_clientid);

CREATE POLICY approach1_bills_branch_policy
    ON approach1.bills
    USING (
    branch_id = ANY (
      SELECT branch_id
      FROM approach1.branchwiseusers
      WHERE user_id = current_userid
        AND is_manager = true
    ));

Approach 2 (Single Policy with Combined Conditions)

Bills table uses a single policy that combines client and branch conditions.

Clients Table

ALTER TABLE approach2.clients ENABLE ROW LEVEL SECURITY;

CREATE POLICY approach2_clients_isolation_policy
    ON approach2.clients
    USING (id = current_clientid);

Branches Table

ALTER TABLE approach2.branches ENABLE ROW LEVEL SECURITY;
      
CREATE POLICY approach2_branches_isolation_policy
    ON approach2.branches
    USING (client_id = current_clientid);

Bills Table

ALTER TABLE approach2.bills ENABLE ROW LEVEL SECURITY;

CREATE POLICY approach2_bills_isolation_policy
    ON approach2.bills
    USING (
      client_id = current_clientid
      AND branch_id = ANY (
      SELECT branch_id
      FROM approach1.branchwiseusers
      WHERE user_id = current_userid
      AND is_manager = true)
    );

Benchmark Structure:

To isolate the impact of Row Level Security (RLS) policy design, the benchmark was intentionally kept simple and consistent across all scenarios:

Simple query

A single-table lookup on bills using COUNT(*). This represents a typical OLTP-style (Online Transaction Processing) query where RLS predicates are evaluated row-by-row.

Complex query

A multi-table query joining Clients → Branches → Bills, combined with date filtering and aggregation (COUNT + SUM). This reflects a realistic reporting workload where RLS is evaluated across multiple relations within a single execution plan.

Across all runs, the following were held constant:

Same database instance
Same data distribution
Same session variables (current_clientid, current_userid)
Identical SQL shape
The only difference was the RLS policy structure.

C# Program

using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Jobs;
using Dapper;
using Npgsql;
using System.Data.Common;

[SimpleJob(RuntimeMoniker.Net80)]
[MemoryDiagnoser]
public class ClientBranchBillRlsBenchmark
{
    private const string ConnectionString =
        "Host=localhost;Port=5432;Database=benchmark_test;Username=benchmark;Password=benchmark";

    /*
     * SCHEMAS & RLS DESIGN
     * -------------------
     *
     * current
     *  - Bills: single RLS predicate (client_id)
     *
     * approach1
     *  - Bills: multiple RLS policies
     *      - client_id isolation
     *      - branch_id isolation via branchwiseusers
     *
     * approach2
     *  - Bills: single RLS policy with combined predicates
     *      - client_id AND branch_id
     */

    // ---------------------------------
    // SIMPLE QUERY (Bills only)
    // ---------------------------------

    [Benchmark(Description = "Current - Bills (single RLS condition)")]
    public int Current_Bills_Simple()
        => ExecuteScalar("SELECT count(*) FROM current.bills");

    [Benchmark(Description = "Approach1 - Bills (multiple RLS policies)")]
    public int Approach1_Bills_Simple()
        => ExecuteScalar("SELECT count(*) FROM approach1.bills");

    [Benchmark(Description = "Approach2 - Bills (combined RLS condition)")]
    public int Approach2_Bills_Simple()
        => ExecuteScalar("SELECT count(*) FROM approach2.bills");

    /*
    * COMPLEX QUERY
    * -------------
    * JOIN: Clients → Branches → Bills
    * AGGREGATION: COUNT + SUM
    * Purpose: Exercise multi-table RLS evaluation in a single query
    */

    [Benchmark(Description = "Current - Complex Join + Aggregation")]
    public (int BillCount, decimal TotalAmount) Current_ComplexQuery()
        => ExecuteQuery(@"
            SELECT
                COUNT(*)      AS billcount,
                COALESCE(SUM(bi.amount), 0) AS totalamount
            FROM current.clients c
            JOIN current.branches b ON b.client_id = c.id
            JOIN current.bills bi ON bi.branch_id = b.id
            WHERE bi.bill_date >= now() - interval '1 day'
        ");

    [Benchmark(Description = "Approach1 - Complex Join + Aggregation")]
    public (int BillCount, decimal TotalAmount) Approach1_ComplexQuery()
        => ExecuteQuery(@"
            SELECT
                COUNT(*)      AS billcount,
                COALESCE(SUM(bi.amount), 0) AS totalamount
            FROM approach1.clients c
            JOIN approach1.branches b ON b.client_id = c.id
            JOIN approach1.bills bi ON bi.branch_id = b.id
            WHERE bi.bill_date >= now() - interval '1 day'
        ");

    [Benchmark(Description = "Approach2 - Complex Join + Aggregation")]
    public (int BillCount, decimal TotalAmount) Approach2_ComplexQuery()
        => ExecuteQuery(@"
            SELECT
                COUNT(*)      AS billcount,
                COALESCE(SUM(bi.amount), 0) AS totalamount
            FROM approach2.clients c
            JOIN approach2.branches b ON b.client_id = c.id
            JOIN approach2.bills bi ON bi.branch_id = b.id
            WHERE bi.bill_date >= now() - interval '1 day'
        ");

    // ---------------------------------
    // Shared helpers
    // ---------------------------------

    private int ExecuteScalar(string sql)
    {
        using var conn = CreateConnection();
        return conn.ExecuteScalar<int>(sql);
    }

    private (int, decimal) ExecuteQuery(string sql)
    {
        using var conn = CreateConnection();
        return conn.QuerySingle<(int, decimal)>(sql);
    }

    private DbConnection CreateConnection()
    {
        var dataSource = new NpgsqlDataSourceBuilder(ConnectionString).Build();
        var conn = dataSource.CreateConnection();

        conn.Open();

        // RLS session context
        conn.Execute("SET current_clientid = 1");
        conn.Execute("SET current_userid = 267");

        return conn;
    }
}

Benchmark Result:

Method	Mean	Error	StdDev	Median	Allocated
Current: Simple Query	72.77 ms	5.162 ms	15.14 ms	68.28 ms	306.39 KB
Approach1: Simple Query	71.19 ms	6.207 ms	18.30 ms	68.77 ms	306.39 KB
Approach2: Simple Query	67.97 ms	6.006 ms	17.23 ms	71.26 ms	306.43 KB
Current: Complex Query	249.36 ms	8.551 ms	24.81 ms	245.05 ms	389.84 KB
Approach1: Complex Query	258.85 ms	8.845 ms	25.66 ms	260.25 ms	390.02 KB
Approach2: Complex Query	255.68 ms	7.725 ms	22.66 ms	258.28 ms	390.02 KB

Result Summary

There is no significant performance difference between using a single RLS policy with multiple conditions and using multiple policies per condition. Execution time and memory allocation remain largely consistent across all approaches.

🎉 Conclusion

No matter which approach we choose, the benchmark results make one thing clear that there’s no meaningful performance difference between the two. And now it all makes sense… that’s probably why the PostgreSQL documentation stays happily silent on this case 😄

Happy Coding!! ☕.

💡 Does PostgreSQL RLS Policy Design Impact Performance? Benchmarks Reveal the Truth

✨ Introduction

🧩 Begin with Real Scenario

🕵️ Benchmark Experiment

Database Setup:

Benchmark Structure:

Benchmark Result:

🎉 Conclusion

SB

Address