Optimizing cross-tenant multi-tenancy in PostgreSQL with Row-level security

Oct 3, 2026

Row Level Security (RLS) policies in PostgreSQL are usually defined in a concrete scope of one session = one tenant. This works quite well as it allows PostgreSQL to rewrite all checks as a simple tenant_id = current_setting('app.tenant_id') comparison, which works great with indexes. The simplicity breaks down when a session cannot be defined in the context of a single tenant. This blog post focuses on the scenario where each row is owned by a single tenant, but the session cannot be scoped to a specific one. Instead of the simple policy, it has to be expressed as tenant_id ∈ <list>.

While the SQL for this policy is quite easy to set up, there are a few things that need to be handled for this to perform well. With the help of AI, I have run 104 tests to optimize the policy as much as possible, and this blog post is an aggregation of what was found and how to replicate it. All tests were run on PostgreSQL 17.

RLS explanation

PostgreSQL RLS is defined as a policy on a specific table. It contains two halves. using is a filter appended to every statement that reads rows: select, update and delete. with check is a constraint on every row that is written, by both insert and update. A single-tenant version looks like this:

create policy tenant_isolation on orders
  using      (tenant_id = current_setting('app.tenant_id'))
  with check (tenant_id = current_setting('app.tenant_id'));

Both checks are expressed here as tenant_id = current_setting('app.tenant_id'). current_setting is a function from PostgreSQL that reads a custom setting, which is stored as a text for a specific session/transaction using set_config. Custom settings need a dotted name, hence the app. prefix. It being a text is important for later. When the table orders is queried, the query will be rewritten. For example:

select * from orders;

will be rewritten to

select * from orders where tenant_id = current_setting('app.tenant_id');

Why expand access to a list?

It is important to emphasize that using a tenant list is a very complex solution that should be supported by a concrete business value that is more valuable than the cost it will introduce. It will become clear throughout this post that you will pay the cost of making this decision in the complexity of the code and the performance of the solution.

With that being said, the reason we chose this solution is quite simple. The most active users of the system work in a flow that goes across tenants. One of the major downsides of the single-tenant workflow is that you have to constantly switch between tenants, which makes keeping track of different tenants and open instances of the solution a tedious task. It also drastically increases the complexity for the users if they have to manage this. The multi-tenant solution drastically simplifies the user experience.

Modelling single-tenant ownership in multi-tenancy

In the end, each row has to be owned by a single tenant. This has to be modelled appropriately for it to work well. The simplest way to do this is to model it as a tree hierarchy. For example, an organization with departments that have teams:

flowchart TD
    A[Organization] --> B[Department A]
    A --> C[Department B]
    B --> D[Team A1]
    B --> E[Team A2]
    C --> F[Team B1]
    C --> G[Team B2]
    classDef me fill:#eb6834,stroke:#eb6834,color:#fff
    classDef visible fill:#2a78d6,stroke:#2a78d6,color:#fff
    class B me
    class A,D,E visible

This introduces three different tenant types in this case, but you could model it to have any number of levels of tenant types. All these different types of tenants can then own data, for example:

When modelling this, all data ownership has to point in the same direction as the tenant structure. By doing this, you can ensure proper access to all related data by always giving any visiting user access to their concrete tenant and all of its parents and children in the tree.

For example, if a user visits from Department A, they can view the Account from their organization, the budget for their department, and the allocations for both Team A1 and A2. Notice the highlights in the flowchart.

In reality, this list can grow quite large with cross-department users who have access to a range of different departments. For this optimization, the focus has been on lists of between 8 and 1,800 tenants, with the tests covering lists from 1 to 10,000.

Properly passing the list to PostgreSQL

As mentioned earlier, a custom setting read with current_setting is the expected way to input the list of tenant ids. This is always saved as a text, so it has to be parsed into a list every time it is used. For the most primitive solutions, this will be for every row that is read from the database, which becomes a problem, especially for larger tenant list sizes. This section will focus on optimizing the parsing, and later sections will focus more on minimizing it.

This was tested for three different types: int, uuid and text (with uuid content). Each was parsed as a literal or using string_to_array(). The results are quite telling:

