pg_column_size(): What you see is not what you get

Thanks to my colleague Ozair, who sent me a JIRA ticket saying “I need to drop that huge column, what are the consequences?” My first question was: how huge? That’s when the rabbit hole opened.

It looks simple. It is simple. Just use the administrative function pg_column_size(). Until you have toasted attributes. Then it gets interesting.

A bit of history 🔗

pg_column_size() was added in PostgreSQL 8.1 by Mark Kirkwood (commit a9236028). The release notes say, simply:

Add pg_column_size() (Mark Kirkwood)

No drama. Just a useful administrative function that’s been quietly doing its job for twenty years.

Basic usage 🔗

The signature is straightforward:

pg_column_size(any) -> integer

It returns the number of bytes used to store a given value. Let’s start with the obvious cases.

Fixed-width types 🔗

select pg_column_size(1::smallint),
       pg_column_size(1::integer),
       pg_column_size(1::bigint);

 pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------
              2 |              4 |              8

No surprises. Fixed-width types always return their type size.

Variable-length types 🔗

create table t (id integer, content text);
insert into t values (1, 'hello');
insert into t values (2, repeat('x', 10));
insert into t values (3, repeat('x', 100));

select id, pg_column_size(content) from t;

 id | pg_column_size
----+----------------
  1 |              6
  2 |             11
  3 |            101

The size reflects the actual content length plus the varlena header. So far so good.

TOAST kicks in 🔗

When a value exceeds the TOAST¹ threshold, PostgreSQL first tries to compress it inline. If it fits after compression, it stays in the main tuple:

insert into t values (4, repeat('x', 10000));

select id, pg_column_size(content), length(content) from t where id = 4;

 id | pg_column_size | length
----+----------------+--------
  4 |            125 |  10000

125 bytes to store 10000 characters. The value is still in the main tuple, compressed. If compression is not enough to fit, PostgreSQL moves the value out-of-line to a separate TOAST table. What remains is a pointer, always exactly 18 bytes. I tried to demonstrate this with repetitive text data. PostgreSQL’s compression is good enough that I couldn’t trigger out-of-line storage that way. Which, come to think of it, is a feature.

The documentation problem 🔗

The official documentation says:

Shows the number of bytes used to store any individual data value. If applied directly to a table column, this reflects any compression that has been applied.

That last sentence is doing a lot of work. “Any compression that has been applied”. Fine. But what about toasted attributes? The documentation is silent.

This matters.

What the source code actually says 🔗

Let’s look at src/backend/utils/adt/varlena.c. For varlena types, pg_column_size() delegates to toast_datum_size():

/* varlena type, possibly toasted */
result = toast_datum_size(value);

And the comment on toast_datum_size() in src/backend/access/common/detoast.c is explicit:

return the physical storage size (possibly compressed) of a varlena datum

Physical storage size. Not logical size. Not uncompressed size.

For out-of-line toasted values, what’s left in the main tuple is a TOAST pointer, always exactly 18 bytes, regardless of how large the original value is.

Interpreting aggregate results 🔗

This is where the trap closes.

The real signal is the gap between physical and logical size:

select avg(pg_column_size(content)) as physical_avg,
       avg(length(content))         as logical_avg
from t;

A large gap means TOAST compression is active. A small gap means values are genuinely small or compression isn’t doing much.

One more thing: pg_column_size(NULL) returns NULL. It’s a regular function, not an aggregate. So rows where the column is NULL are silently excluded from avg(). Your average reflects only non-NULL rows. If your column has lots of NULLs, that average is less representative than it looks. Always check the non-NULL ratio alongside.

TABLESAMPLE on large tables 🔗

When you’re running pg_column_size() for diagnostic purposes on a large production table, don’t run it over the full table. Scanning millions of rows costs. Use TABLESAMPLE:

-- Bernoulli: random row-level sampling, ~1% of rows
select avg(pg_column_size(content))             as physical_avg,
       avg(length(content))                     as logical_avg,
       count(content)::float / count(*)         as non_null_ratio
from t tablesample bernoulli(1);

-- System: block-level sampling, faster, less random, ~0.1% of blocks
select avg(pg_column_size(content))             as physical_avg,
       avg(length(content))                     as logical_avg,
       count(content)::float / count(*)         as non_null_ratio
from t tablesample system(0.1);

BERNOULLI(1) gives you a proper statistical sample, each row has a 1% probability of being selected. SYSTEM(0.1) is faster because it samples at the block level, but the sample is less uniform. For a rough average, either works. For something you’d put in a report, use BERNOULLI.

The patch 🔗

The documentation should say this clearly. The current wording leaves anyone working with toasted columns in the dark.

A proposed clarification for the pg_column_size() entry in func.sgml:

For variable-length (varlena) types, this function returns the physical stored size. For values stored inline in the main tuple, this reflects the actual data size, including any TOAST compression applied. For out-of-line toasted values, this returns the size of the TOAST pointer (18 bytes), not the size of the original data. To obtain the logical size of a value, use length() or octet_length() depending on the type.

I plan to submit this as a documentation patch. If you’ve been bitten by this before, or have thoughts on the wording, I’d love feedback. Find me on the pgsql-hackers list or on Bluesky.