iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
In PostgreSQL, the storage layer that people usually mean by a “database engine” is the table access method. Every ordinary table uses one, the built-in default is heap, and an existing table can be moved to a different method with ALTER TABLE ... SET ACCESS METHOD. That command rewrites the table’s data, so the change is a planned data migration rather than a setting you flip. Only methods that are installed and registered on the server can be used, and registering a new one is a superuser task that normally comes from an extension.
What “database engine” means in PostgreSQL
Outside PostgreSQL, “engine” is used loosely. Some products let you choose a storage engine per table, others have one engine underneath everything. PostgreSQL does not use the word that way. Its documentation separates two jobs that are easy to confuse:
- Table access methods decide how the rows of a table are physically stored and read.
- Index access methods decide how an index structure is built and searched.
Both are recorded in the pg_am system catalog, and each entry is marked as either a table method or an index method. That marker is the quickest way to see which kind of method you are looking at.
When you choose a “storage engine” for a PostgreSQL table, you are choosing a table access method. B-tree, hash, GiST, GIN, SP-GiST and BRIN are index access methods. They speed up lookups on a table but do not replace where the table’s rows are kept, so they are not alternatives to heap.
#1 Best Overall
How a table access method works
PostgreSQL core does not hard-code how table rows are stored. It talks to storage through the table access method interface. A method supplies a TableAmRoutine structure, a set of callback functions that tell the core system how to scan, insert, update, delete and fetch tuples. An extension’s handler function returns that structure. The official PostgreSQL 18 documentation describes the chapter as explaining “the interface between the core PostgreSQL system and table access methods, which manage the storage for tables.”
The interface leaves real design choices to the implementer:
- Buffering. A method may use PostgreSQL shared buffers, but it is not required to.
- Tuple identifiers. A method that supports modifications or indexes must identify each tuple with a TID, which the documentation defines as a block number plus an item number.
- Crash safety. A method can write to the PostgreSQL write-ahead log (WAL) or use its own mechanism.
- Transactions. Letting different table methods take part in one transaction can require close integration with PostgreSQL’s transaction machinery.
These constraints explain why a table method is a substantial piece of C code, not a configuration option. They also mean two methods that both “work” can differ in what they support, which is why you should check documented capabilities before switching anything.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchRank #2
Heap: the baseline you already have
heap is the built-in table access method and the reference implementation that developers study when they write a new one. It is the default for new tables. Unless your server or a session has changed default_table_access_method, the tables you create use heap. That makes heap the right point of comparison: any other method is a deliberate departure from it.
Some general database background helps when judging a switch. Storage engines differ in how they lay out rows (row-oriented versus column-oriented layouts are the most common contrast), how they handle updates and deletes, how they keep data safe after a crash, and how they interact with indexes. Those are the axes on which a table method can differ, and they are the questions to ask of any alternative you consider.
Finding the methods installed on your server
Before you plan anything, list which table methods the server actually has. Connect with psql and run:
SELECT amname, amtype
FROM pg_am
WHERE amtype = 't';Table methods are marked t in amtype, and index methods are marked i. If heap is the only row returned, your server has no additional table methods registered. To see which method a specific table uses, run d+ table_name in psql; the output includes an access method line for tables.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Also check the default for new tables:
SHOW default_table_access_method;Registering a new table access method
CREATE ACCESS METHOD registers a method with the server. It does not convert any existing table. In PostgreSQL 18 the command accepts TABLE and INDEX as access method types, and only superusers can define new methods. A table method needs a handler function that returns a TableAmRoutine, which in practice means the extension that provides the method has already been built and installed on the server.
CREATE ACCESS METHOD method_name TYPE TABLE HANDLER handler_function;In most cases you will not type this yourself. An extension’s installation script normally runs it, so the usual step is to install the extension according to its own documentation and then confirm the method appears in pg_am. If you are registering a method by hand, the handler function name must match the one the extension provides.
Swapping the method on an existing table
Changing a table’s method uses ALTER TABLE:
ALTER TABLE table_name SET ACCESS METHOD method_name;Use DEFAULT in place of a method name to switch the table back to whatever default_table_access_method currently specifies. Because the statement rewrites the table into the new method’s layout, treat it as a migration. A reasonable sequence is:
- Confirm the target method is registered (the
pg_amquery above) and that the extension providing it is installed on the same server version you run in production. - Read that extension’s documentation for its supported PostgreSQL versions, its crash-safety and WAL behavior, and any restrictions on indexes, replication or extensions that touch the table.
- Rehearse on a copy of the table or a restored backup, and time the rewrite on realistic data volume. The PostgreSQL documentation does not give a duration or downtime figure for this operation, so measure it yourself.
- Make sure there is enough free disk space for a second copy of the table during the rewrite and for any indexes that are rebuilt along with it.
- Run the
ALTER TABLEstatement in a maintenance window. Treat the table as unavailable to normal work until you have confirmed the rewrite finished. - Verify the result with
d+ table_nameand check row counts and key queries against the values you recorded before the change.
To return to the previous method, run the same statement with the old method name, or with DEFAULT if the old method was the default. That is another full rewrite, so plan the rollback with the same care as the forward change.
Partitioned tables
A partitioned table holds no rows of its own, so there is no data to rewrite at the parent. For a partitioned parent, the access method setting determines the method for future partitions, unless a specific partition is given its own method. Existing partitions keep their current method until you change them individually, and each of those changes is a rewrite of that partition’s data. If you want a whole partitioned set moved, plan the work partition by partition rather than expecting one statement to convert everything.
Evaluating alternatives
The official documentation explains the interface and the migration semantics. It does not publish a comparative benchmark of table access methods, and the research behind this article did not establish a current, broad catalogue of third-party table methods. If you are choosing between candidates, judge each one on documented facts for your situation:
- Which PostgreSQL major versions the implementation supports, and whether that matches your upgrade path.
- Read and write support, and whether it supports indexes and TIDs for the operations you need.
- Crash-safety design: whether it writes through WAL, how it is replicated, and how it behaves in point-in-time recovery.
- Transaction behavior, particularly for statements that touch tables using different methods.
- Extension compatibility with the other extensions and tools in your stack, including backup tools.
- Migration cost: the rewrite time you measured, the disk space needed, and how you would roll back.
Do not accept a claim that one method is faster or smaller unless it comes with results for a stated PostgreSQL version, hardware, configuration and workload. Such figures are the publisher’s own, and a result on one workload rarely transfers to another.
Quick Recap
Common mistakes
- Expecting
CREATE ACCESS METHODto change existing tables. It only registers the method. Existing tables keep their method until you runALTER TABLE. - Treating index methods as storage engines. Switching a table to GIN or B-tree is not a valid table-storage choice; those are index methods.
- Skipping the rehearsal. A rewrite on production without a timed test is the usual cause of a missed maintenance window.
- Forgetting the default. If
default_table_access_methodis changed, new tables follow the new default, which may not be what you intended.
“
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →
