4 ms·

The graphs for OrioleDB looks very impressive. Can someone give a counter argument to switching to this?

by boxed 1y ago

wwizo 1y ago
Oreole is not a plug-and-play yet. From their docs ( https://www.orioledb.com/docs https://www.orioledb.com/docs ) > OrioleDB currently requires a set of patches to PostgreSQL to enhance the pluggable storage API and other PostgreSQL subsystems. All of these patches have been submitted to the PostgreSQL community and are under review.
- mattashii 1y ago
  (Opinions are my own, not of my employer) I think that "under review" claim is doing some very heavy lifting, especially when it relates to their changes to index tuple lifecycle management. The patches that have been submitted are unlikely to get committed in full anytime soon, even after substantial changes to the patches' designs. PostgreSQL just has not been designed for what OrioleDB is doing, and forcing OrioleDB's designs into PostgreSQL upstream would a lot of (very) sharp edges that the community can't properly test without at least a baseline implementation - which critically hasn't been submitted to upstream. Examples of these sharp edges are varsized TIDs, MVCC-owning indexes, and table AM signalled index inserts. There are certainly ideas in OrioleDB's designs that PostgreSQL can benefit from (retail index tuple deletion! self-clustering tables!), but these will need careful consideration in how this can be brought into the project without duplicating implementations at nearly every level. A wholesale graft of a downstream fork and then hoping it'll work out well enough is just not how the PostgreSQL project works.
  - ksec 1y ago
    Oh this dampens my expectations, I was under the wrong impression that it will stabilise outside of Postgres before it is merged.
Sesse__ 1y ago
You get basically most of the advantages of a B-tree-oriented table, but also most of the disadvantages AFAIK. In particular, any index lookup/scan is going to take twice as long (index blocks don't point to the place on disk, they just contain the primary key and then you need to go lookup _that_ in the actual table).
- akorotkov 1y ago
  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-pointers https://www.orioledb.com/docs/architecture/overview#dual-poi...), and pages are read lock-less (https://www.orioledb.com/docs/architecture/overview#page-structure 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 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.
  - Sesse__ 1y ago
    It would be interesting to see how OrioleDB does with more OLAP-like loads. From when I spent a lot of time benchmarking this, the indirect index design was _the_ main reason why MySQL+InnoDB was losing significantly to Postgres on TPC-H (well, DBT-3).[1] There was a lot of working around it with covering indexes etc.. Of course, the flip side of the coin is that if you do an UPDATE of a row in the presence of a secondary index, and the UPDATE doesn't touch the key, then you don't need to update the index(es) at all. So it really depends on how much you update rows versus how often you index-scan them IME. [1] TPC-H doesn't have difficult enough queries to really stress the planner, so it mattered comparatively less there than in other OLAP work.

The graphs for OrioleDB looks very impressive. Can someone give a counter argument to switching to this?

by boxed 1y ago

The graphs for OrioleDB looks very impressive. Can someone give a counter argument to switching to this?

Oreole is not a plug-and-play yet. From their docs ( https://www.orioledb.com/docs https://www.orioledb.com/docs ) > OrioleDB currently requires a set of patches to PostgreSQL to enhance the pluggable storage API and other PostgreSQL subsystems. All of these patches have been submitted to the PostgreSQL community and are under review.

mattashii 1y ago
(Opinions are my own, not of my employer) I think that "under review" claim is doing some very heavy lifting, especially when it relates to their changes to index tuple lifecycle management. The patches that have been submitted are unlikely to get committed in full anytime soon, even after substantial changes to the patches' designs. PostgreSQL just has not been designed for what OrioleDB is doing, and forcing OrioleDB's designs into PostgreSQL upstream would a lot of (very) sharp edges that the community can't properly test without at least a baseline implementation - which critically hasn't been submitted to upstream. Examples of these sharp edges are varsized TIDs, MVCC-owning indexes, and table AM signalled index inserts. There are certainly ideas in OrioleDB's designs that PostgreSQL can benefit from (retail index tuple deletion! self-clustering tables!), but these will need careful consideration in how this can be brought into the project without duplicating implementations at nearly every level. A wholesale graft of a downstream fork and then hoping it'll work out well enough is just not how the PostgreSQL project works.
- ksec 1y ago
  Oh this dampens my expectations, I was under the wrong impression that it will stabilise outside of Postgres before it is merged.

Sesse__ 1y ago

You get basically most of the advantages of a B-tree-oriented table, but also most of the disadvantages AFAIK. In particular, any index lookup/scan is going to take twice as long (index blocks don't point to the place on disk, they just contain the primary key and then you need to go lookup _that_ in the actual table).

akorotkov 1y ago
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-pointers https://www.orioledb.com/docs/architecture/overview#dual-poi...), and pages are read lock-less (https://www.orioledb.com/docs/architecture/overview#page-structure 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 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.
- Sesse__ 1y ago
  It would be interesting to see how OrioleDB does with more OLAP-like loads. From when I spent a lot of time benchmarking this, the indirect index design was _the_ main reason why MySQL+InnoDB was losing significantly to Postgres on TPC-H (well, DBT-3).[1] There was a lot of working around it with covering indexes etc.. Of course, the flip side of the coin is that if you do an UPDATE of a row in the presence of a secondary index, and the UPDATE doesn't touch the key, then you don't need to update the index(es) at all. So it really depends on how much you update rows versus how often you index-scan them IME. [1] TPC-H doesn't have difficult enough queries to really stress the planner, so it mattered comparatively less there than in other OLAP work.