Using SQL from Application Code

Most SQL you ever run in anger will not be typed into psql. It will be sent by an application, thousands of times a minute, by code written by people who mostly think about something else.

The database does not care what language you use. The failures are the same in all of them: too many connections, a transaction nobody closed, a loop that fires one query per row, and a Decimal quietly becoming a float. The habits below prevent each of them.

A connection is expensive#

Each PostgreSQL connection is a separate server process with its own memory. Opening one takes tens of milliseconds (TCP, TLS, authentication, process startup) and the server has a hard ceiling, max_connections, 100 by default.

Two patterns follow:

• Never open a connection per request. Create a pool at startup and borrow from it.
• Do not size the pool by traffic. A pool of 10 to 20 connections per application instance is usually plenty. More connections beyond the number of CPU cores do not make the database faster, they make it fight itself.

If you have many application instances, put a connection pooler such as PgBouncer in front of the database, so thousands of clients can share a few dozen real connections.

Python with psycopg 3#

import psycopg
from psycopg.rows import dict_row
from psycopg_pool import ConnectionPool

DSN = "postgresql://postgres:postgres@localhost:5432/shop"

pool = ConnectionPool(DSN, min_size=2, max_size=10,
                      kwargs={"row_factory": dict_row})

def get_customer(customer_id: int):
    with pool.connection() as conn:
        return conn.execute(
            "SELECT customer_id, full_name, city FROM customers WHERE customer_id = %s",
            (customer_id,),
        ).fetchone()

print(get_customer(1))
# {'customer_id': 1, 'full_name': 'Aisha Khan', 'city': 'Mumbai'}

Four details in that short snippet matter.

1. pool.connection() is a context manager. It borrows a connection, commits if the block finishes normally, rolls back if it raises, and returns the connection to the pool either way.
2. %s is a placeholder, not string formatting. The value goes as the second argument, always as a tuple, even for one value ((customer_id,)).
3. dict_row makes rows come back as dictionaries, which survive column reordering.
4. fetchone() returns None when nothing matches. Handle that case.

Node with pg#

import pg from 'pg';
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 10 });

async function getCustomer(id) {
  const { rows } = await pool.query(
    'SELECT customer_id, full_name, city FROM customers WHERE customer_id = $1',
    [id]
  );
  return rows[0] ?? null;
}

PostgreSQL's native placeholders are $1, $2, and so on. A transaction needs a single connection for all its statements, so check one out explicitly:

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE account_id = $2', [200, 1]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE account_id = $2', [200, 2]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();
}

The classic bug is calling pool.query('BEGIN') and then pool.query('UPDATE ...'). The pool hands each call to a different connection, so the BEGIN and the UPDATE never share a transaction.

How data types cross the boundary#

In JavaScript, pg returns NUMERIC and BIGINT as strings on purpose, to avoid losing precision. Resist the urge to parseFloat them blindly. Use a decimal library for money.

Transactions in code#

Most drivers open a transaction implicitly at your first statement and keep it open until you commit. Know your driver's default, because forgetting to commit means your writes vanish when the connection returns to the pool, and the locks linger until then.

In psycopg, scope related work explicitly:

with pool.connection() as conn:
    with conn.transaction():
        conn.execute("UPDATE accounts SET balance = balance - %s WHERE account_id = %s", (200, 1))
        conn.execute("UPDATE accounts SET balance = balance + %s WHERE account_id = %s", (200, 2))

Nested conn.transaction() blocks become savepoints, which is the "recover from an expected error" tool from Module 5.

Keep transactions short. Do the API calls, the file reads and the user waiting before you open the transaction, and never while holding one.

Handling errors by meaning#

Catch the specific exception, not a bare Exception, and translate it into something useful.

from psycopg import errors

try:
    conn.execute(
        "INSERT INTO customers (full_name, email, city, country) VALUES (%s, %s, %s, %s)",
        (name, email, city, country),
    )
