I'm using 9.1 and have a table called
dpi in a database with UTF-8 encoding currently sitting at 18,628 rows. There's a
TEXT column in there called
foto that holds B64 strings which represent a unique image file.
I'm trying to figure out how much disk space one row takes. There are 18050 rows with a non
NULL value for
length() for all of them is 87384.
What I don't get is this. Running
select * from pgstattuple('dpi');returns:
-[ RECORD 1 ]------+-------- table_len | 5890048 tuple_count | 18628 tuple_len | 5656063 tuple_percent | 96.03 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 92752 free_percent | 1.57
select pg_size_pretty(pg_relation_size(c.oid)) AS "size" from pg_class c where relname = 'dpi';
I get the following:
size --------- 5752 kB (1 row)
What exactly is going on here? Why is this table so small?
Edit: Also, I checked
/var/lib/postgresql/9.1/main and it measures 574.5MiB. Running:
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 3;
relation | size -------------------------------+--------- pg_toast.pg_toast_17048 | 501 MB public.dpi | 5752 kB pg_toast.pg_toast_17048_index | 5752 kB (3 rows)
select pg_total_relation_size('dpi'); I get
539262976 bytes. Does that mean I can divide that by 18628 and that'll give me an average size per row (28 KiB)? Does that size per row change over time (because of indexes or similar)?
Even so, 28 KiB per row seems exceptionally small. One
foto value alone should be 85 KiB (assuming only 1-byte characters).
The answer is the PostgreSQL TOAST mechanism by default will not only break up very large columns, depending on the data-type it actually attempts to compress them first. Pretty neat.