Bridged Indexes in OrioleDB: architecture, internals and everyday use?
a year ago
- #PostgreSQL
- #Database Indexing
- #OrioleDB
- OrioleDB beta10 introduces bridged indexes to support non-B-tree indexes on OrioleDB tables.
- Bridged indexes are necessary because OrioleDB stores rows in a B-tree on the primary key and uses an undo log for MVCC, which is incompatible with PostgreSQL's Index Access Methods.
- The bridge index layer includes a virtual 'iptr' column that maps to primary-key values, allowing PostgreSQL indexes to work with OrioleDB.
- Bridged indexes automatically handle MVCC and logical updates/deletes, unlike PostgreSQL indexes which rely on VACUUM for cleanup.
- Users can create bridged indexes automatically or manually, with options to control the bridge layer for performance or testing purposes.
- Performance considerations include an extra lookup hop for bridged indexes and additional overhead for updates on columns involved in bridged indexes.
- Bridged indexes provide access to PostgreSQL's rich IndexAM ecosystem (GIN, GiST, etc.) without requiring extensions to be rewritten for OrioleDB.
- The feature allows users to balance performance and functionality, with future updates expected to enhance OrioleDB's capabilities.