Alternatives to the COUNT function

Introduction

COUNT is one of the aggregate functions used to aggregate and compute data
In other databases like MySQL, the total row count in a table is a pre-stored value that can be directly retrieved, but in PostgreSQL, to count data accurately, it must inspect every page/tuple to count only the live tuples returned to you
Differences

COUNT(*): counts all rows including NULL values
COUNT(column): excludes rows with NULL values

Visibility Map (VM)

This is a file containing a sequence of bit arrays for each page, containing the following information:

Bit 1: whether this page is all-visible (1 or 0)
Bit 2: whether this page is all-frozen (1 or 0)

How It Works

When using COUNT:

If there is no index, this is the worst-case scenario because PostgreSQL must use a sequential scan to load pages into RAM
- PostgreSQL uses a sequential scan and the visibility map to inspect each page, checking the all-visible bit to determine if dead tuples exist in that page
  - If all-visible=0, indicating the presence of dead tuples, it must access each row in the heap to verify and count only live tuples
  - If all-visible=1, indicating no dead tuples exist, it can directly accumulate the total row count of that page without inspecting individual rows
- Note that PostgreSQL still reads every page in the heap before verifying with the visibility map
  - This ensures sequential data access, which is faster
  - Compared to checking the visibility map first and then loading the corresponding page in the heap, which incurs random I/O and slower execution speed
If an index exists (typically a B-tree index), PostgreSQL attempts to identify the index with the smallest volume when multiple indexes exist on the table
- Because COUNT only performs row counting, having a B-tree index on any column is sufficient
- The planner analyzes your query to choose the optimal path between index scan or sequential scan
- If a suitable index is available, it checks each record in the index, identifies the page number using the tuple identifier (TID) and checks the all-visible bit in the visibility map
  - If all-visible=0, indicating dead tuples may exist, it uses the TID to load the page from the heap and inspect live tuples
  - If all-visible=1, indicating no dead tuples, it simply increments the record counter
As shown above, functions such as SUM, AVG, MIN, or MAX inherently require reading row data for computation, but COUNT, which only counts rows, also requires reading row data to return a result
This means that even if you only need the total row count of a table, PostgreSQL still loads all data into memory to evaluate rows and calculate the total

Here is a diagram summarizing the operational process when using the COUNT function:

Loop reading each Page in Heap (Page 1 -> 100,000)
                                      │
                                      ▼
                        1. Load Page X from disk into RAM
                                      │
                                      ▼
                  2. Quickly check the Bit representing Page X in VM
                                      │
               ┌──────────────────────┴──────────────────────┐
               │                                             │
      all-visible = 1                               all-visible = 0
               │                                             │
               ▼                                             ▼
  3a. Read Page Header in RAM                  3b. Iterate & check xmin/xmax 
   (Count total line pointers)                    for each row (Heap Tuples)
               │                                             │
               └──────────────────────┬──────────────────────┘
                                      │
                                      ▼
                           4. Add to counter variable
                                      │
                                      ▼
                    5. Evict Page X from RAM (release Buffer Ring)

Characteristics

PostgreSQL divides tables into 8 KB data pages and uses chunking to read pages from disk into a specialized memory buffer called the buffer ring, which typically occupies 256 KB to a few megabytes in RAM
Thanks to this intelligent RAM management mechanism (ring buffer), system RAM usage remains safe when executing COUNT, preventing issues like out-of-memory errors
However, running COUNT acquires an AccessShareLock (the lightest read share lock), allowing SELECT, INSERT, UPDATE and DELETE operations from other users to run concurrently without being blocked
Although it does not cause data locking issues, executing COUNT on large datasets can lead to resource starvation
- Disk I/O bottleneck: Continuous disk reads consume significant disk bandwidth, causing latency for other queries reading from disk
- CPU contention: When PostgreSQL enables parallel query execution (distributing the COUNT query across multiple parallel worker threads), CPU utilization increases significantly
- DDL blocking: It blocks DDL statements that alter table structures, such as ALTER TABLE, DROP TABLE, TRUNCATE, or VACUUM FULL, because these operations require an AccessExclusiveLock

Solutions

As demonstrated, retrieving a row count involves significant underlying processing. If you do not require an exact real-time row count continuously, consider alternative approaches that trade exact precision for higher performance:

Using reltuples from pg_class

pg_class is a system catalog table in PostgreSQL storing metadata for all tables, indexes, views and sequences
reltuples is a column in the pg_class table that stores the estimated row count for a given table
PostgreSQL does not update reltuples on every INSERT, UPDATE, or DELETE operation. This figure is updated only when ANALYZE or VACUUM (including auto-vacuum/auto-analyze) runs on the table
The primary purpose of reltuples is not for direct end-user display, but to provide statistical data for the query planner to estimate query costs and select an appropriate execution plan
Advantages: Extremely fast query execution because it reads a single row from system metadata without scanning the heap
Disadvantages: Less accurate than COUNT. If data has recently changed and ANALYZE has not run yet, reltuples returns stale statistics

Counter Table and Triggers

