The Journey of SELECT 1

The Journey of SELECT 1

The phrase “Even a simple SELECT 1; tells a lot" marks a milestone for anyone beginning to explore the inner workings of a powerful database engine like PostgreSQL. Beneath this tiny query lies a vast system of processes and components. In this journey, we won’t just follow SELECT 1;, but also pause to ask deeper questions: What if it were an INSERT? What happens during a massive JOIN? How does synchronous replication affect response time?

TCP Handshake & TLS Negotiation

When a client (like psql, JDBC, or your app) connects to PostgreSQL, the first thing that happens isn’t SQL it’s networking.

TCP 3-Way Handshake

SYN → SYN-ACK → ACK

The PostgreSQL server, via the postmaster process, listens on the configured listen_addresses and port. Upon receiving a connection request, it calls accept() and forks a dedicated backend process for the session.

PostgreSQL uses a process-per-connection model. This ensures strong isolation (Unix philosophy!), but can be resource-intensive. For high concurrency, connection poolers like PgBouncer are recommended.

TLS Handshake (If Enabled)

If ssl = on, a TLS handshake follows the TCP negotiation:

• Certificates are exchanged,
• A session key is negotiated,
• Communication becomes encrypted.

Key TLS parameters in postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

Encrypted traffic ensures data integrity and privacy over insecure networks.

Authentication via pg_hba.conf & SCRAM-SHA-256

Once the network layer is done, PostgreSQL uses pg_hba.conf to authorize access based on:

• Connection type (host, hostssl, etc.)
• Client IP
• Username
• Database

Modern Auth: SCRAM-SHA-256

Today’s PostgreSQL (default since 13+) recommends SCRAM-SHA-256, a challenge-response authentication:

• Server sends a nonce (random salt),
• Client computes a hash from password + nonce,
• Server validates using a stored verifier in pg_authid.

No password is ever sent in cleartext — anywhere.

Backend Process: Forked and Ready

Every client connection gets its own backend process:

ps aux | grep postgres

This process:

• Parses and executes SQL commands,
• Manages memory for that session,
• Remains alive until the client disconnects.

While SELECT 1; seems trivial, a full-fledged backend is created just the same.

High connection counts = high process counts. Poolers reduce this pressure dramatically.

SQL Lifecycle: From Text to Execution

Let’s see what happens when our backend receives SELECT 1;.

Parse

• SQL is tokenized into a Parse Tree,
• Nodes like SelectStmt, ResTarget, Const are created.

Rewrite (No-op for SELECT 1;)

• Only used if views or rewrite rules are involved.

Analyze & Binding

• Variables and types are resolved,
• Object names are bound to internal OIDs.

Planner / Optimizer

• PostgreSQL consults internal statistics,
• Chooses the cheapest plan (cost-based),
• For SELECT 1;, this results in a constant scan.

Advanced Scenarios

JOIN with Sorting

SELECT * FROM logs WHERE user_id = 42 ORDER BY created_at DESC;

• Planner uses pg_stats to assess cardinality,
• Uses indexes if available,
• Sorts in-memory using work_mem,
• If memory is insufficient → writes to pg_temp.

Performance Tip: work_mem

SET work_mem = '64MB';

If work_mem is too small, PostgreSQL performs external merge sorts (visible in EXPLAIN ANALYZE):

Sort Method: external merge  Disk: 12345kB

work_mem size vs. sort performance.

Executor: Memory vs. Disk

For SELECT 1;:

• The result (ConstExpr) lives in RAM,
• No access to shared_buffers or disk is needed.

For Actual Data Access

SELECT * FROM products WHERE price > 1000;

• If the page is in shared_buffers → served from memory,
• Else → OS page cache → disk read.

For INSERT, UPDATE, DELETE:

• Tuple written to shared_buffers,
• WAL entry written to wal_buffers,
• Periodically flushed to disk (WAL segment).

INSERT Path: WAL, LSN, and fsync

INSERT INTO audit_log (user_id, action) VALUES (42, 'login');

Write-Ahead Logging (WAL):

• Ensures durability,
• Inserted row → shared_buffers,
• WAL record → wal_buffers → disk.

If synchronous_commit = off:

• Client may get ACK before data is on disk.

If fsync = off:

• Even WAL is not guaranteed to be flushed. Risky for prod

Replication & Synchronous Commit

If synchronous_standby_names is set:

• WAL is streamed to standby,
• Standby sends ack after fsync,
• Only then does the client get response.

Pros:

• Strong data durability

Cons:

• Latency increases depending on standby’s I/O and network speed

MVCC: What Does SELECT See?

PostgreSQL uses Multi-Version Concurrency Control (MVCC):

• Every row has xmin and xmax transaction IDs,
• SELECT queries only see rows visible to their snapshot,
• Uncommitted rows are invisible.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- This change is not yet visible to others

MVCC ensures consistency without locking reads.

Response and Idle Session

After execution, the backend sends:

• RowDescription
• DataRow → 1
• CommandComplete

The session enters idle:

• Keeps GUCs (search_path, statement_timeout, etc.),
• Can be reused (via pooling),
• Consumes memory until terminated.

TL;DR — It’s Never Just SELECT 1;

A single SQL query traverses:

• Network stack (TCP/TLS),
• Authentication (pg_hba, SCRAM),
• Process management (forked backends),
• Planning & Optimization,
• Memory & Disk management,
• WAL and Replication,
• MVCC and visibility,
• Client communication.

Behind every trivial query lies an epic tale of database engineering.