I have a very strange misreporting of the size of one specific database.
According to \l+
in psql
the DB size is 292 MB.
Doing a sum over the sizes reported by the following statement also reports very close to 292 MB.
SELECT
table_name,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes
Yet a pg_dump
of this database produces a sql file of 2.02 GB
I suspect that both counting methods skip LOBs since there are two tables in this database that contain TEXT
fields with largish content (up to ~4MB per row).
Is there a way to include LOBs in the size calculation?
EDIT:
It gets even stranger:
This query:
SELECT SUM(LENGTH(text_column))
FROM some_table
Gives a result of 2,091,245,318 (i.e. 2.02 GiB) which is about the size of the dump.
Best Answer
Note that unless your application is going out of its way to use the large objects interface, then it is not storing those columns as LOBs. Normal columns can store up to 1gb using TOAST (The Oversized-Attribute Storage Technique).
In addition to storing the data out of line, it may be stored compressed. Although you don't say so I assume the dump you are composing with is uncompressed, which could account for most of the size difference.