DEV Community: Oliver Plummer

Postgres RLS gotchas & performance

Oliver Plummer — Sat, 11 Jul 2026 01:45:53 +0000

Row Level Security (RLS) is a great feature available in Postgres. It is a nice abstraction layer that simplifies all your queries and implements authorization. However when performance issues arise everybody’s favourite thing to blame is RLS and the general consensus is very mixed. When you first encounter a problem caused by RLS, it can get very frustrating or just flat out confusing. Most people just end up dropping RLS and moving on which is honestly fair. From my experience the feature is solid enough where most of these issues can be solved or small workarounds can be implemented instead but I will say though even getting it right, there is always a performance cost so why use it?

Currently I work on a system where we shamelessly rely on Postgres to do a lot of the heavy lifting. This includes RLS where it basically handles making sure that only the rows the user has access too are visible and nothing else. Best way to think of RLS are just like where clauses applied implicitly at the query level. Which is very nice as it simplifies things a lot... but also sounds very scary, especially when you first hear about it!

In this article I will go over all the gotchas and challenges I encountered along the way when implementing RLS myself as I am confident you will face them with the the out-of-box solution.

Multiple `permissive` policies

Simple one that prevents the planner from using indexes properly. More than one permissive policy (which is the default and probably why it got me) adds ORs to all your queries, meaning you won't be able to efficiently use your indexes or multiple scans will be required. From my experience this usually results in the planner just giving up and doing a sequential scan.

You should aim to have only one permissive policy for a single table or select/update/delete operation, or make use of restrictive policies which is the equivalent of adding AND to your queries. If you still have a scenario where you need to have multiple permissive policies, you will just have to accept the planner not directly using your indexes.

create table project (tenant_id uuid not null, id uuid not null);

-- permissive is the default
create policy project_admin on project for
select
  to my_user using (has_permission (array['admin']));

create policy project_user on project for
select
  to my_user using (has_permission (array['view-projects']));

Do not do this as your queries will effectively look like this:

select
  *
from
  project
where
  (has_permission (array['admin']))
  or (has_permission (array['view-projects']));

To to fix this you can just create database roles for each one you have but to avoid multiple permissive policies:

create role my_admin_role;

create role my_user_role;

create policy project_admin on project for
select
  to my_admin_role using (has_permission (array['admin']));

create policy project_user on project for
select
  to my_user_role using (has_permission (array['view-projects']));

Must surround policies with brackets `(select ...)`

Simple one, anytime you have a policy with a constant expression, you need to surround it in brackets so it gets evaluated first. Otherwise the planner will have no option but to evaluate on every row so doing this will create a “InitPlan”:

create policy project_select on project for
select
  to my_user using (
    (
      select
        has_permission (array['view-projects'])
    )
  );

Queries in policies

You might be thinking that we don't need to add the redundant column as we can use a query to get the tenant_id value from the parent relationship table. Although possible with a few tricks, I have found this too confusing to put in the policy itself and simply adding the redundant column is the easiest. I have also shown an example of a restrictive policy below:

create policy project_tenant on project as restrictive
for all
  to my_user using (tenant_id = get_tenant_id ());

create policy project_select on project for
select
  to my_user using (team_id = any (get_access_team_ids ()));

If you need to check for a array of values you can create a function with the stable security definer leakproof keywords that returns back the array of values. You need to specify all of these or it will be evaluated for every row.

create function get_access_team_ids () returns uuid[] as $$
  select array(
    select
      team_user.team_id
    from
      team_user
    where
      team_user.user_id = current_user_id ()
  );
$$ stable security definer leakproof parallel safe;

Redundancy and multi-column indexes

In our case we needed to support a "multi-tenant" style architecture. What this means for our RLS policies is every table now must have a tenant_id column. The problem with this is that it has a cascade effect on everything you do where now all indexes, including foreign keys, primary keys, constraints should probably include this column. We had tables where two columns always needed to be checked, so this added more columns to our btree indexes as it must always be available. Not including these columns will result in the planner not reliably choosing the index.

This is not too big of a problem other than the added complexity and overall larger index sizes. For our case not creating multi-column indexes bricked our cascade deletes so this was the downside of supporting that, but the same thing applies to queries.

create table project (tenant_id uuid not null, team_id uuid not null, id uuid not null);

create policy project_select on project for
select
  to my_user using (
    tenant_id = get_tenant_id ()
    and team_id = any (get_access_team_ids ())
  );

create index project_tenant_team_id_idx on project (tenant_id, team_id, id);

-- OR
alter table project
add primary key (tenant_id, team_id, id);

Since the policy always adds tenant_id and team_id to the query, we should construct our indexes to include these or we risk the planner not choosing a index for the query. I personally found it easiest to just make it the primary key as strange as it seems, as long as we make sure not to duplicate the id column for different tenant_id and team_id combinations.

