Module 8: Anti-Patterns and Final Project

Anti-pattern: slow `COUNT(*)`

Next →

SELECT COUNT(*) FROM orders; looks like the simplest possible operation. In Excel it would be instant. In MySQL with MyISAM it's pre-computed metadata. But in PostgreSQL, COUNT(*) traverses the whole table — always. On a 10-million-row table, that's several seconds. On a 100-million-row table, tens of seconds.

The problem is structural, not a bug. PostgreSQL uses MVCC (capsule 5 of module 7) — multiple versions of each row coexist temporarily. To know the exact count at this moment, it has to check the visibility of each tuple. There's no pre-computed metadata that avoids it.

In this capsule you're going to understand why COUNT(*) is inherently expensive, and the three alternatives depending on the acceptable trade-offs: estimation with pg_class.reltuples (instant but imprecise), a materialized view with refresh (precise but with a delay), an incremental counter with triggers (precise and real-time but more complex). And you're going to learn to choose among them depending on the business context.

The problem in code

Reproduce it with the table from the previous capsule (1M rows):

EXPLAIN ANALYZE SELECT COUNT(*) FROM pagination_demo;

Typical output:

Aggregate  (cost=18432.00..18432.01 rows=1 width=8)
           (actual time=423.34..423.35 rows=1 loops=1)
  ->  Seq Scan on pagination_demo
      (cost=0.00..15932.00 rows=1000000 width=0)
      (actual time=0.012..289.45 rows=1000000 loops=1)

The Seq Scan reads 1M rows. Time: ~423ms. For a 10M-row table, multiply by 10. For 100M, by 100.

And this case is benign — there are no filters. The most common anti-pattern is:

# Endpoint that does both: pagination + COUNT(*)
@router.get("/orders")
async def list_orders(page: int = 1, page_size: int = 20):
    items = await db.execute(
        select(Order).order_by(Order.id).limit(page_size).offset((page-1) * page_size)
    )
    total = await db.scalar(select(func.count(Order.id)))  # <-- the problem
    return {"items": items, "total": total}

Every paginated request spends the cost of the COUNT. If your endpoint receives 1000 requests/min and each COUNT takes 400ms, you're spending 400 seconds of DB time per minute just on COUNTs. Catastrophic.

Why COUNT(*) is expensive in PostgreSQL

PostgreSQL doesn't keep a pre-computed counter of live rows. Reason: MVCC. At any given moment:

  • There are live tuples (visible to you).
  • There are dead tuples but still referenceable by older transactions.
  • There are tuples inserted in transactions not yet committed.

The "real" count for your transaction depends on your visibility timestamp. PostgreSQL has to traverse the table and apply the visibility logic per row to get the correct count.

Some optimizations exist:

  • Index-only scan on COUNT(*): if you have a covering index and the visibility map is up to date, PostgreSQL can count using only the index. Faster but still O(n).
  • Parallel query: PostgreSQL 9.6+ parallelizes the Seq Scan across multiple workers. It reduces wall-clock time but not total cost.

None reach O(1). If your table has 100M rows, an exact COUNT(*) is going to take seconds.

Alternative 1: estimation with pg_class.reltuples

pg_class.reltuples is the estimate PostgreSQL keeps for the planner (you saw it in capsule 02 of module 7). It's instant (a catalog read, not a table read) but approximate (~5-10% typical error).

SELECT reltuples::BIGINT AS approx_count
FROM pg_class
WHERE relname = 'orders';

Advantages:

  • Instant (~1ms even on billion-row tables).
  • Doesn't load the database.
  • Enough for "show 'approximately 1.2M orders'".

Disadvantages:

  • Imprecise: ±5-10% typical, up to 20% on tables with recent bulk loads without ANALYZE.
  • Doesn't support filters: pg_class.reltuples is the table total, not a subset.

When to use:

  • The UI shows "approx 1.2M orders" or "more than 1M orders." The exact value adds nothing.
  • An analytical case where perfect precision doesn't justify the cost.
  • A total to show the user when they care about the magnitude, not the exact number.