Instead of rescanning the entire table, you can create a dedicated table to track the total row count
Attach triggers to the primary table to update the counter automatically on data modifications:
- Increment the count on INSERT
- Decrement the count on DELETE
Querying total rows simply requires selecting from the counter table
Advantages: Retrieves accurate real-time counts
Disadvantages:
- Write operations like INSERT or DELETE on the primary table incur slight latency while waiting for the trigger to update the counter table
- High-concurrency transactions can lead to lock contention and write conflicts

Using Materialized Views

A materialized view persists query results directly to disk, functioning similarly to a physical table
Retrieving row counts requires reading directly from the materialized view
The row count does not update in real time. You must schedule a cron job or background process to refresh the view periodically (for example, every 5 minutes or nightly)
Advantages: Eliminates write overhead for end-user INSERT, UPDATE, or DELETE operations on the primary table
Disadvantages: Data is near real-time, accurate only as of the last REFRESH execution

Detail

First, create the table and populate test data as follows:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2) NOT NULL,
    notes TEXT 
);

INSERT INTO orders (customer_name, order_date, total_amount, notes)
SELECT 
    'Customer_' || gs AS customer_name,
    NOW() - (random() * INTERVAL '30 days') AS order_date,
    ROUND((random() * 500 + 10)::numeric, 2) AS total_amount,
    CASE 
        WHEN random() > 0.5 THEN 'Note info - ' || gs 
        ELSE NULL 
    END AS notes
FROM generate_series(1, 1000) AS gs;

The data structure will look like this:

select * from orders

-- Query 1
select count(*) from orders

-- Query 2
select count(notes) from orders

-- Query 3
select count(*) from orders where order_id = 1

Query 1: Uses COUNT(*) to count all rows including NULL values
Query 2: Uses COUNT(notes) to count only non-NULL values
Query 3: Uses an efficient index-only scan

To inspect the visibility map file:

CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT 
    blkno, 
    all_visible, 
    all_frozen
FROM pg_visibility_map('orders');

VACUUM orders;

Enable the pg_visibility extension before running the query
The default value of all_visible after inserting data is false. It updates only after running VACUUM to verify dead tuples inside:

true indicates all rows are live tuples
false indicates dead tuples may exist, requiring heap access for verification

Using reltuples from pg_class

-- Query 1
SELECT reltuples::bigint AS estimated_row_count
FROM pg_class
WHERE relname = 'orders';

-- Query 2
SELECT c.reltuples::bigint AS estimated_row_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' 
  AND c.relname = 'orders';

Query 1: Retrieves reltuples metadata for the orders table using the default public schema
Query 2: Allows specifying a custom schema to retrieve table metadata if located outside the public schema

If new rows are inserted before ANALYZE runs, the statistical row count will deviate from COUNT.

Counter Table and Triggers

Create the table_stats table to persist total row counts for target tables:

CREATE TABLE table_stats (
    table_name VARCHAR(50) PRIMARY KEY,
    total_rows BIGINT NOT NULL DEFAULT 0
);

INSERT INTO table_stats (table_name, total_rows)
VALUES ('orders', (SELECT COUNT(*) FROM orders))
ON CONFLICT (table_name) DO NOTHING;

select * from table_stats

Next, define the function and triggers:

CREATE OR REPLACE FUNCTION update_order_count_batch()
RETURNS TRIGGER AS $$
DECLARE
    delta BIGINT := 0;
BEGIN
    IF (TG_OP = 'INSERT') THEN
        SELECT COUNT(*) INTO delta FROM new_table;
        
        UPDATE table_stats 
        SET total_rows = total_rows + delta 
        WHERE table_name = 'orders';
        
    ELSIF (TG_OP = 'DELETE') THEN
        SELECT COUNT(*) INTO delta FROM old_table;
        
        UPDATE table_stats 
        SET total_rows = total_rows - delta 
        WHERE table_name = 'orders';
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_insert_batch
AFTER INSERT ON orders
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION update_order_count_batch();

CREATE TRIGGER trg_orders_delete_batch
AFTER DELETE ON orders
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE FUNCTION update_order_count_batch();

CREATE TRIGGER trg_orders_truncate
AFTER TRUNCATE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION update_order_count_batch();

update_order_count_batch is the function handling data modifications, automatically adjusting the total row count
Trigger trg_orders_insert_batch executes automatically on INSERT operations. Using FOR EACH STATEMENT ensures that bulk insertions run the trigger only once to update the total count
Trigger trg_orders_delete_batch executes automatically on DELETE operations
Trigger trg_orders_truncate executes automatically on TRUNCATE operations

Perform INSERT or DELETE queries on the orders table to verify trigger functionality.

Using Materialized Views

CREATE MATERIALIZED VIEW mv_table_stats AS
SELECT 
    'orders'::text AS table_name,
    COUNT(*) AS total_rows
FROM orders;

CREATE UNIQUE INDEX idx_mv_table_stats_name ON mv_table_stats(table_name);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_table_stats;

SELECT * FROM mv_table_stats;

The code above initializes the materialized view
Creating a unique index is mandatory to enable REFRESH CONCURRENTLY. The row count updates only upon executing the refresh command