skip to content

Walk through what a relational database server does from the moment a client opens a TCP connection until that client can run its first SQL statement.

level: juniorimportance: must knowfreq 58%

answer

  1. TCP -> startup message -> TLS -> auth -> ready for query
  2. Startup carries db, user, encoding, app name
  3. SCRAM/cert/token, not always plain password
  4. Server allocates backend + private memory
  5. Handshake ms + memory = why connections are reused

basics

~20 s

TCP connect, a protocol handshake naming the database and user, an optional TLS upgrade, then authentication. The server allocates a session (a backend process or thread with its own memory), loads role metadata, and signals it is ready for queries.

solid answer

~50 s

A database connection is a stateful session over a long-lived socket, not a stateless request. 1. **Transport** - the client opens a TCP socket (or a local Unix socket) to the server's listener. 2. **Startup/handshake** - the client sends a startup message naming the database, the user, and client parameters (encoding, application name, protocol version); the server replies with what it supports. 3. **Encryption** - if TLS is requested or required, the connection is upgraded and certificates verified before credentials cross the wire. 4. **Authentication** - the server picks a method (password hash, SCRAM challenge-response, client certificate, external token or OS identity) and runs the exchange; failure closes the socket. 5. **Session setup** - the server allocates a backend, loads role and database metadata, applies per-role and per-database defaults, warms catalog caches, then reports *ready for query*. Only then does the first statement run. Those steps cost milliseconds plus real memory, which is why production systems reuse connections instead of opening one per request.

code

text · 8 lines
text
client -> server : TCP connect
client -> server : startup {db, user, encoding, app_name, proto_version}
client <-> server: TLS upgrade + certificate verification   (optional)
client <-> server: auth challenge / response (SCRAM, cert, token)
server          : allocate backend (process|thread) + private memory
server          : load role + database catalog entries, apply defaults
server -> client: ready_for_query  { session params }
client -> server : first SQL statement

go deeper

for a junior

Recall the ordered steps - connect, handshake, optional TLS, authenticate, session ready - and state that a connection is a session that holds state.

for a middle

Add what the server allocates at session setup (backend plus private memory, catalog and role metadata) and quantify why the sequence costs milliseconds.

for a senior

Tie handshake cost and per-session memory to production behaviour: connection reuse, TLS round trips across zones, and the risk of connection churn during traffic spikes.

for a principal

Frame it as a capacity and topology question: where authentication happens, how TLS termination and identity are handled at scale, and how session cost shapes the connection budget of a fleet.

## A connection is a session, not a request HTTP trains developers to think of a server call as stateless: connect, send, receive, forget. A relational database connection is the opposite. It is a long-lived conversation over one socket, and the server keeps state for it the whole time: the authenticated identity, the current transaction, temporary objects, prepared statements, and session settings. Understanding the setup sequence explains why that state exists and why connections are expensive. ## Step 1 - transport The client opens a TCP socket to the server's listening port, or a Unix domain socket for same-host clients (cheaper, no network stack, and it can carry OS-level identity). The listener accepts and hands the socket off to whatever serves sessions. ## Step 2 - the startup handshake The client sends a startup message over the engine's own **wire protocol** - a binary, message-framed format, not HTTP. It carries the target database name, the user name, the protocol version, and client parameters such as character encoding, timezone, and an application name used later for monitoring. The server answers with its own capabilities and, in some engines, a connection identifier and a random salt used by authentication. ## Step 3 - encryption negotiation If TLS is in play, the client asks to upgrade before credentials are sent. The handshake verifies the server certificate (and optionally a client certificate), then all later protocol messages are encrypted. This adds round trips - a real component of connection setup latency, especially across availability zones. ## Step 4 - authentication The server selects a method based on who is connecting and from where: a hashed password, a challenge-response scheme such as SCRAM (which avoids sending a reusable secret), a client TLS certificate, an OS/peer identity for local sockets, or an external token from an identity provider. Each is a small message exchange. Failure closes the socket - there is no half-open unauthenticated session. ## Step 5 - session setup Now the server commits real resources. It creates the execution context that will serve this client - a dedicated OS process, a thread, or a slot bound to a worker - and gives it private memory for parsing, plan caches, and sort/hash workspace. It loads catalog rows for the role and database, checks connection privileges and per-database limits, applies defaults configured for the role or database, and initialises per-session structures. Finally it sends a *ready for query* message that includes the session's initial parameter values, and the client can issue SQL. ## Why this matters The whole sequence is typically a handful of network round trips plus process/thread creation and catalog reads: often 1-10 ms on a LAN, much more with TLS across zones, and it allocates memory that stays reserved until the session ends. A web request that opens and closes its own connection can spend more time on the handshake than on the query. That is the direct motivation for keeping connections open and reusing them, and it is why an idle session is never free - it still holds memory and a server-side slot. ## What interviewers listen for Say *session*, not *socket*. Mention authentication as a distinct negotiated step, mention that the server allocates an execution context with private memory, and connect the cost of the sequence to reuse in production.

  • Why is opening a database connection per HTTP request considered a bad practice?
    Each open pays the full handshake: TCP setup, optional TLS round trips, authentication, backend creation, and catalog loading - often milliseconds, which can exceed the query time itself. It also churns server-side memory and can push the server toward its connection limit under load. Reusing an already-authenticated session removes all of that from the request path.
  • What is carried in the startup handshake beyond the username and password?
    The target database name, the protocol version, and client parameters such as character encoding, timezone, and an application name. Those parameters become part of the session's initial state, and the application name in particular is what shows up in server-side activity views, which makes it valuable for diagnosing which service owns a connection.

Opening a connection is checking into a hotel, not buying a coffee: ID check, paperwork, then a room reserved in your name that stays yours (and costs the hotel) until you check out.

saying these in an interview costs you the question

  • Describing it as stateless like HTTP, so any request can land on any connection
  • Thinking authentication happens once per query rather than once per session
  • Believing an established but idle connection consumes no server resources
  • Assuming the database speaks HTTP or that TLS is inherent to the protocol rather than a negotiated upgrade

context

resolving links…