PostgreSQL Indexes for user_id and tenant_id — When You Actually Need Them

Columns named user_id and tenant_id often sit on the hottest paths in a Supabase or PostgreSQL application. They appear in ownership filters, Row Level Security policies, joins, foreign keys, tenant-scoped lists and background jobs.

That makes them strong index candidates. It does not mean every column with one of those names should automatically receive an index.

The useful question is:

Does this column participate in a query, policy or referential operation where an index can materially reduce the amount of work PostgreSQL must do?

This guide shows how to answer that question, how to choose between single-column and multicolumn indexes, and how to verify the result instead of assuming that an index is useful.

Why user_id and tenant_id are common index candidates

A typical user-owned table might look like this:

create table public.projects (
    id uuid primary key,
    user_id uuid not null,
    name text not null,
    created_at timestamptz not null default now()
);

Application requests may repeatedly run queries such as:

select id, name, created_at
from public.projects
where user_id = $1
order by created_at desc
limit 50;

Or a multi-tenant application may use:

select id, status, created_at
from public.jobs
where tenant_id = $1
  and status = 'queued'
order by created_at;

As these tables grow, repeatedly scanning unrelated rows to find one user’s or one tenant’s rows can become expensive. A suitable B-tree index can give the planner a cheaper access path to the relevant subset.

RLS makes these columns even more important to review

In Supabase, ownership columns frequently appear inside Row Level Security policies:

create policy "Users can read their projects"
on public.projects
for select
to authenticated
using (
    (select auth.uid()) = user_id
);

Supabase recommends indexing columns used in RLS policy predicates when they are not already covered by a primary key or unique index. Its published RLS performance example shows a large improvement after adding an index on user_id for a large test table.

The important boundary is that an index improves how PostgreSQL can execute the policy condition; it does not define the authorization rule itself. A fast incorrect policy is still an incorrect policy.

Do not index a column only because of its name

Indexes are performance structures, and they have a cost. PostgreSQL has to keep them synchronized with the table, so they add work to data modifications and consume storage. The planner can also decide that a sequential scan is cheaper, especially for a small table or a query that needs a large fraction of the rows.

Before adding an index, look for evidence that the column participates in one or more of these paths:

• frequent WHERE user_id = ... or WHERE tenant_id = ... filters,
• RLS USING or WITH CHECK predicates,
• joins to ownership or membership tables,
• foreign-key relationships where the referencing side must be searched during deletes or updates,
• tenant-scoped lists with a stable sort order,
• background work that repeatedly selects rows for one owner or tenant.

A name such as tenant_id is a strong signal to investigate. It is not a substitute for looking at the workload.

Start with an index inventory

The following query inventories user_id and tenant_id columns in the public schema and shows valid indexes where the column appears as an actual index key. It deliberately distinguishes key columns from INCLUDE columns, which are stored in an index but do not participate in its search semantics.

select
    n.nspname as schema_name,
    c.relname as table_name,
    a.attname as column_name,
    idx.index_name,
    idx.index_type,
    idx.key_position,
    idx.is_unique,
    idx.is_primary,
    idx.partial_predicate
from pg_class c
join pg_namespace n
    on n.oid = c.relnamespace
join pg_attribute a
    on a.attrelid = c.oid
   and a.attnum > 0
   and not a.attisdropped
left join lateral (
    select
        ic.relname as index_name,
        am.amname as index_type,
        k.ord::integer as key_position,
        i.indisunique as is_unique,
        i.indisprimary as is_primary,
        pg_get_expr(i.indpred, i.indrelid) as partial_predicate
    from pg_index i
    join pg_class ic
        on ic.oid = i.indexrelid
    join pg_am am
        on am.oid = ic.relam
    join lateral unnest(i.indkey::smallint[])
        with ordinality as k(attnum, ord)
        on k.attnum = a.attnum
       and k.ord <= i.indnkeyatts
    where i.indrelid = c.oid
      and i.indisvalid
      and i.indisready
      and i.indislive
) idx on true
where c.relkind in ('r', 'p')
  and n.nspname = 'public'
  and a.attname in ('user_id', 'tenant_id')
order by
    n.nspname,
    c.relname,
    a.attname,
    idx.key_position nulls last,
    idx.index_name;

A row with a null index_name tells you that this inventory did not find a valid direct index key for the column. A non-null key_position shows where the column appears in a multicolumn key. A non-null partial_predicate means the index only covers rows satisfying that predicate.

This is an inventory query, not an automatic recommendation engine. It does not know whether a particular application query is frequent or expensive enough to justify another index, and it does not attempt to infer equivalent expression indexes.

When a single-column index is enough

If the dominant access path is a simple equality filter, the smallest useful index may be equally simple:

create index projects_user_id_idx
on public.projects (user_id);

This can support queries such as:

select *
from public.projects
where user_id = $1;

It can also help when the same column is evaluated repeatedly as part of an RLS ownership predicate.

When a multicolumn index is a better fit

Real application queries often do more than filter by owner. They filter by owner and then sort or filter again.

For example:

select id, name, created_at
from public.projects
where user_id = $1
order by created_at desc
limit 50;