Functions and `stable parallel safe leakproof`

These keywords are like public static void main from Java - you just have to add it. Any of these emitted just completely prevents you from using features of Postgres. Not knowing this will most likely just confuse the hell out of you and leave you scratching your head as it would seem like the planner does not choose to do things it should be able to do!

create function get_tenant_id () returns uuid as $$
  select current_setting('my.tenant_id', true)::uuid;
$$ stable parallel safe leakproof;

`stable`

Even if you know it wouldn't actually do much harm to have the function run every row, I would recommended just adding it anyways if you are able to. The planner just can't come up with a good execution plan most likely because of the cost of running the function for every row is too high.

`parallel safe`

A simple one to remember to add as functions as this is not the default. Not adding this prevents the planner from ever using parallelism. If you use a function in your policy that you suspect is not parallel safe, I would re-think it or just accept that this as a flaw and make sure not to break it as not having parallel queries you just loose out on performance.

`leakproof`

This one is super annoying, and from what I can see is not really documented. If you emit this keyword the planner just entirely prevents you from using features of indexes, for all the major ones like gist, gin, and btree. Many operators commonly used to directly query indexes directly can't be used since the underlying functions are not leakproof.

Only place I can see in the PostgreSQL docs:

For example, an index scan cannot be selected for queries on security barrier views (or tables with row-level security policies) if an operator used in the WHERE clause is associated with the operator family of the index, but its underlying function is not marked as LEAKPROOF.

The psql program's \dAo+ meta-command is useful to list operator families and determine which of their operators are marked as leakproof.

Running psql \dAo+ btree will show you all the operators and functions that implement them. You can see which ones are marked as leakproof or not in the last column.

For example trying to use a btree index for text search you will see it will just filter out the rows instead of using it in the scan as a condition.

create index my_table_value_idx on my_table using btree (value);

select
  count(*)
from
  my_table
where
  my_table.value like 'Sant%';

First you have to run this:

alter function textlike (text, text) leakproof;

Now this does look like we are introducing a security risk but most people just disable RLS at this point so doing this just brings you to the default security level for out-of-the-box postgres. I have found reasons such as timing attacks and functions that throw errors are not leakproof which I'm sure are very good reasons but in my opinion this is a over-engineered feature of RLS and should just be opt-in, as it just makes it frustrating to use for an extra security benefit.

Common operators:

timestampz range operations
All enum operations
numeric range operations
json/jsonb range and path queries
text similar & like operators
tsvector operators

Concurrency in JavaScript and the power of Web Workers

Oliver Plummer — Wed, 03 Apr 2024 00:43:07 +0000

Introduction

Recently I stumbled upon a problem while working on a browser application I was building where it seemed I ran into the limits of working in JavaScript's single threaded execution environment.

My application needed to process potentially thousands of files all in the browser to scan their metadata to save locally to IndexedDB. The problem was that the library function I was using to process the files with was quite an expensive call which consequently blocked the browser and most of the interactivity with the UI. This is not acceptable in a UI where the user may want to continue using the application in a performant way or in my case the ability to cancel the file processing.

Now the first thing all JavaScript developers learn is that JavaScript runs in a single execution/threaded environment. Whether they are starting out in Node.js or writing front-end browser applications we are told that this is by design as the event loop (in Node.js) or the browser engine will handle long running tasks and queue them accordindly. This is the reason the page blocks on expensive synchronous tasks.

Since I needed to process the files locally, and uploading them to a server to process them was not an option, this is where I thought of Web Workers. They allow you to launch a seperate thread where you can run your expensive function calls freeing the main window thread.

Web Workers Introduction

Here are the basics of using web workers in the browser with an implmentation left out:

// main.ts

// Create a new worker
const worker = new Worker("my-worker.js");

// Listen for responses from the worker
worker.addEventListener("message", event => {
  const data = event.data as MyResponse;

  // do something with the response
});

let message: MyCustomMessage;

// ...

// Sending a message to the worker thread
worker.postMessage(message);

// ... later in the program
worker.terminate();

// my-worker.ts

addEventListener("message", event => {
  const data = event.data as MyMessage

  // ...process result

  let response: MyCustomRepsonse;

  // Send the response back to the main window thread
  postMessage(response);
});

The best way to think about Web Workers and how to utilize them is to think of them as just a plain old function. You send the worker a message (function arguments), the worker processes the input (worker file), then returns a response (function return statement).

You can combine the web worker creation, sending, and message receiving into one function that returns a promise. Or you can launch a web worker that sits in the background waiting for messages. We should note that initiating a web worker (new Worker(...)) is a costly operation but either way just make sure we terminate the worker after use.

