postgreSQL Exploration

Knowing size of all the name spaces within the postgreSQL

SELECT 
    n.nspname AS schema_name, 
    pg_size_pretty(SUM(pg_total_relation_size(c.oid))) AS total_size
FROM 
    pg_class c
JOIN 
    pg_namespace n ON n.oid = c.relnamespace
WHERE 
    n.nspname NOT IN ('pg_catalog', 'information_schema')
    AND n.nspname NOT LIKE 'pg_toast%'
GROUP BY 
    n.nspname
ORDER BY 
    SUM(pg_total_relation_size(c.oid)) DESC;

Output:

 schema_name  | total_size 
---------------+------------
 sretoolkit    | 6xxx kB
...
...

---

If you table specific size

SELECT 
    c.relname AS table_name, 
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS index_and_toast_size
FROM 
    pg_class c
JOIN 
    pg_namespace n ON n.oid = c.relnamespace
WHERE 
    n.nspname = 'your_schema'
    AND c.relkind = 'r'  -- Only ordinary tables
ORDER BY 
    pg_total_relation_size(c.oid) DESC;   

SELECT 
    c.relname AS table_name, 
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS index_and_toast_size
FROM 
    pg_class c
JOIN 
    pg_namespace n ON n.oid = c.relnamespace
WHERE 
    n.nspname = 'sretoolkit'
    AND c.relkind = 'r'  -- Only ordinary tables
ORDER BY 
    pg_total_relation_size(c.oid) DESC;   

---

List Tables with Row Counts and Sizes

SELECT 
    c.relname AS table_name,
    c.reltuples::BIGINT AS estimated_rows,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS data_size
FROM 
    pg_class c
JOIN 
    pg_namespace n ON n.oid = c.relnamespace
WHERE 
    n.nspname = 'your_schema'
    AND c.relkind = 'r'
ORDER BY 
    pg_total_relation_size(c.oid) DESC;   

Finding Largest Indexes

SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM 
    pg_stat_user_indexes
ORDER BY 
    pg_relation_size(indexrelid) DESC
LIMIT 10;   

Detect Unused Indexes

SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM 
    pg_stat_user_indexes
JOIN 
    pg_index USING (indexrelid)
WHERE 
    idx_scan = 0
    AND NOT indisunique -- Exclude unique constraints
    AND NOT EXISTS ( -- Exclude indexes enforcing constraints
        SELECT 1 FROM pg_constraint WHERE conindid = indexrelid
    )
ORDER BY 
    pg_relation_size(indexrelid) DESC;   

Table Size Breakdown (Data vs. Index vs. TOAST)

SELECT 
    relname AS table_name,
    pg_size_pretty(pg_relation_size(oid)) AS data_size,
    pg_size_pretty(pg_indexes_size(oid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(oid) - pg_relation_size(oid) - pg_indexes_size(oid)) AS toast_size,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM 
    pg_class
WHERE 
    relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'your_schema')
    AND relkind = 'r'
ORDER BY 
    pg_total_relation_size(oid) DESC;   

Database Level Overview

SELECT 
    datname AS database_name, 
    pg_size_pretty(pg_database_size(datname)) AS size 
FROM 
    pg_database 
ORDER BY 
    pg_database_size(datname) DESC;   

---

What could be the cause of the index > data

1. Multiple Indexes

The most common cause is simply having multiple indexes on the same table. Each index stores a full copy of the indexed column(s) plus a pointer to every row.

Scenario: A table with small rows (e.g., integers and short strings) but 5–6 different indexes (e.g., on id, email, created_at, foreign keys) can easily have a combined index size that exceeds the table data size.

Verdict: This is normal and often a deliberate trade-off to speed up read queries at the cost of write performance and storage.

1. Index Bloat (MVCC Overhead)

PostgreSQL uses Multi-Version Concurrency Control (MVCC). When rows are updated or deleted, the old versions become "dead tuples."

Table Bloat: Regular VACUUM processes usually clean up dead tuples in the table efficiently.

Index Bloat: Indexes are not automatically shrunk by standard VACUUM. Dead entries remain in the index structure until a REINDEX or VACUUM FULL is performed. If a table has high update/delete churn, the index can become significantly bloated with pointers to non-existent rows.

Fix: Run REINDEX CONCURRENTLY index_name; to rebuild the index and reclaim space without locking writes.

1. Wide Index Keys

If you are indexing wide columns (e.g., long VARCHAR, TEXT, or JSONB fields), the index entry itself might be nearly as large as the row.

INCLUDE Columns: Using INCLUDE in an index adds non-key columns to the leaf nodes of the index. If these included columns are large, the index size can explode.

GiST/SP-GiST Indexes: Certain index types used for full-text search or geospatial data are inherently larger than standard B-Tree indexes.

How to Diagnose

Run this query to compare individual index sizes against their table size. An index_ratio significantly greater than 1.0 (or much higher than your baseline) suggests bloat or excessive indexing.

SELECT 
    n.nspname AS schema_name,
    c.relname AS table_name,
    i.relname AS index_name,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
    ROUND(100 * pg_relation_size(i.oid) / NULLIF(pg_relation_size(c.oid), 0), 2) AS index_ratio_percent
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(i.oid) DESC;