PostgreSQL
PostgreSQL (often called Postgres) is an open-source relational database. It stores data in tables with rows and columns, lets you define relationships between tables, and uses SQL to query and manipulate that data. It's one of the most widely used databases in production systems.
The word "relational" means data is structured and linked — a bookings table can reference a venues table through a foreign key, and the database enforces that the relationship stays valid.
Why PostgreSQL over other databases
Postgres is often chosen because:
- ACID compliance — transactions are atomic, consistent, isolated, and durable. A multi-step operation either completes fully or rolls back entirely. No half-written data.
- Feature-rich — supports RLS, full-text search, JSONB columns, arrays, stored procedures, and more. It's not just a simple row store.
- Battle-tested — has been in active development since 1996. Highly reliable and well-documented.
- Open source — no licensing fees, huge community.
Key concepts
Transactions
A group of SQL statements that execute together. If any statement fails, the whole group rolls back. Critical for operations where partial completion would leave data in a broken state.
BEGIN;
INSERT INTO bookings (...) VALUES (...);
INSERT INTO booking_status_history (...) VALUES (...);
COMMIT; -- both succeed, or both roll back
See also: ACID Properties, Transactions
Stored functions / procedures
You can write logic directly inside the database as a function, using PL/pgSQL:
CREATE OR REPLACE FUNCTION create_booking_atomic(...)
RETURNS bookings AS $$
BEGIN
-- check for conflicts, then insert
END;
$$ LANGUAGE plpgsql;
Calling this function via Supabase RPC keeps the entire operation atomic inside one transaction, which is how this project prevents double-bookings.
SELECT ... FOR UPDATE
A locking mechanism. When a transaction runs SELECT ... FOR UPDATE, it locks the matching rows so no other transaction can modify them until the first one finishes. Used to serialize concurrent writes and prevent race conditions.
SECURITY DEFINER
A function attribute that makes the function run with the privileges of whoever created it (usually the DB owner), regardless of who calls it. Allows an anon-role caller to execute logic that touches restricted tables, without opening those tables up directly.
Indexes
Structures that speed up queries on specific columns. Without an index, Postgres scans every row in the table. With one, it jumps straight to the matching rows. Foreign key columns and frequently-filtered columns are good index candidates.
Postgres in Supabase
Supabase is a managed Postgres layer. When you use Supabase, you're using Postgres under the hood. Features like Row Level Security, stored functions, and RPC are all native Postgres capabilities that Supabase exposes through a convenient client library.
Related notes
- Supabase — Postgres-backed BaaS
- Row Level Security — Postgres security feature
- ACID Properties
- Transactions