Data + backend / Intermediate

SQL, PostgreSQL, and Redis

Relational modeling, SQL queries, indexes, transactions, PostgreSQL production use, and Redis for shared ephemeral state.

sqlpostgresqlredisdatabaseindex

01Relational model

Relational databases store structured rows with constraints and relationships. The database should enforce critical invariants with NOT NULL, UNIQUE, foreign keys, check constraints where appropriate, and transactions.

sqlSchema + index

CREATE TABLE project (
    id BIGSERIAL PRIMARY KEY,
    slug VARCHAR(120) UNIQUE NOT NULL,
    name VARCHAR(180) NOT NULL,
    published BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE INDEX ix_project_published
    ON project (published, id);

02Transactions

A transaction groups related changes so they commit together or roll back together. For example, creating a project and its media database rows should not leave half-completed state if validation or storage fails.

03Where Redis fits

Shared rate-limit counters across web workers.
Short-lived cache entries.
Session or coordination state when architecture requires it.
Queues/pub-sub when using suitable reliability semantics.