Implementation in FastAPI:

@router.get("/orders/approx-count")
async def get_approx_count(db: AsyncSession = Depends(get_db)):
    result = await db.execute(text("""
        SELECT reltuples::BIGINT AS approx_count
        FROM pg_class
        WHERE relname = 'orders'
    """))
    count = result.scalar()
    return {"approx_count": count}

With filters: use statistics to estimate selectivity and multiply:

-- Approximate COUNT(*) WHERE status = 'pending'
SELECT
    (SELECT reltuples FROM pg_class WHERE relname = 'orders')::BIGINT *
    (SELECT (most_common_freqs::FLOAT[])[array_position(most_common_vals::TEXT::TEXT[], 'pending')]
     FROM pg_stats WHERE tablename = 'orders' AND attname = 'status') AS approx_filtered_count;

This is very approximate and hacky. In practice, if you need a COUNT with precise filters, alternatives 2 or 3 are better.

Alternative 2: materialized view with periodic refresh

For exact counts but tolerating some latency, a materialized view pre-computes the result and refreshes every N minutes.

-- Create the materialized view
CREATE MATERIALIZED VIEW orders_stats AS
SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending_orders,
    COUNT(*) FILTER (WHERE status = 'shipped') AS shipped_orders,
    SUM(amount) AS total_amount,
    NOW() AS last_refresh
FROM orders;

-- Create an index for queries on the view (optional)
CREATE UNIQUE INDEX ON orders_stats ((1));

Queries:

SELECT total_orders FROM orders_stats;
-- Instant: the view stores the pre-computed result.

Refresh:

-- Manual
REFRESH MATERIALIZED VIEW orders_stats;

-- Or concurrent (doesn't lock reads, requires a unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY orders_stats;

Advantages:

  • Precise as of the last refresh.
  • Instant reads.
  • Supports filters and complex aggregations.
  • REFRESH ... CONCURRENTLY doesn't lock read queries.

Disadvantages:

  • There's a delay between changes and the view being updated.
  • The refresh is still a Seq Scan (expensive). On giant tables the refresh can take minutes.
  • Requires infrastructure to run the refresh (cron, pg_cron, lambda, etc.).

When to use:

  • Stats that tolerate a delay (5min, 1h, 1 day). "Total orders in the last month" doesn't need to be real-time.
  • Dashboards and administrative reports.
  • KPI metrics shown in aggregate.

Refresh with cron (system):

# /etc/cron.d/refresh_stats
*/5 * * * * postgres psql -d production -c "REFRESH MATERIALIZED VIEW CONCURRENTLY orders_stats;"

Refresh with pg_cron (extension, better option):

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
    'refresh-orders-stats',
    '*/5 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY orders_stats;'
);

pg_cron keeps the jobs in the database itself. If you do a failover, they replicate with the database. Recommended over system cron.

Endpoint:

@router.get("/orders/stats")
async def get_stats(db: AsyncSession = Depends(get_db)):
    result = await db.execute(text("SELECT * FROM orders_stats"))
    row = result.mappings().first()
    return dict(row)

Alternative 3: incremental counter with triggers

For exact real-time counts, keep a counter in a separate table that's updated via triggers.

-- Counters table
CREATE TABLE counters (
    name TEXT PRIMARY KEY,
    value BIGINT NOT NULL DEFAULT 0
);

INSERT INTO counters (name, value)
VALUES ('orders_total', (SELECT COUNT(*) FROM orders));

-- Trigger function
CREATE OR REPLACE FUNCTION update_orders_counter() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE counters SET value = value + 1 WHERE name = 'orders_total';
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE counters SET value = value - 1 WHERE name = 'orders_total';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Trigger
CREATE TRIGGER trigger_orders_counter
AFTER INSERT OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_orders_counter();

Query:

SELECT value FROM counters WHERE name = 'orders_total';
-- Instant: a PK lookup