Here is a example of using a Web Worker that checks if a number is prime of not.

// main.ts

function isPrime(value: number) {
  return new Promise<boolean>(resolve => {
    const worker = new Worker("is-prime-worker.js");

    worker.addEventListener("message", event => {
      const data = event.data as boolean;

      resolve(data);

      worker.terminate();
    });

    worker.postMessage(value);
  });
}

// is-prime-worker.ts

addEventListener("message", event => {
  const data = event.data as number;

  postMessage(isPrime(data));
});

function isPrime(value: number) {
  for (let i = 2, s = Math.sqrt(num); i <= s; i++) {
    if (num % i === 0) return false;
  }
  return num > 1;
}

Concurrency

Concurrency is therefore possible in JavaScript if we use multiple web workers, with the main window thread acting as the communicator for all worker threads.

When building my application I needed to send an array of ArrayBuffer's to the web workers for them to process and send back a result.

Since the time to process is dependent on an external thread we cannot use a loop but a recursive function. What I mean is the worker.onmessage event will how we trigger the next item to be processed. Let me explain below:

Concurrent web worker setup

First of all we need a pool of workers. The first question I had was how many to create? Well for optimal performance we should utilize all threads the user has available. Luckily the browser provides a global property for this and can be accessed via navigator.hardwareConcurrency. Since we already have one being used and we wish not to block it, use navigator.hardwareConcurrency - 1.

const MAX_WORKERS = navigator.hardwareConcurrency - 1;

Now let's create the pool with each worker having a flag that specifies when it is currently processing.

interface WorkerPoolItem {
  value: Worker;
  isProcessing: boolean;
}

const pool: WorkerPoolItem[] = [];

for (let i = 0; i < MAX_WORKERS; i += 1) {
  const worker: WorkerPoolItem = {
    isProcessing: false,
    value: new Worker(workerURL),
  };

  pool.push(worker);
}

Now we will create a function called batchProcessor. This function will be responsible for finding free workers in the pool and sending work to be processed. Please note this function mutates the work array passed in because of the use of pop() but you could of course do this many other ways.

function batchProcessor<T>(
  pool: WorkerPoolItem[],
  work: T[],
  onComplete: () => void,
) {
  const batch: [T, WorkerPoolItem][] = [];

  // find available workers
  const availableWorkers =
    pool.filter(worker => !worker.isProcessing);

  // assign work to available workers
  for (const worker of availableWorkers) {
    const value = work.pop();

    // if value is undefined this means
    // there is no more work to do
    if (value === undefined) break;

    batch.push([value, worker]);
  }

  // no work left?
  if (batch.length === 0) {
    // are any workers still processing?
    if (pool.every(worker => !worker.isProcessing)) {
      // now we are done
      onComplete();
    }

    // always return as there is no more work to do
    return;
  }

  // send work to workers
  for (const item of batch) {
    const [value, worker] = item;

    // mark worker as processing
    worker.isProcessing = true;

    // send work to worker
    worker.value.postMessage(value);
  }
}

The first time this function is called on the pool it will get all workers started with work to do. As we will get to see this function will be called recursively and it has to handle the initial case (where all workers are free) and the ending case (where there is more workers free than work left to do). It also handles another scenario which will get into where we may want to do some processing before we send out a message to a worker and during this time multiple workers may have completed and we have a batch of work to send out - this is because of the way the event loop works.

Combining the code for the worker pool initilization and the batchProcessor function we get a function concurrentProcessor that processes an array of arbitrary values by concurrently sending it to a pool of workers. Note how the worker.onmessage event is what triggers the rest of the data to be processed.

async function concurrentProcessor<T, V>(
  workerURL: string,
  work: T[],
  onProcess: (value: V) => void,
) {
  const pool: WorkerPoolItem[] = [];

  try {
    // promisify the recursive function
    await new Promise<void>(resolve => {
      // initialize worker pool
      for (let i = 0; i < MAX_WORKERS; i += 1) {
        const worker: WorkerPoolItem = {
          isProcessing: false,
          value: new Worker(workerURL),
        };

        // on recieve message from worker
        worker.value.addEventListener("message", event => {
          onProcess(event.data as V);

          worker.isProcessing = false;

          // Since a worker has just completed processing
          // a value, we can start processing another batch
          batchProcessor(pool, work, resolve);
        });

        pool.push(worker);
      }

      // start processing first batch of work
      batchProcessor(pool, work, resolve);
    });
  } finally {
    // terminate workers
    for (const worker of pool) {
      worker.value.terminate();
    }
  }
}

Final Implementation

This implementation extends the functionality a bit further by allowing us to process the value before sending it out to the worker.

GitHub

