PostgreSQL Wire Protocol Simulator
Visualize the PostgreSQL wire protocol with an interactive simulator. Step through TLS negotiation, StartupMessage, SCRAM authentication, simple queries, extended queries, RowDescription, DataRow streams, ErrorResponse, COPY, and ReadyForQuery.
Category: Database
What You Will Learn
- Visualize PostgreSQL frontend and backend protocol messages
- Understand StartupMessage, AuthenticationSASL, and ReadyForQuery
- Compare simple query and extended query protocol flows
- See how RowDescription and DataRow messages encode result sets
- Learn how COPY streams bulk data over the wire
Topics covered: postgresql, postgres, database, wire-protocol, networking, sql, backend, educational, interactive
// simulator
PostgreSQL Wire Protocol Simulator
Visualize the PostgreSQL wire protocol with an interactive simulator. Step through TLS negotiation, StartupMessage, SCRAM authentication, simple queries, extended queries, RowDescription, DataRow streams, ErrorResponse, COPY, and ReadyForQuery.
Startup and authentication: Ask for TLS
Goal: Connect and authenticate: from SSLRequest to ReadyForQuery. Pick Startup with TLS on and press Play.
Startup and authentication, message 1 of 10: Ask for TLS.
How the PostgreSQL wire protocol works
Startup
- SSLRequest: the client may ask whether the server accepts TLS before sending startup parameters.
- StartupMessage: carries protocol version, user, database, and session options.
- ReadyForQuery: marks the connection as safe for the next command.
Query execution
- Simple query: sends one SQL string and receives metadata, rows, completion, and ready state.
- Extended query: splits work into Parse, Bind, Describe, Execute, and Sync.
- COPY: switches into a streaming mode for bulk import or export.
Why it matters
- Connection poolers need transaction state from ReadyForQuery.
- Drivers use RowDescription to decode text and binary values correctly.
- Prepared statements reduce SQL injection risk and can reduce parse overhead.
Overview
Every PostgreSQL client, from psql to an ORM, talks to the server with the same message-based wire protocol, and knowing its shape explains a lot of connection and performance behavior. This simulator animates those messages one at a time. The startup scenario shows the optional TLS request, the startup parameters, a SASL authentication exchange that ends in AuthenticationOk, the ParameterStatus messages and finally ReadyForQuery. The simple query scenario sends one Query message and gets back a RowDescription, a stream of DataRow messages and CommandComplete. The extended query scenario splits the work into Parse, Bind, Describe, Execute and Sync, which is how prepared statements and parameter binding work and why pipelining several of them saves round trips. The COPY scenario shows bulk data streamed as CopyData chunks. You can turn TLS and pipelining on and off, change the number of rows and inject an error to see an ErrorResponse arrive, so the flow stops being a black box when you debug a driver, a pooler or a slow query.
Try this
- Play the startup scenario and follow the messages from the TLS request to ReadyForQuery.
- Compare the simple query and extended query scenarios, then turn pipelining off and see the extra round trips.
- Turn on error injection and watch where the ErrorResponse appears in the flow.
Frequently asked questions
- What is the difference between the simple and extended query protocol in PostgreSQL?
- The simple protocol sends the whole SQL string in one Query message. The extended protocol splits it into Parse, Bind and Execute, which allows prepared statements, separate parameters and pipelining.
- How does PostgreSQL authenticate a client?
- After the startup message the server asks for credentials. Modern servers use SASL with SCRAM-SHA-256, a challenge and response exchange that proves the client knows the password without sending it.
- Why is COPY faster than many INSERT statements?
- COPY streams rows as raw data chunks in a single command, avoiding a round trip and a parse step per row, so bulk loads are much faster.
Try next
// simulator
SQL Terminal Simulator
Practice SQL in an interactive browser terminal. Learn SELECT, WHERE, ORDER BY, DISTINCT, aggregates, GROUP BY, HAVING, JOINs, subqueries, and window functions against a small e-commerce schema, with single-table queries evaluated live and verified Postgres output.
// simulator
PostgreSQL Terminal Simulator
Practice the psql tool and Postgres engine in an interactive browser terminal. Explore with meta-commands, read real EXPLAIN plans, add an index to turn a sequential scan into a bitmap index scan, and use BEGIN and ROLLBACK to make changes safe, all against a small e-commerce database with verified PostgreSQL output.
// simulator
MongoDB Terminal Simulator
Practice MongoDB queries in an interactive browser terminal. Learn find and projections, query operators like $lt, $gt, and $in, sort, skip, limit, countDocuments, insert, update, delete, and the aggregation pipeline with $match, $group, and $sort, against a small e-commerce dataset that runs live in your browser.