akorotkov

We keep going. Planning GA till the end of this year. Only a couple of missing features left. The main focus is stability.

Exclusion constraints should work well for this case. On any transaction isolation level.

https://www.postgresql.org/docs/current/ddl-constraints.html...

We will eventually add the SERIALIZABLE isolation level to OrioleDB, but right now that's not our highest priority. Let me explain why. At first, SSI (serializable snapshot isolation) in PostgreSQL comes with significant shortcomings, including.

1) Overhead. SSI implies a heavyweight lock on any involved index page or heap tuple (even for reads). The overhead of SSI was initially measured at ~10%, but nowadays, scalability has gone much farther. These many HW-locks could slow down in multiple times a typical workload on a multicore machine.

2) SSI needs the user to be able to repeat any transaction due to serialization failure. Even a read-only transaction needs to be DEFERRABLE or might be aborted due to serialization failure (it might "saw impossible things" and needs a retry).

In contrast, typically it's not hard to resolve the concurrency problems of writing transactions using explicit row-level and advisory locks, while REPEATABLE READ is enough for reporting. Frankly speaking, during my whole career, I didn't see a single case where SERIALIZABLE isolation level was justified.

Yep, regular PostgreSQL indexes point to a heap location (block number + offset). And it is the same for primary and secondary indexes.

Thank you, that would be on the TODO list.

This is generally true, but there are some additional aspects.

1. With PostgreSQL heap, you need to access the heap page itself. And it's not for free. It goes all through the buffer manager and other related components.

