postgresql

    PostgreSQL Course

    Master production PostgreSQL — SQL, indexes, MVCC, JSONB, replication, backups, security, administration and performance tuning.

    143
    Lessons
    17
    Modules
    0/143
    Completed

    Architecture

    SQL Query Lifecycle

    From an application request through the parser, optimizer and executor — back to a typed result set.

    App Request
    HTTP
    SQL Query
    text
    Parser
    AST
    Optimizer
    cost-based
    Executor
    plan run
    Storage
    pages + WAL
    Result Set
    rows
    Indexing
    B-tree / GIN / BRIN
    Joins
    Nested · Hash · Merge
    ACID + Locks
    Tx isolation levels
    Scaling
    Replicas · Sharding
    Curriculum

    Enterprise learning path

    17 modules · 143 lessons

    Foundations

    0/10 complete
    1. 1
      PostgreSQL Home
      Next up

      postgresql home postgresql home is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    2. 2
      What is PostgreSQL?

      what is postgresql? what is postgresql? is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    3. 3
      PostgreSQL Architecture

      postgresql architecture postgresql architecture is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    4. 4
      Installing PostgreSQL

      installing postgresql installing postgresql is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    5. 5
      psql Basics

      psql basics psql basics is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    6. 6
      PostgreSQL Data Types

      postgresql data types postgresql data types is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    7. 7
      Creating Databases

      creating databases creating databases is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    8. 8
      Creating Tables

      creating tables creating tables is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    9. 9
      CRUD Operations

      crud operations crud operations is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    10. 10
      Constraints

      constraints constraints is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    SQL Fundamentals

    0/11 complete
    1. 11
      SELECT Queries

      select queries select queries is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    2. 12
      WHERE Clause

      where clause where clause is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    3. 13
      ORDER BY

      order by order by is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    4. 14
      LIMIT & OFFSET

      limit & offset limit & offset is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    5. 15
      DISTINCT

      distinct distinct is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    6. 16
      Aliases

      aliases aliases is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    7. 17
      CASE Statements

      case statements case statements is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    8. 18
      NULL Handling

      null handling null handling is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    9. 19
      Aggregate Functions

      aggregate functions aggregate functions is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    10. 20
      GROUP BY

      group by group by is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    11. 21
      HAVING

      having having is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    Joins

    0/8 complete
    1. 22
      INNER JOIN

      inner join inner join is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    2. 23
      LEFT JOIN

      left join left join is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    3. 24
      RIGHT JOIN

      right join right join is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    4. 25
      FULL OUTER JOIN

      full outer join full outer join is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    5. 26
      SELF JOIN

      self join self join is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    6. 27
      CROSS JOIN

      cross join cross join is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    7. 28
      Join Optimization

      join optimization join optimization is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    8. 29
      Real-World Join Patterns

      real-world join patterns real-world join patterns is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    Advanced SQL

    0/8 complete
    1. 30
      Subqueries

      subqueries subqueries is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    2. 31
      Common Table Expressions (CTE)

      common table expressions (cte) common table expressions (cte) is a core postgresql skill for application developers, data engineers, dbas, and architects. this

    3. 32
      Recursive CTEs

      recursive ctes recursive ctes is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    4. 33
      Window Functions

      window functions window functions is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    5. 34
      Ranking Functions

      ranking functions ranking functions is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    6. 35
      LEAD & LAG

      lead & lag lead & lag is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    7. 36
      Pivot Techniques

      pivot techniques pivot techniques is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    8. 37
      Set Operations

      set operations set operations is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    Indexing

    0/10 complete
    1. 38
      Index Fundamentals

      index fundamentals index fundamentals is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    2. 39
      B-Tree Indexes

      b-tree indexes b-tree indexes is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    3. 40
      Hash Indexes

      hash indexes hash indexes is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    4. 41
      GIN Indexes

      gin indexes gin indexes is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    5. 42
      GiST Indexes

      gist indexes gist indexes is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    6. 43
      BRIN Indexes

      brin indexes brin indexes is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    7. 44
      Partial Indexes

      partial indexes partial indexes is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    8. 45
      Composite Indexes

      composite indexes composite indexes is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    9. 46
      Covering Indexes

      covering indexes covering indexes is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    10. 47
      Index Maintenance

      index maintenance index maintenance is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    Performance

    0/10 complete
    1. 48
      EXPLAIN

      explain explain is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    2. 49
      EXPLAIN ANALYZE

      explain analyze explain analyze is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    3. 50
      Query Planning

      query planning query planning is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    4. 51
      Sequential Scans

      sequential scans sequential scans is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    5. 52
      Index Scans

      index scans index scans is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    6. 53
      Query Optimization

      query optimization query optimization is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    7. 54
      VACUUM

      vacuum vacuum is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    8. 55
      ANALYZE

      analyze analyze is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    9. 56
      Performance Monitoring

      performance monitoring performance monitoring is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    10. 57
      Connection Pooling

      connection pooling connection pooling is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    Transactions

    0/9 complete
    1. 58
      ACID Principles

      acid principles acid principles is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    2. 59
      Transactions

      transactions transactions is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    3. 60
      COMMIT

      commit commit is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    4. 61
      ROLLBACK

      rollback rollback is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    5. 62
      SAVEPOINT

      savepoint savepoint is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    6. 63
      Isolation Levels

      isolation levels isolation levels is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    7. 64
      MVCC

      mvcc mvcc is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    8. 65
      Deadlocks

      deadlocks deadlocks is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    9. 66
      Locking Strategies

      locking strategies locking strategies is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    Programming

    0/8 complete
    1. 67
      Functions

      functions functions is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    2. 68
      Stored Procedures

      stored procedures stored procedures is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    3. 69
      Triggers

      triggers triggers is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    4. 70
      Views

      views views is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    5. 71
      Materialized Views

      materialized views materialized views is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    6. 72
      Sequences

      sequences sequences is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    7. 73
      Generated Columns

      generated columns generated columns is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    8. 74
      Extensions

      extensions extensions is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    JSON & Modern Features

    0/8 complete
    1. 75
      JSON

      json json is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    2. 76
      JSONB

      jsonb jsonb is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    3. 77
      JSON Queries

      json queries json queries is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    4. 78
      JSON Indexing

      json indexing json indexing is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    5. 79
      Arrays

      arrays arrays is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    6. 80
      Composite Types

      composite types composite types is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    7. 81
      UUID

      uuid uuid is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    8. 82
      Full-Text Search

      full-text search full-text search is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    Security

    0/8 complete
    1. 83
      Authentication

      authentication authentication is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    2. 84
      Authorization

      authorization authorization is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    3. 85
      Roles

      roles roles is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    4. 86
      Privileges

      privileges privileges is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    5. 87
      Row-Level Security

      row-level security row-level security is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    6. 88
      SSL Connections

      ssl connections ssl connections is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    7. 89
      Auditing

      auditing auditing is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    8. 90
      Security Best Practices

      security best practices security best practices is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    Administration

    0/10 complete
    1. 91
      User Management

      user management user management is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    2. 92
      Backup & Restore

      backup & restore backup & restore is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    3. 93
      pg_dump

      pg_dump pg_dump is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    4. 94
      pg_restore

      pg_restore pg_restore is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    5. 95
      Replication

      replication replication is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    6. 96
      Streaming Replication

      streaming replication streaming replication is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    7. 97
      Failover

      failover failover is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    8. 98
      High Availability

      high availability high availability is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    9. 99
      Partitioning

      partitioning partitioning is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    10. 100
      Maintenance Tasks

      maintenance tasks maintenance tasks is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    Cloud

    0/6 complete
    1. 101
      PostgreSQL on AWS RDS

      postgresql on aws rds postgresql on aws rds is a core postgresql skill for application developers, data engineers, dbas, and architects. this

    2. 102
      Azure Database for PostgreSQL

      azure database for postgresql azure database for postgresql is a core postgresql skill for application developers, data engineers, dbas, and architects. this

    3. 103
      Google Cloud SQL

      google cloud sql google cloud sql is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    4. 104
      Managed PostgreSQL

      managed postgresql managed postgresql is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    5. 105
      Scaling Strategies

      scaling strategies scaling strategies is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    6. 106
      Monitoring Cloud Databases

      monitoring cloud databases monitoring cloud databases is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    Advanced

    0/8 complete
    1. 107
      Logical Replication

      logical replication logical replication is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    2. 108
      Physical Replication

      physical replication physical replication is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    3. 109
      Foreign Data Wrappers

      foreign data wrappers foreign data wrappers is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    4. 110
      Sharding Concepts

      sharding concepts sharding concepts is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    5. 111
      TimescaleDB

      timescaledb timescaledb is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    6. 112
      PostGIS

      postgis postgis is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    7. 113
      pgBouncer

      pgbouncer pgbouncer is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    8. 114
      Pgpool-II

      pgpool-ii pgpool-ii is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    Troubleshooting

    0/7 complete
    1. 115
      Slow Queries

      slow queries slow queries is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    2. 116
      Deadlocks

      deadlocks deadlocks is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax, execution behavior,

    3. 117
      Connection Problems

      connection problems connection problems is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    4. 118
      Replication Issues

      replication issues replication issues is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    5. 119
      Disk Space Problems

      disk space problems disk space problems is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    6. 120
      Recovery Strategies

      recovery strategies recovery strategies is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    7. 121
      Troubleshooting Checklist

      troubleshooting checklist troubleshooting checklist is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    Use Cases

    0/7 complete
    1. 122
      E-Commerce Databases

      e-commerce databases e-commerce databases is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    2. 123
      Banking Systems

      banking systems banking systems is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    3. 124
      Analytics Platforms

      analytics platforms analytics platforms is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    4. 125
      SaaS Applications

      saas applications saas applications is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    5. 126
      Multi-Tenant Systems

      multi-tenant systems multi-tenant systems is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    6. 127
      Event Logging

      event logging event logging is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    7. 128
      Inventory Systems

      inventory systems inventory systems is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects sql syntax,

    Projects

    0/8 complete
    1. 129
      E-Commerce Database

      e-commerce database e-commerce database is a production postgresql project that combines schema design, relational modeling, indexes, backups, monitoring, and operational trade-offs.

    2. 130
      Banking Management System

      banking management system banking management system is a production postgresql project that combines schema design, relational modeling, indexes, backups, monitoring, and operational

    3. 131
      Blogging Platform

      blogging platform blogging platform is a production postgresql project that combines schema design, relational modeling, indexes, backups, monitoring, and operational trade-offs.

    4. 132
      Inventory Management System

      inventory management system inventory management system is a production postgresql project that combines schema design, relational modeling, indexes, backups, monitoring, and operational

    5. 133
      Analytics Dashboard Backend

      analytics dashboard backend analytics dashboard backend is a production postgresql project that combines schema design, relational modeling, indexes, backups, monitoring, and operational

    6. 134
      Multi-Tenant SaaS Database

      multi-tenant saas database multi-tenant saas database is a production postgresql project that combines schema design, relational modeling, indexes, backups, monitoring, and operational

    7. 135
      PostgreSQL Monitoring Dashboard

      postgresql monitoring dashboard postgresql monitoring dashboard is a production postgresql project that combines schema design, relational modeling, indexes, backups, monitoring, and operational

    8. 136
      High Availability Setup

      high availability setup high availability setup is a production postgresql project that combines schema design, relational modeling, indexes, backups, monitoring, and operational

    Interview

    0/7 complete
    1. 137
      PostgreSQL Master Interview Bank

      postgresql interview bank sql joins indexing transactions performance jsonb administration replication troubleshooting dba architect explain analyze vacuum mvcc backup restore high availability

    2. 138
      SQL Fundamentals Interview

      sql fundamentals interview sql fundamentals interview is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    3. 139
      PostgreSQL Interview (0-2 Years)

      postgresql interview (0-2 years) postgresql interview (0-2 years) is a core postgresql skill for application developers, data engineers, dbas, and architects. this

    4. 140
      PostgreSQL Interview (3-5 Years)

      postgresql interview (3-5 years) postgresql interview (3-5 years) is a core postgresql skill for application developers, data engineers, dbas, and architects. this

    5. 141
      PostgreSQL Performance Interview

      postgresql performance interview postgresql performance interview is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    6. 142
      PostgreSQL DBA Interview

      postgresql dba interview postgresql dba interview is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects

    7. 143
      PostgreSQL Architect Interview

      postgresql architect interview postgresql architect interview is a core postgresql skill for application developers, data engineers, dbas, and architects. this lesson connects