Where PostgreSQL table storage can change

Piotr Styczyński [BioseqDB]

5 min read

·

Oct 13, 2026

--

Listen

Share

Following a small specimen table from SQL into its access method, and looking at the work that remains after a custom engine can return its first row.

When I look at a slow query, I usually start with the query and its indexes, because those are the parts I can change without asking awkward questions about where the rows actually live. PostgreSQL also has a place to change that last part: a table access method.

For this post I wanted to start with an ordinary table and a question I can answer from SQL: which part owns its storage, and which part helps me find a row?

For a working extension to compile and inspect, continue with writing a small table access method in C++.

Two meanings of access method

The pg_am catalog records both table and index access methods. Its amtype column distinguishes them, which is useful because USING appears in both table and index definitions and can make the two choices look more similar than they are.

SELECT amname,
  CASE amtype
    WHEN 't' THEN 'table'
    WHEN 'i' THEN 'index'
  END AS method_kind
FROM pg_am
WHERE amname IN ('heap', 'btree', 'gin')
ORDER BY method_kind, amname;

Result · 3 rows

amname / name | method_kind / text
--------------+-------------------
btree         | index
gin           | index
heap          | table

Executed against Bioseq extension 1.0 on PostgreSQL 17.10

A table method manages the table’s rows; an index method manages an index. Choosing a B-tree for a specimen identifier does not turn the underlying table into a B-tree table, and dropping that index does not discard the specimen records. I find that distinction easier to remember when I inspect both objects together.

Follow one table

Here is the small table I use below. The specimens are invented, and the counts are just values which make the output easy to check.

CREATE TABLE lab_observations (
  specimen text PRIMARY KEY,
  read_count integer NOT NULL,
  needs_review boolean NOT NULL
) USING heap;
 
CREATE INDEX lab_observations_count_idx
  ON lab_observations USING btree (read_count);

heap is PostgreSQL's default table access method. I spell it out so the example does not depend on a changed session default. The method actually attached to a relation is recorded in pg_class.relam, so I can join that value back to the catalog above:

SELECT relation.relname AS object_name,
  method.amname AS access_method
FROM pg_class AS relation
JOIN pg_am AS method ON method.oid = relation.relam
WHERE relation.oid IN (
  'lab_observations'::regclass,
  'lab_observations_count_idx'::regclass
)
ORDER BY object_name;

Result · 2 rows

object_name / name         | access_method / name
---------------------------+---------------------
lab_observations           | heap
lab_observations_count_idx | btree

Executed against Bioseq extension 1.0 on PostgreSQL 17.10

There are two objects here with two different jobs, even though they describe the same collection of observations. That gives me a useful boundary for an experiment: I can ask whether I need a different way to locate candidates, or whether I really need a different representation of the table itself.

Press enter or click to view image in full size

Returning a row is the beginning

A table access method supplies a handler returning a TableAmRoutine, whose callbacks let PostgreSQL request operations such as starting a scan or inserting a tuple. A tuple slot provides a representation of a row that the rest of the executor can use without understanding every detail of its storage. The table access method documentation describes that interface and the constraints around row identifiers.

This is an attractive place to experiment, but the first successful SELECT answers a very small question. If I put three records into an array and read them back, I have demonstrated that particular path through my code. I have not established what happens when a second connection arrives, a transaction aborts, or the server disappears halfway through a write.

For example, MVCC gives a query a view of the database through a snapshot. “Return the newest record I can find” is not enough when that record belongs to a transaction the reader cannot see. I would test an uncommitted update from another connection before spending time comparing scan speeds.

An index lookup has the same visibility problem. The table method’s index_fetch_tuple callback receives a candidate identifier and a snapshot; it must find a suitable visible tuple rather than blindly trust the index entry. The API comments also distinguish ordinary vacuum work from the callbacks used by ANALYZE and table rewrites. These are different operations, even when they all involve visiting stored data.

Keep an ordinary query as a reference

I would keep this kind of deliberately boring query beside a storage experiment:

SELECT specimen, read_count
FROM lab_observations
WHERE needs_review
ORDER BY read_count DESC, specimen;

Result · 2 rows

specimen / text | read_count / integer
----------------+---------------------
S-32            | 480
S-11            | 120

Executed against Bioseq extension 1.0 on PostgreSQL 17.10

The expected result is small enough to check by eye. From there I would add updates, rollbacks, deleted rows and overlapping readers, keeping the expected answers seperate from the engine’s implementation. A faster result is useful only after I know it is the right result.

Reclaiming obsolete data also needs attention. PostgreSQL’s heap requires routine vacuuming; a different storage design still needs a clear account of when old information can be removed. I would include a long-running reader in that experiment, because reclaiming something too early is harder to notice than retaining it too long.

The part that survives a restart

Write-ahead logging provides the ordering needed to recover logged changes after a crash. A custom method needs its own coherent recovery story; PostgreSQL’s table interface permits its WAL facilities or another implementation. An in-memory demonstration should not be mistaken for durable storage just because it accepts familiar SQL.

There is an operational boundary too. In PostgreSQL 17, changing an existing table with ALTER TABLE ... SET ACCESS METHOD rewrites it and requires an ACCESS EXCLUSIVE lock. I would plan that as a migration, with room for the rewrite and a tested way back, rather than treat it as an inexpensive setting. The version-specific ALTER TABLE documentation is the place to check before doing it.

What interests me here is the amount of PostgreSQL I can keep while investigating a different storage layout. The SQL examples above deliberately stay with heap; they establish the boundary and a few expected results, leaving the larger implementation questions visible instead of hiding them behind a successful demo.

Please check my OS project — BioseqDB