Lorixen.
EN

Slow 'total rows' counts on big tables: faster alternatives

Short answer: In PostgreSQL, SELECT count(*) on a big table has to look at every row to decide which ones are visible to your transaction, so it gets slower as the table grows. Admin lists showing "Page 1 of 4,812" and dashboards with exact totals are typical victims. Ask whether an exact number is really needed: often an estimate ("about 120,000 orders"), a capped count ("10,000+"), a stored counter, or "Next page" without a total is just as useful and much faster.

Why counting is slow

The PostgreSQL wiki explains it directly: a full count(*) can be comparatively slow because of how PostgreSQL handles concurrent transactions (MVCC). Different transactions can see different versions of the data, so there's no single stored total; PostgreSQL must walk through the rows to check visibility, which usually means a sequential scan of the whole table. Index-only scans help in some cases, where the table's visibility information allows it, but on busy tables they often still need to visit many rows.

Counting with a filter, such as WHERE status = 'pending', can use an index, but large matching sets are still slow to count.

Where counts hide in apps

  • Pagination that shows total pages or "Showing 1–50 of 182,341"
  • Admin dashboards with tiles like "Total customers" or "Orders this year"
  • Tab badges such as "Pending (3,214)" recalculated on every page load
  • ORM pagination helpers that silently run a count query alongside each page

Check your slow query log or performance monitoring: a count query running on every page view is a frequent surprise.

Option 1: use an estimate

PostgreSQL keeps statistics for its query planner, including an approximate row count per table in the pg_class catalogue (reltuples). The documentation explains these values aren't updated on the fly; they're refreshed by VACUUM, ANALYZE and some other operations, so they're approximate. For "about how many orders do we have?", that's fine, and reading it is instant:

SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';

The wiki points to estimated counts as the much faster alternative. Show them with honest wording: "about 182,000".

For filtered counts, some teams use the planner's estimate from EXPLAIN for the query. It's rougher, but good enough for "lots" versus "a few".

Option 2: cap the count

People rarely need to know there are exactly 182,341 results; they need to know there are many. Count up to a limit instead:

SELECT count(*) FROM (SELECT 1 FROM orders WHERE status = 'pending' LIMIT 10001) t;

Show "10,000+" when the result hits the cap. Small counts stay exact; large ones stay fast.

Option 3: keep a counter

When you genuinely need exact, fast totals, such as a pending-orders badge, maintain them:

  • a small counters table updated by your application code or a trigger when rows are inserted, deleted or change status
  • or a periodically refreshed summary table for dashboards

Counters add write overhead and need care to stay correct, so use them for the few numbers that really matter.

Option 4: pagination without totals

"Previous / Next" pagination doesn't need a total at all, and pairs well with cursor-based pagination for large lists. For most admin screens, staff use filters and search to find things, not page 2,417. Dropping the total page count is often the simplest big win.

Option 5: cache it

Dashboard totals that can be a few minutes old can be cached in your app or a materialised summary refreshed on a schedule. Label them "updated 5 minutes ago" if precision matters to the reader.

Counts in your API

If your app offers an API, avoid returning an exact total with every list response by default. Clients of the API often ignore it, and it costs a count on every call. Offer a has_more flag or a next-page cursor instead, and provide totals only on request, ideally capped or estimated. Document the choice so integrators don't build features that depend on a precise number for millions of rows.

Reports are different

Monthly reports, invoices and anything finance or stock depends on need exact figures. That's fine: they run occasionally, can take a few seconds, and can be scheduled outside busy hours or run against a read replica. The problem is only exact counts on screens people load constantly. Separate "operational screens", which should be fast and approximate where acceptable, from "reports", which should be exact and can be slower.

Measure before and after

Use EXPLAIN ANALYZE on the count query to see whether it's scanning the whole table and how long it takes. After changing the approach, check the page's total response time, not just the query, since ORMs may run extra counts you didn't expect.

Keep statistics fresh

Estimates are only as good as the statistics. Autovacuum normally keeps them reasonably current. After bulk imports or deletions, run ANALYZE on the table so estimates catch up quickly.

A worked example

An illustrative Brisbane parts supplier's internal order system had grown to millions of order lines. The orders list took several seconds to load, and logs showed most of that time in a count query used for "Page 1 of N". The developer switched the list to next/previous pagination, showed "10,000+" for large filtered results, replaced the dashboard's total orders tile with an estimate labelled "about", and kept an exact counter only for the pending-orders badge. The list now loads quickly and staff haven't missed the total page count.

FAQ

Will an index make count(*) fast?

Sometimes for filtered counts, but not reliably for whole-table counts on busy tables.

Is MySQL different?

Other databases and storage engines have their own trade-offs; measure on your setup.

How wrong can estimates be?

Usually close after recent vacuum or analyse, but don't use them for billing or stock.

Should I count in the background?

For dashboards, yes: compute totals on a schedule and store them.

If your app slows down as tables grow, send us the slow pages and we'll help you find where counts and queries can be trimmed.

Sources

WhatsAppBook a call