Saving a Flood of Data Without Melting Your Database

UXClone is my self-hosted version of a session-replay tool. A little script sits inside someone's app and records what users do - every click, scroll, and tap

• then ships those events to my backend in bursts. One active user can generate hundreds of events in a few seconds. Multiply by lots of users at once and this is the busiest part of the whole system.

Saving all that data is the kind of thing that's easy to get quietly wrong. Let me walk from the obvious-but-broken version to the one that holds up, using a moving truck the whole way.

The setup: boxes and a warehouse

Think of each event as a box that needs to get to a warehouse (the database). Your job is to move a big pile of boxes there, fast, without anything breaking.

Attempt 1: one trip per box (please don't)

for (const ev of events) {
  await db.query('INSERT INTO events (...) VALUES (...)', [...]);
}

This drives to the warehouse, drops off one box, drives back, and repeats - 300 times for 300 events. Each "trip" to a database has a fixed overhead (the drive time), and you're paying it for every single box. Under load the warehouse spends all day on drive-time instead of actual stocking. The fix: carry many boxes in one trip.

Attempt 2: one truck, many boxes

A single database insert can carry lots of rows at once: INSERT INTO events (...) VALUES (box1), (box2), (box3). Since I don't know how many boxes will show up, I build the list as I go:

const values: unknown[] = [];
const placeholders: string[] = [];
let i = 1;

for (const ev of events) {
  placeholders.push(
    `($${i},$${i+1},$${i+2},$${i+3},$${i+4},$${i+5},$${i+6},$${i+7},$${i+8},$${i+9})`
  );
  values.push(
    sessionId, projectId, ev.type ?? 'custom', new Date(ev.timestamp ?? Date.now()),
    ev.elapsedMs ?? 0, ev.x ?? null, ev.y ?? null, ev.target ?? null,
    ev.screenName ?? null, ev.value ?? null
  );
  i += 10;
}

await db.query(
  `INSERT INTO events (session_id, project_id, type, timestamp, elapsed_ms, x, y, target, screen_name, value)
   VALUES ${placeholders.join(',')}`,
  values
);

Those $1, $2, … are placeholders - the values travel separately from the query text, which means a sneaky value can't be misread as a command (this is what keeps you safe from "SQL injection"). One trip, many boxes. Much better… until the truck gets too full.

The ambush: the truck has a maximum load

Here's the limit nobody mentions out loud: a single Postgres query can carry at most 65,535 of those placeholder values. It's a hard ceiling baked into how the database talks over the wire. Each of my events uses 10 placeholders, so that's a ceiling of about 6,553 events in one trip. Pile on more and the trip fails - and the error message is cryptic, so you waste an afternoon before realizing the truck was simply overloaded.

A buggy or malicious client could easily send a burst that big. So I refuse to overload the truck, capping it well under the limit and writing down why right next to the number:

// Postgres maxes out at 65535 placeholders; at 10 per event that's ~6553 events.
const MAX_EVENTS_PER_BATCH = 500;

const capped = events.slice(0, MAX_EVENTS_PER_BATCH);

500 keeps every trip small and predictable while still being a massive win over one-box-per-trip. (If you genuinely need to move more than the cap in one request, you send several truckloads - but for an SDK that ships often, capping each burst is simpler and keeps any one request bounded.)

The details that keep it correct under a flood

A fast process that saves data to the wrong place isn't fast - it's broken. Three guards:

Check the boxes belong to this customer's unit before storing them. UXClone is multi-tenant (many separate customers share the system), so customer A must never write into customer B's session:

const ok = await db.query(
  'SELECT id FROM sessions WHERE id = $1 AND project_id = $2',
  [sessionId, projectId]
);
if (ok.rows.length === 0) return 0;   // not yours → reject before writing anything

Update the running count in one clean stroke. Each session keeps a tally of how many events it has, so the dashboard never has to count millions of rows live. I add the whole truckload to the tally in a single statement, so two trucks arriving at once can't clobber each other's count:

await db.query(
  'UPDATE sessions SET event_count = event_count + $1 WHERE id = $2 AND project_id = $3',
  [capped.length, sessionId, projectId]
);

Tear down the stale summary. New events make the cached dashboard summary out-of-date, so I delete it and let the next dashboard view rebuild a fresh one:

await redis.del(`analytics:summary:${projectId}`);

And anything genuinely heavy - like scanning a finished session for rage-clicks - happens after the response is sent, never while the client is waiting:

analyzeAndStoreRageClicks(sessionId, projectId)
  .catch((err) => console.error('rage click analysis failed:', err));

Takeaway

Saving a flood of data is a balance between two ways to fail: too many tiny trips (one box at a time, drowning the database in drive-time) and one overloaded trip (blowing past the hidden placeholder limit). The sweet spot is a single batched insert with a capped size - fast, safe, and predictable. Wrap it with an ownership check, a one-stroke count update, and a cache cleanup, and your busiest endpoint stays correct even when the data really starts pouring in.