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.
PostgreSQL 18 Installation: macOS, Ubuntu, Windows, or Docker. This episode shows how to install PostgreSQL 18 on one platform and then prove which server you actually reached: the client, the server version, and the data directory. macOS uses Homebrew postgresql@18. Ubuntu and Debian use the PGDG packages and the postgresql@18-main cluster.
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.
Three writable PostgreSQL regions. No single global primary - all three accept writes at once. That is active-active, with pgEdge Spock. When one region dies, the survivors keep writing.
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.
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
For years, pg stats knew a table's name, but not its identity.