In PostgreSQL, what’s the fastest way to search for a single record?

If you said primary key or some field with an index, there’s a much faster way.

Rows on disc

Each row is stored in a fixed sized page, and each page contains slots for each row.

The primary key index stores the page and offset id of the row that you’re looking for.

You can access the tuple by that page and offset id instead of traversing the index first.

How do I find the tuple and page id of a record and then search by it?

Simple, you can access and search by the ctid field on each record. It looks like (page,offset).

No more primary keys?

Then why don’t we use just use ctid in our system instead of primary keys if they’re faster?

The problem is that the physical location of the record is not static.

An update on a record is the equivalent of a delete then an insert so the ctid will likely change on each update.

If my table records are immutable, then can I use it?

Not exactly… VACUUM FULL can move tuples around when it compacts space. (VACUUM is PostgreSQL’s garbage collector; With running transactions, old records need to be freed.)