CODEWITHSUNDEEP
postgresqlblob
wutzebaer
wutzebaer

Reputation: 14865

Postgres determine size of all blobs

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?

Upvotes: 1

Views: 2981

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61526

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.

Upvotes: 2

Related Questions