HomePostgreSQL
How the database really works
@DrIbrarAhmed
Production PostgreSQL for people who keep databases alive: indexes, EXPLAIN, HA, Patroni, PgBouncer, PITR, RDS, Aurora, and what actually changes in new major versions. Articles sit beside the full videos.
psql tutorial: read the PostgreSQL session before you CREATE. A prompt can look connected and still not name the database, the role, or where an unqualified table will be created. This PostgreSQL psql lesson shows the commands that answer that before any DDL: - Start psql, then read current_database(), current_user, and SHOW search_path - SQL goes to the server.
Install PostgreSQL 18 step by step on Windows, Mac, Linux, and Docker, then prove which server you actually reached: the client, the server version, and the data directory. This PostgreSQL 18 installation tutorial covers one path per platform: - macOS uses Homebrew postgresql@18 - Ubuntu and Debian use the PGDG packages and the postgresql@18-main cluster - Windows uses the EnterpriseDB installer, Path, and the postgresql-x64-18 service - Docker uses the postgres:18 image, with the named volume mounted at /var/lib/postgresql The Docker persistence demo was run on this machine: docker rm leaves the volume, and docker volume rm deletes it.
Before we open a single terminal, here is what you are getting. Every index type PostgreSQL 18 ships, on a five million row table. Measured, not guessed. You will watch the planner refuse a perfectly good index, and you will find out why. If you have ever added an index and watched nothing get faster, this is the one that fixes it.
Managed doesn't mean unbreakable. But on Aurora, the thing that breaks is compute. Your data sits in a separate storage service that the instance failure never touched. So when the writer dies, what happens next depends on one question. Do you have a replica? With one, Aurora promotes it, usually in under thirty seconds. Without one, Aurora has to build you a new writer, and that takes minutes. Lose a whole availability zone and writes keep going. We'll build all of it, break it, and measure it.
Managed doesn't mean it never breaks. When the writer goes away, every open connection goes with it. What happens next depends on the architecture you picked. Single-AZ has no standby, so recovery means a restart, or a replacement. AWS documents sixty to one hundred twenty seconds for a Multi-AZ DB instance, and under thirty-five for a Multi-AZ DB cluster. Sockets never migrate. So we build each pattern, break it, and measure what the application sees.
================================================================
Let's build a PostgreSQL cluster that survives things going wrong, and let's build it one honest step at a time. This is where everyone starts: one application, one PostgreSQL eighteen server called db one. A write arrives, it is recorded in the write-ahead log, and the commit comes back. It works perfectly. And that is exactly the trap, because the postgres processes, the kernel, the disk, and the address your clients dial all live inside a single failure domain.
PG-Perf-E04 · Connection Pooling · Narration (calm read)
================================================================
Here is a setting that looks completely innocent: work_mem, set to one