When we write:
SELECT *
FROM users
WHERE id = 100;
it looks incredibly simple.
We send SQL.
PostgreSQL gives us rows.
But what actually happens between those two steps?
There is a lot going on internally.
Understanding this process changed the way I think about database performance.
Instead of thinking:
"I wrote a query and PostgreSQL executed it."
I started thinking:
"What path does PostgreSQL take to turn my SQL into actual data?"
Let's follow that journey.
🧭 The High-Level Journey
A simplified version looks like this:
SQL Query
│
▼
Parser
│
▼
Parse Tree
│
▼
Analyzer / Rewriter
│
▼
Query Planner
│
▼
Execution Plan
│
▼
Executor
│
▼
Buffer Manager
│
┌──────┴──────┐
▼ ▼
Shared Buffers Disk
│ │
└──────┬──────┘
▼
Rows
Let's break this down.
1. The Client Sends SQL
Suppose our application sends:
SELECT id, name
FROM users
WHERE id = 100;
The PostgreSQL server receives this SQL statement.
At this point, PostgreSQL doesn't immediately start reading rows from the table.
First, it needs to understand what we wrote.
2. Parser: "What Did You Write?"
The parser checks whether the SQL is syntactically valid.
For example:
SELECT id, name
FROM users
WHERE id = 100;
is valid.
But:
SELEC id name
FROM users;
is not valid SQL syntax.
The parser builds an internal representation called a parse tree.
You can think of it as PostgreSQL converting:
SELECT
↓
columns
↓
table
↓
condition
into a structure the database can work with.
3. Analyzer: "What Do These Names Mean?"
Now PostgreSQL needs to understand the objects referenced by the query.
For example:
FROM users
Does the users table actually exist?
And:
SELECT name
Does the name column exist?
PostgreSQL resolves things such as:
- table names
- column names
- data types
- functions
- operators
If we write:
SELECT something
FROM users;
and something doesn't exist, PostgreSQL will reject the query.
4. Rewriting
PostgreSQL can transform the query before planning it.
This stage is particularly important for things such as:
- views
- rules
- some query transformations
For a simple query, you might not notice this stage.
But internally, PostgreSQL can rewrite the query into another representation that is easier to plan and execute.
5. The Query Planner
Now things get interesting.
PostgreSQL needs to decide:
"What's the cheapest way to execute this query?"
Suppose we have:
SELECT *
FROM users
WHERE email = 'user@example.com';
PostgreSQL might have several possible strategies.
Option 1: Sequential Scan
Read the table from beginning to end.
Row 1
Row 2
Row 3
Row 4
...
Row 1,000,000
Option 2: Index Scan
If an appropriate index exists:
CREATE INDEX idx_users_email
ON users(email);
PostgreSQL may use the index to locate the matching row much more efficiently.
The planner compares possible execution strategies using estimated costs.
6. EXPLAIN Lets Us See the Plan
We can ask PostgreSQL what it intends to do:
EXPLAIN
SELECT *
FROM users
WHERE email = 'user@example.com';
You might see something like:
Index Scan using idx_users_email on users
Index Cond: (email = 'user@example.com')
Now we have a window into the planner's decision.
This is one reason EXPLAIN is such an important tool for backend developers.
7. The Executor Takes Over
The planner creates an execution plan.
The executor then actually runs that plan.
For example:
Index Scan
↓
Find matching index entry
↓
Locate table row
↓
Return row
The executor is responsible for producing the actual result.
8. But Where Is the Data?
This is where PostgreSQL's storage internals become important.
The table data ultimately lives on disk.
But PostgreSQL doesn't simply perform a disk read for every row.
That would be extremely expensive.
Instead, PostgreSQL uses a buffer cache.
One important component involved here is:
shared_buffers
These are PostgreSQL's shared memory buffers used to cache data pages.
9. PostgreSQL Works With Pages
PostgreSQL doesn't think of a table simply as:
User 1
User 2
User 3
User 4
Internally, table data is organized into fixed-size pages.
A PostgreSQL page is normally:
8 KB
So conceptually:
Table
│
├── Page 0
├── Page 1
├── Page 2
├── Page 3
└── ...
Each page contains multiple tuples/rows depending on their size.
This is one of the most important concepts to understand before diving into PostgreSQL internals.
10. Buffer Hit vs Disk Read
Suppose PostgreSQL needs a page.
First, it can check whether that page is already available in memory.
If it is:
Query
↓
Buffer Manager
↓
Page already in memory
↓
Buffer Hit
Much faster.
If it isn't:
Query
↓
Buffer Manager
↓
Page not in memory
↓
Read from disk
↓
Put page into buffer
↓
Use the data
This is why memory and I/O have such a significant effect on database performance.
11. What Happens With Our Indexed Query?
Let's put everything together.
We execute:
SELECT id, name
FROM users
WHERE email = 'user@example.com';
Suppose we have:
CREATE INDEX idx_users_email
ON users(email);
A simplified execution might look like:
SQL
│
▼
Parser
│
▼
Analyzer
│
▼
Planner
│
│
├── Sequential Scan?
│
└── Index Scan? ← selected
│
▼
Executor
│
▼
Index
│
▼
Find matching tuple location
│
▼
Check required table page
│
├── Buffer Hit
│
└── Disk Read
│
▼
Return row
That's quite a journey for one simple SELECT.
12. Why Should Application Developers Care?
Because database internals directly affect application performance.
Consider two queries:
SELECT *
FROM orders
WHERE customer_id = 100;
and:
SELECT id, total, status
FROM orders
WHERE customer_id = 100;
The difference isn't simply about SQL style.
It can affect:
- amount of data read
- memory usage
- network traffic
- CPU usage
- serialization
- index usage
- overall API latency
Understanding what's happening underneath helps us make better decisions.
13. A Simple Mental Model
When debugging a slow PostgreSQL query, I now think about it in this order:
1. What SQL did I write?
↓
2. How did PostgreSQL parse it?
↓
3. What execution plan did PostgreSQL choose?
↓
4. Is it using an index?
↓
5. How many rows/pages are being processed?
↓
6. Are pages already in memory?
↓
7. How much disk I/O is happening?
↓
8. Is the planner's estimate accurate?
This is much more useful than blindly adding indexes.
🔥 The Most Important Lesson
A database isn't just:
SQL → Result
There is a whole execution pipeline between them:
SQL
↓
Parser
↓
Analyzer / Rewriter
↓
Planner
↓
Execution Plan
↓
Executor
↓
Buffer Manager
↓
Pages
↓
Disk / Memory
↓
Result
Once you understand this pipeline, concepts like:
- indexes
EXPLAIN ANALYZE- shared buffers
- sequential scans
- index scans
- MVCC
- VACUUM
- WAL
start making much more sense.
🚀 What's Next?
This is only the beginning.
If you're also learning PostgreSQL internals, follow along. I'm documenting the concepts as I learn them and trying to explain them without assuming you're already a database expert.
Top comments (0)