Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 37 ยท 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