DEV Community: Abhishek tiwari

Preventing Double Booking at 2M Concurrent Users: Redis Seat Locks + MySQL + Kafka" published: false

Abhishek tiwari — Wed, 04 Nov 2026 09:51:00 +0000

How a BookMyShow-style ticket booking system handles seat locking, idempotent payments and async events during a blockbuster release."
tags: systemdesign, architecture, redis, backend

2 million people open the app at 7:00 PM on release day. Seat A12 can be sold exactly once.

The customer experience is simple: pick seats, pay ₹600, done in under 2 minutes. Behind that is a concurrency problem. Here is how I would design it. (This is my own design, not BookMyShow's actual internals.)

The flow

Client → CDN → API Gateway → Load Balancer → Services → Redis lock → MySQL → Kafka → Notifications

Edge: the CDN serves static content, and the gateway and load balancer absorb the spike.
Core services: Movie, Theatre, Seat Lock, Booking and Payment.
Data: MySQL for bookings, Redis for temporary seat holds, Kafka for async events.

Step 1: Lock seats in Redis

When a user selects seats, try to take a short-lived lock:

SET seat:{show:42}:A12 <user-token> NX PX 600000

NX means set only if the key doesn't exist, so the first request wins.
PX 600000 gives a 10-minute hold. If the user abandons payment, the lock expires and no cleanup job is needed.

Users usually pick several seats, so the lock must be all-or-nothing. A Lua script is atomic in Redis:

-- KEYS = seat keys, ARGV[1] = user token, ARGV[2] = ttl in ms
for i = 1, #KEYS do
  if redis.call("EXISTS", KEYS[i]) == 1 then return 0 end
end
for i = 1, #KEYS do
  redis.call("SET", KEYS[i], ARGV[1], "PX", ARGV[2])
end
return 1

In Redis Cluster, all keys in one script must be in the same hash slot. That is why the {show:42} hash tag is in the key name.

Release locks safely by checking the token first, so a user can never free someone else's lock:

if redis.call("GET", KEYS[1]) == ARGV[1] then
  return redis.call("DEL", KEYS[1])
end
return 0

Step 2: MySQL is the final judge

Redis is fast, but locks can be lost during a failover. The database must be able to refuse a double booking on its own:

CREATE TABLE booking_seats (
  id         BIGINT PRIMARY KEY AUTO_INCREMENT,
  booking_id BIGINT NOT NULL,
  show_id    BIGINT NOT NULL,
  seat_id    VARCHAR(10) NOT NULL,
  UNIQUE KEY uniq_show_seat (show_id, seat_id)
) ENGINE=InnoDB;

Write the booking and its seats in one transaction. If two requests somehow get past Redis, the second insert fails on the unique key. Redis handles the crowd, and MySQL guarantees correctness. On cancellation, delete the seat rows so the seats can be sold again.

Step 3: Idempotent payments

Users double-tap "Pay" and networks retry. Generate one idempotency key per payment attempt and store it with a unique constraint:

CREATE TABLE payment_attempts (
  idempotency_key CHAR(36) PRIMARY KEY,
  booking_id      BIGINT NOT NULL,
  status          VARCHAR(20) NOT NULL
);

If the same key arrives again, return the stored result instead of charging again.

Step 4: Kafka and the outbox pattern

Emails, SMS and push notifications must never slow down a booking, so they go through Kafka. The risk is saving a booking but failing to publish the event. The fix is an outbox table written in the same transaction as the booking:

CREATE TABLE outbox_events (
  id           BIGINT PRIMARY KEY AUTO_INCREMENT,
  event_type   VARCHAR(50) NOT NULL,
  payload      JSON NOT NULL,
  published_at TIMESTAMP NULL
);

A small relay process reads unpublished rows and sends them to Kafka. Kafka delivery is at-least-once, so consumers must also be idempotent.

Step 5: Fail gracefully

Retries with backoff for transient errors.
A circuit breaker so a failing payment or SMS provider doesn't take down the flow.
Fallbacks, such as queueing a notification instead of dropping it.

What breaks first

Failure	What protects you
Two users tap the same seat	Redis `NX` lock
Redis restarts and loses locks	MySQL unique constraint
User double-taps Pay	Idempotency key
SMS provider is down	Kafka + retries
Booking saved, event lost	Outbox pattern

Open questions

How long should a seat hold last? Too short hurts payments, too long locks inventory.
Is a virtual waiting room worth it for blockbuster releases?
Where do you see hot-key problems at peak?

I'd like to hear how you'd approach this in the comments.

Daily Career Digest

Abhishek tiwari — Mon, 02 Nov 2026 07:38:36 +0000

Unauthorized spam post attempt

Silent N+1 Database Query Bottlenecks in TypeScript & Node.js REST APIs

Abhishek tiwari — Sun, 18 Oct 2026 13:50:28 +0000

Most slow PostgreSQL queries in Node.js REST APIs don’t happen because of missing database indexes.

They happen because of silent N+1 query patterns executing inside map loops.

When building REST endpoints with Node.js and TypeScript, it’s easy to write clean-looking code like this:

const users = await getUsers();
const posts = await Promise.all(
users.map(user => getPostsByUserId(user.id))
);

On a local machine with 10 test records? It runs in 5ms.
In production with 1,000 active users? That single HTTP request triggers 1,001 database roundtrips—exhausting your PostgreSQL connection pool and bringing response times to a crawl.

Here is how we eliminate N+1 query bottlenecks in Node.js + Postgres services:

Batching with WHERE id = ANY($1)
Instead of querying per item in a loop, collect IDs into a single array and fetch all matching records in one database roundtrip.
Leveraging PostgreSQL JOIN and JSON_AGG
Let Postgres do the heavy lifting. Using SQL joins with json_agg() yields structured JSON directly from the database layer, skipping Node.js mapping overhead entirely.
DataLoader Pattern for Nested REST Dependencies
If you have nested dependencies across sub-services, leverage DataLoader to automatically batch and cache queries per request context.
Query Count Logging in Staging
Enable query logging or APM tracing in non-production environments to catch high query counts before code hits production.

Clean code is great, but database network roundtrips are expensive.

How do you catch N+1 query patterns during code reviews in your team? Let’s discuss below! 👇

Silent N+1 Database Query Bottlenecks in TypeScript & Node.js REST APIs

Abhishek tiwari — Sun, 18 Oct 2026 09:56:23 +0000

Most slow PostgreSQL queries in Node.js REST APIs don’t happen because of missing database indexes.

They happen because of silent N+1 query patterns executing inside map loops.

When building REST endpoints with Node.js and TypeScript, it’s easy to write clean-looking code like this:

const users = await getUsers();
const posts = await Promise.all(
users.map(user => getPostsByUserId(user.id))
);

Here is how we eliminate N+1 query bottlenecks in Node.js + Postgres services:

Batching with WHERE id = ANY($1)
Instead of querying per item in a loop, collect IDs into a single array and fetch all matching records in one database roundtrip.
Leveraging PostgreSQL JOIN and JSON_AGG
Let Postgres do the heavy lifting. Using SQL joins with json_agg() yields structured JSON directly from the database layer, skipping Node.js mapping overhead entirely.
DataLoader Pattern for Nested REST Dependencies
If you have nested dependencies across sub-services, leverage DataLoader to automatically batch and cache queries per request context.
Query Count Logging in Staging
Enable query logging or APM tracing in non-production environments to catch high query counts before code hits production.

Clean code is great, but database network roundtrips are expensive.

How do you catch N+1 query patterns during code reviews in your team? Let’s discuss below! 👇

Developer Growth & Tech Trends

Abhishek tiwari — Sun, 18 Oct 2026 09:56:18 +0000

Developer Growth & Tech Trends

Building Resilient TypeScript REST APIs in Node.js with PostgreSQL: Beyond Basic CRUD

Abhishek tiwari — Sat, 17 Oct 2026 19:42:54 +0000

Most backend bottlenecks don’t happen because of poor hardware.

They happen because of unoptimized queries, missing connection pools, and sloppy TypeScript types.

Moving from basic CRUD to a production-grade Node.js and PostgreSQL architecture requires a shift in mindset:

• Strict Type Safety: Leveraging TypeScript interfaces for request validation and database models to catch errors at compile time, not in production.
• Connection Pooling: Managing PostgreSQL pools efficiently to prevent socket exhaustion under high concurrent load.
• Indexed Queries: Analyzing execution plans with EXPLAIN ANALYZE rather than blindly adding indexes.
• Graceful Error Handling: Centralizing exception handling to maintain consistent REST API response contracts.

Your database isn't slow. Your query lifecycle probably is.

What’s the most stubborn bottleneck you’ve debugged in a Node.js REST API recently?

TypeScript #NodeJS #PostgreSQL #BackendDevelopment #WebDev

Building Resilient TypeScript REST APIs in Node.js with PostgreSQL: Beyond Basic CRUD

Abhishek tiwari — Sat, 17 Oct 2026 09:56:37 +0000

Most backend bottlenecks don’t happen because of poor hardware.

They happen because of unoptimized queries, missing connection pools, and sloppy TypeScript types.

Moving from basic CRUD to a production-grade Node.js and PostgreSQL architecture requires a shift in mindset:

Your database isn't slow. Your query lifecycle probably is.

What’s the most stubborn bottleneck you’ve debugged in a Node.js REST API recently?

TypeScript #NodeJS #PostgreSQL #BackendDevelopment #WebDev

Scaling Node.js REST APIs with TypeScript and PostgreSQL: Beyond Basic CRUD

Abhishek tiwari — Fri, 16 Oct 2026 19:24:26 +0000

Most backend bottlenecks don’t happen because of poor hardware.

They happen because of unoptimized queries, missing connection pooling, and poor async orchestration in Node.js.

When scaling a TypeScript and Node.js REST API with PostgreSQL under heavy traffic, standard practices quickly break down. Here is how we build production-grade, resilient backends:

Connection Pooling & Limits — Avoid creating a new database client per request. Use a robust pool manager with pg or Prisma to handle concurrency safely.
Strict TypeScript DTOs — Validate incoming payloads at the edge using Zod or class-validator before touching the database layer.
Indexing Strategy — B-tree and GIN indexes save high-latency queries when handling millions of rows in PostgreSQL.
Async Background Jobs — Offload heavy computations, webhooks, and third-party API calls to queue workers.

Great architecture isn't about complex tooling; it's about predictable performance under stress.

How do you handle DB connection spikes in your Node.js apps? Drop your thoughts below. 👇

TypeScript #NodeJS #PostgreSQL #RESTAPIs #SoftwareEngineering #BackendDevelopment

Scaling Node.js REST APIs with TypeScript and PostgreSQL: Beyond Basic CRUD

Abhishek tiwari — Fri, 16 Oct 2026 18:51:19 +0000

Most backend bottlenecks don’t happen because of poor hardware.

They happen because of unoptimized queries, missing connection pooling, and poor async orchestration in Node.js.

When scaling a TypeScript and Node.js REST API with PostgreSQL under heavy traffic, standard practices quickly break down. Here is how we build production-grade, resilient backends:

Connection Pooling & Limits — Avoid creating a new database client per request. Use a robust pool manager with pg or Prisma to handle concurrency safely.
Strict TypeScript DTOs — Validate incoming payloads at the edge using Zod or class-validator before touching the database layer.
Indexing Strategy — B-tree and GIN indexes save high-latency queries when handling millions of rows in PostgreSQL.
Async Background Jobs — Offload heavy computations, webhooks, and third-party API calls to queue workers.

Great architecture isn't about complex tooling; it's about predictable performance under stress.

How do you handle DB connection spikes in your Node.js apps? Drop your thoughts below. 👇

TypeScript #NodeJS #PostgreSQL #RESTAPIs #SoftwareEngineering #BackendDevelopment

Scaling Node.js REST APIs with TypeScript and PostgreSQL: Beyond Basic CRUD

Abhishek tiwari — Fri, 16 Oct 2026 09:55:56 +0000

Most backend bottlenecks don’t happen because of poor hardware.

They happen because of unoptimized queries, missing connection pooling, and poor async orchestration in Node.js.

When scaling a TypeScript and Node.js REST API with PostgreSQL under heavy traffic, standard practices quickly break down. Here is how we build production-grade, resilient backends:

Connection Pooling & Limits — Avoid creating a new database client per request. Use a robust pool manager with pg or Prisma to handle concurrency safely.
Strict TypeScript DTOs — Validate incoming payloads at the edge using Zod or class-validator before touching the database layer.
Indexing Strategy — B-tree and GIN indexes save high-latency queries when handling millions of rows in PostgreSQL.
Async Background Jobs — Offload heavy computations, webhooks, and third-party API calls to queue workers.

Great architecture isn't about complex tooling; it's about predictable performance under stress.

How do you handle DB connection spikes in your Node.js apps? Drop your thoughts below. 👇

TypeScript #NodeJS #PostgreSQL #RESTAPIs #SoftwareEngineering #BackendDevelopment

How I Built a 768-Dimensional Vector Search Engine with Node.js, TypeScript, and Supabase

Abhishek tiwari — Fri, 16 Oct 2026 04:34:00 +0000

How paragraph-aware chunking, embeddings, and PostgreSQL pgvector can make AI retrieval more efficient.

When you build an AI application that needs to work with large documents, one problem shows up quickly: how do you give an LLM the information it needs without sending the entire document with every request?

Sending more text isn't always the answer. It can increase token usage, introduce irrelevant context, and make retrieval harder to manage.

I explored a different approach: a vector search engine built with Node.js, TypeScript, and Supabase.

The idea is simple: turn document content into embeddings, store those vectors, and retrieve the passages most relevant to a user's query.

The architecture

The retrieval pipeline follows this flow:

Ingest the source documents.
Split the content into meaningful paragraph-based chunks.
Preserve headings and contextual metadata.
Generate 768-dimensional embeddings.
Store the chunks and embeddings in Supabase with pgvector.
Embed the incoming query and rank relevant chunks using cosine similarity.
Pass the selected context to the LLM.

This approach forms the retrieval layer of a retrieval-augmented generation (RAG) system.

1. Chunking documents without losing context

A common mistake is to split a document every fixed number of characters without considering its structure.

That can separate a paragraph from its heading or break a thought in the middle.

Instead, I use paragraph-aware chunking and preserve contextual headers wherever possible.

For example, a chunk might retain this structure:

Document: Vector Search Architecture
Section: Similarity Scoring
Content: How cosine similarity ranks query and document embeddings.

This metadata helps make each chunk more meaningful on its own.

For production systems, chunk size and overlap should be tested against the document type and retrieval quality rather than chosen arbitrarily.

2. Storing 768-dimensional embeddings with Supabase

Once the content is chunked, an embedding model converts each chunk into a vector containing 768 numerical dimensions.

These vectors represent semantic features learned by the embedding model. Similar meanings can be close in vector space, even when the exact words differ.

Supabase provides a PostgreSQL database, while the pgvector extension supports vector storage and similarity operations.

A simplified table could look like this:

create extension if not exists vector;

create table document_chunks (
  id bigint generated always as identity primary key,
  content text not null,
  metadata jsonb default '{}'::jsonb,
  embedding vector(768) not null
);

Important: The embedding model must actually produce 768-dimensional vectors. The database dimension needs to match the model output, and query and document embeddings must come from a compatible embedding space.

3. Ranking results with cosine similarity

After a user submits a query, the application generates a query embedding and compares it with the stored document vectors.

With pgvector, cosine distance can be calculated using the <=> operator:

select
  id,
  content,
  1 - (embedding <=> $1::vector) as similarity
from document_chunks
order by embedding <=> $1::vector
limit 5;

Here, $1 represents the query vector supplied by the application.

The query returns the five nearest chunks according to cosine distance. The similarity expression is a convenient ranking score, not a calibrated probability that a result is correct.

For larger datasets, vector indexes, query plans, filtering, and recall-versus-latency trade-offs become important.

4. Keeping retrieval fast

A vector search system is only useful if it can return relevant context within the application's latency budget.

My performance goal for this project is sub-200ms semantic search responses.

To evaluate that properly, I would measure query latency separately from embedding generation, network overhead, and the subsequent LLM response. I would also track p50 and p95 latency across representative queries.

The actual result depends on the dataset size, index configuration, hardware, query patterns, and network conditions.

Fast retrieval also needs to preserve relevance. Returning the wrong passages quickly doesn't solve the original problem.

5. Why this architecture matters

This design is useful for applications such as:

Documentation and codebase search
Internal knowledge assistants
Semantic search across long documents
Retrieval-augmented generation pipelines
Multimodal applications, when compatible text and non-text embeddings are supported by the selected model

One distinction matters: a 768-dimensional vector is not automatically multimodal. Multimodal retrieval requires an embedding model that supports the relevant modalities and produces compatible vectors.

Final thoughts

Building a vector search engine taught me to treat retrieval as its own engineering problem rather than an afterthought in an LLM application.

Good chunking preserves meaning. Embeddings make semantic comparison possible. pgvector makes retrieval practical inside PostgreSQL. And proper benchmarking tells you whether the architecture meets its performance goals.

The next challenge is balancing retrieval quality, latency, and cost as the dataset grows.

If you're building a RAG pipeline, I'd be interested to know what has worked best for you: smaller chunks, larger chunks, hybrid search, or reranking?

Tech stack: Node.js, TypeScript, Supabase, PostgreSQL, pgvector, vector embeddings, RAG.

node #typescript #ai #database #rag

The Hidden Cost of Unoptimized REST API Payloads in Node.js

Abhishek tiwari — Thu, 15 Oct 2026 15:49:51 +0000

Most REST API performance issues in Node.js apps don’t happen because of heavy queries.

They happen because we send the entire database model down the wire when the client only needs two fields.

Here is what usually happens:
• Database returns 50 columns with nested relations.
• Mongoose or TypeORM serializes the whole object graph.
• JSON.stringify blocks the Node.js event loop for 40ms.
• Network bandwidth gets wasted on metadata the client discards.

Fixing this isn't about rewriting in Rust or upgrading your AWS instance.

It’s about intentional API design:

Project your queries strictly at the SQL/ORM level using DTOs or explicit selects.
Apply pagination limits by default — never trust SELECT *.
Profile your JSON serialization overhead using clinic.js or built-in node --prof.

Fast APIs are built by moving less data, not just writing faster algorithms.

What’s the largest JSON payload you've ever had to debug in production? Let's discuss in the comments.