SQL 130 🛢️ Database Driver Mechanics and Wire Protocols (PostgreSQL Protocol, MySQL Protocol)
A database driver is a translator. The application speaks in function calls and typed objects; the database speaks in a binary protocol over a socket. The wire protocol is the contract between them: a sequence of messages with defined framing, a startup handshake that negotiates capabilities and authenticates the user, and a query phase that differs significantly between simple and extended execution. PostgreSQL and MySQL implement different protocols with different design philosophies. PostgreSQL’s protocol is message-oriented and explicitly separates parsing, binding, and execution in its extended query mode. MySQL’s protocol has a connection phase and a command phase, with prepared statements handled through a separate binary protocol. Understanding these mechanics explains why connection poolers behave the way they do, why prepared statements sometimes perform differently than expected, and why a driver’s choice of protocol mode affects latency.
Key point: PostgreSQL’s extended query protocol splits a statement into Parse, Bind, and Execute messages, which allows prepared statements to be reused and parameters to be bound separately from the query text. MySQL’s prepared statement protocol uses a similar prepare-execute cycle but is a distinct binary protocol from the text-based simple query path.
Why wire protocols matter to application developers
The prepared statement problem. A driver that uses the extended query protocol can prepare a statement once and execute it many times with different parameters. The server parses the statement once, caches the plan, and binds new values on each execution. A driver that uses the simple query protocol concatenates the query text and parameters into a single string and sends it for parsing every time. The performance difference is small for a single query but significant for an application that executes the same statement thousands of times per second. The choice of protocol mode is a driver configuration decision, and it determines which behavior the application gets.
The connection pooler problem. A connection pooler sits between the application and the database, multiplexing many client connections onto a smaller number of server connections. The pooler must understand the protocol to some degree. If the application uses the extended query protocol and creates named prepared statements, those statements exist on a specific server connection. When the pooler reassigns that connection to a different client, the client may see prepared statements it did not create, or its own prepared statements may be missing. This is why transaction-level pooling requires either the unnamed prepared statement or a protocol-aware pooler that tracks statement state.
The type mapping problem. PostgreSQL’s protocol supports both text and binary formats for parameter values and result columns. The format is specified per-value in the Bind message. A driver that sends parameters in binary avoids the server’s text-to-type conversion and the client’s string formatting, which reduces CPU overhead for numeric and temporal types. A driver that sends everything as text is simpler to implement but pays a conversion cost on every value. The protocol allows the choice; the driver makes it.
a. PostgreSQL Protocol: Startup and Simple Query
The PostgreSQL protocol begins with a startup phase. The client sends a startup message that includes the protocol version and connection parameters such as the username and database name. The server responds with an authentication request, and the client completes the authentication exchange. After authentication succeeds, the server sends ParameterStatus messages for runtime parameters, BackendKeyData for cancellation, and finally ReadyForQuery to signal that the connection is ready for commands .
Startup message: protocol version 3.0, user, database
Server: Authentication request (MD5, SCRAM-SHA-256, etc.)
Client: Authentication response
Server: AuthenticationOk, ParameterStatus, BackendKeyData, ReadyForQueryEvery subsequent message has a one-byte type code followed by a four-byte length and the message payload. The startup message is the only exception; it has no type byte for historical reasons . This framing is what allows a client to read an entire message into a buffer before processing it, which makes error recovery possible.
The simple query protocol is the direct path. The client sends a Query message containing a text string, and the server responds with RowDescription, zero or more DataRow messages, and CommandComplete, followed by ReadyForQuery. A simple query can contain multiple SQL statements separated by semicolons, and the server processes them sequentially, sending a separate response sequence for each .
-- Simple query: the entire text is sent at once
SELECT * FROM users WHERE id = 1;The limitation is that parameters cannot be bound. The query text must contain the literal values, which means the client must construct the SQL string. This is the mechanism that makes SQL injection possible if the values are not properly escaped.
b. PostgreSQL Extended Query: Parse, Bind, Execute
The extended query protocol breaks the simple query cycle into distinct steps. The client sends a Parse message containing a query string and optionally the data types of parameter placeholders. The server responds with ParseComplete or ErrorResponse. The parsed statement is stored as a prepared statement, either named or unnamed. A named statement lasts for the session; an unnamed statement lasts only until the next Parse message .
The Bind message creates a portal from a prepared statement. It supplies the parameter values and specifies the format (text or binary) for each parameter and for the result columns. The server responds with BindComplete. The portal is the ready-to-execute object, and it lasts until the end of the transaction for named portals, or until the next Bind for the unnamed portal .
The Execute message runs a portal. It specifies a maximum row count, which allows fetching results in batches for large result sets. The server responds with the result rows and a CommandComplete message. The Sync message closes the extended query cycle and produces a ReadyForQuery .
Parse("SELECT * FROM users WHERE id = $1") → ParseComplete
Bind(portal, statement, [42]) → BindComplete
Execute(portal) → DataRow, CommandComplete
Sync → ReadyForQueryThe protocol allows the client to send Parse, Bind, and Execute in a single batch without waiting for the intermediate responses. This is called pipelining, and it reduces the number of network round trips. The Sync message is what forces the server to process the batch and return the results .
The extended query protocol is what makes prepared statements efficient. The server parses the statement once, and subsequent Bind messages can supply different parameter values without re-parsing. The server may also cache the query plan, though it will only reuse a generic plan if the plan is not significantly less efficient than a parameter-specific plan .
c. MySQL Protocol: Connection Phase and Prepared Statements
The MySQL protocol has two phases: the connection phase and the command phase. In the connection phase, the server sends a handshake packet with its version, connection ID, capability flags, and a random salt. The client responds with its capability flags, username, authentication response, and database name. The server validates the authentication and sends an OK or Error packet. If the connection is successful, the command phase begins .
Server: Handshake (version, capabilities, salt)
Client: HandshakeResponse (capabilities, username, auth response, database)
Server: OK packetThe command phase is a request-response cycle. The client sends a command packet with a command byte (such as COM_QUERY for a text query or COM_STMT_PREPARE for a prepared statement), and the server responds with the appropriate packets. The text protocol sends the query as a string and receives results as text strings. The binary protocol is used for prepared statements, where parameter values and result columns are transmitted in a binary format .
COM_QUERY("SELECT * FROM users") → Result set packets, EOF/OK
COM_STMT_PREPARE("SELECT * FROM users WHERE id = ?") → Prepare OK (statement ID, parameter count, column count)
COM_STMT_EXECUTE(statement ID, parameters) → Binary result set packetsThe prepared statement flow in MySQL is similar to PostgreSQL’s extended query protocol but is a distinct protocol path. The client sends COM_STMT_PREPARE with the query string, and the server responds with a statement ID and metadata about the parameters and result columns. The client then sends COM_STMT_EXECUTE with the statement ID and the parameter values. The server executes the statement and returns the results in binary format .
The MySQL C API exposes this through mysql_stmt_prepare(), mysql_stmt_bind_param(), and mysql_stmt_execute(). The MYSQL_BIND structure defines the type and buffer for each parameter, and the buffer_type field determines how the value is interpreted . The binary protocol is what allows MySQL to avoid the text conversion overhead for prepared statements.
The connection pooling problem is slightly different in MySQL. The protocol is stateful within a session: temporary tables, user variables, and prepared statements are session-scoped. A connection pooler that reassigns a server connection to a different client must either reset the session state or track which state belongs to which client. The MySQL protocol does not have a built-in mechanism for session state transfer, which is why transaction-level pooling with MySQL requires careful handling of prepared statements and session variables.
Complete Example Session
// ============================================
// PART 1: POSTGRESQL STARTUP SEQUENCE
// ============================================
Client → StartupMessage(protocol 3.0, user=app, database=prod)
Server → AuthenticationSASL (SCRAM-SHA-256)
Client → SASLInitialResponse, SASLResponse
Server → AuthenticationOk, ParameterStatus, BackendKeyData, ReadyForQuery
// ============================================
// PART 2: POSTGRESQL SIMPLE QUERY
// ============================================
Client → Query("SELECT count(*) FROM users")
Server → RowDescription, DataRow, CommandComplete, ReadyForQuery
// ============================================
// PART 3: POSTGRESQL EXTENDED QUERY — PARSE
// ============================================
Client → Parse("stmt1", "SELECT * FROM users WHERE id = $1", [int4])
Server → ParseComplete
// ============================================
// PART 4: POSTGRESQL EXTENDED QUERY — BIND
// ============================================
Client → Bind("portal1", "stmt1", [42])
Server → BindComplete
// ============================================
// PART 5: POSTGRESQL EXTENDED QUERY — EXECUTE
// ============================================
Client → Execute("portal1", 0)
Server → DataRow(id=42, name=Alice), CommandComplete("SELECT 1")
Client → Sync
Server → ReadyForQuery
// ============================================
// PART 6: POSTGRESQL EXTENDED QUERY — REUSE
// ============================================
// The prepared statement "stmt1" is already parsed
Client → Bind("portal2", "stmt1", [99])
Server → BindComplete
Client → Execute("portal2", 0)
Server → DataRow(id=99, name=Bob), CommandComplete
Client → Sync
Server → ReadyForQuery
// ============================================
// PART 7: MYSQL CONNECTION HANDSHAKE
// ============================================
Server → Handshake(version=8.0, salt, capabilities)
Client → HandshakeResponse(user=app, auth=..., database=prod)
Server → OK
// ============================================
// PART 8: MYSQL TEXT QUERY
// ============================================
Client → COM_QUERY("SELECT * FROM users")
Server → Column definitions, Row data, EOF
// ============================================
// PART 9: MYSQL PREPARED STATEMENT
// ============================================
Client → COM_STMT_PREPARE("SELECT * FROM users WHERE id = ?")
Server → PrepareOK(statement_id=1, params=1, columns=2)
Client → COM_STMT_EXECUTE(statement_id=1, id=42)
Server → Binary result set
// ============================================
// PART 10: DRIVER CONFIGURATION IMPLICATIONS
// ============================================
-- PostgreSQL driver: prefer extended query for prepared statements
-- MySQL driver: use binary protocol for prepared statements
-- Pooler: transaction-level pooling requires protocol awarenessThe ten parts trace the two protocols from startup through query execution. Parts 3 through 6 show the PostgreSQL extended query cycle and the reuse of a parsed statement. Parts 8 and 9 show the MySQL text and binary paths.
Quick Reference
Protocol Comparison
| Aspect | PostgreSQL | MySQL |
|---|---|---|
| Startup | StartupMessage, authentication | Handshake, HandshakeResponse |
| Simple query | Query message (text) | COM_QUERY (text) |
| Prepared query | Parse, Bind, Execute | COM_STMT_PREPARE, COM_STMT_EXECUTE |
| Parameter binding | Separate from query text | Separate from query text |
| Result format | Text or binary (per Bind) | Text (simple) or binary (prepared) |
| Message framing | Type byte + length + payload | Command byte + payload |
PostgreSQL Extended Query Messages
| Message | Direction | Purpose |
|---|---|---|
Parse | Client | Create prepared statement |
Bind | Client | Create portal with parameters |
Execute | Client | Run portal |
Sync | Client | End cycle, get ReadyForQuery |
ParseComplete | Server | Statement created |
BindComplete | Server | Portal created |
DataRow | Server | Result row |
Driver Configuration Implications
| Concern | PostgreSQL | MySQL |
|---|---|---|
| Prepared statement reuse | Extended query protocol | Binary protocol |
| Binary parameter format | Bind format codes | COM_STMT_EXECUTE types |
| Pooler compatibility | Unnamed statements for transaction pooling | Session state reset required |
Best Practices
✅ Do This:
-- Use extended query protocol for repeated statements
-- The server parses once and reuses the plan -- ✅-- Use binary format for numeric and temporal parameters
-- Avoids text conversion on both client and server -- ✅-- For connection poolers, use unnamed statements
-- Named statements are bound to a specific server connection -- ✅❌ Don’t Do This:
-- Concatenate parameters into the query string
-- This is the simple query path and invites injection -- ❌-- Assume named prepared statements survive connection pooling
-- The statement may not exist on the reassigned connection -- ❌-- Use text format for all values in high-throughput paths
-- The conversion cost is measurable -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Prepared statement not found after pooling | Named statement bound to old connection | Use unnamed statements or transaction-aware pooler |
| High CPU from type conversion | Text format for numeric values | Use binary format in Bind |
| Driver uses simple query for prepared statements | Driver not configured for extended protocol | Check driver options |
| MySQL prepared statement slow | Binary protocol overhead for one-off queries | Use text protocol for single execution |
Real-World Examples
1. PostgreSQL Startup Message
StartupMessage: protocol 3.0, user=app, database=prod2. PostgreSQL Parse
Parse("stmt1", "SELECT * FROM users WHERE id = $1", [int4])3. PostgreSQL Bind
Bind("portal1", "stmt1", [42], result_format=binary)4. PostgreSQL Execute
Execute("portal1", max_rows=0)5. PostgreSQL Sync
Sync → ReadyForQuery6. MySQL Handshake
Server: Handshake(version, salt, capabilities)
Client: HandshakeResponse(user, auth, database)7. MySQL Text Query
COM_QUERY("SELECT * FROM users")8. MySQL Prepare
COM_STMT_PREPARE("SELECT * FROM users WHERE id = ?")9. MySQL Execute
COM_STMT_EXECUTE(statement_id=1, id=42)10. Binary Format Parameter
Bind format code 1 (binary) for int4 parameterVisual
PostgreSQL Extended Query Flow
┌──────────────────────────────────────────────────────────────┐
│ CLIENT SERVER │
│ │
│ ┌─────────────────┐ │
│ │ Parse("stmt1", │───────►│ ParseComplete │
│ │ "SELECT...$1") │ │ (statement parsed) │
│ └────────┬────────┘ └─────────────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Bind("portal1", │───────►│ BindComplete │
│ │ "stmt1", [42]) │ │ (portal created) │
│ └────────┬────────┘ └─────────────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Execute( │───────►│ DataRow, CommandComplete │
│ │ "portal1") │ │ (results returned) │
│ └────────┬────────┘ └─────────────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Sync │───────►│ ReadyForQuery │
│ └─────────────────┘ └─────────────────┘ │
│ │
│ Parse happens once. Bind and Execute can repeat. │
│ │
└──────────────────────────────────────────────────────────────┘MySQL Protocol Phases
┌──────────────────────────────────────────────────────────────┐
│ CONNECTION PHASE COMMAND PHASE │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ Handshake │ │ COM_QUERY │ │
│ │ (server→client) │ │ (text) │ │
│ └────────┬────────┘ └─────────────────┘ │
│ ▼ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ HandshakeResponse│ │ COM_STMT_PREPARE│ │
│ │ (client→server) │ │ (binary) │ │
│ └────────┬────────┘ └─────────────────┘ │
│ ▼ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ OK/Error │ │ COM_STMT_EXECUTE│ │
│ │ (auth result) │ │ (binary) │ │
│ └─────────────────┘ └─────────────────┘ │
│ │
│ Connection phase negotiates capabilities and authenticates. │
│ Command phase handles queries. │
│ │
└──────────────────────────────────────────────────────────────┘The Pooler Problem
┌──────────────────────────────────────────────────────────────┐
│ CLIENT A POOLER │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ Parse("stmtA") │─────────►│ Server conn 1 │ │
│ │ Bind, Execute │ │ stmtA exists │ │
│ └─────────────────┘ └────────┬────────┘ │
│ │ │
│ CLIENT B │ connection │
│ ┌─────────────────┐ │ reassigned │
│ │ Parse("stmtB") │◄──────────────────┘ │
│ │ │ ┌─────────────────┐ │
│ │ │ │ Server conn 1 │ │
│ │ │ │ stmtA exists │ │
│ │ │ │ stmtB unknown? │ │
│ └─────────────────┘ └─────────────────┘ │
│ │
│ Named statements are bound to the server connection. │
│ Transaction pooling must use unnamed statements or │
│ a pooler that tracks statement state. │
│ │
└──────────────────────────────────────────────────────────────┘Text vs. Binary Format
┌──────────────────────────────────────────────────────────────┐
│ TEXT FORMAT BINARY FORMAT │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ Parameter: 42 │ │ Parameter: 42 │ │
│ │ as "42" string │ │ as 4-byte int │ │
│ └────────┬────────┘ └────────┬────────┘ │
│ ▼ ▼ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ Server parses │ │ Server uses │ │
│ │ string to int │ │ value directly │ │
│ └─────────────────┘ └─────────────────┘ │
│ │
│ Text is simpler. Binary is faster for numeric and temporal. │
│ Bind format codes select per-parameter. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| PostgreSQL protocol | Message-oriented, type byte + length + payload |
| Simple query | Text query string, multiple statements allowed |
| Extended query | Parse, Bind, Execute, Sync |
| Prepared statement | Named or unnamed; named lasts for session |
| Portal | Ready-to-execute object; named lasts for transaction |
| MySQL protocol | Connection phase + command phase |
| MySQL simple query | COM_QUERY, text format |
| MySQL prepared | COM_STMT_PREPARE, COM_STMT_EXECUTE, binary format |
| Format codes | Text (0), Binary (1) in Bind |
| Pooler issue | Named statements bound to server connection |
Key takeaways:
- PostgreSQL’s extended query protocol is what makes prepared statements efficient. The
Parsemessage parses the statement once, and subsequentBindmessages supply new parameter values without re-parsing. TheExecutemessage runs the portal, andSynccloses the cycle . - MySQL separates connection and command phases. The connection phase negotiates capabilities and authenticates; the command phase handles queries. Prepared statements use a binary protocol (
COM_STMT_PREPARE,COM_STMT_EXECUTE) that is distinct from the text-basedCOM_QUERYpath . - The format code in PostgreSQL’s Bind message controls text versus binary transmission. Binary avoids the conversion overhead for numeric and temporal types, but requires the driver to encode values correctly. Text is simpler and portable .
- Named prepared statements are bound to a server connection. When a connection pooler reassigns a server connection to a different client, the client may see statements it did not create or lose access to its own. Transaction-level pooling requires unnamed statements or a protocol-aware pooler.
- The protocol is the contract between driver and database. The driver’s choice of simple versus extended query, text versus binary format, and named versus unnamed statements determines the application’s performance and compatibility with connection pooling. These are configuration decisions, not implementation details.
Remember: A database driver is not a black box. It speaks a protocol with specific messages, and the choice of which messages to send determines the performance and the behavior of the application. PostgreSQL’s extended query protocol separates parsing from execution, which makes prepared statements efficient and allows parameters to be bound separately from the query text. MySQL’s binary protocol serves the same purpose for prepared statements but through a different message set. The connection pooler problem is a protocol problem: state that lives on a server connection does not automatically move when the connection is reassigned. Understanding the protocol is what allows a developer to choose the right driver configuration and to diagnose the failures that occur when the configuration does not match the deployment.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!