Advantages:

  • Precise in real-time (no delay).
  • Instant reads.
  • Supports filters (with one counter per common filter).

Disadvantages:

  • Each INSERT/DELETE on the table originates an UPDATE on counters. Write overhead.
  • Massive contention if there's high INSERT throughput: all the INSERTs try to UPDATE the same row in counters → severe lock contention.
  • More complex to maintain (triggers, edge cases in migrations).

When to use:

  • Counters queried very frequently.
  • Tables with moderate INSERT throughput (not 1000s per second).
  • When the materialized view's delay isn't acceptable.

Mitigate contention with sharded counters:

If you have high throughput, instead of a centralized counter, use N shards:

CREATE TABLE counters_sharded (
    name TEXT NOT NULL,
    shard INTEGER NOT NULL,
    value BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (name, shard)
);

-- 16 shards, distributed randomly
INSERT INTO counters_sharded (name, shard, value)
SELECT 'orders_total', s, 0 FROM generate_series(0, 15) s;

The trigger function updates a random shard:

CREATE OR REPLACE FUNCTION update_orders_counter_sharded() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE counters_sharded
        SET value = value + 1
        WHERE name = 'orders_total' AND shard = (random() * 15)::INT;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE counters_sharded
        SET value = value - 1
        WHERE name = 'orders_total' AND shard = (random() * 15)::INT;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

A read aggregates the shards:

SELECT SUM(value) FROM counters_sharded WHERE name = 'orders_total';

Distribution across 16 shards reduces contention 16x. Trade-off: the read is an Index Scan + aggregation instead of a PK lookup, but over 16 rows that's <1ms.

Comparison of the three alternatives

Criterionpg_class.reltuplesMaterialized viewIncremental counter
Read latency<1ms<1ms<1ms
Precision±5-10%Exact as of the refreshExact real-time
Update latencyDepends on autovacuum/ANALYZEN minutes (refresh interval)Real-time
Overhead on writes00Yes (UPDATE counters)
Supports dynamic filtersHackyYes (pre-defined filters)Yes (a counter per filter)
Operational complexityLowMedium (cron)High (triggers, migrations)
Compatible with migrationsTrivialWatch the refresh post-migrationWatch the triggers in migrations

Typical decision:

  • Show "1.2M orders" in the UI: pg_class.reltuples.
  • Dashboard with hourly stats: a materialized view with a refresh every 5min.
  • Real-time counter for an individual user (my_unread_messages): an incremental counter.
  • High-throughput counter (total_pageviews): a sharded counter.
  • {"items":[...], "total":N} on every response: Remove it from the response. Cursor pagination doesn't need the total. If the frontend asks for it, consider whether it really needs it or it's just habit.

The zero option: you don't need the COUNT

Sometimes the best refactor is to eliminate the COUNT from the response.

Typical OFFSET pagination pattern:

return {
    "items": [...],
    "total": COUNT(*),  # ← expensive
    "page": N,
    "page_size": 20,
}

With cursor pagination (capsule 02), the response doesn't need total:

return {
    "items": [...],
    "next_cursor": "...",  # ← enough
}

The frontend shows "Load more" or uses infinite scroll. It doesn't need "page X of Y."

And if the UX requires showing a total, consider:

  • Is it OK to show "1.2M+" (an estimate)?
  • Is it OK to show the total with a delay (a materialized view updated every 5min)?
  • Does the user really need the exact total on every response?

The answer to the "COUNT anti-pattern" is often not choosing among the three alternatives — it's eliminating the COUNT from the flow.

Traps and common mistakes

1. Assuming that COUNT(1) is faster than COUNT(*).

A classic MySQL myth. In PostgreSQL, the planner is smart enough to treat COUNT(*) and COUNT(1) identically. There's no difference.

2. COUNT(DISTINCT col) when you could do a GROUP BY with an index.

COUNT(DISTINCT col) always does a Seq Scan + sort. For large distinct counts, consider HyperLogLog (the postgresql-hll extension) — approximate but O(1) in memory.

