Diving into Databases: Indexes, Locks, and the Problem of Selling the Same Ticket Twice

After my URL shortener, I wanted a project where the database had to do real work. I picked a ticket reservation API because it's a textbook database problem: many people wanting the same scarce thing at the same moment.

Here's what I built and what it taught me about tables, keys, indexes, and locks. Full source: github.com/Ango-Jay/ticket-reservation-app.

For this project I picked Postgres as my db, which I ran in a local Docker container, and I used TypeORM as my ORM, the library that lets my code work with the database tables. I chose Postgres for no deep reason: most of the places I've worked use it, so I gravitated to it. I'd been learning SQL syntax and wanted to go past simple queries.

Scoping the tables

My first job was deciding which tables to create. I got help from AI here, because there were fields I didn't know I needed (why a reservation needs an expires_at, for example). I ended up with six tables.

The booking tables

• events: the things people reserve tickets for (title, a unique URL slug, start time, location, status).
• ticket_types: the kinds of ticket an event offers, like General Admission or VIP, each with a price, currency, and total quantity.
• reservations: one person's hold or booking against a ticket type (email, quantity, status, and when the hold expires).

The admin tables

• admins: the people allowed to manage events, with a status of pending, active, or inactive.
• admin_sessions: one row per logged-in admin session. It stores a hash of the refresh token (never the token itself), so a session can be renewed or revoked.
• admin_token_blacklist: IDs of access tokens that were logged out before they expired naturally, so they can be rejected.

The decision I'm happiest with is making ticket types their own table. An event doesn't have to hold the number of tickets, because that would tie us to a single kind of ticket. In real-world events, an event can have multiple ticket classes, like General Admission and VIP, each with its own price and quantity. That later told me exactly which row needed protecting.

Keys and constraints

This is where I put primary and foreign keys into practice. The part I didn't expect to like was constraints, which are rules the database enforces no matter what my code does:

CONSTRAINT "CHK_reservations_quantity_positive" CHECK ("quantity" > 0),
CONSTRAINT "CHK_reservations_status" CHECK ("status" IN ('held','confirmed','cancelled','expired'))

Even if my app has a bug, the database refuses bad data. I also learned that ON DELETE CASCADE and ON DELETE RESTRICT encode a decision: what should happen to reservations if their parent disappears?

Indexes

I'd heard about indexes for ages. The way I think about one now is as a phone book. Without it, Postgres reads the whole table to find your rows (a sequential scan). With it, it jumps to the right page.

I used three kinds:

Normal: one column, like reservations(event_id) for "all bookings for this event."

Partial: this is the one I loved. A partial index only contains rows matching a condition:

CREATE INDEX "IDX_reservations_held_expires_at"
  ON "reservations" ("expires_at") WHERE "status" = 'held';

Read it like a sentence: index the expires_at of reservations, but only the ones whose status is held. A reservation that's confirmed, cancelled, or expired is never added to this index at all. Its job is to answer one question quickly: which holds have run out of time?

To see why that question matters, you need to know what a hold is. When someone reserves tickets, they aren't sold straight away. The reservation starts as a hold: the tickets are set aside for that person for a short window (30 minutes in my setup) so they can finish checkout. If they never confirm, those tickets can't stay tied up forever. The hold has to expire so the tickets go back on sale.

Part of this takes care of itself. When my app counts how many tickets are taken, a hold only counts while its expires_at is still in the future, so a lapsed hold stops blocking other buyers the moment its time is up. But the row in the table still says held. That's why I have an expiry job: on a schedule (hourly), it finds holds whose time has run out and marks them expired, so the status in the table matches reality. Its query is roughly:

UPDATE reservations
SET status = 'expired'
WHERE status = 'held' AND expires_at < NOW();

And that's the query this index exists for. Without it, Postgres would have to check every reservation ever made each time the job runs. With it, Postgres can jump straight to the held reservations whose time has run out. Cancelled and expired reservations pile up forever, but they never enter the index, so it stays small however big the table gets. One catch: a query only uses a partial index if its own WHERE includes a matching condition. Here that's status = 'held', which the job's query has.

Composite and partial together: the most important index in the project turned out to be both:

CREATE INDEX "IDX_reservations_ticket_type_open"
  ON reservations (ticket_type_id, status)
  WHERE status IN ('held', 'confirmed');

Here's why I needed it. Every booking attempt counts how many tickets of a ticket type are already taken, and it does that while holding a lock (more on that below). The reservations table only ever grows, because every hold, expired hold, and cancellation is a row. Without an index, that count scans an ever-growing table while everyone else waits. With it, Postgres jumps straight to that ticket type's open reservations.

Two details:

• Only held and confirmed reservations count toward availability, so cancelled and expired rows never enter the index.
• Column order matters in a composite index. ticket_type_id comes first because every lookup filters on it. An index on (a, b) mainly helps queries that filter on a, or on a and b, not on b alone.

A couple of things worth knowing about indexes. Postgres doesn't create indexes for foreign key columns automatically, so you have to add them yourself. And indexes aren't free: every insert, and any update touching an indexed column, has to maintain them.

Choosing which index to create. There's no single best kind of index; it depends on the queries your application actually runs. A partial index shines when your queries only touch a small slice of the table, like the held reservations above. If most rows match the condition, or your queries often need rows outside it, a regular index is probably the better choice. One more thing to watch: Postgres can only use a partial index when it can tell that the query's conditions fall inside the index's condition. That can get tricky with parameterized queries and prepared statements, where Postgres may plan the query before it knows the actual values.

The overselling problem

With one ticket left, two people hit "reserve" at the same moment:

A: reads "1 left"
B: reads "1 left"
A: inserts a reservation  → 0 left
B: inserts a reservation  → oversold

Both checks passed because neither could see the other's insert. The fix was a pessimistic lock, SELECT ... FOR UPDATE. In my TypeORM code:

await this.dataSource.transaction(async (manager) => {
  // ...load the active event first

  const ticketType = await manager.findOne(TicketType, {
    where: { id: dto.ticketTypeId, eventId: event.id },
    lock: { mode: 'pessimistic_write' }, // SELECT ... FOR UPDATE
  });
  if (!ticketType) {
    throw new NotFoundException('Ticket type not found for this event');
  }

  const reserved = await this.getSumOfReservedForTicketType(
    manager,
    ticketType.id,
  );
  const available = ticketType.quantity - reserved;
  if (dto.quantity > available) {
    throw new ConflictException('Not enough tickets available');
  }

  // ...create the held reservation with an expiry time and save it
});

The ticket type row records which kind of ticket is being reserved and how many exist, so that's the row I lock. The first buyer locks it, and everyone else queues at that line until the first transaction commits. Then each one re-counts and decides. Buyers of other ticket types aren't affected, and plain reads aren't blocked either. "Not enough tickets" returns a 409, which is an expected answer rather than a crash.

Pessimistic means assuming conflict will happen and locking up front. The alternative, optimistic locking, lets everyone proceed and checks a version number at write time.

Key takeaways

• Constraints are the database's last line of defense.
• Partial indexes make an index smarter, and composite ones need the right column order.
• Postgres doesn't index foreign keys for you, and every index you add costs writes.
• Overselling is a race condition, and a lock inside a transaction fixes it.
• Correct under load and slow on one hot row can both be true, and a good index is what keeps the lock short.