Postgres in depth · Chapter 49 of 65

Beyond the B-tree

By the end of this chapter you can choose between Postgres's six index types (B-tree, hash, GIN, GiST, SP-GiST and BRIN) for a question, and explain what each one stores. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Inside Postgres
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

LCO's event log now records more than times: a JSON payload from the player, tags, the viewer's IP address, the window of video watched. Kabir wants a report on captioned lessons, Sana wants the events from the office network, Arjun wants one day out of nine months. A B-tree sorts whole values; it cannot answer "does this array contain captions?" or "does this window overlap 8 pm?" and on a huge append-only table it is bigger than it needs to be. Postgres has other index types for those questions.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    gin.sql: events with a tag

    The Check builds event_log exactly as the lesson does: 200,000 events, each with an array of tags; 1 event in 40 has the tag captions. Kabir's accessibility report counts the events whose tags contain captions (the query is in the starter file).

    Create an index this query can use. Afterwards its plan must use your index, and it must be a GIN index.

    Careful: a B-tree indexes a whole array as one value; it cannot answer "contains this element".

  2. Task 2

    brin.sql: a tiny index for time

    event_log is append-only, so its rows are stored in happened_at order. Arjun's daily report counts the events of 1 May 2026, India time (the query is in the starter file).

    Create a BRIN index this query can use. Afterwards its plan must use your index, a BRIN index of only a few pages (8 at most).

    Careful: BRIN works because the column follows the physical order of the table; keep its default range size.

  3. Task 3

    spgist.sql: who watched from the office?

    Sana wants the events that came from LCO's office network, 10.20.0.0/16: the rows whose client_ip is inside that network (the query is in the starter file).

    Create an SP-GiST index this query can use. Afterwards its plan must use your index, and it must be an SP-GiST index.

    Careful: the index type is chosen in the USING clause; a B-tree can serve this query too, but the task asks for SP-GiST.

  4. Task 4 · interview question

    Interview: operators.sql, what can this index answer?

    "Why does a query comparing payload's device field with = ignore the GIN index on payload?" Prove it from the catalog. The Check builds event_log with the GIN index event_log_payload_gin on payload.

    List the operators that index's operator class supports: the operator class's name, each operator's name, and the type of its right-hand side.

    Return three columns: opclass, operator and right_type. Order by operator, then right_type.

    Careful: the index records its operator class; operators belong to the class's family.

What you will learn

  • Access methods: what USING chooses, and operator classes.
  • Hash: equality only.
  • GIN: inverted indexes for arrays, jsonb and text search.
  • GiST and SP-GiST: trees for ranges, geometry and prefixes.
  • BRIN: a few kilobytes that summarise huge tables in physical order.

More in Inside Postgres

Build "Beyond the B-tree" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.