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 基准测试中带来了显著提升，该测试近似模拟了真实的事务工作负载。以下基准测试在 c7g.metal 实例上进行，显示 OrioleDB 的性能超过了默认的 Postgres 堆方法，速度提升了 3.3 倍。

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

🌐 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

你可以通过在 Supabase 仪表板中启用扩展来开始使用 OrioleDB。 要开始使用 OrioleDB，你需要创建一个新的 Supabase 项目，并选择 OrioleDB Public Alpha 版本的 Postgres。

🌐 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

要使用 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
 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

资源 #

🌐 Resources

• 官方 OrioleDB 文档
• OrioleDB GitHub 仓库