A multicolumn B-tree index can match that access pattern more closely:

create index projects_user_id_created_at_idx
on public.projects (user_id, created_at desc);

For B-tree indexes, column order matters. PostgreSQL documents that multicolumn B-tree indexes are most efficient when constraints apply to the leading columns. An index on (user_id, created_at) is therefore not interchangeable with (created_at, user_id) for a workload dominated by WHERE user_id = ....

Similarly, for a tenant-scoped queue:

create index jobs_tenant_status_created_idx
on public.jobs (tenant_id, status, created_at);

may fit a frequent query that constrains both tenant_id and status before ordering by created_at. It should not be added merely because those columns happen to exist.

Separate indexes versus one composite index

Suppose a table is queried sometimes by user_id, sometimes by tenant_id, and sometimes by both.

You might consider separate indexes:

create index events_user_id_idx
on public.events (user_id);

create index events_tenant_id_idx
on public.events (tenant_id);

PostgreSQL can combine multiple indexes for some AND and OR conditions using bitmap index scans. A multicolumn index can instead be more efficient for a stable query shape that constrains both columns, especially when index ordering also helps avoid a separate sort.

There is no universal winner. Choose indexes around the query patterns you actually need, not around a rule that every useful column deserves every possible index combination.

Foreign keys are another reason to inspect supporting indexes

A foreign key references columns backed by a primary key, unique constraint or suitable unique index on the referenced side. PostgreSQL does not automatically create an index on the referencing foreign-key columns.

That matters because deleting a referenced row or updating a referenced key can require PostgreSQL to find matching rows in the referencing table. PostgreSQL’s documentation explicitly notes that indexing the referencing columns is often a good idea, while also noting that it is not always required.

If projects.user_id is both a foreign key and a common application filter, the same supporting index can be useful for application queries and referential work.

Verify with EXPLAIN instead of trusting the DDL

Creating an index does not guarantee that PostgreSQL will use it. The planner estimates the cost of alternative plans and can still prefer a sequential scan.

Start by inspecting the planned access path:

explain
select id, name, created_at
from public.projects
where user_id = '00000000-0000-0000-0000-000000000001'
order by created_at desc
limit 50;

On a safe read-only query, you can inspect actual execution too:

explain (analyze, buffers)
select id, name, created_at
from public.projects
where user_id = '00000000-0000-0000-0000-000000000001'
order by created_at desc
limit 50;

Look at the chosen scan type, estimated versus actual row counts, rows removed by filters, buffer activity and whether a separate sort is still required.

EXPLAIN ANALYZE actually executes the statement. That is normally straightforward for a read-only SELECT, but data-modifying statements require additional care.

A simple decision framework

Situation	Index signal	What to verify
user_id is used in a frequent equality filter	Strong	Table size, selectivity and EXPLAIN plan
user_id appears in an RLS ownership predicate	Strong	Existing PK/unique coverage and policy query cost
tenant_id scopes most queries on a growing table	Strong	Whether a composite index better matches filters and ordering
Column is a referencing foreign key	Often useful	Delete/update behavior and application joins
Table is tiny and rarely queried	Weak	Whether PostgreSQL already prefers a sequential scan
Column exists but is not used in filters, joins or policies	Weak	Do not index by naming convention alone

Pre-launch index checklist

• Inventory user_id, tenant_id and other ownership columns.
• Check whether they are used in real WHERE, join or RLS predicates.
• Check existing primary-key, unique and secondary indexes before creating duplicates.
• Review foreign-key columns on the referencing side.
• Match multicolumn B-tree index order to the dominant query shape.
• Include sort columns only when the workload justifies them.
• Use EXPLAIN and, where safe, EXPLAIN (ANALYZE, BUFFERS).
• Do not assume an index is useful merely because it exists.
• Do not create every possible composite index.
• Account for write overhead and storage cost.
• For RLS-heavy tables, test with the authorization paths the application actually uses.
• Plan index creation on large live tables as an operational change, not an ad-hoc production experiment.

Adding an index to a live production table

A standard CREATE INDEX allows reads but blocks writes on the table while the index is built. On a live system, that can be unacceptable. PostgreSQL provides CREATE INDEX CONCURRENTLY to avoid blocking normal inserts, updates and deletes, but concurrent builds take more work and come with operational caveats.

Treat production index creation as a deployment decision. Review table size, workload, available I/O capacity, transaction behavior and rollback steps before running DDL on a busy database.

The practical rule

user_id and tenant_id deserve attention because they frequently encode ownership or tenancy and often sit on critical query paths.

The right rule is not “always index these names.” It is:

Index the ownership and tenant keys that your real filters, joins, RLS policies and referential operations depend on — then verify the plan.

Sources and further reading

• PostgreSQL: Introduction to indexes
• PostgreSQL: Multicolumn Indexes
• PostgreSQL: Combining Multiple Indexes
• PostgreSQL: Constraints and foreign keys
• PostgreSQL: pg_index catalog
• PostgreSQL: Using EXPLAIN
• PostgreSQL: CREATE INDEX
• Supabase: Row Level Security