Postgres determine size of all blobs

Question

hi i'm trying to find the size of all blobs. I always used this

SELECT sum(pg_column_size(pg_largeobject)) lob_size FROM pg_largeobject

but while my database is growing ~40GB this takes several hours and loads the cpu too much.

is there any more efficent way?

seems good, but why do they give different results? SELECT pg_table_size('pg_largeobject'), sum(pg_column_size(pg_largeobject)) lob_size FROM pg_largeobject 750MB/545MB — wutzebaer
– wutzebaer, Commented May 9, 2014 at 7:43
pg_table_size includes the padding between rows, the dead rows, row headers... that's the real size on disk. — Daniel Vérité
– Daniel Vérité, Commented May 9, 2014 at 8:53

Daniel Vérité · Accepted Answer · 2014-05-09 15:49:01Z

2

Some of the functions mentioned in Database Object Management Functions give an immediate result for the entire table.

I'd suggest pg_table_size(regclass) which is defined as:

Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map)

It differs from sum(pg_column_size(tablename)) FROM tablename because it counts entire pages, so that includes the padding between the rows, the dead rows (updated or deleted and not reused), and the row headers.

answered May 9, 2014 at 15:49

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres determine size of all blobs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related