3. A materialized view without a unique index — REFRESH can't be CONCURRENT.

If you want REFRESH ... CONCURRENTLY (without locking readers), you need at least one unique index on the view. If the view is a single row, add CREATE UNIQUE INDEX ON view ((1));.

4. An incremental counter without handling an UPDATE of the column that affects the counter.

If you have a pending_orders counter, you have to handle INSERT, DELETE, and UPDATE of status. The trigger has to check OLD.status vs NEW.status and adjust accordingly.

-- Correct handling of UPDATE
IF TG_OP = 'UPDATE' THEN
    IF OLD.status = 'pending' AND NEW.status != 'pending' THEN
        UPDATE counters SET value = value - 1 WHERE name = 'pending_orders';
    ELSIF OLD.status != 'pending' AND NEW.status = 'pending' THEN
        UPDATE counters SET value = value + 1 WHERE name = 'pending_orders';
    END IF;
END IF;

5. Triggers under high concurrency without sharding.

A centralized counter with 1000 INSERTs/sec on the source table → 1000 UPDATEs/sec on the counter row. Serious lock contention. Sharded counters mitigate.

6. Refreshing a materialized view during peak hours.

REFRESH MATERIALIZED VIEW (without CONCURRENTLY) locks readers. CONCURRENTLY is online but still expensive. Schedule it for low-activity hours.

7. Trusting pg_class.reltuples right after a bulk load.

reltuples is only updated with ANALYZE (manual or auto). After a bulk load without ANALYZE, reltuples has the old value. Always apply ANALYZE after bulk loads.

Exercise: three implementations of stats

Setup: an orders table with 1M rows (you can reuse pagination_demo from the module).

Step 1: create the table with data.

DROP TABLE IF EXISTS test_orders;
CREATE TABLE test_orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    amount NUMERIC(10, 2),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO test_orders (customer_id, status, amount)
SELECT
    (random() * 10000)::INT + 1,
    (ARRAY['pending', 'shipped', 'delivered', 'cancelled'])[floor(random() * 4)::INT + 1],
    (random() * 1000)::NUMERIC(10, 2)
FROM generate_series(1, 1000000);

ANALYZE test_orders;

Step 2: measure the COUNT(*) baseline.

\timing on
SELECT COUNT(*) FROM test_orders;
SELECT COUNT(*) FROM test_orders WHERE status = 'pending';
\timing off

Step 3: implement alternative 1 (estimation with reltuples).

SELECT reltuples::BIGINT AS approx_count FROM pg_class WHERE relname = 'test_orders';

Compare with the real count.

Step 4: implement alternative 2 (materialized view).

CREATE MATERIALIZED VIEW test_orders_stats AS
SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending,
    COUNT(*) FILTER (WHERE status = 'shipped') AS shipped,
    NOW() AS last_refresh
FROM test_orders;

CREATE UNIQUE INDEX ON test_orders_stats ((1));

Measure the read time:

\timing on
SELECT * FROM test_orders_stats;
\timing off

Step 5: implement alternative 3 (incremental counter).

CREATE TABLE test_counters (name TEXT PRIMARY KEY, value BIGINT NOT NULL DEFAULT 0);
INSERT INTO test_counters VALUES ('orders_total', (SELECT COUNT(*) FROM test_orders));

CREATE OR REPLACE FUNCTION update_test_counter() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE test_counters SET value = value + 1 WHERE name = 'orders_total';
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE test_counters SET value = value - 1 WHERE name = 'orders_total';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_test_counter AFTER INSERT OR DELETE ON test_orders
FOR EACH ROW EXECUTE FUNCTION update_test_counter();

Measure the overhead of an INSERT with and without the trigger:

\timing on

-- Before (without trigger): drop the trigger
DROP TRIGGER trg_test_counter ON test_orders;
INSERT INTO test_orders (customer_id, status, amount) VALUES (1, 'pending', 10.00);