except errors.UniqueViolation:
    return "That email is already registered."
except errors.CheckViolation:
    return "One of the values is out of range."

Retry on the two errors that are expected under load, deadlock detected and serialization failure, and nothing else. A blanket retry on every error hides real bugs and turns an outage into a retry storm.

The N+1 problem#

This is the single most common performance bug in database-backed applications. One query fetches a list, then the code runs one more query per item.

# N+1: 1 query for the list, plus 1 per customer
customers = conn.execute("SELECT customer_id, full_name FROM customers").fetchall()
for c in customers:
    n = conn.execute(
        "SELECT COUNT(*) AS n FROM orders WHERE customer_id = %s", (c["customer_id"],)
    ).fetchone()["n"]
    print(c["full_name"], n)

For 10 customers that is 11 queries. For 10,000 customers it is 10,001 round trips. At a millisecond each, ten seconds, and at network latency to a managed database, far more. Every one of those queries is individually fast, which is why the profiler never flags them.

Ask for everything in one statement:

rows = conn.execute("""
    SELECT c.customer_id, c.full_name, COUNT(o.order_id) AS orders
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.full_name
    ORDER BY c.customer_id
""").fetchall()

When the second query cannot be folded into a join, batch it. Pass a list and use ANY:

ids = [c["customer_id"] for c in customers]
orders = conn.execute(
    "SELECT order_id, customer_id FROM orders WHERE customer_id = ANY(%s)", (ids,)
).fetchall()

Two queries instead of N+1. ORMs produce N+1 silently through lazy loading: touching customer.orders inside a loop triggers a query each time. Turn on query logging in development and count the queries per request.

Large results: do not fetch everything#

fetchall() on 20 million rows loads them all into application memory. Stream with a server-side (named) cursor instead:

with pool.connection() as conn:
    with conn.cursor(name="export_orders") as cur:
        cur.execute("SELECT order_id, customer_id, order_date FROM orders ORDER BY order_id")
        for row in cur:                  # rows arrive in batches
            write_csv_line(row)

Memory stays flat no matter how big the result.

Timeouts and observability#

Set limits so a bad query cannot hold the system hostage.

SET statement_timeout = '5s';                        -- abort queries that run too long
SET idle_in_transaction_session_timeout = '30s';     -- kill forgotten open transactions

Set these per role (ALTER ROLE web_app SET statement_timeout = '5s') or per connection. Also pass an application_name in the connection string. It appears in pg_stat_activity, and when something is stuck at 3 a.m. you will be glad to see which service it was.

ORM or raw SQL#

An ORM saves you from writing repetitive INSERT and SELECT by id, maps rows to objects, and often handles migrations. It costs you control of the SQL, and it makes N+1 easy.

A practical split: use the ORM for ordinary create, read, update and delete on single entities. Write raw SQL, with parameters, for reports, aggregations, window functions and anything performance-sensitive. Whichever you pick, read the SQL it actually generates.

Common mistakes#

A connection per request. Handshake cost and a quickly exhausted max_connections.

A huge pool "to be safe". Contention on the server gets worse, not better.

Starting a transaction on one pooled connection and running statements on another. The statements never join it.

Converting NUMERIC to float. Money drifts.

N+1 queries. One join or one ANY fixes it.

fetchall() on unbounded results. Out-of-memory in production, fine in testing.

Catching all exceptions and retrying. Hides bugs.

Practice#

1. Write a small script that opens a pool, runs a parameterized query for customers in a given country, and prints them. Test it with a value containing a single quote.
2. Write the N+1 version and the joined version of "each customer with their order count". Turn on log_statement = 'all' (or count queries in your driver) and compare the number of statements.
3. Write a transfer function using an explicit transaction. Force an error between the two updates and verify the balances do not change.
4. Stream the entire order_items table to a CSV file with a named cursor.
5. Set statement_timeout = '1s' on your session and run SELECT pg_sleep(3);. Read the error, then explain where you would set this in production.