SELECT count(*) FROM some_big_table is a full scan. On a 200 GB table that takes minutes and hammers the buffer pool. Most of the time when you ask for a row count, you actually want “roughly how many rows are in this table right now,” and the planner already has that number.

-- Fast, approximate, updated by ANALYZE and autovacuum.
SELECT
    c.relname AS table,
    n.nspname AS schema,
    c.reltuples::bigint AS estimated_rows,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY c.reltuples DESC;

The number comes from the last ANALYZE. For active tables autovacuum re-runs ANALYZE often, so the estimate is usually within a few percent. For tables that see big bulk loads, run ANALYZE <table> yourself right after the load.

If you need better accuracy for one specific query, sampling is cheap:

SELECT count(*) * (100.0 / 1) AS approx_rows
FROM big_table TABLESAMPLE SYSTEM (1);

That takes a 1% sample and scales up. TABLESAMPLE SYSTEM is block-based and very fast; TABLESAMPLE BERNOULLI samples row by row and is more accurate but slower.

See also /posts/postgres-bitmap-heap-scan-surprise/.