Tracking last-seen timestamps at scale #

“Last seen 3 minutes ago” looks like a tiny feature, and the first implementation is one line: on every heartbeat or message, UPDATE users SET last_seen = now() WHERE id = $1. With a thousand users that is invisible. With 400,000 connected users sending a heartbeat every 25 seconds, it is 16,000 writes per second to the primary database — more than the rest of the application combined — for a value nobody reads at that precision. Rows are locked constantly, replication lags, vacuum cannot keep up, and the product team is surprised to learn that the busiest table in the system exists to render a gray label. Last-seen data needs to be recorded coarsely, buffered in memory or Redis, and flushed in batches.

Root cause #

Last-seen is written far more often than it is read, and it is read at much lower precision than it is written. Heartbeats arrive every few tens of seconds per connection; a profile page or contact list shows “online”, “5 minutes ago”, “yesterday”. Writing every heartbeat through to durable storage converts connection count directly into database write load, and because each write updates the same row repeatedly, it also creates contention and bloat in row-versioned databases such as PostgreSQL.

It is also a different question from presence. Presence asks “is this user connected now?” and is answered from live connection state — see building a WebSocket presence system with Redis. Last-seen asks “when was this user last active?” and matters mostly for users who are not online. That asymmetry is the key to making it cheap: while a user is online, their last-seen value does not need updating at all — it is “now”.

Database writes per second for last-seen Writing on every twenty-five-second heartbeat costs sixteen thousand database writes per second at four hundred thousand users; updating at most once per ten minutes cuts it to sixty-seven, and flushing batches of a thousand rows cuts the number of write statements to about seven per second. Database writes per second for last-seen per strategy write per heartbeat (25 s) coarse: once per 10 min batched flush (1,000 rows/write) 0 10k 20k 30k 40k 100k users online 400k users online 1M users online
Coarsening and batching remove over 99.9% of last-seen write statements.

Resolution #

Combine three techniques. Coarsen: record activity at most once per interval per user (say every 5–10 minutes), since the display rounds anyway. Buffer: record it in Redis, where a write is cheap and a sorted set gives you ordering and range queries for free. Batch: periodically flush changed users from Redis to the database in bulk statements. And on disconnect — the moment last-seen actually becomes interesting — record the exact time.

import { createClient } from 'redis';
import type { Pool } from 'pg';

const redis = createClient(); await redis.connect();
const COARSE_INTERVAL_MS = 10 * 60 * 1000;   // record activity at most every 10 min per user
const FLUSH_INTERVAL_MS = 30_000;
const FLUSH_BATCH = 1_000;
const DIRTY_KEY = 'lastseen:dirty';           // users with unflushed values
const SEEN_KEY = 'lastseen:ts';               // sorted set: user -> last-seen ms

// Called on heartbeats and messages. Cheap: one local check, rarely a Redis write.
const lastRecorded = new Map<string, number>();   // per node, per user
export async function touch(userId: string, now = Date.now()) {
  const prev = lastRecorded.get(userId) ?? 0;
  if (now - prev < COARSE_INTERVAL_MS) return;     // coarse: skip until the interval passes
  lastRecorded.set(userId, now);
  await redis.multi().zAdd(SEEN_KEY, { score: now, value: userId }).sAdd(DIRTY_KEY, userId).exec();
}

// Called when the user's LAST connection closes: the value that actually gets displayed.
export async function onUserOffline(userId: string, now = Date.now()) {
  lastRecorded.delete(userId);
  await redis.multi().zAdd(SEEN_KEY, { score: now, value: userId }).sAdd(DIRTY_KEY, userId).exec();
}

// Background flusher: move dirty users to the database in bulk.
export async function flushLoop(db: Pool) {
  for (;;) {
    const ids = await redis.sPop(DIRTY_KEY, FLUSH_BATCH);           // atomically claim a batch
    if (ids.length) {
      const scores = await redis.zmScore(SEEN_KEY, ids);
      const rows = ids.map((id, i) => [id, new Date(Number(scores[i]))] as const).filter(([, d]) => !isNaN(+d));
      // One statement for the whole batch; GREATEST keeps a newer DB value from being overwritten.
      await db.query(
        `UPDATE users u SET last_seen = GREATEST(u.last_seen, v.ts)
         FROM unnest($1::text[], $2::timestamptz[]) AS v(id, ts) WHERE u.id = v.id`,
        [rows.map((r) => r[0]), rows.map((r) => r[1])],
      );
    }
    await new Promise((r) => setTimeout(r, ids.length === FLUSH_BATCH ? 0 : FLUSH_INTERVAL_MS));
  }
}

