GIN, GiST, BRIN, hash
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
pick the right index type for a workload.
Planned topics
- GIN: many keys per row;
jsonb, arrays, full-text search. Slower writes, fast containment lookups. - GiST: geometric and range types, nearest-neighbour, exclusion constraints.
- BRIN: tiny index of min/max per block range; good for huge, naturally ordered tables (append-only
created_at). - Hash: equality only; B-tree is usually just as good.
- Default to B-tree; use the others when the operator demands it.
Draft outline details
Demonstration
a table of workload to index type, with CREATE INDEX ... USING gin (attrs jsonb_path_ops) and USING brin (created_at).
Check your understanding
Which type suits a 10 M-row append-only table filtered by date range with minimal space?
Show answer
BRIN.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.