Indexing Techniques: PostgreSQL Indexes for Developers
Choose the right PostgreSQL index for a query and confirm in the execution plan that it is used. Know what each index costs on writes and maintenance, and spot the ones that will never be used.
- €200per person, before VAT
- 1 live session2½ hours
- Theory and live demoswith Q&A
- Live online via Zoomin English
- Certificate of Attendance
Upcoming dates
What you'll learn
How indexes work, when they help, and what they cost, from the page layout on disk to the index types PostgreSQL offers and the plans that use them.
The course covers
- How PostgreSQL stores rows, and why an index speeds up a selective query
- Sequential, index, index-only and bitmap scans, and how the planner chooses between them
- B-tree, Hash, BRIN, GIN, GiST and SP-GiST indexes, and pgvector for vector search
- Indexes for filtering, sorting, grouping, joins and JSON data
- Multicolumn, partial, expression and covering (INCLUDE) indexes
- What indexes cost: write amplification, HOT updates, locking and CREATE INDEX CONCURRENTLY
- Keeping indexes healthy: bloat, rebuilding, and finding unused indexes with pg_stat_user_indexes
Prerequisites
For developers who add indexes to fix slow queries and want to know which index to create, and when not to create one.
You should be comfortable writing SQL. The session is self-contained: no other session of the track is required.
For details see the course terms & conditions.
Syllabus
1Data access in PostgreSQL
- Page layout, tuples and the ctid system column.
- Sequential scans, random access and shared_buffers.
- Linear against logarithmic lookups.
2Index access methods
- Index scan, index-only scan and the visibility map.
- Bitmap scans, lossy bitmaps and rechecks; combining indexes.
- How the planner chooses between access methods.
3Index types and use cases
- B-tree, Hash, BRIN, GIN, GiST, SP-GiST and vector indexes.
- Indexes for WHERE, ORDER BY, grouping, joins and JSON data.
- Loose index scans, and skip scan in PostgreSQL 18.
4Indexes and writes
- Write amplification and HOT updates.
- Locking and CREATE INDEX CONCURRENTLY.
- Indexes and parallel query.
5Maintenance and advanced indexing
- Index bloat, rebuilding indexes, pg_repack and amcheck.
- Monitoring index use with pg_stat_user_indexes.
- Multicolumn, partial, expression and covering indexes in practice.
Frequently asked questions
Who is this session for?
Developers who decide which indexes their application needs. It assumes working SQL, not knowledge of PostgreSQL internals.
Can I book this session on its own?
Yes. Every session of the developer track is self-contained. It is also one of the seven sessions of High Performance SQL with Postgres, which you can book in full at a package price.
Is the session recorded?
No. The session is live, with time for questions, and is not recorded. You receive the handouts: the slides with the trainer's commentary and the full demo transcripts.
Can my company pay by invoice?
Yes — write to connect@dataegret.com and we invoice your company; card booking is also available online.
Instructors
A specialist in monitoring, high availability, performance tuning and scalable system architecture, with a background in banking, finance and telecommunications.
Over 15 years of experience across Oracle, DB2, MySQL, SQL Server and PostgreSQL. A recognised PostgreSQL contributor, active in the community through committees, user groups and international conferences.
Ready to book, or still deciding?
Live, small-group, taught by the people who run PostgreSQL in production. Questions first? Write to us — no sales pitch.