--- title: "15 — Indexes and Schema Design: The Final Layer" uid: indexes-schema-design tags: ["community", "roadmap:postgresql-dba", "indexes", "materialized-views", "postgresql", "gist", "b-tree", "schema-design", "gin"] excerpt: "Six index types, each built for a different query shape — and schema design is matching structures (normalized, star, materialized views) to workload shapes. 'Add an index' becomes a precise choice." date: 2026-08-13T03:27:50+0000 source: https://www.aveshina.my.id/en/blog/indexes-schema-design --- "B-Tree is the only index, normalize everything" was my schema doctrine, and both halves were wrong. The framing that organized it: **Postgres has six index types, each engineered for a different query shape, and schema design is the discipline of choosing structures — normalized for integrity, star for analytics, materialized views for precomputed aggregates — that fit the workload** [1][2]. Once the index types stopped being interchangeable and the schema patterns mapped to workload shapes, "add an index" became a precise choice. These notes close the roadmap, and the last node is the one that keeps the whole ecosystem alive: contributing back. ## Why indexes matter Without an index, finding rows matching a condition means a **sequential scan** — reading every row in the table. With an index, Postgres looks up matching entries in a smaller, ordered structure and fetches only the relevant pages [1]. The tradeoff: indexes speed up reads but slow down writes (every insert/update maintains the index) and consume disk. The skill is indexing the columns that queries actually filter and join on, and nothing more. ## The six index types ```figure ``` - **B-Tree** — the default, and the right choice for most cases. Handles equality (=) and range (<, >, BETWEEN) on sortable values, and supports ORDER BY because entries are stored sorted [1]. - **Hash** — equality only, no range or ordering. Useful for simple point lookups where the smaller structure helps; historically less used since B-Tree covers it. - **GIN (Generalized Inverted Index)** — the index for "one row maps to many values." Powers array containment, JSONB key/path lookups, and full-text search (tsvector) [3]. The right choice whenever the predicate tests membership in a multi-valued column. - **GiST (Generalized Search Tree)** — a framework for custom indexing, used for geometric data, range overlap (the exclusion constraint on time ranges uses GiST), and PostGIS spatial queries [4]. - **SP-GiST (Space-Partitioned GiST)** — for non-balanced, space-partitioning structures like quadtrees and k-d trees; suits non-uniform spatial or hierarchical data [5]. - **BRIN (Block Range Index)** — stores a small summary (min/max) per block of pages, tiny compared to a B-Tree. Ideal for huge tables whose data is naturally ordered by the indexed column (like a timestamp on an append-only events table), where it accelerates range scans at minimal cost [6]. ## Specialized index forms On top of the types, three forms shape *which rows* an index covers: - **Partial index** — CREATE INDEX ... WHERE active = true indexes only the matching rows. Smaller and faster when queries always include the predicate. - **Expression index** — CREATE INDEX ON users (lower(email)) indexes the result of an expression, speeding up WHERE lower(email) = ?. - **Composite index** — CREATE INDEX ON orders (user_id, created_at) indexes multiple columns together, useful for queries that filter or sort on both. ## Schema design patterns Indexing is one half of design; the other is the table structures themselves [2]: - **Normalized** — minimize redundancy via the normal forms; the default for transactional (OLTP) data, protecting integrity. - **Denormalized** — duplicate data to avoid joins; for read-heavy cases where the join cost dominates. - **Star schema** — a central fact table (measurable events) surrounded by dimension tables (the context), the standard for data warehousing [2]. - **Snowflake schema** — a star with the dimension tables further normalized; saves space at the cost of more joins. - **Materialized views** — CREATE MATERIALIZED VIEW precomputes and stores a query result, refreshed on demand. The right tool when many queries read the same heavy aggregate. The mapping: OLTP workloads → normalized + B-Tree indexes on filter/join columns. Analytics → star/snowflake + materialized views + BRIN on time-ordered fact tables + GIN for full-text search. The structure follows the workload. ## The closing node: get involved The roadmap's last section is the one that's easy to skip but matters most [7]. Postgres is open-source, developed by a community, and its quality depends on contributors. The on-ramps, roughly in order of effort: - **Mailing lists** — pgsql-general for usage, pgsql-hackers for core development, pgsql-novice for beginners. Reading the archives is itself a contribution to one's own understanding. - **Bug reporting and testing** — reporting real bugs with a reproducible case, and testing patches in the commitfests, materially improves quality. - **Reviewing patches** — the commitfest process runs on community review; even non-core contributors can review correctness, performance, and docs. - **Writing patches** — fixing bugs or adding features, following the coding standards, submitting via the mailing list. - **Documentation and translations** — improving the docs is high-leverage and welcomes subject-matter experts. - **Support and advocacy** — answering questions, giving talks, writing posts like these. The reason this belongs in a learning-notes series: the deepest understanding comes from engaging with the project, not just consuming it. Reading pgsql-hackers discussions on, say, a new vacuum behavior, teaches the internals faster than any tutorial. ## How I use this The index-type-by-query-shape matrix is the daily habit. For equality and range on regular columns, B-Tree. For JSONB, arrays, and full-text, GIN. For range-overlap and exclusion constraints, GiST. For huge append-only tables ordered by time, BRIN. I add partial and expression indexes when the query pattern justifies them, and I drop indexes that aren't used — they cost writes. Schema-side, I normalize for transactional data, reach for star schemas and materialized views in analytics, and partition time-series tables. And, closing the loop the roadmap insists on, I read the mailing lists and contribute back where I can — because the database I rely on is built in the open by people who showed up. Mastery of Postgres ends where it began: with the community that builds it. ## References [1] PostgreSQL Global Development Group, "Index Types," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/indexes-types.html](https://www.postgresql.org/docs/current/indexes-types.html) [2] Timescale, "How to design your PostgreSQL database: two schema examples," 2024. [Online]. Available: [https://www.timescale.com/learn/how-to-design-postgresql-database-two-schema-examples](https://www.timescale.com/learn/how-to-design-postgresql-database-two-schema-examples) [3] PostgreSQL Global Development Group, "GIN Introduction," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/gin-intro.html](https://www.postgresql.org/docs/current/gin-intro.html) [4] PostgreSQL Global Development Group, "GiST Indexes," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/gist.html](https://www.postgresql.org/docs/current/gist.html) [5] Sling Academy, "PostgreSQL SP-GiST," 2024. [Online]. Available: [https://www.slingacademy.com/article/postgresql-sp-gist-space-partitioned-generalized-search-tree/](https://www.slingacademy.com/article/postgresql-sp-gist-space-partitioned-generalized-search-tree/) [6] PostgreSQL Global Development Group, "BRIN Indexes," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/brin.html](https://www.postgresql.org/docs/current/brin.html) [7] PostgreSQL Global Development Group, "Mailing Lists," 2024. [Online]. Available: [https://www.postgresql.org/list/](https://www.postgresql.org/list/) ```quiz Q: Which index type is the right default for equality and range queries on a sortable column, and supports ORDER BY? - BRIN - B-Tree correct: 1 explain: B-Tree is the default and handles equality, range, and ordering because entries are stored sorted. It's the right first choice for most columns. Q: You need to index a JSONB column for key/path lookups and an array column for containment. Which index type? - BRIN - GIN correct: 1 explain: GIN (Generalized Inverted Index) maps one row to many values, powering JSONB, array containment, and full-text search. It's the index for multi-valued columns. Q: What is a partial index, and when is it useful? - An index on a subset of rows matching a WHERE clause; useful when queries always include that predicate, keeping the index small - An index that is only sometimes used by the planner correct: 0 explain: A partial index (CREATE INDEX ... WHERE active = true) indexes only matching rows. It's smaller and faster, ideal when queries consistently filter on the same condition. Q: A huge append-only events table ordered by created_at needs faster range scans with minimal index size. Best choice? - B-Tree on created_at - BRIN on created_at correct: 1 explain: BRIN stores a small min/max summary per block range. On naturally-sorted data like a timestamp on append-only events, it accelerates range scans at a fraction of a B-Tree's size. Q: Why does the roadmap end with "get involved in development"? - It's optional filler - Because Postgres is open-source community-developed, and contributing (mailing lists, patches, review, docs) is both the deepest learning path and what keeps the project alive correct: 1 explain: The closing node reframes mastery: engaging with the community — reading pgsql-hackers, reviewing patches, improving docs — teaches the internals fastest and sustains the database the learner relies on. ```