Access tokens and Knex pool exhaustion: why last_used_at updates on every request

[dondanidang]

Short version

If you use @adonisjs/auth access tokens under high authenticated QPS, you may see:

KnexTimeoutError: Timeout acquiring a connection. The pool is probably full.

even when PostgreSQL CPU and connection count look fine.

One quiet amplifier: DbAccessTokensProvider.verify() always UPDATEs last_used_at on every authenticated request. There is no built-in way to throttle or disable that write. On a busy API that can mean one extra write per request, which holds Knex pool slots and crowds out everything else — including login.

We mitigated by subclassing the provider and only updating last_used_at every N minutes. A first-class option in core would help a lot of production apps.

---

What we saw

Symptoms looked like “the database is dying”:

• Intermittent 500s on authenticated routes and on POST /auth/signin
• p99 latency pegged near the Knex acquireTimeoutMillis (e.g. 5s)
• Sentry: KnexTimeoutError rotating across random “culprits” (AuthMiddleware, sign-in, random services)

But infra contradicted that story:

• RDS CPU low, server-side connections low
• App task CPU also low (single-digit %)
• Traffic was normal, not a spike

So this was not “Postgres is out of capacity.” It was per-process Knex pool starvation: every Node process has a small pool (e.g. max: 10–20). When too many concurrent requests each hold a connection — especially on short writes — new acquires time out after a few seconds.

Classic red herring: the error message asks about missing .transacting(trx). Sometimes that is the cause. Often it is just “the pool is busy.”

---

The Adonis access-token path

With the access tokens guard, every authenticated request roughly does:

1. verify(token) → SELECT token row
2. UPDATE last_used_at = now()
3. Validate hash + expiry
4. SELECT user by id

In @adonisjs/auth (access tokens provider), the update is unconditional:

// DbAccessTokensProvider.verify (simplified)
dbRow.last_used_at = new Date()
await db.from(this.table).where({ id: dbRow.id, type: dbRow.type }).update({
  last_used_at: dbRow.last_used_at,
})

That is a reasonable default for audit / “last active” / idle cleanup. Under load it becomes expensive:

Authenticated QPS	Extra writes / minute from last_used_at alone
100	~6,000
500	~30,000
1,000	~60,000

Mobile apps that poll dashboard / “me” endpoints make this especially painful: the same token is verified many times per minute, and almost every verify is a write.

Meanwhile unauthenticated login only needs a single User.findBy(...). If the process pool is already full of auth middleware work, login fails with a pool timeout — which feels like “auth is broken” when the real issue is connection slot contention.

---

What did not fix it (alone)

• Scaling the database instance
• Staring at RDS Proxy / max_connections first
• Only raising DB_POOL_MAX (helps briefly; can increase pressure on the proxy/DB)
• Assuming the Sentry “culprit” frame is the root cause (it is usually just who lost the race for a connection)

What did help as relief:

• More API tasks (more separate pools behind the load balancer)
• Lowering background worker concurrency so the shared DB path was less busy
• Removing unnecessary query volume and write-on-every-request paths

---

Mitigation: throttle last_used_at

Subclass DbAccessTokensProvider, override verify(), and skip the UPDATE when last_used_at is recent (e.g. 15 minutes). Keep SELECT + hash/expiry checks every time.

Sketch:

import { AccessToken, DbAccessTokensProvider } from '@adonisjs/auth/access_tokens'
import type { Secret } from '@adonisjs/core/helpers'

const UPDATE_INTERVAL_MS = 15 * 60 * 1000

export class ThrottledDbAccessTokensProvider extends DbAccessTokensProvider {
  static forModel(model, options) {
    return new ThrottledDbAccessTokensProvider({ tokenableModel: model, ...options })
  }

  async verify(tokenValue: Secret<string>) {
    const decoded = AccessToken.decode(this.prefix, tokenValue.release())
    if (!decoded) return null

    const db = await this.getDb()
    const dbRow = await db
      .query()
      .from(this.table)
      .where({ id: decoded.identifier, type: this.type })
      .limit(1)
      .first()

    if (!dbRow) return null

    const now = new Date()
    const lastUsed = dbRow.last_used_at ? new Date(dbRow.last_used_at).getTime() : 0
    if (!lastUsed || now.getTime() - lastUsed >= UPDATE_INTERVAL_MS) {
      dbRow.last_used_at = now
      await db
        .from(this.table)
        .where({ id: dbRow.id, type: dbRow.type })
        .update({ last_used_at: now })
    }

    const token = this.dbRowToAccessToken(dbRow)
    if (!token.verify(decoded.secret) || token.isExpired()) return null
    return token
  }
}

// User model
static accessTokens = ThrottledDbAccessTokensProvider.forModel(User)

Trade-off: last_used_at becomes “last used within ~15 minutes,” not “exact last request.” For most products that is fine. Security (hash + expiry) is unchanged.

Also audit your own middleware for accidental full-table scans or N+1 on every request — those hold pool slots even longer than a single last_used_at update.

---

Ask for core: make this configurable

It would be great if @adonisjs/auth supported something like:

DbAccessTokensProvider.forModel(User, {
  // proposed API (names bikeshed-friendly)
  lastUsedAtUpdateInterval: '15m', // or false / 0 to disable updates
})

Or a hook / strategy:

shouldUpdateLastUsedAt(tokenRow) => boolean

Defaults can stay as today (update every verify) for backward compatibility. Production APIs with high authenticated QPS could opt into throttling without forking verify().

Happy to open an issue / PR against adonisjs/auth if maintainers are open to the idea.

---

Checklist if you hit KnexTimeoutError

1. Confirm RDS/server metrics are actually healthy.
2. Check Knex pool.max and acquireTimeoutMillis per process.
3. Count queries (especially writes) on the auth middleware path.
4. Remember access-token verify = SELECT + UPDATE today.
5. Look for other hot middleware that loads unbounded rows.
6. Prefer fixing query volume before endlessly raising pool size.

---

Closing

Access tokens that touch last_used_at on every request are a footgun at scale, not a bug in Adonis — but the lack of a throttle option makes the footgun hard to avoid without subclassing. If this resonates, a small core knob would save a lot of production debugging.

Feedback welcome — especially from folks who solved this differently (cache layer, JWT, etc.).

[MILLERMARRU]

Nothing in the current @adonisjs/auth API surface gives you a built-in throttle for last_used_at, and subclassing DbAccessTokensProvider the way you did is the sanctioned extension point, forModel() exists specifically so you can swap in a custom provider without touching the guard or middleware layer, so this isn't a hack bolted onto something not designed for it.

One thing worth changing in the implementation, though: your sketch does a SELECT, checks the interval in JS, then conditionally UPDATEs, that's still two round trips on the common "needs update" path and a wasted branch check on every request either way. Pushing the interval check into the UPDATE's WHERE clause makes the skip atomic and removes the JS-side branching entirely:

UPDATE access_tokens
SET last_used_at = now()
WHERE id = ? AND type = ?
  AND (last_used_at IS NULL OR last_used_at < now() - interval '15 minutes')

Now every verify still does exactly one UPDATE statement, but it's a no-op write (0 rows affected) on the throttled path instead of a full row write, and you don't need Date arithmetic or an extra field read in application code to decide. That should shave real pool time off the hot path on top of what the throttling itself already buys you.

The core feature request is reasonable on its own merits, but I'd frame the ask around the WHERE-clause version rather than an interval config knob, it gets you the same behavior with less surface area for the maintainers to review, and it's a smaller diff against the existing verify() implementation than adding a new option and branching path.