SQL / RELATIONAL FOUNDATIONS
How clients talk to a database server
Explain what crosses the wire when you run SQL, why a connection is a stateful session, and why each statement costs a network round trip.
What you will learn
- Name exactly what the client sends and what the server sends back for one SELECT
- Use SET and temp tables knowing that state lives on the server and dies with the connection
- Spot per-row round-trip loops and fold them into one statement
- Explain why another client cannot see your temp tables or uncommitted rows
Understanding How clients talk to a database server
When you press run, your editor evaluates no SQL at all. It opens a TCP connection to a host and port, authenticates, and sends your statement across as bytes; the parser, the planner, the executor and the data all sit in the server process on the far side. That split is why the same statement can succeed against one server and fail against another with a different version, different tables or different privileges, and why the verdict on your SQL always comes from the server rather than from the tool you typed it into.
The connection is not a stateless pipe. For as long as it is open, the server keeps a session attached to it: current user and database, search_path, time zone, an open transaction if you started one, temporary tables, prepared statements and cursors. That is why a SET on one statement changes the behaviour of the next one on the same connection, why one client cannot see another client's temp tables or uncommitted rows, and why everything session-scoped disappears the instant the connection closes instead of at some later cleanup.
Every statement costs at least one round trip: request out, result back. The latency of that trip is roughly fixed whether the statement reads one row or aggregates a million, so 500 single-row lookups fired from a loop pay 500 latencies while one statement covering the same rows pays one. Results come back the other way as a stream of rows that the client buffers into a copy, and that copy is a snapshot of what the server saw while the statement ran, not a live window, which is why a grid on screen does not change by itself when someone else commits.
-- PostgreSQL, run in psql
SET my.report_label = 'Q3 rollup';
SELECT current_setting('my.report_label') AS label,
length(current_setting('my.report_label')) AS len;A SQL client only ships statement text and reads rows back; the parsing, the work and the session state that remembers what you did all live in the server on the other end of one connection.
Worked examples
The work happens on the server side
A million rows are produced and reduced inside the server, and only one row crosses the connection.
SELECT count(*) AS rows_read_on_server,
sum(n) AS total
FROM generate_series(1, 1000000) AS g(n);Example explained
Line 1generate_series runs inside the server process, so the million integers exist there and nowhere near your client.
Line 2count and sum collapse those rows to a single row before anything is written to the socket.
Line 3The client sends roughly a hundred bytes of statement text and reads back one row of two values.
Line 4Drop the aggregates and the identical amount of server work would instead ship a million rows over the connection.
Session state belongs to one connection
A transaction and a temporary table are held in the server session for this connection, then thrown away.
BEGIN;
CREATE TEMP TABLE cart(item text);
INSERT INTO cart VALUES ('socks'), ('lamp');
SELECT count(*) AS items FROM cart;
ROLLBACK;
SELECT to_regclass('cart') IS NULL AS cart_is_gone;Example explained
Line 1BEGIN opens transaction state inside the server backend serving this one connection; no other client's session changes.
Line 2CREATE TEMP TABLE puts cart in a per-session schema, so a second connection asking for cart finds nothing even before the rollback.
Line 3The count comes back as one row, so the two inserted rows themselves never travelled to the client.
Line 4ROLLBACK discards the rows and the table creation on the server, which is why to_regclass('cart') then resolves to NULL.
Important notes
SQLite is the exception to all of this: the engine is a library inside your own process and a connection is a handle on a file, so ports, authentication and network round trips do not apply.
Closing your client program is not the same as closing the connection; an abandoned session can keep an open transaction and its locks alive on the server until a timeout removes it.
Common mistakes
Treating a returned result set as a live view of the table; it is a copy taken while the statement ran, so a screen built from it silently goes stale as soon as another client commits.
Looping in application code and sending one SELECT per row: each iteration pays the full round-trip latency, so 500 lookups at 2 ms each burn a second of pure waiting while the server does almost no work.
Running SET search_path or CREATE TEMP TABLE on a pooled connection and expecting later statements to see it; the pool may hand the next statement a different backend, where the setting is simply the server default and the temp table does not exist.
Try it yourself
Change, predict, then run
In a browser Postgres editor run SELECT count(*) FROM generate_series(1, 200000); and then SELECT * FROM generate_series(1, 200000); and note which one makes the page crawl. Write one line saying what crossed the connection in each case.
Open the SQL workspaceCheck your understanding
An application opens a connection, runs SET TIME ZONE 'UTC', returns the connection to a pool, and later reads timestamps that come back in the server's default zone instead of UTC. What best explains this?
- SET only takes effect once the surrounding transaction commits.
- The client driver re-formats timestamps into the machine's local zone after receiving them.
- The later read was served by a different pooled connection, and the time zone was stored only in the session of the connection that ran SET.
- Time zone is server-wide configuration, so a single client is not permitted to change it.
Show answer
SET writes into the session the server keeps for one specific connection, so the setting is gone the moment a different connection serves the next statement, which is exactly what a pool is free to do. Option 1 is tempting because drivers really do convert timestamps sometimes, but then every read would be shifted the same way rather than only the ones that land on another connection; and SET TIME ZONE applies immediately, with no commit needed.