How to Check Table Bloat in PostgreSQL Quickly

To check table bloat in PostgreSQL, you can run a query that compares the actual size of a table to its estimated ideal size based on row count and average row length. Using pgstattuple extension or custom SQL queries helps identify bloat by showing wasted space in tables and indexes.

Syntax

Use a SQL query that calculates bloat by comparing the table's physical size with its estimated size based on live row data. The query typically uses system catalog tables like pg_class, pg_namespace, and functions like pg_relation_size().

Alternatively, the pgstattuple extension provides a function pgstattuple('table_name') to get detailed bloat statistics.

sql

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_relation_size(relid)) AS real_size,
  pg_size_pretty((relpages * 8192)) AS expected_size,
  ROUND((pg_relation_size(relid)::numeric / (relpages * 8192))::numeric, 2) AS ratio
FROM (
  SELECT
    c.oid AS relid,
    nspname AS schemaname,
    relname AS tablename,
    relpages
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE relkind = 'r'
) AS tables
ORDER BY ratio DESC
LIMIT 10;

Example

This example query shows how to find the top 10 tables with the highest bloat ratio by comparing actual size to expected size based on page count. It helps you spot tables that use more disk space than needed.

sql

WITH table_bloat AS (
  SELECT
    c.oid,
    nspname AS schema_name,
    relname AS table_name,
    relpages,
    pg_relation_size(c.oid) AS real_size,
    reltuples,
    (reltuples * 100) / (relpages * 8) AS tuple_density
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE relkind = 'r'
)
SELECT
  schema_name,
  table_name,
  pg_size_pretty(real_size) AS actual_size,
  relpages * 8192 AS expected_size_bytes,
  ROUND((real_size::numeric / (relpages * 8192))::numeric, 2) AS size_ratio
FROM table_bloat
ORDER BY size_ratio DESC
LIMIT 10;

Output

 schema_name | table_name | actual_size | expected_size_bytes | size_ratio 
-------------+------------+-------------+---------------------+------------
 public      | users      | 20 MB       | 10485760            | 1.91       
 public      | orders     | 15 MB       | 8388608             | 1.79       
 ...

Common Pitfalls

One common mistake is relying only on pg_relation_size() without considering the actual number of live rows, which can mislead about bloat. Another is not running VACUUM or ANALYZE regularly, which keeps statistics accurate.

Also, using outdated or overly complex bloat queries can cause confusion. Using the pgstattuple extension is simpler and more reliable.

sql

/* Wrong: Only checking size without row count */
SELECT relname, pg_relation_size(oid) FROM pg_class WHERE relkind = 'r';

/* Right: Use pgstattuple for detailed bloat info */
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('your_table_name');

Quick Reference

Method	Description	Usage
pg_relation_size()	Returns actual disk size of a table or index	SELECT pg_size_pretty(pg_relation_size('table_name'));
pgstattuple	Extension providing detailed bloat stats	CREATE EXTENSION pgstattuple; SELECT * FROM pgstattuple('table_name');
Custom SQL Queries	Calculate bloat by comparing size and row count	Use queries joining pg_class, pg_namespace, and pg_stat_user_tables
VACUUM and ANALYZE	Maintain accurate stats for bloat detection	Run VACUUM ANALYZE regularly on tables

Key Takeaways

Use pgstattuple extension for accurate table bloat statistics.

Compare actual table size with expected size based on row count to detect bloat.

Regularly run VACUUM and ANALYZE to keep statistics up to date.

Avoid relying solely on table size without considering live rows.

Custom SQL queries can help identify bloat but require understanding system catalogs.