npm install concurrent-processor

In my case I needed to send the ArrayBuffer of a File object to the worker but I did not want to have an array of ArrayBuffer's as this would use far to much memory.

const workerURL = "metadata-scanner.js";

const values: File[] = [
  // ...
];

// to be passed into options.onRead
function onRead(file: File): Promise<ArrayBuffer> {
  return file.arrayBuffer();
}

type MyData = unknown;

const data: MyData[];

function onProcess(value: MyData) {
  data.push(data);
}

await concurrentProcessor<File, MyData, ArrayBuffer>({
  workerURL,
  onRead,
  onProcess,
  values,
});

// metadata-scanner.js

addEventListener("message", event => {
  const data = event.data as ArrayBuffer;

  postMessage(scanMetadata(data));
});

function scanMetadata(data: ArrayBuffer): MyData {
  // ...
}

// concurrent-processor.ts

const MAX_WORKERS = navigator.hardwareConcurrency - 1;

export async function concurrentProcessor<T, V, R = T>(
  options: ConcurrentProcessorOptions<T, V, R>,
) {
  const pool: WorkerPoolItem[] = [];

  try {
    // promisify the recursive function
    await new Promise<void>(resolve => {
      // initialize worker pool
      for (let i = 0; i < MAX_WORKERS; i += 1) {
        const worker: WorkerPoolItem = {
          isProcessing: false,
          value: new Worker(options.workerURL, options.workerOptions),
        };

        worker.value.addEventListener("message", event => {
          options.onProcess(event.data as V);

          worker.isProcessing = false;

          // Since a worker has just completed processing
          // a value, we can start processing another batch
          void batchProcessor<T, V, R>(pool, options, resolve);
        });

        pool.push(worker);
      }

      // Start processing first batch of work
      void batchProcessor<T, V, R>(pool, options, resolve);
    });
  } finally {
    // terminate workers
    for (const worker of pool) {
      worker.value.terminate();
    }
  }
}

async function batchProcessor<T, V, R = T>(
  pool: WorkerPoolItem[],
  options: ConcurrentProcessorOptions<T, V, R>,
  onComplete: () => void,
) {
  const batch: [T, WorkerPoolItem][] = [];

  // find available workers
  const availableWorkers = pool.filter(worker => !worker.isProcessing);

  // assign work to available workers
  for (const worker of availableWorkers) {
    const value = options.values.pop();

    // if value is undefined this means there is no more work to do
    if (value === undefined) break;

    batch.push([value, worker]);
  }

  // no work left?
  if (batch.length === 0) {
    // are any workers still processing?
    if (pool.every(worker => !worker.isProcessing)) {
      // now we are done
      onComplete();
    }

    // always return as there is no more work to do
    return;
  }

  // mark workers as processing
  // we should do this here as below we may have to wait for promises to resolve
  // and we don't want another batch to be processed in the meantime
  for (const item of batch) {
    const worker = item[1];

    worker.isProcessing = true;
  }

  let values: [R, WorkerPoolItem][] = [];

  if (options.onRead) {
    const promises: [Promise<R>, WorkerPoolItem][] = [];

    for (const item of batch) {
      const [value, worker] = item;

      const result = options.onRead(value);

      if (result instanceof Promise) {
        promises.push([result, worker]);
      } else {
        values.push([result, worker]);
      }
    }

    if (promises.length > 0) {
      const promiseValues = await Promise.all(promises.map(([promise]) => promise));

      for (let i = 0; i < promiseValues.length; i += 1) {
        // @ts-expect-error
        values.push([promiseValues[i], promises[i][1]]);
      }
    }
  } else {
    // if there is no onRead function, it just passes the values through
    values = batch as unknown as [R, WorkerPoolItem][];
  }

  // send work to workers
  for (const item of values) {
    const [value, worker] = item;

    if (options.transfer && isTransferable(value)) {
      worker.value.postMessage(value, [value]);
    } else {
      worker.value.postMessage(value);
    }
  }
}

function isTransferable(value: unknown): value is Transferable {
  return (
    value instanceof ArrayBuffer ||
    value instanceof OffscreenCanvas ||
    value instanceof ImageBitmap ||
    value instanceof MessagePort ||
    value instanceof ReadableStream ||
    value instanceof WritableStream ||
    value instanceof TransformStream ||
    value instanceof VideoFrame
  );
}

interface WorkerPoolItem {
  value: Worker;
  isProcessing: boolean;
}

export interface ConcurrentProcessorOptions<T, V, R = T> {
  workerURL: string;
  workerOptions?: WorkerOptions;
  transfer?: boolean;
  values: T[];
  onRead?: (value: T) => Promise<R> | R;
  onProcess: (value: V) => void;
}