Counting per tenant in PostgreSQL: count, estimate, counter

“412 open invoices.” Every multi-tenant SaaS shows counts per tenant — on the dashboard, in a tab, next to a filter — and every one of them is a count(*) under row-level security. For a small tenant that costs nothing. For the largest one, that count is usually the cause when “the dashboard is slow” — months after launch.

We measured three ways to get the number on PostgreSQL 17.10, in a throwaway container with default settings: 2,074,910 invoices across 100 tenants with a skewed distribution (the largest 400,000, then 200,000, down to 4,000), an index on (tenant_id, issued_on), and row-level security with FORCE, queried as an application role. Times are the median of five runs.

How slow is count(*) for one tenant?

A plain count used an index-only scan on (tenant_id, issued_on) with Heap Fetches: 0: PostgreSQL counted index entries and never touched the table. A count with a condition on a column that is not in the index has to read the rows themselves, and cost two to three times as much. With an extra index on (tenant_id, status) the same count became an index-only scan too: 2.3 ms for the largest tenant instead of 27.4, and 0.14 ms for the smallest. The cost grows with the tenant’s size, though less than proportionally: a hundred times the rows took 37 times as long. All of that is on a freshly vacuumed database. The trouble starts when the data changes.

Why did count(*) get 5× slower for the largest tenant?

We marked 20% of the largest tenant’s invoices as paid — one statement changing 80,000 rows, an ordinary month for a busy customer — and counted again: 64.7 ms instead of 13.2, with Heap Fetches: 424,560. The index-only scan can only skip the table for pages that the visibility map marks as all-visible, and every updated page lost that mark. There are more heap fetches than rows because the index now also holds entries for the old row versions. Our update hit every fifth invoice, so it touched nearly every page of that tenant; updates bunched in recent months touch fewer pages and cost less. Updating the visibility map is one of the things vacuum is for.

So why did vacuum not run? With autovacuum on, we waited 90 seconds after the update: no autovacuum, and the count still took 65.4 ms. The table’s threshold is 50 dead rows plus autovacuum_vacuum_scale_factor — 20% by default — “of table size”: 50 + 0.2 × 2,074,910 = 415,032 dead rows. The largest tenant had produced 80,000. In a shared table, one tenant’s churn is a small share of the whole, so autovacuum waits while that tenant’s counts, and every other index-only scan on that tenant’s pages, stay slow. Other tenants, whose pages were not touched, notice nothing.

The fix is a threshold per table. With alter table invoices set (autovacuum_vacuum_scale_factor = 0.01) autovacuum ran 31 seconds later, and the count was back to 13.3 ms with Heap Fetches: 0.

Can you estimate a tenant’s count instead?

For large tenants, yes. EXPLAIN gives the planner’s estimate without counting, and under row-level security it includes the policy, so as the application role it is an estimate for this tenant:

That works because the planner keeps a list of the most common values per column, 100 entries by default, and with 100 tenants every tenant is on it. (Whether a tenant is on it, you can read from pg_stats.most_common_vals.) We then added 1,900 small tenants (50 to 650 invoices each) and analysed again. The top tenants stayed accurate (397,604; 40,920; 8,860 against 400,000; 40,000; 8,000). Every tenant outside the 100 most common got the same estimate: 401 rows — for tenants that actually had 50, 350 or 650. For those the planner only knows an average: the remaining rows divided by the remaining number of distinct tenants.

That gives a workable rule: use the estimate for a tenant large enough to be among the most common values, and count exactly below that. We only tested estimates for a tenant’s total; a count with an extra condition such as a status is an estimate built on another estimate. The small tenants, where the estimate is useless, are exactly the ones where count(*) takes a millisecond or less.

What does a counter table cost?

The other classic answer: keep the count in a table, one row per tenant, updated by a trigger on every insert. We measured inserts with pgbench, 8 clients for 10 seconds (the busy-tenant column twice):

Spread over many tenants the counter row costs next to nothing. For one busy tenant it cut throughput by 77% and raised the average latency from 0.75 to 3.26 ms: every insert has to wait for the lock on the same row until the previous transaction commits. That is precisely your largest tenant, during their busiest hour.

Append-only deltas do not contend: each insert writes its own row. The price moves to reading. With 109,135 unmerged deltas, the counter plus the sum of the deltas took 12.6 ms — hardly better than counting, which took 19–21 ms at that point on the same, not yet vacuumed table. A job that folds the deltas into the counter took 100 ms for all of them, after which the count read in 0.15 ms and matched the actual number exactly (509,273). A delta table only pays off if something merges it regularly.

Two things apply to any counter table: it needs the same row-level security policy as the table it counts, and deletes and status changes need their own trigger, or the number drifts.

A checklist for counts per tenant

1. Index-only scans need a vacuumed table. Set autovacuum_vacuum_scale_factor per large shared table; 20% of a table holding all tenants is a threshold a single tenant rarely reaches.
2. Put the columns you count on in the index if the count has a condition: (tenant_id, status) took the largest tenant’s count from 27.4 to 2.3 ms.
3. Estimate for large tenants, count for small ones. The estimate is good for the tenants in the most-common-values list and a constant for everyone else.
4. A counter row per tenant does not survive a busy tenant. Use delta rows and merge them, or accept the count.
5. Counter tables get a policy too. A table of counts per tenant holds tenant data.

What we did not measure

Materialized views refreshed on a schedule, pg_class.reltuples (only for a whole table, never per tenant), counts with several conditions at once, a higher statistics target for tenant_id, and counters that also follow deletes and status changes.

Sources: the PostgreSQL 17 documentation on routine vacuuming (vacuum threshold, visibility map) and autovacuum settings (autovacuum_vacuum_scale_factor, default 20% of table size), read on 1 October 2026. All measurements are our own, on PostgreSQL 17.10, on 1 October 2026.