PostgreSQL Table Access Methods: How to Change

This article explains what PostgreSQL table access methods are and how to change them. You’ll learn the difference between heap and index-organized storage, how to set a custom access method for a table, and when you might want to do so.

Background: What Are Table Access Methods?

In PostgreSQL, a table access method defines how rows are physically stored and retrieved on disk. The default is the heap method, which stores rows in an unordered collection called a heap. Each row has a unique identifier (ctid) that points to its physical location. This design supports efficient full scans and updates, but it means that retrieving rows in a specific order requires an index.

PostgreSQL introduced a pluggable table access method interface in version 12. This allows developers to implement alternative storage engines—such as columnar stores, in-memory tables, or index-organized tables—without changing the core server. As of the latest stable versions, the built-in methods are heap and tableam (the latter is just a placeholder for extensions). Most users will only encounter the heap method unless they install an extension like zheap (not yet production-ready) or a third-party columnar engine.

How Table Access Methods Work

When you create a table, PostgreSQL assigns it an access method. The method controls the physical layout of the table’s data file and the set of operations the planner can use. For example, the heap method supports sequential scans, index scans, and bitmap heap scans. It also handles concurrency via multi-version concurrency control (MVCC), where updated rows are written as new versions and old versions are eventually removed by vacuuming.

An alternative method, like an index-organized table (IOT), would store rows sorted by the primary key, eliminating the need for a separate index. However, PostgreSQL does not ship an IOT implementation by default. Extensions can provide one, but they must implement the full API, including tuple visibility, vacuum, and crash recovery. This is why most custom methods are experimental or niche.

How to Change a Table’s Access Method

You can change the access method of an existing table using the ALTER TABLE command. The syntax is:

ALTER TABLE table_name SET ACCESS METHOD method_name;

This command rewrites the entire table to the new format. It requires the new method to be installed and have the necessary permissions. If you want to set the default for all future tables, you can use:

SET default_table_access_method = 'method_name';

When creating a new table, you can specify the method directly:

CREATE TABLE my_table (id int, data text) USING method_name;

To see which method a table currently uses, query the pg_class system catalog:

SELECT relname, relam FROM pg_class WHERE relname = 'my_table';

The relam value is an OID that maps to pg_am. You can join with pg_am to get the method name.

Choosing an Access Method: When It Makes Sense

For the vast majority of applications, the default heap method is the right choice. It is battle-tested, supports all features, and is optimized for OLTP workloads. You should only consider changing it if you have a specific need that the heap cannot meet. For example:

• Columnar storage for analytical queries that scan only a few columns of a large table. Extensions like cstore_fdw (deprecated) or columnar (from Citus) provide this.
• In-memory tables for extremely fast access to small, frequently accessed datasets. Some extensions offer this, but PostgreSQL’s shared buffers often suffice.
• Index-organized tables for tables where the primary key is the main access path and you want to avoid a separate index. No official extension exists, but research prototypes do.

Before switching, benchmark your workload. Rewriting a large table takes time and disk space. Also, verify that the new method supports indexes, constraints, and foreign keys—many custom methods do not.

Common Problems and Misconceptions

One common misconception is that changing the access method will automatically speed up queries. In reality, the heap method is already very efficient for most operations. The main benefit of custom methods is for specialized workloads, and they often come with trade-offs, such as slower updates or lack of support for certain features.

Another issue is that ALTER TABLE ... SET ACCESS METHOD locks the table and rewrites it, which can cause downtime. For large tables, this can take minutes or hours. Plan accordingly, and consider using pg_repack or similar tools to minimize downtime, though those tools also have their own limitations.

Also, note that not all access methods are compatible with all PostgreSQL features. For example, a columnar store might not support UPDATE or DELETE efficiently, or might not support unique constraints. Always read the extension’s documentation thoroughly.

Comparing Access Methods: Heap vs. Alternatives

The heap method is the default and is designed for general-purpose use. It handles concurrent transactions well, supports all data types and indexes, and is the only method that most users will ever need. Its main weakness is that it stores rows without any particular order, so range queries on non-indexed columns require a full scan.

Columnar methods, on the other hand, store data column-by-column, which reduces I/O for analytical queries that read only a few columns. However, they are poor for point updates and inserts, and they often lack support for indexes. In-memory methods store data in RAM, which is fast but volatile; they are not suitable for durable storage unless combined with a write-ahead log.

Index-organized tables combine the primary key index with the data, so that the table itself is sorted by the key. This can speed up key-based lookups and range scans, but it makes updates that change the key expensive, and it can lead to fragmentation.

Costs and Safety Considerations

Changing an access method is not a trivial operation. It requires a full table rewrite, which consumes CPU, I/O, and disk space. During the rewrite, the table is locked, so concurrent writes are blocked. You should perform this during a maintenance window.

Before making any change, take a backup. Also, test the new method on a copy of the data first. If you are using an extension, make sure it is compatible with your PostgreSQL version and that it is actively maintained. Unmaintained extensions can be a security risk and may break after a major upgrade.

If you decide to revert, you can use the same ALTER TABLE command to set the method back to heap. The rewrite will happen again, so the same downtime applies.

Alternatives to Changing Access Methods

Before resorting to a custom access method, consider simpler alternatives that often solve the same performance problems:

• Indexes: Adding the right index can make queries orders of magnitude faster without changing storage.
• Partitioning: Splitting a large table into smaller pieces can improve maintenance and query performance.
• Materialized views: Pre-compute complex aggregations and store them as a table.
• Foreign data wrappers: Use a different database engine (e.g., a columnar database) as a foreign table and query it via PostgreSQL.
• Upgrading hardware: Sometimes the simplest fix is more RAM or faster storage.

Changing the access method is a low-level optimization that should be a last resort, not a first step.

Frequently Asked Questions

Can I change the access method of a table without downtime?

No, ALTER TABLE ... SET ACCESS METHOD takes an exclusive lock on the table and rewrites it. For large tables, this can cause significant downtime. You can use tools like pg_repack to reduce the lock time, but they work by creating a new table and swapping it in, which still requires a brief lock at the end.

How do I see which access method a table uses?

Query the pg_class system catalog and join with pg_am. For example: SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.relname = 'your_table';

Are there any built-in access methods besides heap?

As of PostgreSQL 16, the only built-in method is heap. There is also a placeholder tableam that is not usable. All other methods come from extensions.

Does changing the access method affect indexes?

Yes, the table rewrite will rebuild all indexes on the table. The new access method must be compatible with the existing index types. For example, a columnar store may not support b-tree indexes, so you might need to drop and recreate them.

Can I use a custom access method for a temporary table?

Yes, you can specify the access method when creating a temporary table, but the method must be available in the session. However, most custom methods are not designed for temporary tables, so test carefully.

What happens if I set default_table_access_method to a non-existent method?

PostgreSQL will raise an error when you try to create a table without an explicit USING clause. The default is only used if the method exists and is valid.

Related

• Google Forms Header Image Size: Full Guide
• Convert Excel Time to Decimal Seconds
• Dual Tuner DVR: What Two Tuners Let You Do