PostgreSQL

1. 1

   What is PostgreSQL

   Imagine you are keeping track of every customer, order, and product for a small shop in a spreadsheet. It works, right up until two people try to...

   Relational databaseACIDMVCCPostgres vs MySQL vs SQLiteWhere Postgres shines
   20 min
2. 2

   psql and First Commands

   You now know what Postgres is for. The next problem is more practical: how do you actually talk to it? A running Postgres server just sits there...

   Psql command line clientConnecting as postgres userMeta commands like \l \c \dt \d \qSemicolons end statementsMultiline queriesSELECT version()
   25 min
3. 3

   Creating Databases and Tables

   So far you have only looked around a server someone else set up. That is fine for learning the tools, but it does not get you anywhere on a real...

   CREATE DATABASECREATE TABLEPRIMARY KEYNOT NULLDEFAULTDROP TABLE+1
   30 min
4. 4

   Data Types

   Every column you have created so far has had a type attached to it, TEXT, SERIAL, TIMESTAMPTZ, without much explanation of why that type and not...

   Integer and bigintNumeric vs floatText and varcharBooleanTimestamptz vs timestampDate+3
   25 min
5. 5

   SELECT and WHERE

   You have a table with a few thousand rows in it and you need seven of them. Not all of them, not the first hundred, the seven that match a rule you...

   SELECT specific columnsWHERE with AND ORBETWEENINLIKE and ILIKEIS NULL+2
   30 min
6. 6

   Joins

   Your data is not in one table, and it should not be. Users live in users, their posts live in posts, and the only thing tying a post to its author is...

   INNER JOINLEFT JOINRIGHT JOINFULL OUTER JOINSelf joinUSING vs ON
   35 min
7. 7

   Aggregates and Grouping

   Somebody asks you how the shop did last month. They do not want the four thousand order rows. They want one number, or maybe one number per customer,...

   COUNTSUMAVGMINMAXGROUP BY+3
   30 min
8. 8

   Indexes

   The query worked fine on your laptop with two hundred test rows. In production, against four million, the same query takes eleven seconds and the...

   B-tree defaultCREATE INDEXMulti-column indexPartial index WHERE activeUnique indexWrite cost+1
   35 min
9. 9

   Transactions and Isolation

   Two UPDATE statements move money from Alice to Bob. The first one runs, the connection drops, the second one never happens, and thirty pounds have...

   BEGIN COMMIT ROLLBACKSavepointsREAD COMMITTED defaultREPEATABLE READSERIALIZABLEDirty read non-repeatable read phantom read serialization anomaly
   35 min
10. 10

    Functions and Triggers

    Every table in your schema has an updated_at column, and every one of them is wrong. Not always, just on the code paths where somebody forgot to set...

    CREATE FUNCTIONLANGUAGE plpgsqlRETURNSCREATE TRIGGERBEFORE UPDATENEW and OLD+1
    35 min
11. 11

    JSONB and Arrays

    Some data refuses to sit still in columns. A products table where laptops have a RAM size, keyboards have a switch type and monitors have a diagonal,...

    JSONB column-> operator->> operator@> contains operatorGIN index on jsonbText[] and integer[] arrays+2
    35 min
12. 12

    EXPLAIN and Query Tuning

    A page that used to load instantly now takes nine seconds, and the instinct is to start guessing. Add an index here. Rewrite the join. Maybe it is...

    EXPLAINEXPLAIN ANALYZESeq scan vs index scan vs index-only scanFixing a missing indexPg_stat_statements overview
    35 min
13. 13

    Backup and Restore

    The nightly job has been writing a file to disk for eight months and nobody has ever opened it. That is not a backup, that is a hopeful file. A...

    Pg_dump custom formatPg_restorePlain SQL dumpsPoint-in-time recovery overview
    25 min
14. 14

    Capstone: Design a Schema

    Every lesson so far handed you a table and asked you to query it. Real work runs the other way round: somebody describes a product, and you have to...

    Schema designPrimary and foreign keysIndexes on hot columnsUpdated_at triggerJSONB metadataJoins with aggregates+1
    60 min

Every lesson, quiz, and tutor reply renders in your language. The AI tutor matches the language you ask in.

See how hiring teams interview engineers on PostgreSQL, in production-like environments.