CODEWITHSUNDEEP
postgresql
Thomas Kappler
Thomas Kappler

Reputation: 4115

Does pg_database_size include BLOBs in pg_largeobject?

If I have BLOBs in a Postgres database, referenced by an oid type column, will their size be counted in pg_database_size?

Upvotes: 0

Views: 989

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324325

Yes it does, because they're in the pg_largeobject system catalog. That catalog is not shared across DBs, so it's counted toward the DB it's in.

The easiest way to find out would've been to lo_import a file and see; in this case a 10MB file of zeroes created with dd if=/dev/zero of=bigzero count=10 bs=1M:

regress=> SELECT pg_database_size(current_database());
 pg_database_size 
------------------
        730961016
(1 row)

regress=> \lo_import bigzero
lo_import 383810
regress=> SELECT pg_database_size(current_database());
 pg_database_size 
------------------
        731518072
(1 row)

You'll notice the DB has indeed grown. It's only grown by 500kb though. The reason for that is that PostgreSQL will compress this sort of data, and long runs of zeroes compress remarkably well.

Upvotes: 2

Related Questions