Domain 2 of 4 · Chapter 8 of 12

Model PostgreSQL schemas, data types, and indexes

Unlock the complete study guide + 1,040 practice questions across 16 full exams.

Bundled into the existing Developing AI Cloud Solutions on Azure premium course — no separate purchase.

14-day money-back guarantee — no questions asked.

Included in this chapter:

  • What the schema decides and what the index decides
  • Choosing column types for data you will query
  • Surrogate keys and index locality
  • Constraints and the indexes that come with them
  • Normalizing, and when to denormalize the retrieval path
  • The index types PostgreSQL offers
  • Column order in a multicolumn index
  • Indexing fewer rows or more columns
  • Confirming the index is used: reading EXPLAIN
  • What an index costs, and finding the ones not earning it

Choosing an index type by the operator your query uses

PropertyB-treeGINGiSTBRIN
Operators it serves< <= = >= > and ordered scansContainment and membership: jsonb @>, array overlap, full-text @@Whatever the operator class implements: range overlap, geometric, distance orderingRange comparisons resolved against block-range summaries
Column shape it suitsAny scalar with a sort orderValues holding multiple component values (jsonb, arrays, tsvector)Ranges, geometry and other extensible typesColumns well correlated with the physical order of the rows
Can order results (ORDER BY, nearest neighbour)Yes, ordered scansNoYes, including nearest-neighbour orderingNo
Can be declared UNIQUEYes, and only B-tree canNoNo, but it backs exclusion constraintsNo
Supports INCLUDE payload columnsYesNoYesNo
Created automatically by a constraintYes, by PRIMARY KEY and UNIQUENoNoNo

Decision tree

Start from the operator in the query, not the type of the columnContainment or membership?jsonb @>, array overlap, full textyesGINone entry per component valuenoRange, geometry, distance order?exclusion constraints tooyesGiSTone framework, many strategiesnoVector similarity on embeddings?top-k nearest neighboursyesExtension access methodpgvector: ivfflat, hnsw, diskannnoColumn tracks physical row order?large, append-only tablesyesBRINblock-range summaries, tinynoB-tree, the default= < <= > >=, ORDER BY, UNIQUE
Cheat sheet

Cheat sheet

  • Prefer jsonb over json for stored, queryable documents
  • timestamptz stores a timezone-aware instant normalized to UTC
  • Use uuid for distributed keys and numeric for exact decimals, not float
  • A surrogate key can be a sequence-backed IDENTITY column or a uuid, with different locality
  • PRIMARY KEY and UNIQUE constraints automatically create a supporting B-tree index
  • Normalize to remove redundancy, denormalizing hot retrieval metadata selectively
  • B-tree is the default index for equality, range, and ORDER BY on scalar columns
  • GIN indexes jsonb, arrays, and full-text tsvector for containment and membership
  • GiST is the extensible framework for ranges, geometry, and nearest-neighbor access
  • A composite index is most efficient when the query constrains its leading columns
  • Partial indexes shrink the index; covering (INCLUDE) indexes enable index-only scans
  • Every index must be maintained on writes, so index only for real query patterns

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. JSON Types
  2. Supported versions of PostgreSQL in Azure Database for PostgreSQL flexible server
  3. Date/Time Types
  4. Numeric Types
  5. UUID Type
  6. Identity Columns
  7. UUID Functions
  8. UUID Functions (PostgreSQL 17)
  9. Constraints
  10. Unique Indexes
  11. Partial Indexes
  12. Architecture strategies for optimizing data performance (PE:08) Well-Architected
  13. CREATE INDEX
  14. Optimize performance when using pgvector in Azure Database for PostgreSQL flexible server
  15. Index Types
  16. GIN and GiST Index Types for Text Search
  17. Multicolumn Indexes
  18. Indexes and ORDER BY
  19. Index-Only Scans and Covering Indexes
  20. Using EXPLAIN
  21. Introduction to Indexes
  22. Heap-Only Tuples (HOT)
  23. Autonomous tuning in Azure Database for PostgreSQL flexible server