Postgres

You're designing a workshop-booking app. There are attendees, workshops, and bookings connecting the two. Every booking needs a real attendee and workshop; nobody should book the same workshop twice. The data has a fairly clear structure, and SQL looks like a sensible fit.

The shortlist is MySQL and PostgreSQL. Both can handle this app, including its transactions and foreign keys. You choose Postgres because the team already knows it, your hosting provider supports it, and you expect to use its richer types and JSONB support for optional workshop metadata.

PostgreSQL, usually called Postgres, grew from Michael Stonebraker's POSTGRES research project at UC Berkeley, which began in 1986. The project investigated ideas beyond the relational systems of the time, including extensible types. SQL support arrived later, and the PostgreSQL name followed in 1996. That history helps explain why it can be both a conventional SQL database and a platform for extensions such as PostGIS.

Modelling workshops and bookings

With a local server running and the Postgres command-line tools installed, you create a development database. Your local role needs permission to create it:

createdb workshops
psql workshops

Inside psql, you declare the relationships you've already drawn on paper:

CREATE TABLE attendees (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE workshops (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  remaining INTEGER NOT NULL CHECK (remaining >= 0)
);

CREATE TABLE bookings (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  attendee_id BIGINT NOT NULL REFERENCES attendees(id),
  workshop_id BIGINT NOT NULL REFERENCES workshops(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (attendee_id, workshop_id)
);

INSERT INTO attendees (email) VALUES ('maya@example.com');
INSERT INTO workshops (title, remaining) VALUES ('Pottery basics', 1);

The database now rejects an unknown workshop, a duplicate booking, or negative availability, even if the write comes from a maintenance script. These are rules worth enforcing below the API. NOT NULL matters too: a check such as remaining >= 0 on its own would not reject a null value.

You can inspect the schema without leaving the prompt:

\dt
\d bookings

Reserving the final seat

The first booking endpoint reads remaining, checks that it's positive in Python, then decrements it. It passes your test. Two people booking the final seat at the same time expose the flaw: both can read 1 before either changes it.

You move the decision into the update itself and create a booking only if that update succeeds. In this fresh database, the inserted attendee and workshop both have ID 1; use the returned IDs in application code:

WITH reserved AS (
  UPDATE workshops
  SET remaining = remaining - 1
  WHERE id = 1 AND remaining > 0
  RETURNING id
)
INSERT INTO bookings (attendee_id, workshop_id)
SELECT 1, id FROM reserved
RETURNING id;

At the default Read Committed isolation level, another updater of that workshop waits for the first writer, then rechecks the condition against the changed row. The second request gets no booking row when capacity has reached zero. If inserting the booking fails, the reservation is rolled back too: this is one atomic statement.

This doesn't make retries automatically harmless. If the client loses the response, check whether its booking already exists before reporting failure. The unique constraint protects the attendee/workshop pair; an idempotency key is useful when an operation doesn't have such a natural identity.

A booking confirmation will also need an email. Holding the database transaction open while calling an email provider would keep locks held through a network request. Instead, record a pending notification alongside the booking and let a worker deliver it. A database transaction can protect its own writes; it cannot roll back an email already sent.

Loading an attendee's bookings

The account page needs something more readable than two numeric IDs, so you join the records:

SELECT w.title, b.created_at
FROM bookings AS b
JOIN workshops AS w ON w.id = b.workshop_id
WHERE b.attendee_id = 1
ORDER BY b.created_at DESC;

A join follows the relationship at query time. You don't have to copy the workshop title into every booking and update all those copies after correcting a spelling mistake. Sometimes you do want a historical snapshot, such as the price paid; that is a separate field with a separate meaning.

After a busy season, the account page slows down. You add an index for its filter and ordering, then measure the actual query:

CREATE INDEX bookings_attendee_created_idx
ON bookings (attendee_id, created_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT w.title, b.created_at
FROM bookings AS b
JOIN workshops AS w ON w.id = b.workshop_id
WHERE b.attendee_id = 1
ORDER BY b.created_at DESC
LIMIT 20;

ANALYZE runs the statement. In this tiny database, a sequential scan may still be cheaper. With real data, look at actual versus estimated rows, scanned pages, and time spent; an index's existence doesn't prove the planner should use it. Each extra index also makes writes and storage more expensive.

Adding optional workshop details

Later, workshops acquire optional settings: whether tools are supplied, a minimum age, or a map reference. You keep the booking relationships in columns and add a JSONB field for those details:

ALTER TABLE workshops
ADD COLUMN details JSONB NOT NULL DEFAULT '{}'::jsonb;

UPDATE workshops
SET details = '{"tools_supplied": true, "minimum_age": 12}'::jsonb
WHERE id = 1;

SELECT title, details ->> 'minimum_age' AS minimum_age
FROM workshops;

->> extracts text; cast it when you need a numeric comparison. If minimum age becomes an important rule across every workshop, a typed column with a constraint may be clearer. Flexible fields and relational structure can coexist.

Postgres keeps multiple row versions so ordinary reads can observe a snapshot while writes proceed. Conflicting writers can still block, and long transactions can hold back cleanup of old versions. You monitor slow queries and connection counts, use a connection pool, and test restoring backups before the app starts taking money. The workshop rules are now recorded in the database, and the final seat has one winner even when two requests arrive together.