The int is clearly the best-performing version. This should be taken into consideration by anyone trying to implement this. However, the system I am working on uses uuids represented by text. A major win for us was switching from a text literal to string_to_array(). Parsing-wise, it also performs on par with int. Note that this test only measures the parsing; how the types compare once the list is parsed, for example in comparisons and index size, is not part of it.

Because of this parsing, we introduced a helper function to get the tenant list:

create or replace function current_tenant_ids()
returns text[]
language sql stable parallel safe
as $$
  select string_to_array(current_setting('app.tenant_ids', true), ',')
$$;

An important note is to mark the function as stable parallel safe. Functions are volatile parallel unsafe by default, and a single unsafe function in a policy stops every query on the table from running in parallel; we see a 2-3x regression in performance without it.

Read policy (using)

The read policy needs to compare a tenant_id to a list of tenants. This can be done in multiple ways, and the following have been tested:

• tenant_id = any(current_tenant_ids())
• tenant_id = any((select current_tenant_ids())::text[])
• tenant_id in (select unnest(current_tenant_ids()))
• exists (select 1 from membership m where m.user_id = current_setting('app.user_id') and m.tenant_id = orders.tenant_id)

The last one, referred to as exists membership from here on, differs from the rest. The tenant list is not passed as a setting, but stored in a table with one row per tenant the user can access, and the session only sets the user id:

create table membership (
  user_id   text not null,
  tenant_id text not null,
  primary key (user_id, tenant_id)
);

Each of these provides a different way of doing the same thing, but they perform differently. Tested against reading from a single table and reading with 3 joins, the results are as follows:

