The whole course has run SQL from psql: you type, you hit Enter, you read the result. In a real application none of that happens. There's a web process serving hundreds of requests per second, each with a few milliseconds of budget, that can't afford to open a connection, that runs its queries from code in another language, that often doesn't even write them —an ORM generates them— and in which a design mistake shows up not as a message but as "the site has been slow since yesterday".

This lesson is that environment: how an application connects, how it executes and how it manages its transactions, what an ORM gives you and what it takes away, the four antipatterns that kill a website —starting with the N+1 that 08-04 left pending—, the patterns that do work and the checklist for when something goes wrong. With it the module closes and you're ready for the final project.

1. The connection and the pool
2. Running SQL from code
3. ORM versus hand-written SQL
4. The antipatterns that kill a website
5. Useful patterns
6. Operations: deployment, monitoring and timeouts
7. "The site is slow": a checklist
8. Common Mistakes and Tips
9. Exercises
10. Module conclusion

A connection to PostgreSQL starts with a connection string, and the URI format is the canonical one:

Four things to always look at: the user —web_prod, not the superuser or the owner (11-03)—; the sslmode, which must be at least require and preferably verify-full, because prefer silently accepts an unencrypted connection; the application_name, which will show up in pg_stat_activity and tell you which service is firing the query that's choking the database; and the fact that the password is never in the repository, but in an environment variable or a secrets manager.

Opening a connection to PostgreSQL is expensive. It isn't just a socket: the server creates an operating-system process per connection, negotiates TLS, authenticates and initializes its memory. That's on the order of tens of milliseconds, against the 0.2 ms the query you were about to run takes. If you open and close a connection per request, 99 % of the time goes on the connection.

And it isn't fixed by opening lots and leaving them open: every idle connection consumes memory and max_connections (100 by default) is a limit that, once exceeded, makes requests fail. The solution is a pool: a small set of already-open connections that requests borrow and give back.

The size surprises everybody: it's small. A widely used starting rule is cores × 2 + disk spindles, which for an ordinary machine gives between 10 and 20 connections, not two hundred. The reason is that the database doesn't go faster by receiving more requests at once: if it has 8 cores, 200 simultaneous queries don't run in parallel, they fight. A small pool queues in the application, which is where you can wait in an orderly fashion, instead of saturating the server.

⚠️ Careful with the multiplication. The limit that matters is the total: 8 application instances with a pool of 20 is 160 connections, plus the background worker's, plus the reporting ones. That number has to fit in max_connections, with room to spare for administrative connections.

