6

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 foto and 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

Running

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;

yields:

           relation            |  size
-------------------------------+---------
 pg_toast.pg_toast_17048       | 501 MB
 public.dpi                    | 5752 kB
 pg_toast.pg_toast_17048_index | 5752 kB
(3 rows)

Edit 2: Using 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.

Source: http://www.postgresql.org/docs/9.1/static/storage-toast.html

6

Postgresql does not have "blobs type". What happens is that it automatically slices "big columns" ( ie larger than 1 page, 8k ) into "toast" tables.

Look here for informations on disk usage on postgres

and here for postgresql admin function where you will learn that the functions of interest for you are: pg_table_size and pg_total_relation_size

Both take into account toast space. The first does not count index space, the second does.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.