It becomes very clear that, for performance, the unnest scales better with the tenant list size, but the = any((select performs much better on smaller tenant list sizes. Therefore, a split should happen at some tenant list size. The plain = any(current_tenant_ids()) is the slowest throughout, as it parses the list for every row, and the exists membership stays flat as the list grows but only catches up with the unnest at the largest list sizes.

Looking into the planner explains this:

• tenant_id = any(current_tenant_ids()) will execute current_tenant_ids() for every row it filters, parsing the list each time. The tenant_id = any((select current_tenant_ids())::text[]) ends up being faster because the sub-select is only executed once per table in the query, and the result is reused for all of that table’s rows.
• The unnest works by creating a hash set and comparing the tenant_id of the rows to that set. It has a high constant cost, but scales a lot better with the size of the tenant list.

Write policy (with check)

The write policy differs, as it will be run for every row inserted or updated. This allows for a different strategy of using a like to compare directly against the input text without parsing the list:

• ',' || current_setting('app.tenant_ids') || ',' like '%,' || tenant_id || ',%'

The commas around both the list and the tenant_id are required. Without them it is a substring match, where for example an int tenant 1 would match 21. Also be aware that % and _ in a text tenant_id act as wildcards in a like, so the column needs a foreign key or check constraint that rules them out. Alternatively, strpos does the same comparison without wildcards.

Technically, the like could also be used for read, but it was so slow it skewed the graph, so it is left out of the read chart.

In order to highlight the strengths of the different strategies, an axis was included representing the number of rows inserted in a single statement. The results are as follows:

At first impression, the best policy seems to be the exists membership. However, the current version is running without the membership table updates. Running a test on different ways of inserting into the membership table gives the following results:

From these results, it becomes quite clear that even though the other tests did not include the set_config call in the timing, the inserts into the membership table are an order of magnitude slower than the set_config. It could still be interesting, because if a session lasts for many statements, the insertion time might be amortized over the session’s lifetime. Running against a few different session reuse patterns gives the following results:

This shows that even though the membership strategy performs better, it needs to be put into the context of how many times the session is reused. This is something that will differ from application to application, so no definitive conclusion can be drawn. As a reference point from these results: at 10,000 tenants and single-row inserts, writing the list costs around 37 ms, so it takes around 10 statements to beat the array strategies and around 170 to beat the like.

These experiments were also run with returning, which is a common way to return, for example, the id of the inserted row. Overall, the numbers were about the same (all are around 0.2 ms slower with no outliers), so the results are not included here.

The conclusion becomes:

• For small tenant lists, the write policy does not seem to matter much.
  • The next section explains how the list can be limited. Also, you know the exact tenants at insert time, so the application can customize the given list to match them.
• For larger tenant lists, it depends on your insert batch size.
  • For smaller batches, the like performs better. This is what you would expect to get when using an ORM, where the batches are usually smaller by default.
  • For larger batch sizes, the unnest strategy performs well.
  • If sessions are reused a lot, it is worth looking into the exists membership strategy, as it performs better when the creation can be amortized over many calls.

Restricting tenant lists application-side

The conclusion from the write section is clear: if you can restrict the tenant list size, the write policy hardly matters for your performance.

A major win for the performance of the application I am currently working on is that most actions in the system are performed on an element/row where the tenant list can be restricted to ~8 tenants.

As an example, take the earlier hierarchy: if a user has access to 3 different departments, they might access or create something related only to Department A. In that case, we can remove the other departments from their context. By doing this, we have been able to limit most interactions to around 8 tenants, which drastically speeds up the system.

This strategy does take extra effort, as each action in a system has to specify that it can be restricted and how to restrict it. We had good success with this in a system with a command bus for the application layer, where each command or query could specify which id can be used to restrict on, and the restriction can happen in a decorator. This enabled us to easily restrict 2/3 of the system’s actions, but the rest had to be restricted manually.

Indexing

Indexing on the tenant_id is quite complicated when done with the tenant list setup:

• The unnest and exists membership strategies cannot use a tenant_id in an index, since they do a hash comparison.
  • The pure tenant_id index will be useless
  • In composite indexes containing tenant_id, only the columns before the tenant_id can narrow down the index scan. Columns after it can still be checked inside the index, but only after reading every entry that matches the columns before it.
• The any check can use a tenant_id in the index, but it will do a lookup for each tenant in the list.

The planner cannot see into the tenant list, so it assumes that the any check is a list of 10 elements, no matter the actual size. This can become quite dangerous: if the list is much larger, the planner will think an index is much more efficient than it actually is. With a ~1500-tenant list, we saw that queries we would expect to take 200 ms took upwards of 200 seconds, so this can be quite extreme. We can see that even at 10 tenants, the effect can make the performance almost 3x slower:

Effectively, this shows that for queries that look rows up by a key with the any strategy, you should not place the tenant_id as the first column in a composite index, as it can potentially lead to much worse performance. The chart is for a 10-tenant list, and the planner’s fixed guess makes this worse as the list grows.

There are a few different points in relation to indexes that are worth addressing before a strategy makes sense.

Index: Sorting and collate C

In an index using tenant_id, it is important to pre-sort the list, as any lookup into indexes requires the list to be sorted. The results show a 10%-20% gain from this (the difference is small enough that it is barely visible in a log-plot, which is why the next one is linear). Additionally, if the text type is used, it can benefit from being collate C, which will do a raw byte comparison:

Index: Using the index effectively

In the initial experiments, there was no difference between including the tenant_id in the index or not. This was until an experiment that evicts the cache using pg_buffercache_evict() before each query, ensuring a cold cache. Using this, a difference could be seen:

This shows that when the join can be answered by an index-only scan, where everything the query needs from the table is in the index, having the tenant_id either included in the composite index or as an include benefits performance. Without the tenant_id in the index, the policy forces PostgreSQL to fetch every row from the table just to check its tenant. It also shows that if you do read outside the index, it does not make any difference. Please also note that this had to be plotted on a linear chart because the difference is small.

The split tenant policy size

When choosing between the unnest strategy and the any strategy, there is no universal answer. Different queries seem to perform differently, so it should be tested against the application’s most common queries. The following experiment shows a few different join options that perform differently:

This suggests that anywhere between 10 and 1000 is the right place to switch. When choosing the precise number, it is also worth taking into consideration the “Restricting tenant lists application-side” option. For example, we chose a limit of 25 tenants, because when we limit from the application side, the list will always be below that, and therefore we use the any check. We expect anything above it to be a cross-tenant query.

The complete policy

Putting the pieces together, the policy has to pick between the any and the unnest strategy based on the size of the tenant list. A single policy cannot switch between the two without losing performance, so each strategy gets its own policy on its own role, using the helper function from earlier:

create role app_user;
create role app_bulk;
grant app_user, app_bulk to app_login with inherit false;
grant select, insert, update, delete on orders to app_user, app_bulk;

alter table orders enable row level security;

-- default role: small tenant lists
create policy tenant_isolation on orders
  for all to app_user
  using      (tenant_id = any((select current_tenant_ids())::text[]))
  with check (tenant_id = any((select current_tenant_ids())::text[]));

-- bulk role: large tenant lists
create policy tenant_isolation_bulk on orders
  for all to app_bulk
  using      (tenant_id in (select unnest(current_tenant_ids())))
  with check (tenant_id in (select unnest(current_tenant_ids())));

The application connects as app_login and, at the start of each transaction, sets the list and picks the role from its size. The limit is 25 tenants in our case:

select set_config('app.tenant_ids', 'id1,id2,id3', true);
set local role app_user; -- or app_bulk when the list is above the limit

Both the true and the local scope the change to the transaction, which keeps the list and the role from leaking to the next user of a pooled connection. If the list is never set, the helper function returns null and no rows are visible. The with check mirrors the using here, but it can be swapped for whatever the write section points to for your batch sizes.

The with inherit false is important. A policy applies to every role the current role inherits from, so a connection that inherited both roles would get both policies combined with an or, which is the merged policy described below.

It is tempting to merge the two into a single policy with an or or a case on the size of the list. Do not do this: the merged expression can no longer be used as an index condition, so the any check loses its ability to use the tenant_id in an index.

Takeaway

If the business win is worth it, this strategy can perform well with some invested effort. We have gained all the benefits of being able to go across tenants in the user experience without it being too slow to deal with. However, the effort to keep the system performing is costly and requires setting up the policies correctly.

Here is a quick checklist if you are dealing with the same problem:

• Helper functions should be marked stable parallel safe. The parallel safe is what keeps parallel plans available. (2-3x speedup)
• For parsing the tenant list: int > text > uuid
  • For text and uuid, string_to_array performs better than literal arrays.
• The read policy (using) should be split in two depending on the tenant list size:
  • Smaller tenant lists should use tenant_id = any((select current_tenant_ids())::text[])
  • Larger tenant lists should use tenant_id in (select unnest(current_tenant_ids()))
  • This split should be chosen based on other factors too. The findings suggest that the right number for deciding when to use which is somewhere between 10 and 1000 tenants, depending on the query. We use 25, as the application-side restriction keeps most lists below that.
• The write policy (with check) should depend on three variables that differ for most applications: session reuse, tenant list size and batch size:
  • The exists membership strategy only pays off when the cost of writing the list is spread over many statements in a session. How many depends on the tenant list size and the strategy it is compared against, so it has to be measured for the application.
  • With smaller tenant counts, you do not need to worry about the write check.
  • Smaller batch sizes can benefit from the like strategy to avoid parsing the array for each insert.
  • Larger batch sizes should choose the policy depending on the tenant list size and use the same as the read policy, as the list can be reused in a batch.
  • It makes no difference to the result whether a returning is used.
• The most performant with check change is being able to restrict the tenant list size in the application layer before writing to the database.
• tenant_id should not be included in indexes by default. It can only be used by the any policy and can lead to bad performance if used incorrectly. The default should be to not include it in indexes.
  • For lookups by key, the tenant_id should be the last equality check in a composite index. For example (id, tenant_id) is better than (tenant_id, id).
  • Since the planner expects the tenant list to be of size 10, a tenant_id-first index becomes more dangerous the larger the list is.
  • If tenant_id is included, it benefits the lookup to provide the tenant list pre-sorted.
  • If tenant_id is a text, index lookups benefit from it being collate C.
  • tenant_id can effectively be placed as the last column in a composite index or as an include to allow index-only scans, if the table is used for joining and is not otherwise read from.

View next or previous post: