Retrieving row count for all tables in PostgreSQL

Find the row count for all tables in PostgreSQL

In PostgreSQL, you can easily retrieve the row count for each table in a database. This can be particularly useful for database analysis, monitoring, or reporting. There are a couple of methods to accomplish this, using either SQL queries that interact with PostgreSQL’s system catalog or by querying the pg_stat_user_tables system view, which provides statistical information about tables.

Method 1: Using pg_stat_user_tables

The pg_stat_user_tables system view contains row counts for all tables that the current user has access to.

Syntax:

-- Retrieve row counts for all tables
SELECT relname AS table_name, n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY table_name;

Method 2: Using the pg_class Catalog Table with pg_namespace

You can also join pg_class and pg_namespace to find row counts, which is especially useful if you want to include specific schema names.

Syntax:

-- Get row counts from pg_class catalog table
SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       c.reltuples AS estimated_row_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY schema_name, table_name;

• reltuples provides an estimate of the row count for each table in PostgreSQL.

Examples and Code Explanation:

1. Using pg_stat_user_tables to Retrieve Exact Row Counts

Code:

-- Select table names and row counts from pg_stat_user_tables
SELECT relname AS table_name, -- Get the name of each table
       n_live_tup AS row_count -- Get the live (current) row count
FROM pg_stat_user_tables -- Query system view with table stats
ORDER BY table_name; -- Sort results by table name

Explanation:

• pg_stat_user_tables: Provides table statistics, including row counts for tables.
• n_live_tup: The number of live rows (current rows) in each table.

2. Using pg_class and pg_namespace for Schema-Specific Row Counts

Code:

-- Retrieve schema name, table name, and estimated row count
SELECT n.nspname AS schema_name, -- Schema where the table is located
       c.relname AS table_name, -- Table name
       c.reltuples AS estimated_row_count -- Estimated row count
FROM pg_class c -- Access catalog table for table info
JOIN pg_namespace n ON n.oid = c.relnamespace -- Join to get schema names
WHERE c.relkind = 'r' -- Filter only for regular tables
ORDER BY schema_name, table_name; -- Sort by schema and table

Explanation:

• pg_class: Catalog table containing metadata about each table, including estimated row counts.
• pg_namespace: Used here to include schema names with tables.

Important Notes:

• Estimated vs. Exact Counts: pg_stat_user_tables gives a more precise row count compared to pg_class which provides an estimate.
• Permissions: You need sufficient privileges to access these system views or catalog tables.
• Frequent Analysis: For very large tables, regular ANALYZE operations improve accuracy in pg_class statistics.