// Reads: online users show "online"; others read Redis first, then the database.
export async function lastSeen(userId: string, isOnline: (id: string) => Promise<boolean>, db: Pool) {
  if (await isOnline(userId)) return { online: true as const };
  const score = await redis.zScore(SEEN_KEY, userId);
  if (score !== null) return { online: false as const, at: new Date(score) };
  const r = await db.query('SELECT last_seen FROM users WHERE id = $1', [userId]);
  return { online: false as const, at: r.rows[0]?.last_seen ?? null };
}

GREATEST in the flush makes batches idempotent and order-independent: replaying a batch, or two flushers racing, can never move a timestamp backwards. The sorted set also answers product questions cheaply — “users active in the last 24 hours” is a ZCOUNT over a score range — and can be trimmed of entries older than the retention you need, since the database holds the durable copy.

Presentation should match the precision you store: “online”, “active in the last 10 minutes”, then relative times rounded to minutes, hours and days. Pair the display with presence so a user who is connected shows as online rather than “last seen just now”.

Last-seen data path Heartbeats are coarsened in memory to at most one record per user per ten minutes, buffered in a Redis sorted set with a dirty set, flushed in batches of a thousand with a GREATEST update every thirty seconds, and stored durably in a database column. Last-seen data path Heartbeat / message every 25 s per connection — never written through hot path Per-node coarsening at most one record per user per 10 min in memory Redis sorted set + dirty set cheap writes; range queries; source for reads buffer Batched flush 1,000 users per UPDATE with GREATEST every 30 s Database column durable last_seen for offline users and analytics durable Each layer removes load from the one below it
From 16,000 writes per second to a handful of batch statements.

Edge cases #

Multiple connections per user. A user with a phone and a laptop goes “offline” only when their last connection closes. Record the exact time on the last disconnect, which requires knowing the user’s connection count — the presence system already tracks it.

Privacy. Last-seen reveals activity patterns. Offer users a setting to hide it (and, reciprocally, to not see others’), round displayed values, and avoid exposing exact timestamps in APIs that other users can query.

Redis loss. If Redis loses unflushed data, the database retains the previous flush, which is at most one flush interval older — an acceptable error for this feature. Keep the flush interval short enough that the error stays within display precision.

Verification #

Measure database write statements attributable to last-seen before and after the change, at the same connection count, and confirm the reduction. Check correctness with a small scripted scenario: connect a user, keep them active across several coarsening intervals, disconnect, and confirm the stored value equals the disconnect time within the flush interval. Test idempotency by running two flushers concurrently against the same dirty set and verifying no timestamp moves backwards.

-- Sanity check after rollout: how many users were flushed recently, and the most recent value.
SELECT count(*) FILTER (WHERE last_seen > now() - interval '1 hour') AS active_last_hour,
       max(last_seen) AS newest
FROM users;
One user's last-seen over an afternoon A user connects and is recorded, receives coarse activity records at ten and twenty minutes, disconnects at twenty-seven minutes when the exact time is recorded and flushed shortly after, and an hour later is shown as last seen one hour ago. One user's last-seen over an afternoon connects (recorded) (0 min) coarse record (10 min) coarse record (20 min) disconnects: exact time (27 min) flushed to DB (27.5 min) shown: 'last seen 1 h ago' (90 min) Hundreds of heartbeats in this window produced four Redis writes and one database row update
Precision where it is shown; silence where it is not.

Operational checklist #

FAQ #

How do chat apps store “last seen” without overloading the database? #

They record activity coarsely, buffer it in memory or Redis, and write to the database in batches — and they record the exact time when the user disconnects, which is when the value becomes visible to others.

Should I update last-seen on every heartbeat? #

No. Heartbeats exist to keep connections alive and detect dead peers. Updating a database on each one turns connection count into write load for a value that is displayed with minute-level precision at best.

What’s the difference between presence and last-seen? #

Presence is whether a user is connected right now, derived from live connection state. Last-seen is when they were last active, which matters mainly once they are offline. They share data but have very different write and read patterns.

How precise should last-seen be? #

As precise as you display. If the UI says “active 5 minutes ago” at minute granularity and “yesterday” beyond a day, storing activity at 10-minute granularity while online and exact time at disconnect is enough.

Back to Presence & Online Tracking.