Skip to content

Lesson 34 ยท Performance and Patterns

Connection Pooling and Caching

Size the connection pool in TypeORM and Sequelize, avoid leaks and exhaustion, and add a cache-aside layer with expiry and invalidation.

Opening a database connection is expensive: a TCP handshake, authentication, sometimes TLS. A connection pool opens a small number of connections once and lends them out to queries. Both TypeORM and Sequelize use one by default. When the pool is configured badly, you get a classic production failure: everything is fast, then under load requests start hanging for 30 seconds and then fail with "timeout acquiring connection".

The second half of this lesson covers caching: not asking the database for the same answer over and over. The demos are plain Node (a hand-made pool and cache), so you can watch the mechanics. ORM settings are read-only.

How a pool works

flowchart LR
  R1["Request 1"] --> P{"Pool<br/>max 3"}
  R2["Request 2"] --> P
  R3["Request 3"] --> P
  R4["Request 4<br/>waits"] -.-> P
  P --> C1["Connection A"]
  P --> C2["Connection B"]
  P --> C3["Connection C"]

When all connections are busy, the next request queues until one is released or the wait times out.

Node.js A tiny connection pool (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

With two connections and four requests, C and D start only after A and B finish. Total time is two query lengths, not one. If a request never releases, a connection is lost forever; with max = 2, two such leaks stop the whole service. This is why transaction helpers use finally (see Transactions in TypeORM and Sequelize).

Configuring the pool

TypeScript
// TypeORM with PostgreSQL: options inside `extra` go to the pg driver
new DataSource({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  poolSize: 10,                         // max connections (pg, mysql)
  extra: { idleTimeoutMillis: 30000, connectionTimeoutMillis: 5000 },
});
JavaScript
// Sequelize
new Sequelize(process.env.DATABASE_URL, {
  pool: {
    max: 10,        // maximum connections
    min: 0,         // keep none open when idle
    acquire: 30000, // ms to wait for a free connection before throwing
    idle: 10000,    // ms an idle connection stays open
  },
});

Sizing

  • The database has a limit (max_connections, 100 by default on PostgreSQL). Total connections = app instances x pool size (plus workers and cron jobs). Ten instances with max: 20 ask for 200.
  • More connections is not faster. A database with 8 cores serves 10 to 30 concurrent queries well; beyond that they just queue inside the database. A pool of 10 per instance is a common starting point.
  • Serverless and many small instances need an external pooler such as PgBouncer or the provider's proxy.
  • Set a sensible acquire timeout so a saturated pool fails fast instead of hanging requests.
  • Close the pool on shutdown (dataSource.destroy(), sequelize.close()), or your process and tests will not exit.

Cache-aside

A cache keeps recent answers in memory (or Redis) so the database does less work. The standard recipe is cache-aside: check the cache; on a miss, query the database and store the result with an expiry; on a write, delete the cached entry.

Node.js Cache-aside with expiry and invalidation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Three calls, two database reads. The middle one came from the cache; the write removed the stale entry so the next read was fresh.

Pitfalls

  • Stale data. A TTL bounds staleness; invalidation reduces it. Decide what delay is acceptable per query.
  • Stampede. When a hot key expires, hundreds of requests may query the database at once. Share the in-flight promise so only one does.
  • Multiple instances. An in-process Map is per instance; with several app servers, use Redis or accept short TTLs.
  • Cache only what is expensive and read often. Caching cheap primary-key lookups often adds complexity for no gain.
Node.js Avoid a stampede by sharing the in-flight promise

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Built-in ORM caches

TypeORM has an optional query result cache (cache: true on the data source, cache: 60000 per find or .cache(true) on query builders) that stores results in a table, Redis or memory, keyed by the SQL. It is easy to turn on but invalidates only by time or manually. Sequelize has no built-in cache; use cache-manager, Redis, or the cache-aside pattern above. Neither replaces thinking about which data may be stale.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does a connection pool help?

    Connections are reused instead of being established again each time.
  2. 2. Ten app instances each use a pool max of 20. What is the potential load on the database?

    Instances multiply: 10 x 20 = 200, which may exceed the database limit.
  3. 3. What happens if code acquires a connection and never releases it?

    Leaked connections shrink the pool until requests wait and time out.
  4. 4. In cache-aside, what should a write do?

    Removing the stale entry makes the next read fetch fresh data.

Summary

  • Pools reuse connections; size them so instances x max stays under the database limit.
  • Always release connections, set an acquire timeout and close the pool on shutdown.
  • Cache-aside: read the cache, fall back to the database, store with TTL, invalidate on write.
  • Guard against stampedes and remember each instance has its own memory.

Next: Project: Blog API with Migrations