How can I find out how big a large TEXT field is in Postgres?

Question

I'm storing some large XML documents in TEXT fields in Postgres and I'm trying to find out how efficiently TOAST is compressing them. I've got a 2.2mb XML doc that is able to be zipped down to 51kb so I want to understand how close the compression ratio of TOAST can match it to make a final decision on how I'll be archiving these documents over time.

Is there a function in Postgres that will allow me to identify the TOAST compressed size of a specific column and row like this?

Answer 1

You want pg_column_size for TOASTed size, octet_length for untoasted size. pg_column_size is in the system administration functions section of the documentation. See the docs and this question for more details.

Example:

craig=> CREATE TABLE toastdemo(x text);
CREATE TABLE
craig=> insert into toastdemo(x) select * from  repeat('abcdef',1000);
INSERT 0 1
craig=> select pg_column_size(x), pg_column_size(repeat('abcdef',1000)) FROM toastdemo;
 pg_column_size | pg_column_size 
----------------+----------------
             84 |           6004
(1 row)