How to build · 06

Databases

SQL, PostgreSQL, MySQL, Redis and NoSQL, design, indexes, transactions, ORMs and migrations.

Read: 0 / 12Database Fundamentals SQL PostgreSQL Database Design Indexes Transactions ORM & Migrations Query Optimization Redis NoSQL Concepts MySQL

Database Fundamentals

Fundamentals✓
Databases: tables, keys and why a business needs them
A relational database stores information in tables: each row is one record (a client, an order) and each column is one property with a fixed data type. A primary key identifies a row unambiguously, and a foreign key links tables: an order references a client, and the database refuses to create an order for a client that does not exist. A DBMS such as PostgreSQL enforces these rules itself, handles many users at once and answers questions written in SQL — things an Excel file cannot do.
Beginner · 10 min read

SQL

PostgreSQL

Tutorial✓
PostgreSQL: running in Docker, psql and a first table
The easiest way to run PostgreSQL locally is Docker: one docker run with a superuser password, a port and a named volume starts the server, and the psql client is already inside the container. Then you create a dedicated database owner for the application, define tables with suitable types (bigint, text, numeric for money, timestamptz for points in time) and NOT NULL, CHECK, UNIQUE and DEFAULT constraints, and take backups with pg_dump — always checking them with a test restore. The current stable release is PostgreSQL 18; this lesson is verified on 17.11, and everything in it works the same on 18 except the path the volume is mounted to.
Beginner · 11 min read · practice ~45–90 min

Database Design

Tutorial✓
Database design: relationships, keys and normalisation
Database design starts with the entities of the business domain and the relationships between them: 1:1 is a foreign key that is also the primary key, 1:N is a foreign key on the “many” side, and N:M is a separate junction table with a composite primary key. For every foreign key you choose what happens on delete: RESTRICT for history, CASCADE for parts of a whole, SET NULL for optional references. Normalising to third normal form removes duplicates and contradictions; denormalise deliberately, in a way that lets the database keep the copy consistent with its source.
Intermediate · 10 min read · practice ~1.5–3 h

Indexes

Deep dive✓
Indexes: speeding up queries without slowing down writes
An index is a separate ordered structure that lets PostgreSQL find the rows it needs without reading the whole table. You design an index for a specific query: composite when there is an equality filter plus a sort, partial when you query a small fraction of rows, expression when the condition applies a function, GIN for jsonb and full-text search. Every index slows down INSERT and UPDATE and takes disk space, so confirm the effect with EXPLAIN and drop indexes nobody uses.
Intermediate · 9 min read

Transactions

Deep dive✓
Transactions and isolation: ACID without the mystery
A transaction groups several changes into one all-or-nothing action: after COMMIT every change is visible, after ROLLBACK or an error none is. The isolation level decides what a transaction sees of concurrent ones. PostgreSQL defaults to Read Committed, where each query sees fresh data, so a read-compute-write pattern loses updates. The defences are an atomic UPDATE, a SELECT … FOR UPDATE lock, a UNIQUE or EXCLUDE constraint, or the Serializable level with a retry on error 40001.
Intermediate · 8 min read

ORM & Migrations

Tutorial✓
ORM and migrations with Prisma
An ORM (object-relational mapper) turns work with objects in code into SQL queries against a relational database: in Prisma you describe models in schema.prisma, get a generated typed client, and prisma migrate dev turns each schema change into a SQL migration file that lives in Git. An ORM speeds up routine CRUD and catches typos, but it doesn't replace SQL knowledge: generated migrations need reading, N+1 needs watching, and complex reports are easier to write in raw SQL.
Intermediate · 13 min read · practice ~1.5–3 h

Query Optimization

How-to✓
Query optimisation: EXPLAIN ANALYZE in practice
First find the slow query, with pg_stat_statements or a log driven by log_min_duration_statement, then look at its plan with EXPLAIN (ANALYZE, BUFFERS). In the plan, look for the node that eats the most time and pages, and compare the estimated rows with the actual rows: an error of several times almost always explains a bad planner choice. The usual fixes are an index for the filter or join, rewriting a condition so no function wraps the column, NOT EXISTS instead of NOT IN, fresh statistics via ANALYZE, and extended statistics for dependent columns.
Advanced · 9 min read · practice ~2–4 h

Redis

Tutorial✓
Redis: cache, TTL and counters
Redis is an in-memory key-value store: commands take fractions of a millisecond, and every key can have a time to live (TTL). That's why it's used as a cache in front of a slow database or API, and for counters, rate limits, short-lived codes and locks. The main caching pattern is cache-aside: the application looks in Redis first, on a miss loads the data from the source and stores it with a TTL, and when the data changes it deletes the stale key.
Intermediate · 11 min read · practice ~1.5–3 h

NoSQL Concepts

Comparison✓
NoSQL: documents, key-value, and when they beat SQL
NoSQL is an umbrella name for databases that do not store data in related tables: they use documents (MongoDB), key-value pairs (Redis), wide rows (Cassandra) or a graph (Neo4j). The document model fits data that is read and changed together, such as an order with its line items; it gets in the way when entities have many relationships, shared data gets duplicated, and one operation must change several records atomically. If most of your data is relational and only part of it needs to be flexible, a jsonb column in PostgreSQL is usually enough.
Intermediate · 9 min read

MySQL

Comparison✓
PostgreSQL or MySQL: practical differences
PostgreSQL and MySQL are mature relational databases with transactions and replication, and either works for a typical web application. The differences show up in the details: PostgreSQL defaults to Read Committed while InnoDB in MySQL defaults to Repeatable Read; MySQL compares strings case-insensitively and cannot roll back CREATE TABLE; PostgreSQL has richer types, jsonb with GIN indexes and an extension system. Choose PostgreSQL for a complex data model, in-database analytics and extensions such as PostGIS or pgvector; choose MySQL when your stack requires it (WordPress, for example) or your team and hosting already run it.
Intermediate · 8 min read

Learning paths with this direction

SQL and PostgreSQL · 9 →← All directions

Tell us about your task — we'll review your request and get back to you

Got a project?
We'll scope and estimate it — for free.

Tell us about the product or the problem. After reviewing the task we'll come back with an architecture, a timeline and a price for the first version.

Scope and risks estimated before we start
NDA — yours or ours
Work starts within a week

Message on WhatsApp