Back to TIL
TIL

TIL - The Smallest Possible Row in Postgres

What is the smallest possible row in Postgres? How many bytes does it take to store a single row with no columns?

A brown hand pinching a tiny PostgreSQL elephant emblem between two fingers

While working on some PostgreSQL/PostGIS stuff at work, one thought led to another, and I found myself wondering what the smallest possible row in Postgres could be.


Take a minute to think about it before reading on. How small do you think it could be?


My initial guess was a boolean. After all, a boolean value only takes one byte, plus whatever overhead Postgres needs to store the row itself.

But then I remembered that Postgres has a bit type, which lets you represent a value using a single bit.

So maybe it's row(0::bit(1))? One bit of data, plus the overhead? Right?

But then I remembered reading somewhere that Postgres allows rows with no columns at all.

Wait, so could the smallest possible row just be an empty row? Zero bytes of actual data, with nothing but the overhead required to store it?

An empty row

Turns out, yes! Postgres allows you to construct a row with absolutely no columns using row().

We can use pg_column_size(), which returns the number of bytes used to store a value, to see how much space it occupies.

SQL
SELECT pg_column_size(row()) AS empty;

And the result:

text
 empty
-------
    24
(1 row)

24 bytes! For a row containing absolutely nothing.

Where are those 24 bytes coming from?

Well, Postgres needs to store some information about each row, even if the row itself has no columns. This information lives in something called a tuple header.

For an ordinary heap tuple, the header contains metadata such as transaction visibility information, tuple flags, and information needed to locate the actual column data.

The fixed portion of this header is 23 bytes. On a typical 64-bit PostgreSQL installation, it gets aligned to an 8-byte boundary, bringing it to 24 bytes.

So we have:

ComponentSize
Tuple header23 bytes
Alignment padding1 byte
Actual data0 bytes
Total24 bytes

And that's the minimum size of an ordinary PostgreSQL heap tuple on this installation.

But there's a catch.

It's actually 28 bytes?

The pg_column_size(row()) result tells us the size of the empty composite value. It doesn't tell us everything about the space required to store a row inside an actual table.

Postgres organizes table data into fixed-size pages, typically 8 KiB each.A PostgreSQL page is a fixed-size block that stores table or index data. It is the smallest unit PostgreSQL reads from or writes to disk. Each stored heap tuple also needs a line pointer, a 4-byte entry that identifies where the tuple is located within its page.

So let's create a table with no columns and insert a row into it.

SQL
CREATE TABLE tiny ();

INSERT INTO tiny DEFAULT VALUES;

Now, using PostgreSQL's pageinspect extension, we can look at the physical contents of the page.

SQL
CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT
    lp_len AS tuple_bytes,
    4 AS line_pointer_bytes,
    lp_len + 4 AS total_bytes
FROM heap_page_items(get_raw_page('tiny', 0))
WHERE lp_flags = 1;

Here's what I got:

text
 tuple_bytes | line_pointer_bytes | total_bytes
-------------+--------------------+------------
          24 |                  4 |          28
(1 row)

There it is. The tuple itself occupies 24 bytes, but storing it in a heap page requires another 4 bytes for its line pointer.

That makes 28 bytes per empty row, excluding overhead shared by all the rows on the page.

How many empty rows can fit in one page?

Now I was curious about something else.

If an empty row takes 28 bytes of page space, how many of them can we squeeze into a single PostgreSQL page?

A standard PostgreSQL page is 8,192 bytes, with 24 bytes reserved for the page header.

That leaves us with 8,168 bytes.

Divide that by 28:

text
floor((8192 - 24) / 28) = 291

So, theoretically, 291 empty rows should fit in a single page.

Let's find out.

SQL
TRUNCATE tiny;

INSERT INTO tiny
SELECT FROM generate_series(1, 291);

SELECT
    pg_relation_size('tiny') AS bytes,
    pg_relation_size('tiny') / 8192 AS pages;

Result:

text
 bytes | pages
-------+------
  8192 |     1
(1 row)

Exactly one page!

Now let's insert just one more row.

SQL
INSERT INTO tiny DEFAULT VALUES;

SELECT
    pg_relation_size('tiny') AS bytes,
    pg_relation_size('tiny') / 8192 AS pages;

And the result:

text
 bytes | pages
-------+------
 16384 |     2
(1 row)

Ha! Adding a single row containing absolutely no data caused the table to grow from 8 KiB to 16 KiB.

Of course, the second page isn't full. PostgreSQL simply had to allocate another page because there wasn't enough room for the 292nd row on the first one.

So, what's the smallest possible row?

24 bytes, if we're talking about the size of an ordinary PostgreSQL heap tuple on a typical 64-bit installation.

But there's a difference between the size of a tuple and the space needed to store it:

  • 24 bytes for the empty tuple itself.
  • 28 bytes including its line pointer on a heap page.
  • 8 KiB for the first allocated page of a table, even if it contains just one empty row.

PostgreSQL has other internal tuple representations, so 24 bytes isn't a universal minimum for every kind of row-like structure it uses.

Still, I find it fascinating that a row containing literally nothing needs 24 bytes just to exist.

And that 291 such rows can fit in a single page, but the 292nd needs another 8 KiB.

That's it.