A naukri.com initiative
Dev
3w
339
Image Credit: Dev
Indexing for a Scalable Serialization Isolation Level
- YugabyteDB uses wait-on-conflict to prevent a serializable error, allowing transactions to commit without index.
- Serializable isolation can be scalable, but requires optimal schema and indexes with a good knowledge of how the database works.
- Indexes define the predicate locks for PostgreSQL Serializable Snapshot Isolation and the primary key defines the range locks used by YugabyteDB's Two-Phase Commit.
- The primary key should be defined in YugabyteDB instead of an additional index to minimize latency.
- In PostgreSQL, even if read intents are defined by scan predicates, they are stored in memory.
- YugabyteDB uses a primary key LSM tree to store rows, so intent and table row locks are stored together.
- If the table has a primary key defined as a unique constraint in YugabyteDB, both transactions can be committed without waiting for the other.
- There are no limitations because all secondary indexes are global and shared on their key in YugabyteDB.
- Primary key and secondary indexes perform a crucial role in using serializable isolation level.
- In PostgreSQL, predicate locks define the indexes, and in YugabyteDB, range locks define indexes.
Read Full Article
20 Likes
For uninterrupted reading, download the app