Pluggable Storage Engine

Summary: In this article, you will learn what is pluggable storage engine in PostgreSQL.

Table of Contents

What are table access methods?

It is a feature that allows an alternative implementation for how data in a table should be stored. Until PostgreSQL 11, access methods were provided for index data, to choose different storage methods such as B-Tree or hash, but no similar mechanism was available for tables.

PostgreSQL 12 introduced this feature, so now access methods can be implemented for tables as well as indexes, allowing the selection of different table storage mechanisms.

Table access methods expose APIs, which allows PostgreSQL developers to create their own methods. In PostgreSQL 12, the traditional heap format is migrated to a table access method, and is available by default.

Access mechanisms - PostgreSQL 11 and earlier

Tables can only be accessed by heap

Access mechanisms - starting from PostgreSQL 12

Users can specify the table access method

How to use the table access method interface?

To define a table access method, use CREATE ACCESS METHOD with TYPE TABLE. To then have a table use the new access method, specify it in the USING clause in CREATE TABLE, CREATE TABLE AS SELECT or CREATE MATERIALIZED VIEW. Alternatively, you can specify the default table access method in the postgresql.conf file parameter default_table_access_method.

CREATE ACCESS METHOD heap1 TYPE TABLE HANDLER heap_tableam_handler;
CREATE TABLE tbl1(id int, name text) USING heap1 ...; 

Advantages and how to use

The appeal of this functionality includes the following:

• Concise, pluggable architecture that is easy to use and PostgreSQL developer-friendly
• Users can specify the access method for each table
• Makes way for both open source and commercial databases
• Co-existence of difference table access methods in the same database

PostgreSQL 12 supports only heap as the table access method, but the next version of PostgreSQL is expected to provide new table access methods, such as columnar and in-memory.

In the future, users will be able to choose the appropriate table access method for their job, such as heap for OLTP operations, columnar table for OLAP operations, and in-memory for ultra-fast search processing. By providing users with an interface that allows them to use a specific table access method, the system will be able to meet a variety of business processing needs.

What’s the difference with foreign data wrappers?

Foreign data wrappers are intended to access foreign data, while table access methods are used to access local data.

For example, suppose you have a requirement to use columnar data in your application. With a foreign data wrapper, you would need to use it to access a remote server containing the columnar data (in this case using cstore_fdw), which would reduce processing performance.

But with table access methods, it is possible to store columnar tables locally, which speeds up processing. Note though, that PostgreSQL 17 does not support table access methods for columnar tables.

In short, foreign data wrappers and table access methods target different needs. The former allows users to access data in a remote server that was never intended to be stored locally, while the latter allows users to store data locally using different supported methods.

Accessing columnar data in a foreign data source via cstore_fdw

Accessing columnar data in PostgreSQL via access method interface

Architecture

PostgreSQL architecture with multiple pluggable storage engines as follows:

  flowchart TD
subgraph "SQL Engine"
%% Nodes
   A("Parser")
   B("Planner")
   C("Executor")
   D("DDL")
   E(("Catalog"))
   F("Table Access Manager")
%% Edge connections between nodes
   A --> B --> C
   A --> D --> E
   B & C --> E
   C & E --> F
end

subgraph "Storage Engine"
%% Nodes
   G("HeapAM")
   H("Zedstore")
   I("WhatAM")
   J("Buffer Manager")
   K(("Buffers"))
   L("Storage Manager (IO)")
   M("Page Cache")
   N[("Disk")]
%% Edge connections between nodes
   G & H & I --> J
   J --> L --> M --> N
   J --> K
end

%% Edge connections between nodes
    F --> G & H & I

Summary

The table access method interface introduced in PostgreSQL 12 allows PostgreSQL users to access table data using methods tailored to their requirements. I look forward to seeing more and more PostgreSQL applications with the ability to choose multiple table access methods that match the characteristics of the business.