2. In OrioleDB, we have a lightweight protocol to read from pages. In-memory pages are connected using direct links (https://www.orioledb.com/docs/architecture/overview#dual-poi...), and pages are read lock-less (https://www.orioledb.com/docs/architecture/overview#page-str...). Additionally, tree navigation for simple data types skips both copying and tuple deforming (https://www.orioledb.com/blog/orioledb-fastpath-search).

According to all of the above, I believe OrioleDB still wins in the case of secondary key lookup. I think this is indirectly confirmed by the results of the TPC-C benchmark, which contains quite a lot of log of secondary key lookups. However, this subject is worth dedicated benchmarking in the future.

Hey HN, I'm the creator of OrioleDB, an extension for PostgreSQL that serves as a drop-in replacement for the default Heap storage engine. It is designed to address scalability bottlenecks in PostgreSQL's buffer manager and reduce the WAL, enabling better utilization of modern multi-core CPUs and high‑performance storage systems. We are getting closer to GA.

This blog post is dedicated to describing a new OrioleDB optimization: ordered insert optimization. When multiple processes are trying to insert into the same page, the first one that grabs the lock does the job for the others. That dramatically cuts the locker waiting time and thus improves the overall throughput. This is very critical for IoT, time series and others.

We would love for more people to test and benchmark OrioleDB. You can check how ordered insertion optimization would help your workload. The fastest way to do that is to use the Docker image provided (please note you need to use a specific tag):

    docker run -d --name orioledb -p 5432:5432 orioledb/orioledb:ordered-inserts-pg17

We're planning to reach GA this year. Pushing all the patches to PostgreSQL core and making OrioleDB a pure extension will take more time.

Currently, OrioleDB can use GIN indexes via index bridging mechanism. https://www.orioledb.com/blog/orioledb-bridged-indexes But more effective native analogues for GIN/RUN are in our roadmap.

Hey HN, I'm the creator of OrioleDB, an extension for PostgreSQL that serves as a drop-in replacement for the default Heap storage engine. It is designed to address scalability bottlenecks in PostgreSQL's buffer manager and reduce the WAL, enabling better utilization of modern multi-core CPUs and high‑performance storage systems. We are getting closer to GA.

This blog post is dedicated to describing a new OrioleDB optimization: fastpath search, an inter-page search that bypasses page copying and tuple deformation for certain fixed-length datatypes.

We would love for more people to test and benchmark OrioleDB. You can check how fastpath search would help your workload. The fastest way to do that is to use the Docker image provided:

    docker run -d --name orioledb -p 5432:5432 orioledb/orioledb

Additionally, OrioleDB beta12 features a new fastpath tree search, which can accelerate workloads with intensive key-value lookups by up to 20%. Stay tuned for a new blog post about this later this week.

That kind of exists thanks to NeonDB?

This is unrelated to NeonDB. OrioleDB has been acquired by Supabase. https://supabase.com/blog/supabase-acquires-oriole

OrioleDB solve the vacuum problem with the introduction of the undo log.

Way more than just this!

With some work you can run OrioleDB AND neon storage and get benefits of both.

This would require significant design work, given that significant OrioleDB benefits are derived from row-level WAL.

That's also so cool we are getting externally validated! https://x.com/MinisterOfEng/status/1864793214282019065 My kudos to MinisterOfEng!

Additionally, PostgreSQL requires N^2 of memory depending on the number of connections,

For sure, not all the PostgreSQL memory is N^2. AFAIR, just a couple of components, including deadlock decoding, require a quadratic amount of memory. Normally, they are insignificant but growing fast if you are rising max_connections.

Thank you for your feedback! We tried to enable think time with go-tpc, thanks to @pashkinelfe. That leaves us with 1 tpmC per connection, growing linearly up to ~300 connections for both heap and OrioleDB. So, in order to experience a storage bottleneck, we would need dozens of thousands of connections. Given PostgreSQL runs a process per connection, that would be more of a stress test for the Linux kernel. Additionally, PostgreSQL requires N^2 of memory depending on the number of connections, and it becomes sensible at this scale. All of that could be resolved by migrating PostgreSQL to co-routines and resolving memory requirement issues. However, this is currently out of scope for us. Could you recommend another benchmark to reveal storage bottlenecks, given that TPC-B and YCSB are too trivial?

Thank you, your feedback is appreciated.

The important design issue about building active-active multi-master on the base of raft protocol is about being able to apply changes locally without immediately putting them into a log (without sacrificing durability). MySQL implements a binlog, which is separate from a log, to ensure durability. OrioleDB implements copy-on-write checkpoints and row-level WAL. That gives us a chance to implement MM and durability using a single log.

Some notable benchmarks from the OrioleDB beta7 release:

* 5.5x Faster at 500 Warehouses: In TPC-C benchmarks with 500 warehouses, OrioleDB outperformed PostgreSQL's default heap tables by 5.5 times. This highlights significant gains in workloads that stress shared memory cache bottlenecks.

* 2.7x Faster at 1000 Warehouses: Even when the data doesn't fit into the OS memory cache (at 1000 warehouses), OrioleDB was 2.7 times faster. Its index-organized tables improve data locality, reducing disk I/O and boosting performance.

Try it yourself:

Clone the OrioleDB repository from GitHub and follow the build instructions, or use their Docker image. https://github.com/orioledb/orioledb#installation Alternatively, run OrioleDB on Supabase. Read the blog post for more details. https://supabase.com/blog/orioledb-launch

Run your own workloads or existing benchmarks like go-tpc or HammerDB to see the performance differences firsthand. We Would love to hear about others' experiences with OrioleDB, especially in production-like environments or with different workloads.

This was driven by Andres Freund for pg12. Please, check this. https://anarazel.de/talks/2018-10-25-pgconfeu-pluggable-stor... However, the current table AM API in many aspects assumes heap-like storage. This is why OrioleDB comes with a patchset we're intended to upstream to PostgreSQL.

Sure, there are downsides. 1. The replay of WAL records becomes somewhat more CPU-expensive. But at the same time, OrioleDB can do that in parallel. 2. Replace becomes not the file-level equivalent of primary. Thus, you, for instance, can't continue file-level incremental backup started taken from the primary with the replica. Nevertheless, I strongly believe that row-level WAL is a great win on average, and opens brilliant perspectives for further developments.

I'm happy to join the Supabase. Working in Supabase gives me a unique opportunity to find use cases for OrioleDB and scale up the development team. I'll be around to answer any technical questions.

Does the author have any info on how they plan to implement these more complex (but extremely useful) index methods?

Regarding GiST analogue my plan is to build B-tree over some space-filling curve. Also, I'm planning to add union keys to the internal pages to make search over this tree faster and simpler.

Regarding GIN analogue, it would be still possible to compress the posting lists. The possible option would be to associate undo record not with posting list item, but with the whole posting list.

Regarding BRIN, I don't think we can do some direct analogue since we're using index-organized tables. But we can do something interesting with union keys in the internal pages of PK.

This doesn't even consider the issues that might appear if the ordering rules (collation) change.

You're right, collation issue is serious. We will need to stick every collation-aware index to particular libicu collation version, before we go to GA.

- Row-level anything introduces write alignment and fsync alignment problems; pages are easier to align than arbitrary-sized rows

OrioleDB uses row-level WAL, but still uses pages. The row-level WAL becomes possible thanks to copy-on-write checkpoints, providing structurally consistent images of B-tree. Check the architecture docs for details. https://github.com/orioledb/orioledb/blob/main/doc/arch.md

- PostgreSQL is very conservative (maybe extremely) conservative about data safety (mostly achieved via fsync-ing at the right times), and that propagates through the IO stack, including SSD firmware, to cause slowdowns

This is why our first goal is to become pure extension. Becoming part of PostgreSQL would require test of time.

- MVCC is very nice for concurrent access - the Oriole doc doesn't say with what concurrency are the graphs achieved

Good catch. I've added information about VM type and concurrency to the blog post.

- The title of the Oriole doc and its intro text center about solving VACUUM, which is of course a good goal, but I don't think they show that the "square wave" graphs they achieve for PostgreSQL are really in majority caused by VACUUM. Other benchmarks, like Percona's (https://www.percona.com/blog/evaluating-checkpointing-in-pos...) don't yield this very distinctive square wave pattern.

Yes, it's true. The square patters is because of checkpointing. The reason of improvements here is actually not VACUUM, but modification of relevant indexes only (and row-level WAL, which decreases overall IO).

I'm seeing OrioleDB as a future engine for PostgreSQL. I'd like to see it as the default engine. However, the changes in OrioleDB are too big to be made incrementally. This is why I'm comparing the current PostgreSQL engine (with more than just heap, but many other subsystems as well) with OrioleDB.

Please, check this. https://supabase.com/blog/postgres-pluggable-strorage https://www.pgcon.org/events/pgcon_2023/schedule/session/470... Pushing patches to PostgreSQL Core requires a lot of work. But there is a progress already: 5k lines patchset to PG 14, and 2k lines patchset to PG 16.

E: I don't see in the article when rows get evicted from the undo logs.

The undo records are truncated once they aren't needed for any transaction.

If when they are no longer needed, I'm not sure where the improvement comes from because it should be similar amount of bookkeeping?

It depends on what exactly is "bookkeeping". If we consider amount of work, then improvement comes because old undo records can be just bulk deleted very cheap (corresponding files get unliked). No vacuum scan is needed. If we consider amount of space occupied, then indeed the same amount of versions take the same amount of space. But saving old versions of rows in the separate storage can save their primary storage from long-term degradation. Also, note that OrioleDB implements automatic merging of sparse pages.

If it's a circular buffer that can ran out of space like Oracle does it that would mean under high write load long-running transactions starts to fail which is pretty unpleasant.

OrioleDB implements in-memory circular buffer for undo logs. Once circular buffer can't handle all the undo records, least recent records are evicted to the storage. Currently, we don't place limitation on the site of undo logs. Undo records are kept while any transaction can need them. So, no "Snapshot Too Old" errors. However, we can consider implementing this Oracle-like error as an option, which allows to limit the undo size.

Also, please, check the architecture documentation of github (if didn't already). https://github.com/orioledb/orioledb/blob/main/doc/arch.md

Right now OrioleDB is an extension, which comes with PostgreSQL core patch. The mid-term goal for OrioleDB is to become a pure extension. The long-term goal is to make OrioleDB part of PostgreSQL core.

Ok. I have to also mentions that none of the issues I've mentioned are consequences of in-memory nature of index. Both are issues of implementation.

This blog post makes me uneasy. The pg_embedding code on github gives impression of PoC, while blog post creates impression that pg_embedding is ready for use.

If we consider pg_embedding ready for use, why don't you compare with pgvector:

1) The need to rebuild index every instance restart,

2) Replication support?

How to stop the exhausting data freezing in PostgreSQL?