orioledb VS pg_hint_plan

hey hn, supabase ceo here

we've been fans of Oriole for a while now and have been long-time supporters

in case you're jumping straight to the comments: OrioleDB is a table storage extension for Postgres. it acts as a drop-in replacement for the default postgres storage engine using the Table Access Method APIs (pluggable storage). the storage engine changes the representation of table data on disk. its architecture is designed to take advantage of modern hardware like SSDs and NVRAM. it implements MVCC, the feature that allows allows multiple connected users to see different versions of the data depending on when their transaction started, via an UNDO log rather than tuple versioning.

one caveat: it requires several patches to the postgres core to expand on the type of features external storage engines extensions can implement. for this reason it could be a while before you see this land as a default engine on supabase. we will probably make it available as an option for customers who want to experiment - no timeline is decided yet.

finally, we have been working with the team on decoupled storage and compute [0]. this is experimental but promising, especially with some recent advances in S3 (specifically Express One Zone [1]). we have a demonstration in the blog post.

i'll message Alexander in case there are any technical questions

[0] https://github.com/orioledb/orioledb/blob/main/doc/usage.md#...

[1] https://aws.amazon.com/s3/storage-classes/express-one-zone/

When I saw "cloud native" I was expecting S3-ish the way Neon does it but they say it's experimental: https://github.com/orioledb/orioledb/blob/beta4/doc/usage.md... and for them to say "beta, don't use in production" and then a separate "experimental" label must make it really bad

There are some interesting things in development to potentially solve that problem.

Here's a recent HN submission about OrioleDB of the more promising ones: https://news.ycombinator.com/item?id=36740921

Source code: https://github.com/orioledb/orioledb

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 took a look at https://github.com/orioledb/orioledb which is a project attempting to remedy some of Postgres' shortcomings, including MVCC. It looks like they're doing something similar to MySQL with a redo log, as well as some other optimizations. So maybe this is the answer.

You don’t need a database written (or rewritten in Rust). we’re working to make Postgres scalable for the next decade too https://github.com/orioledb/orioledb

> I’d love to see B-Tree primary storage option. Aka store the row data inside the primary index.

It is coming: https://github.com/orioledb/orioledb

sorry to underwhelm!

if you like Neon, then I imagine you like their database branching model? On Friday we announced[0] our 500K investment into OrioleDB, who are working on branching[1], with the plan to upstream these changes into Postgres core.

It would be possible for us to run a fork of Postgres today which supports branching, but our long-term view is that developers would prefer a non-forked version of Postgre (to mitigate any risk of lock-in). So we will work on adding branching to Postgres core in the background, which will be a benefit to the entire Postgres ecosystem.

[0] Announcement:https://supabase.com/blog/supabase-series-b#where-were-going

[1] https://github.com/orioledb/orioledb/wiki/Database-branching

Okay so it isn't entirely clear to me, can the pg_hint_plan extension (linked in the OP) do the simple thing where we specify, for each table, which index to use?

I can't find it here

https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hin...

Because, the mssql WITH(INDEX()) is simple and intuitive. This hint table stuff seems complicated, and it's unclear to me if they can do the simple thing

pg_hint_plan —— Give PostgreSQL ability to manually force some decisions in execution plans. https://github.com/ossc-db/pg_hint_plan

I really wish the PostgreSQL core team would acknowledge that their stance on that hurts more than helps. Even Oracle with decades of engineering behind it doesn't get execution plans correct 100% of the time and provides a way to tune query execution via hints.

However, TIL that https://github.com/ossc-db/pg_hint_plan exists so that will probably become a standard thing I deploy.

With PostgreSQL, the extension to do it, pg_hint_plan is really good, but not widely used because not included in the core, not even in contrib. The consequence is that people install it only when needing it, without the time to learn hot to hint properly, may think that "my hint is not used" and give up.

cat > Dockerfile <<'DOCKERFILE' # install pg_hint_plan from rpm FROM docker.io/postgres:14 ADD https://github.com/ossc-db/pg_hint_plan/releases/download/REL14_1_4_0/pg_hint_plan14-1.4-1.el8.x86_64.rpm . RUN apt-get update -y ; apt-get install -y alien wget ; alien ./pg_hint_plan*.rpm ; dpkg -i pg-hint-plan*.deb # copy the minimal files to a postgres image FROM docker.io/postgres:14 COPY --from=0 /usr/pgsql-14/share/extension/pg_hint_plan.control /usr/share/postgresql/14/extension COPY --from=0 /usr/pgsql-14/share/extension/pg_hint_plan--1.4.sql /usr/share/postgresql/14/extension COPY --from=0 /usr/pgsql-14/lib/pg_hint_plan.so /usr/pgsql-14/lib/pg_hint_plan.so /usr/lib/postgresql/14/lib ENV PGPASSWORD=postgres CMD ["postgres","-c","shared_preload_libraries=pg_hint_plan,pg_stat_statements"] DOCKERFILE docker build -t pachot/pg_hint_plan --platform=linux/amd64 . docker push pachot/pg_hint_plan

there is a maintained index hint extension: https://github.com/ossc-db/pg_hint_plan - at least as far as 13 (and likely 14).

if we're going to talk about index functionality that would be good and effective for Postgres, an index across all partitioned tables (both normal and unique) would be very much welcomed.

the problem is finding someone to maintain it for life.