EasyEnv
PostgreSQL
Intermediate

PostgreSQL

Learn PostgreSQL end to end. SQL basics, joins, indexes, transactions, JSONB, EXPLAIN, and schema design.

14 lessons7.6h hands-on11 languages
Start learning freePostgreSQL course

14 lessons, in order

  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
PostgreSQL Course: Hands-on, in 11 Languages | Engineering Courses