-- After (with trigger): recreate it
CREATE TRIGGER trg_test_counter AFTER INSERT OR DELETE ON test_orders
FOR EACH ROW EXECUTE FUNCTION update_test_counter();
INSERT INTO test_orders (customer_id, status, amount) VALUES (2, 'pending', 20.00);

\timing off

Step 6: decide which alternative you'd use for three business cases:

a) A dashboard header that shows "Total orders: X." Accepts ±5% error. b) An hourly KPI in an administrative dashboard. c) A user notification "You have X pending orders" on every login.

Justify each choice.

See solution and discussion

Step 2 — baseline:

  • COUNT(*) total: ~400ms on 1M rows.
  • COUNT(*) WHERE status = 'pending': similar, ~400ms (Seq Scan + filter).

Step 3 — estimation:

  • reltuples: <1ms.
  • Precision: typically 99-100% on a recently analyzed table.

Step 4 — materialized view:

  • SELECT * FROM test_orders_stats: <1ms.
  • REFRESH MATERIALIZED VIEW: ~400-600ms (the same cost as the underlying COUNT).

Step 5 — incremental counter:

  • INSERT without trigger: ~0.5ms.
  • INSERT with trigger: ~1.0ms (100% overhead on a trivial operation, but small in absolute terms).
  • Reading the counter: <0.5ms.

Step 6 — decisions:

a) Header with ±5% tolerance: pg_class.reltuples. Zero overhead, precise enough. A single trivial query.

b) Hourly KPI in a dashboard: a materialized view with a refresh every hour. Exact data, instant read, a refresh on a schedule consistent with the dashboard's frequency.

c) User notification: an incremental counter. It needs real-time (the user expects the number to be exact at the moment of login). The overhead of each INSERT is acceptable.

Key lesson: the three alternatives have legitimate applications. The senior skill is choosing based on business trade-offs (tolerable latency, needed precision, write throughput). There's no universal "right answer."

Summary and next step

What you learned:

  • COUNT(*) in PostgreSQL is always O(n): MVCC requires traversing the table to check visibility. There's no pre-computed metadata.
  • Three alternatives with different trade-offs:
    • pg_class.reltuples: instant, ±5-10% error, zero overhead. For "approx X".
    • Materialized view: instant, exact as of the last refresh, requires cron. For dashboards.
    • Incremental counter: instant, exact real-time, overhead on writes. For critical counters.
  • Sharded counters mitigate contention under high throughput.
  • The best option is often to eliminate the COUNT: cursor pagination doesn't need it in the response.
  • The classic myths (COUNT(1) faster than COUNT(*), EXISTS better than COUNT > 0) don't apply in modern PostgreSQL.

Before moving on, you should be able to:

  • Recognize an endpoint with COUNT(*) on every response.
  • Decide which of the three alternatives applies to the case.
  • Implement the three in SQLAlchemy 2.0.
  • Question whether the frontend really needs the total or whether the UX can change.

In the next capsule you go to the third most common anti-pattern: over-indexing. Each index slows down writes, occupies disk space, and adds mental complexity. Creating indexes "just in case" is one of the most expensive junior habits. You're going to see the real cost with benchmarks (10x slower INSERT with 10 indexes vs 0), learn to audit usage with pg_stat_user_indexes, and develop judgment to decide which ones to keep and which to remove.

Resources

  1. PostgreSQL Wiki — Slow Counting — the official reference on the problem.
  2. Citus Data — Faster PostgreSQL counting — deep analysis with all the alternatives.
  3. Heap — Speeding up COUNT(*) — a real case of using reltuples.
  4. Brandur Leach — Materialized views — materialized views in production.
  5. pg_cron documentation — extension for cron-style jobs.
  6. PostgreSQL HyperLogLog (postgresql-hll) — approximate distinct counts.
  7. GitLab — Count caching — a real case of migrating to counters.

Capsule 03 of 08 — Module 8 — Database Performance & Query Tuning Guide

← PreviousAnti-pattern: large OFFSETNext →Anti-pattern: Over-indexing