#PostgreSQL

The Indexes

When you create an index in PostgreSQL, you might think there’s just one type of index structure working behind the scenes. But PostgreSQL actually provides six different index types, each engineered for specific use cases with completely different internal structures and access patterns.

This is the final stop on our SQL Query Roadtrip. In the previous article , we explored how PostgreSQL’s execution engine uses the Volcano model to transform abstract query plans into concrete operations. Before that, we covered parsing, analysis, rewriting, and planning. Now we’re diving into one of the most critical components that makes all of this fast: indexes.

The Execution Engine

In the previous article , we explored how PostgreSQL’s planner chooses the optimal execution strategy. The planner produces an abstract plan tree—nodes like “Sequential Scan,” “Hash Join,” “Sort”—that describe what to do. Now the execution engine needs to actually do the work: read pages from disk, follow indexes, join tables, and produce results.

The executor uses the Volcano execution model—a beautifully simple pattern where every operation implements the same interface: “give me the next row.” A sort operation doesn’t care whether its input comes from a table scan or a join—it just asks for rows and sorts whatever it gets. This uniform approach allows arbitrarily complex queries to be built from simple, composable pieces.

The Query Planner

In the previous article , we explored how PostgreSQL’s rewriter transforms queries—expanding views, applying security policies, and executing custom rules. By the end of that phase, your query has been fully expanded and secured, ready for execution.

But here’s the million-dollar question: How should PostgreSQL actually execute your query?

Let me show you why this matters. Take this simple query:

SELECT c.first_name, c.last_name, r.rental_date
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
WHERE c.active = 1;

PostgreSQL could execute this in dozens of different ways:

Query Rewriting

In the previous article , we explored how PostgreSQL transforms SQL text into a validated Query tree through parsing and semantic analysis. By the end of that journey, PostgreSQL knows that your tables exist, your columns are valid, your types match up, and your query makes sense.

But before the planner can figure out how to execute your query, there’s one more critical transformation step: query rewriting.

Let me show you why this matters. When you write this simple query:

Parsing and Analysis

In the previous article , we explored how PostgreSQL establishes connections and communicates using its wire protocol. Once your connection is established and the backend process is ready, you can finally send queries. But when PostgreSQL receives your SQL, it’s just a string of text—the database can’t execute text directly.

Let me show you what happens when PostgreSQL receives this query:

SELECT name FROM users WHERE id = 42;

PostgreSQL doesn’t see this as a command yet. It sees characters: S, E, L, E, C, T, and so on. The journey from this raw text to something PostgreSQL can execute involves two major transformations: parsing (understanding structure) and semantic analysis (adding meaning).

Connections and Communication

In the previous article , we explored the complete journey a SQL query takes through PostgreSQL—from parsing to execution. But before any of that can happen, your application needs to establish a connection with the database.

This might seem like a simple handshake, but there’s actually a sophisticated process happening behind the scenes—involving process management, authentication, and a binary protocol for efficient communication.

Understanding how PostgreSQL handles connections helps explain why connection pooling matters, how to troubleshoot connectivity issues, and why PostgreSQL’s architecture differs from thread-based databases. Let’s trace what happens when your application connects to PostgreSQL.

Overview

Ever wonder what happens when you type SELECT * FROM users WHERE id = 42; and hit Enter? That simple query triggers a fascinating journey through PostgreSQL’s internals—a complex series of operations involving multiple processes, sophisticated memory management, and decades of optimization research.

This is the first article in a series where we’ll explore PostgreSQL’s query execution in depth. In this overview, I’ll walk you through the complete journey from SQL text to results, giving you the roadmap. Then, in subsequent articles, we’ll dive deep into each component—the parser, analyzer, rewriter, planner, and executor—exploring the details of how each one works.