PostgreSQL is known for its powerful features and extensibility. One such feature that allows deep customization of data storage is the Table Access Method. While it's not commonly modified by everyday users, understanding it can give you insights into PostgreSQL's internal storage engine—and even allow you to build your own.
In this blog, we'll explain what table access methods are, their purpose, built-in types in PostgreSQL, how they're managed via the system catalog pg_am, and how you can create and drop your own table access methods.
What is a Table Access Method in PostgreSQL?
A table access method (also called a table AM) defines how PostgreSQL physically stores, retrieves, updates, and deletes rows in a table.
To put it simply:
* When you execute SELECT, INSERT, UPDATE, or DELETE on a table, PostgreSQL delegates these operations to a table access method.
* It acts like a storage engine plug-in that handles the physical implementation of table operations.
* Think of it as the internal driver for managing how table data lives on disk.
Use Case: You can create a new table access method if you want to customize how tables behave—for example, build a columnar store instead of the default row-based storage.
Purpose of Table Access Methods
The table access method provides a layer of abstraction between the SQL execution engine and the actual disk storage. This design has two major benefits:
1. Extensibility: You can write your own custom access methods for different storage strategies (row-store, column-store, compressed formats, etc.).
2. Optimization: PostgreSQL core can remain generic, while storage behavior can be optimized through specialized handlers.
The access method also controls
* How data is physically written and read.
* How row-level locking and MVCC are handled.
* How visibility and indexing interact with the table.
Built-in Table Access Methods in PostgreSQL (from pg_am)
PostgreSQL stores all registered access methods (both for tables and indexes) in the system catalog named pg_am.
Query Example:
Run this SQL to view the available access methods:
SELECT * FROM pg_am;
oid | amname | amhandler | amtype
------+--------+----------------------+--------
2 | heap | heap_tableam_handler | t
403 | btree | bthandler | i
405 | hash | hashhandler | i
783 | gist | gisthandler | i
2742 | gin | ginhandler | i
4000 | spgist | spghandler | i
3580 | brin | brinhandler | i
(7 rows)
Explanation:
* amname: Name of the access method.
* amhandler: Name of the handler function in the backend (written in C).
* amtype: Indicates type — 't' for table, 'i for index.
Built-in Table Access Method:
1. heap (amtype = 't')
* The default access method for tables.
* Stores rows in an unordered format (heap).
* Supports MVCC and is compatible with vacuum and indexing.
* All PostgreSQL tables, by default, use this unless specified otherwise.
Built-in Index Access Methods:
2. btree
* The default and most widely used index access method.
* Supports equality, range, sorting, and uniqueness checks.
* Check the file path in the PostgreSQL source code to learn more about the bthandler()
postgresql/src/backend/access/nbtree/nbtree.c
3. hash
* Optimized for equality comparisons (=).
* Not commonly used due to limited features and concurrency.
Check the file path in the PostgreSQL source code to learn more about the hashhandler()
postgresql/src/backend/access/hash/hash.c
4. gist (Generalized Search Tree)
* Extensible index type for spatial, full-text, and other custom types.
Check the file path in the PostgreSQL source code to learn more about the gisthandler()
postgresql/src/backend/access/gist/gist.c
5. gin (Generalized Inverted Index)
* Best suited for full-text search, array values, and composite types.
Check the file path in the PostgreSQL source code to learn more about the ginhandler()
postgresql/src/backend/access/gin/ginutil.c
6. spgist (Space-Partitioned GiST)
* More efficient than GiST for certain use cases, especially for partitioned spaces like IP ranges.
Check the file path in the PostgreSQL source code to learn more about the spghandler()
postgresql/src/backend/access/spgist/spgutils.c
7. brin (Block Range Index)
* Efficient for very large tables with naturally sorted data (e.g., logs).
* Works by summarizing block ranges.
Check the file path in the PostgreSQL source code to learn more about the brinhandler()
postgresql/src/backend/access/brin/brin.c
Create a New Table Access Method (Using Existing Handler)
PostgreSQL allows you to define custom table access methods, which control how tables store and retrieve data. While creating your own handler in C is an advanced task, you can also create a new access method by reusing an existing handler, such as the built-in heap_tableam_handler.
Steps to Create a New Table Access Method Using an Existing Handler
1. SQL Command to Create the Access Method
CREATE ACCESS METHOD heap1
TYPE TABLE
HANDLER heap_tableam_handler;
* heap1 is the name of your new access method.
* heap_tableam_handler is PostgreSQL's built-in handler used by the standard heap storage.
You don’t need to write any C code — you're just reusing the handler that implements heap-based table storage.
Important Notes
* This method reuses the behavior of the standard heap, so it's not technically "custom" in implementation — only in naming.
* Useful for experimentation or testing new behaviors (e.g., setting up an environment for a future patched handler).
* You must explicitly specify the access method when creating a table:
CREATE TABLE test_table (
id serial PRIMARY KEY,
name text
) USING heap1;
How to Drop the Custom Access Method
To remove the access method:
DROP ACCESS METHOD heap1;
Note: Make sure no tables are currently using it, or PostgreSQL will raise an error.
conclusion
Table access methods play a foundational role in PostgreSQL’s architecture, acting as the core mechanism through which the database reads, writes, and manages data in tables. While they often operate behind the scenes, their significance becomes clear when you explore how PostgreSQL achieves flexibility, performance, and extensibility at the storage level. By abstracting the storage engine through these methods, PostgreSQL enables developers and system architects to design custom storage strategies tailored to specific workloads—whether it's row-oriented storage for OLTP systems or columnar models for analytics.
This design empowers PostgreSQL to adapt to diverse data requirements without altering the SQL layer or query processing logic. Understanding table access methods is not just about knowing how data is stored—it's about gaining insight into how PostgreSQL balances performance, concurrency, and storage efficiency. For anyone looking to push the boundaries of PostgreSQL’s capabilities, whether by contributing to its core or by building high-performance extensions, mastering table access methods is an essential step toward unlocking its full potential. Delve into the practical aspects of PostgreSQL storage management and learn how to use tablespaces in PostgreSQL to organize your database objects, enhance performance, and improve disk space utilization.