0
showcase
Shipped: Ticket sales
Built a Ticket Sales API — my internship project
I just wrapped up a backend project that simulates real ticket reservation: events, seats, and the race-condition problem that shows up the moment two people try to buy the same seat at the same time.
Stack: FastAPI · SQLAlchemy · PostgreSQL · Alembic · Docker · pytest
What I built:
Three related tables (events, seats, orders) with foreign keys and constraints enforced at the database level — not just in application code
12,000 seeded seats across 4 events for realistic performance benchmarking
A free-seat listing endpoint, returning in under 200 ms
A reservation endpoint that is safe under concurrency
16 automated tests covering endpoints, side effects, and concurrency at both the DB and HTTP layers
The two problems that taught me the most:
1. Indexing is not about "adding an index" — it's about making the filter selective.
The free-seat query filters on event_id and status = 'free'. My first index only covered event_id, so PostgreSQL still had to fetch every row and check status on the heap.
Adding a composite index on (event_id, status) moved both conditions into the index. Heap reads dropped from 23 blocks to 3, and the median query time went from 0.778 ms to 0.159 ms — a ~4.9× improvement.
But here's the honest part: the endpoint latency barely changed. The database was never the bottleneck — JSON serialization and HTTP overhead dominate the endpoint. Documenting that honestly was more valuable than pretending the index solved everything.
2. Concurrency is a database problem, not an application problem.
The naive flow — SELECT → check status == 'free' → UPDATE — allows two requests to both read free before either one writes reserved. Both "win." I fixed it by combining the check and the write into one atomic SQL statement:
sql
UPDATE seats
SET status = 'reserved'
WHERE id = :seat_id AND event_id = :event_id AND status = 'free';
PostgreSQL guarantees at most one concurrent transaction can match that WHERE clause. The loser sees rowcount = 0 and returns 409 Conflict.
Proved it with two simultaneous HTTP requests. Exactly one 201, exactly one 409, exactly one order in the database. Reproduced on three different seats.
I also hit a Windows socket abort under concurrent load — a real infrastructure issue, not a logic bug — and diagnosed it to the SQLAlchemy connection pool. Fixing that with pool_pre_ping=True was a lesson in production hardening.
What I'd take away:
The project brief asked for endpoints, an index, and a concurrency-safe reservation. What I actually learned was:
How to measure before optimizing
Why the database should enforce invariants, not the application
How to write evidence, not claims — every number in the README comes from a real captured run
The difference between "it works" and "here's proof it works under load"
#backend #fastapi #postgresql #python #concurrency
Repository: https://github.com/Ramsha-Anwar/ticket-sales-api
Built for the Verified Backend Internship · a data model and the queries on it
Add a comment
0/2000