OrioleDB 概览

OrioleDB Postgres 扩展提供了一个可直接替换默认堆存储方法的存储引擎。它旨在提升 Postgres 的可扩展性和性能。

🌐 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 通过在高并发下消除共享内存缓存的瓶颈来解决 Postgres 的可扩展性限制。它还通过行级 WAL 日志优化了预写日志（WAL）的插入。这些改进在行业标准的 TPC-C 基准测试中带来了显著提升，该测试近似模拟了真实的事务工作负载。以下基准测试在 8xlarge 实例上进行，结果显示 OrioleDB 的性能超过了默认的 Postgres 堆方法，速度提升了 1.8 倍。

🌐 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 8xlarge instance and shows OrioleDB's performance outperforming the default Postgres heap method with a 1.8x speedup.

概念 #

🌐 Concepts

索引组织表 #

🌐 Index-organized tables

OrioleDB 使用索引组织表，其中表数据存储在索引结构中。这种设计消除了对单独堆存储的需求，减少了开销，并提高了主键查询的查找性能。

🌐 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

内存中的页面通过直接链接与存储页面相连。这让 OrioleDB 可以绕过 Postgres 的共享缓冲池，消除缓冲映射中相关的复杂性和争用问题。

🌐 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

多版本并发控制（MVCC）是通过撤销日志实现的。撤销日志保存了之前的行版本和事务信息，这样可以实现一致性读取，同时完全不需要进行表清理。

🌐 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 实现了写时复制检查点来高效地持久化数据。这种方法在检查点时只写入被修改的数据，相比传统的 Postgres 检查点减少了 I/O 开销，同时支持行级 WAL 日志记录。

🌐 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

创建 OrioleDB 项目 #

🌐 Creating OrioleDB project

创建项目时你可以选择 OrioleDB。你不能把 OrioleDB 添加到已有项目里，也不能以后再移除它。

🌐 You choose OrioleDB when you create a project. You can't add OrioleDB to an existing project or remove it later.

1. 创建一个新的 Supabase 项目。
2. 展开 高级配置。
3. 在 Postgres 类型 下，选择 带 OrioleDB 的 Postgres。
4. 完成创建这个项目。

创建表格 #

🌐 Creating tables

要使用 OrioleDB 存储引擎创建表，请执行标准的 CREATE TABLE 语句。默认情况下，它会使用 OrioleDB 存储引擎创建表。例如：

🌐 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 表总是有主键。如果没有明确定义，会使用 ctid 列创建一个隐藏的主键。另外，你还可以创建二级索引。

🌐 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

你可以使用标准 SQL 语句查询和修改 OrioleDB 表中的数据，包括 SELECT、INSERT、UPDATE、DELETE 和 INSERT ... ON CONFLICT。

🌐 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

你可以使用标准的 EXPLAIN 语句查看执行计划。

🌐 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
 Custom Scan (o_scan) on blog_post  (cost=0.15..8.17 rows=1 width=120)
11
   Forward index scan of: blog_post_pkey
12
   Conds: (id = 1)
13
14
explain (analyze, buffers) select * from blog_post order by published_at desc limit 10;
15
                                                                      QUERY PLAN
16
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
17
 Limit  (cost=0.15..1.67 rows=10 width=120) (actual time=0.052..0.054 rows=1 loops=1)
18
   ->  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)
19
 Planning Time: 0.186 ms
20
 Execution Time: 0.088 ms

配置 #

🌐 Configuration

按计算规模自动调优 #

🌐 Automatic tuning by compute size

Supabase 会根据你项目的 compute add-on 自动调整 OrioleDB 的内存和工作线程设置。这些设置控制 OrioleDB 分配给内部页面池、撤销日志和恢复工作线程的内存，取代了 shared_buffers 在默认堆存储引擎中的作用。Supabase 也会在 OrioleDB 项目上减少 shared_buffers，因为 OrioleDB 表使用自己的缓冲池。

🌐 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

要获得更大的计算池和更多的恢复工作者，升级你的项目计算附加组件。要查看当前应用于你项目的值，请查询 pg_settings：

🌐 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

OrioleDB 的一小部分设置有一个 user 上下文，可以像其他 可自定义的 Postgres 设置 一样，通过 SQL 在数据库或角色级别进行更改。

🌐 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.

例如，要在数据库中新建的 OrioleDB 表启用压缩：

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

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

资源 #

🌐 Resources

• 官方 OrioleDB 文档
• OrioleDB GitHub 仓库