Performance

PostgreSQL tips

Total size of BLOBs

BLOBs are stored in the pg_largeobject in postgres. Each BLOB is divided into rows (pages) of 2048 bytes typically. More info ​here

Query below computes the space that all BLOBs take.

SELECT pg_size_pretty(count(loid) * 2048) FROM pg_largeobject;

Size of individual BLOBs

SELECT 	loid,
        pg_size_pretty(count(*) * 2048)
FROM pg_catalog.pg_largeobject
GROUP BY loid
ORDER BY count(*) DESC;

Size of tables

SELECT tablename,
       pg_size_pretty(size) AS size_pretty,
       pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *, pg_relation_size(schemaname||'.'||tablename) AS size,
                pg_total_relation_size(schemaname||'.'||tablename) AS total_size
      FROM pg_tables) AS TABLES
WHERE TABLES.schemaname = 'public'
ORDER BY total_size DESC;