Building a Table Access Method, Part 1

The standard storage engine of Postgres is called the “heap” because of the storage method. The data is stored in “heap files” in no particular order, just added wherever there’s room and extending the file at the end only when there are no space inside the pages of the file. This storage engine is simple to implement, but it has drawbacks, which is why Table Access Methods (TAM) were added in Postgres version 12. The intention was to allow other kinds of storage engines to be used as extensions to Postgres.

At that time, Postgres already had Foreign Data Wrappers (FDW) that can be used to read data from other database implementations. It seems like there was already a method available to read data in different formats than the standard heap format, so why was the table access method added?

In this series of posts, we will look into the architecture of a table access method and implement an in-memory table access method to demonstrate how a TAM can be implemented and what you need to consider when doing that. This first post will go through the difference between FDWs and TAMs and give an understanding of when each is suitable.

Foreign data wrappers and table access methods

Foreign data wrappers and table access methods serve two very different goals:

• A Foreign Data Wrapper focuses on translating data stored in a different format, potentially remotely, into a result set. This can be a remote database, a CSV file, a REST API, or another Postgres cluster. The executor treats a foreign table as opaque storage and makes very few assumptions about the format: it doesn’t assume the remote side understands MVCC, locking, or anything else about how Postgres manages data. The FDW is used to create foreign tables, which are more similar to a view into data stored elsewhere than to a table.

  Note that even though the intention is to provide access to remote data, there is nothing that requires the data to be remote. For example, file_fdw, which is part of core Postgres, reads CSV or text files sitting on the server’s own filesystem.

• A Table Access Method focuses on providing access to data that is stored on the same instance, just not in the traditional heap format. It looks like a normal relation: it has a pg_class row, gets planned, locked, indexed, constrained, and backed up. This is much closer to a MySQL storage-engine swap (InnoDB vs. MyISAM) than to anything Postgres foreign data wrappers do.

  Note that even though the intention is to implement an engine for local storage, there is nothing that requires the data to be local. It is possible to write a table access method where all the data is remote.

A breakdown of the differences between them can be seen in the table below.

Feature	FDW	TAM	Notes
Indexes	Not supported	Supported	An FDW can use qual pushdown to reach a remote index
Create new table	N/A	Creates the table	Table must already exist on the remote side when creating a foreign table
MVCC	Not supported	Supported
Transactions	Not supported	Supported
Row locks	Not supported	Supported	The FDW can emulate FOR UPDATE by re-checking the row.
Triggers	Partially supported	Supported	INSTEAD OF, constraint, and TRUNCATE triggers are not supported by FDWs.
Parallel scans	Not supported	Supported

How are they used by the executor

Foreign data wrappers work directly on the execution plans, and the executor hands the plans and then reads the tuples one by one using FDW callbacks. Everything about how those tuples got produced, whether the write actually committed on the remote side, whether two remote writes are consistent with each other is entirely the FDW author’s problem, invisible to the rest of Postgres.

Table access methods have a quite elaborate set of callbacks that are called during the execution of the plan. Note that here Postgres is responsible for executing the plan and just calls the TAM interface when it needs a particular row or wants to modify a row.

You will see the details of how this interface works in the rest of the posts of this series.

Visibility and MVCC

Foreign data wrappers have no built-in transaction integration. The reason is that the FDW is intended to provide access to a remote system, and the remote system doesn’t necessarily have the same idea about transactions or snapshots as Postgres, so information about transactions or snapshots is not passed through the API. If you want a remote write to roll back when the local transaction aborts, you need to build that yourself. Most FDWs, including postgres_fdw, don’t attempt true two-phase commit by default. Consistency across the boundary is opt-in and the responsibility of the FDW.

Table access methods sit inside the transaction system of Postgres and are an integral part of the executor. There is some implementation work you need to do, and some callbacks to implement, but the surrounding machinery deals with snapshots, subtransactions, command IDs, ON COMMIT behavior, etc.

Indexes

Foreign tables can’t have local indexes and CREATE INDEX on a foreign table will fail with an error. Instead, if the FDW author knows the remote side has indexes, it can propose an execution path that reflects this. It is not a real local index structure, but reliance on indexes in the remote implementation.

In contrast, a TAM-backed table is a real relation, so CREATE INDEX works, and the planner will consider all kinds of index scans against it if the storage engine supports it.

Create new table

When creating foreign tables with CREATE FOREIGN TABLE using an FDW, it does not create anything on the remote side. It declares a local mapping onto data that is assumed to already exist on the remote system. If the remote table does not actually exist, you will not find out until you query it and the scan callback for the FDW fails. There is no storage-allocation hook in the API for the simple reason that there is no local storage to allocate.

When creating tables with CREATE TABLE using a TAM, it calls into the access method to allocate real storage before the statement returns. This means that you can create and drop real tables in the database.

Row locks

The FDW API has callbacks that let it emulate SELECT ... FOR UPDATE by re-fetching a row and checking whether it still matches what the query saw earlier, but this does not work as a real lock. Nothing stops another session, local or remote, from modifying the same row in the meantime, and the remote system is not obligated to know that Postgres considers the row “locked” at all.

The tuples managed by a TAM participate in the normal locking done by the lock manager and work the same way as for the heap. SELECT ... FOR UPDATE against a TAM-backed table blocks other backends exactly as it would against a heap table, because it is the same lock manager doing the blocking.

Triggers

Foreign tables have supported BEFORE/AFTER triggers, both row-level and statement-level, since Postgres 9.4. A BEFORE INSERT trigger on a foreign table runs locally and can modify or reject a row before it is ever sent to the remote side, the same as it would on a heap table. What is not supported are INSTEAD OF triggers (these only make sense on views), constraint triggers (they need transactional guarantees), and triggers on TRUNCATE.

Tables using a TAM support all sorts of trigger types since a TAM-backed relation is not a special case. All relations use a TAM, so triggers work exactly as they do on a heap table.

Parallel scans

Postgres’s parallel query launches multiple background workers that each scan a slice of the same relation concurrently, coordinated through dedicated parallel scan callbacks of the TAM. Nothing in the FDW API launches extra worker processes to fetch a single foreign table faster. postgres_fdw does offer “asynchronous execution”, but that lets one backend interleave requests across several different foreign scans already gathered under one Append node, for example separate partitions living on separate foreign servers. It does not parallelize a single scan.

A TAM can implement genuine parallel scans, which is already used by the heap implementation. The TAM built later in this series demonstrates this for its sequential scan.

Concluding remarks

In order to decide if you need a foreign data wrapper or a table access method, you can use these guiding principles:

• If the data genuinely lives somewhere else, and you don’t need Postgres-grade transactional guarantees, indexing, or table lifecycle management (create, alter, drop) over it locally, reach for an FDW.

• If the data is a Postgres relation in every sense except how the bytes are laid out, a TAM is the extension point built for that, and the forty-callback surface is the price of getting MVCC, indexing, and planner integration out-of-the-box.

You now know more about foreign data wrappers and, more importantly, about table access methods. In the next post you will see the first steps of writing an implementation of an in-memory TAM.