OrioleDB Overview

The OrioleDB Postgres extension provides a drop-in replacement storage engine for the default heap storage method. It is designed to improve Postgres' scalability and performance.

OrioleDB addresses Postgres's scalability limitations by removing bottlenecks in the shared memory cache under high concurrency. It also optimizes write-ahead-log (WAL) insertion through row-level WAL logging. These changes lead to significant improvements in the industry standard TPC-C benchmark, which approximates a real-world transactional workload. The following benchmark was performed on a c7g.metal instance and shows OrioleDB's performance outperforming the default Postgres heap method with a 3.3x speedup.

Concepts#

Index-organized tables#

OrioleDB uses index-organized tables, where table data is stored in the index structure. This design eliminates the need for separate heap storage, reduces overhead and improves lookup performance for primary key queries.

No buffer mapping#

In-memory pages are connected to the storage pages using direct links. This allows OrioleDB to bypass Postgres's shared buffer pool and eliminate the associated complexity and contention in buffer mapping.

Undo log#

Multi-Version Concurrency Control (MVCC) is implemented using an undo log. The undo log stores previous row versions and transaction information, which enables consistent reads while removing the need for table vacuuming completely.

Copy-on-write checkpoints#

OrioleDB implements copy-on-write checkpoints to persist data efficiently. This approach writes only modified data during a checkpoint, reducing the I/O overhead compared to traditional Postgres checkpointing and allowing row-level WAL logging.

Usage#

Creating OrioleDB project#

You can get started with OrioleDB by enabling the extension in your Supabase dashboard. To get started with OrioleDB you need to create a new Supabase project and choose OrioleDB Public Alpha Postgres version.

Creating tables#

To create a table using the OrioleDB storage engine, execute the standard CREATE TABLE statement. By default it will create a table using OrioleDB storage engine. For example:

1
-- Create a table
2
create table blog_post (
3
  id int8 not null,
4
  title text not null,
5
  body text not null,
6
  author text not null,
7
  published_at timestamptz not null default CURRENT_TIMESTAMP,
8
  views bigint not null,
9
  primary key (id)
10
);

Creating indexes#

OrioleDB tables always have a primary key. If it wasn't defined explicitly, a hidden primary key is created using the ctid column. Additionally you can create secondary indexes.

1
-- Create an index
2
create index blog_post_published_at on blog_post (published_at);
3
4
create index blog_post_views on blog_post (views) where (views > 1000);

Data manipulation#

You can query and modify data in OrioleDB tables using standard SQL statements, including SELECT, INSERT, UPDATE, DELETE and INSERT ... ON CONFLICT.

1
insert into blog_post (id, title, body, author, views)
2
values (1, 'Hello, World!', 'This is my first blog post.', 'John Doe', 1000);
3
4
select * from blog_post order by published_at desc limit 10;
5
 id │     title     │            body             │  author  │         published_at          │ views
6
────┼───────────────┼─────────────────────────────┼──────────┼───────────────────────────────┼───────
7
  1 │ Hello, World! │ This is my first blog post. │ John Doe │ 2024-11-15 12:04:18.756824+01 │  1000

Viewing query plans#

You can see the execution plan using standard EXPLAIN statement.

1
explain select * from blog_post order by published_at desc limit 10;
2
                                                 QUERY PLAN
3
────────────────────────────────────────────────────────────────────────────────────────────────────────────
4
 Limit  (cost=0.15..1.67 rows=10 width=120)
5
   ->  Index Scan Backward using blog_post_published_at on blog_post  (cost=0.15..48.95 rows=320 width=120)
6
7
explain select * from blog_post where id = 1;
8
                                    QUERY PLAN
9
──────────────────────────────────────────────────────────────────────────────────
10
 Index Scan using blog_post_pkey on blog_post  (cost=0.15..8.17 rows=1 width=120)
11
   Index Cond: (id = 1)
12
13
explain (analyze, buffers) select * from blog_post order by published_at desc limit 10;
14
                                                                      QUERY PLAN
15
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
16
 Limit  (cost=0.15..1.67 rows=10 width=120) (actual time=0.052..0.054 rows=1 loops=1)
17
   ->  Index Scan Backward using blog_post_published_at on blog_post  (cost=0.15..48.95 rows=320 width=120) (actual time=0.050..0.052 rows=1 loops=1)
18
 Planning Time: 0.186 ms
19
 Execution Time: 0.088 ms

Configuration#

Automatic tuning by compute size#

Supabase automatically sizes OrioleDB's memory and worker settings based on your project's compute add-on. These settings control the memory OrioleDB allocates for its internal page pools, undo log, and recovery workers, replacing the role that shared_buffers plays for the default heap storage engine. Supabase also reduces shared_buffers on OrioleDB projects, since OrioleDB tables use their own buffer pools instead.

The following settings are tuned automatically and aren't configurable:

• orioledb.main_buffers
• orioledb.free_tree_buffers
• orioledb.catalog_buffers
• orioledb.undo_buffers
• orioledb.xid_buffers
• orioledb.logical_xid_buffers
• orioledb.recovery_queue_size
• orioledb.recovery_pool_size
• orioledb.recovery_idx_pool_size
• orioledb.bgwriter_num_workers

To get larger pools and more recovery workers, upgrade your project's compute add-on. To see the values currently applied to your project, query pg_settings:

1
select name, setting
2
from pg_settings
3
where name like 'orioledb.%';

User-configurable settings#

A smaller set of OrioleDB settings has a user context and can be changed at the database or role level with SQL, the same way as other customizable Postgres settings.

For example, to enable compression for new OrioleDB tables in a database:

1
alter database "postgres" set "orioledb.default_compress" to 1;

Resources#

• Official OrioleDB documentation
• OrioleDB GitHub repository