When there are many instances, or when the platform creates a process per request (PHP's classic model), you put an external pooler in front: PgBouncer sits between the application and PostgreSQL and multiplexes hundreds of client connections over a few real ones.

transaction mode has small print and you need to know it: since the connection changes between transactions, everything that lives in the session stops working — named prepared statements, session SETs (use SET LOCAL), temporary tables, LISTEN/NOTIFY and 09-05's session advisory locks. If your ORM uses named prepared statements, you have to disable them or use a PgBouncer version that supports them.

Three pieces, using what you learned in 11-03 and in module 9:

One query, five rows, everything the screen needs: the customer's name, the status, the total and the number of lines. Keep this query in mind, because section 4 is going to compare it with the version that fires one more query for every order in the list.

When you insert, you need the generated id. The right way is RETURNING (05-02), not a subsequent SELECT MAX(id) —which is also wrong under concurrency:

09-03's pattern, written in code. Everything that has to happen together goes inside the same block:

Four rules you learn through incidents: rollback on every error, because an open transaction holds locks and blocks VACUUM (09-02); return the connection in the finally, or the pool runs dry and the whole application freezes; keep the transaction as short as possible (section 4); and tell expected errors from unexpected ones — a UNIQUE violation is a message to the user, not a 500.

An ORM (Object-Relational Mapper) translates between tables and the language's objects: Order.objects.filter(status="paid") becomes a SELECT.

The course's criterion: an ORM for CRUD, hand-written SQL for reports and critical queries. It isn't a cowardly middle ground: it's that the two are different problems. "Save an order and its lines" is exactly what an ORM does well. "Monthly revenue with running total, moving average and change" is exactly what it does badly.

And here's the concrete example, section 2's query: a JOIN to two tables, a LEFT JOIN that mustn't lose orders, a GROUP BY, an amount expression with a discount and an ORDER BY with a tie-breaker. No ORM writes it well unaided: either it makes three queries, or it fetches whole objects to count their lines in memory, or it generates a GROUP BY with every column of the model. Write it by hand, keep it in a .sql file in the repository (11-02) and run it through the ORM's driver — they all let you. And when you do, keep parameterizing: raw()/text()/$queryRaw is exactly where SQL injection comes back (11-03).

21 queries against 1 to render the same table. And what makes the N+1 serious is how it scales, because the cost isn't each query's time but the fixed cost you pay on every one: network round trip, parsing, planning:

How to detect it: count the queries per request. A counter in the middleware logging "this request made 61 queries" finds an N+1 in five minutes; pg_stat_statements gives it away as a query with a tiny mean_exec_time and an absurd number of calls, which is why you sort by total time. The tools for each environment: django-debug-toolbar, Rails Bullet, Laravel Telescope, Hibernate statistics.

Over the course's 20 orders, the second one with (:last_date, :last_id) = ('2026-01-13', 17) returns orders 16, 15, 14, 13 and 12 with LIMIT 5: exactly the page after section 2's, without rereading anything.

It's 09-01's point taken into the real world, and the damage goes beyond that request: a long transaction stops VACUUM cleaning up old row versions across the whole database (09-02), so a single slow request degrades every other one. Watch idle in transaction and set idle_in_transaction_session_timeout.

The systematic way to find them: list the screens, and for each one write down the WHERE and the ORDER BY it runs. That list is your list of candidate indexes — and only that, because every surplus index slows down all writes (08-02).

• Cursor pagination for infinite scroll and APIs. The response carries an opaque next_cursor (the last row's tuple, encoded) and the client sends it back. Constant cost and no repeated or lost rows when somebody inserts while the user is browsing.
• Optional filters with 11-01's pattern, and a defensive LIMIT on every user-facing query: even if the interface only lets you ask for 50, the API must impose a maximum (LEAST(:limit, 100)). Without it, somebody will ask for ?limit=1000000 on your busiest day.
• Caching for what changes little and is read a lot: the catalogue, the categories, the home page. And with the usual warning: invalidation is the hard problem. Start with time-based expiry, which is simple and predictable, and leave event-based invalidation for when you really need it.
• Job queues with SELECT ... FOR UPDATE SKIP LOCKED (09-05). Several worker processes take tasks from the same table without treading on each other or waiting: each skips the rows already locked by another. It's the canonical pattern for sending emails, generating invoices or processing images without setting up a separate queueing system.

• JSON straight from PostgreSQL (10-06): jsonb_build_object + jsonb_agg return the order with its lines nested in one row and one query, instead of fetching flat rows and reassembling them in the language. When it pays off: deeply nested responses the application only passes straight through. When it doesn't: if the application has to walk, validate or transform the object —then prefer typed rows—, if the JSON gets huge, or if mixing it with the ORM forces you to maintain two ways of reading the same thing.

During a deployment, the new code and the old code coexist, if only for a few seconds. If the migration and the code are deployed together and the migration breaks the previous schema, the old version fails for as long as the change lasts. Hence 05-06's expand/contract, in three deployments:

And two operational details that prevent an outage: CREATE INDEX CONCURRENTLY, because a normal CREATE INDEX blocks the table's writes while it's built; and a short lock_timeout before an ALTER TABLE, so the migration fails in three seconds instead of queueing every query behind a lock it can't get (09-05).

Sort by total_exec_time, not by the average: it's the only thing that makes the N+1 visible, since its average is tiny and its total is enormous. And three settings that should be in place from day one:

Set them per role: ALTER ROLE web_prod SET statement_timeout = '10s'; lets reports and migrations, on other roles, have their own limits.

In this order, because it goes from the most likely and cheapest to the rarest and most expensive:

1. Is it everything or one screen? If it's one, look at its queries; if it's everything, suspect the server, the pool or a lock.
2. How many queries does that request make? A counter. If it's dozens, it's an N+1 and you're done.
3. Is the pool running dry? If requests are waiting to get a connection, the problem isn't in the database: it's the pool, or transactions that never close. Check idle in transaction.
4. pg_stat_statements by total time. The top three queries usually explain 80 % of the load.
5. EXPLAIN ANALYZE the suspect (08-05). A Seq Scan where there should be an index? Estimates far from reality?
6. Are there locks? pg_locks with pg_stat_activity: a query in Lock isn't slow, it's waiting (09-05).
7. Have the statistics or the volume changed? Was there a bulk load without an ANALYZE (08-04)?
8. Are too many rows being fetched? A fast query returning 200,000 rows saturates the network and the application's memory.
9. And only then, the server: CPU, memory, disk, pending VACUUM, number of connections.

The rule is 08-04's applied to the web: measure before you touch. And count queries, not just milliseconds, because the most common problem of all appears in no slow-query ranking.

• Opening a connection per request. The connection cost dwarfs the query's. Use a pool.
• Configuring the pool with 200 connections "just in case". The database doesn't go faster for receiving more at once; a small pool queues where waiting is possible. And remember to multiply by the number of instances.
• Not returning the connection to the pool. A return in the middle of a try with no finally drains the pool and freezes the whole application.
• Leaving a transaction open during an external call. Locks, idle in transaction and a VACUUM that can't do its job.
• Trusting the ORM to do the right thing. Look at the SQL it generates: nearly all of them have a logging mode. What you don't look at, you don't know. And using raw() with concatenation: that's where injection comes back (11-03).
• Paginating with OFFSET in an API. Slow on deep pages and with repeated or lost rows if somebody inserts.
• Not putting a LIMIT on a user-facing query. Somebody will ask for a million rows.
• Deploying a migration and code together with a breaking change. Expand/contract, and CREATE INDEX CONCURRENTLY.
• Tip: log the number of queries and the database time of every request. With those two metrics, this section's problems show up before a user sees them.
• Tip: set application_name in the connection string. When the database is suffering, you'll know which service is hammering it instead of guessing.
• Tip: keep complex queries in .sql files in the repository, not embedded between lines of code. They review better, they can be tested in psql and they can be formatted (11-02).

The customer page shows their details, their orders and, for each order, its lines with the product's name and its category. The log says 23 queries per load on Lucía's page.

1. Where exactly do the 23 come from? (Hint: Lucía has 3 orders, of 3 lines each.)
2. Reduce the page to two queries and write them.
3. Why two and not one?

Your application runs on 6 instances, each with a pool of 25 connections. There's also a background worker process with 10 and a reporting server with 5. max_connections is set to 100.

1. What's the problem and what error will users see?
2. Give three solutions, from the cheapest to the most expensive.
3. If you choose PgBouncer in transaction mode, what do you have to review in the code?

An API returns an order's detail with its customer and its lines. The team is debating between (a) three queries and reassembling in code, (b) one query with a JOIN and reassembling, (c) one query returning the JSON already built (10-06).

1. Write option (c) for order 1 and say what it returns.
2. Give one argument for and one against each option.
3. Which would you choose if the API has to return 50 orders in the same response?

Solution 1

1. 1 for the customer + 1 for their list of orders + 3 (one per order, to fetch its lines) + 9 (one per line, for the product's name) + 9 (one per line, for the product's category) = 23. It's a nested N+1: each level of the template multiplies the previous one, which is why the number grows so fast. With a customer with 20 orders of 5 lines it would be 1 + 1 + 20 + 100 + 100 = 222 queries for a single screen.

2. Two queries: one for the orders' headers and another for all their lines at once, with the product already joined.

For Lucía (customer 1), the first returns 3 rows —orders 1, 5 and 15, with €42.10, €32.10 and €33.40, which add up to her canonical €107.60— and the second, 9 rows. Notice = ANY(%(ids)s) with an array: it's the right way to pass a list of ids without building the SQL by concatenation (11-03).

3. Because header and detail have different cardinalities. With a single query, the order's total would repeat on every one of its lines and you'd have to deduplicate it in code, or you'd have to aggregate the lines into JSON. Two queries, each at the granularity of what it returns, are clearer and more efficient than one that multiplies rows. Every ORM's "eager loading" rule is exactly this: one query per level, not one per item.

Solution 2

1. 6 × 25 + 10 + 5 = 165 possible connections against a max_connections of 100. As soon as there's load, PostgreSQL will reject new ones with FATAL: sorry, too many clients already, and users will see intermittent 500 errors — intermittent and therefore hard to diagnose, because they only appear at the peaks. Worse still, it can even stop you connecting to administer the server if there are no reserved slots left (superuser_reserved_connections).

2. (a) Lower the pool size to 10 per instance: 6 × 10 + 10 + 5 = 75, with room to spare. It's free, immediate and probably won't make performance worse, because 165 simultaneous queries don't fit on the machine's cores anyway. (b) Put PgBouncer in transaction mode: hundreds of client connections over 20 real ones. (c) Raise max_connections and the server's memory: it's the most expensive, it needs a restart and it only moves the problem, because every connection is a process with its own memory.

3. With transaction the connection changes between transactions, so you have to review: named prepared statements (many drivers use them by default: disable them or use the PgBouncer version that supports them), session SETs (switch to SET LOCAL inside the transaction — including 11-01's audit SET app.username and 11-03's RLS one), temporary tables, LISTEN/NOTIFY and session advisory locks (09-05). Anything that relies on "still being in the same session" stops working.

Solution 3

1. It's 10-06's query: jsonb_build_object with the id, the date and the shipping, the customer nested in another object and the lines in a correlated jsonb_agg. It returns one row and one column with order 1 complete: Lucía Martínez Soler, Spain, and her three lines —olive oil €23.90, rice €11.70 and chamomile tea €6.50— adding up to the order's €42.10.

2. (a) Three queries: for, it's the simplest and each query is trivial to cache and index; against, that's three network round trips. (b) JOIN and reassemble: for, a single round trip and typed data the application can transform; against, it repeats the header on every line and forces you to group in code, which is manual, error-prone work. (c) JSON from PostgreSQL: for, one round trip and zero reassembly code; against, the application receives an opaque blob it can't transform without parsing it, the typing is lost and the API's response shape gets coupled to the SQL — changing a field of the contract means touching the query.

3. With 50 orders, (c), for one specific reason: it's the only one that doesn't grow in round trips and doesn't force you to group 50 headers with their ~120 lines in code. Option (a) would turn into an N+1 if done per order —you'd have to rewrite it as two queries with = ANY(...), which is exercise 1's solution— and (b) would return 120 rows with the header repeated. The condition for (c) being a good idea is still the same: that the API forwards the JSON as is. If it has to touch it, (a) in its two-query version comes back.

This is how SQL reaches a real application:

• The connection carries a least-privilege user, TLS (verify-full, not prefer) and an application_name, and the password is never in the repository. And it always goes through a pool, because opening a connection costs tens of milliseconds and each one is a process on the server. The size is small —10-20 per instance— and you have to multiply it by the number of instances. PgBouncer in transaction mode multiplexes hundreds of clients over a few real connections, at the price of losing everything that lives in the session.
• From code: parameterized queries (11-03), RETURNING for the id (05-02) and the try / commit / except rollback / finally putconn block (09-03), with the transaction as short as possible and the connection always returned.
• ORM versus SQL: the ORM gives mapping, migrations, security by default and productivity; it takes away control of the SQL and performance predictability. The criterion: an ORM for CRUD, hand-written SQL for reports and critical queries — like the latest-orders listing, which no ORM writes well.
• The antipatterns: the N+1, which turns 1 query into 21 or 501 and appears in no slow-query ranking —closing 08-04's promise—; fetching everything and paginating in memory, fixed with LIMIT and keyset; the open transaction waiting on an external API, which holds locks and slows down VACUUM across the whole database; and missing indexes on the columns the site filters by, starting with the foreign keys.
• The patterns: cursor pagination, optional filters, a defensive LIMIT on every exposed query, caching with time-based expiry, queues with SKIP LOCKED and JSON straight from PostgreSQL when the API only forwards it.
• Operations: backwards-compatible migrations in expand/contract, CREATE INDEX CONCURRENTLY, a lock_timeout before an ALTER, pg_stat_statements sorted by total time, and statement_timeout and idle_in_transaction_session_timeout per role. Plus the nine-point checklist for when "the site is slow", which starts by counting queries.

And with that module 11 closes and, with it, the learning. In five lessons you've gone from knowing the language to knowing the craft: the use cases that reappear in every project and which tool solves each one; the best practices of naming, formatting, design, reliability and process, with their catalogue of antipatterns and their checklist; security, with SQL injection and its one real defence, PostgreSQL's role and permission model and the handling of personal data; data analysis, where window functions turned into reports and where you learned that defining and validating matter more than querying; and web development, where the SQL actually runs.

One thing is left, and it's the only one you can't learn by reading: doing it yourself. In module 12, Final project, you'll build a complete system from start to finish: the project description and its business context, the requirements it has to meet, the step-by-step implementation —model, DDL, data, queries, indexes, views, security and performance—, the annotated solutions with each decision justified one by one, and the presentation of the results. Everything from twelve modules, together and in a single piece of work. It's time to stop following a course and start building.