PostgreSQL When It's Not Your Job

on YouTube ↗57m

" summary

Use packages to install PostgreSQL and set shared_buffers to 25% of RAM capped at 8GB, set max_connections ~200, maintenance_work_mem = RAM/16, and tune checkpoints (checkpoint_timeout 10–30m, checkpoint_segments ~32) and planner hints (effective_io_concurrency, random_page_cost). Turn on CSV logging and log slow statements and lock waits. Prefer COPY for bulk loads, avoid blobs/sessions in the DB, avoid long transactions, add nullable columns then backfill before adding NOT NULL, use streaming replication for large databases, and use pgbouncer for pooling; monitor disk, i/o, and vacuum activity.

AI-written summary · about

" chapters (28)

1. 0:00Start
2. 2:10Configure and Maintain Postgres
3. 5:33How Do You Install Postgres
4. 6:51Configuring Linux
5. 8:34Postgres Configuration
6. 9:19Logging
7. 11:11Csv Format
8. 12:46Basic Resource Configuration
9. 16:26Checkpoints
10. 20:21Effective Io Concurrency
11. 21:41Performance Boosts
12. 21:57Maximum Number of Locks per Transaction Value
13. 29:15Indexing
14. 31:21What's a Bad Index
15. 34:12Memory and I / O Utilization
16. 35:00Back Backups
17. 36:20Streaming Streaming Replication
18. 38:20Query Cancellation
19. 39:19Partial Replication
20. 41:14You Can Do a Series of Base Backups and Wall Segments on a Remote Server Effectively You Are Taking a Disk Based Snapshot of It and Then a List of All the Wall Segments That Are the Changes That Have Happened to Date to that the Nice Part about this Is You Can Reply that to a Point in Time
21. 41:27The Nice Part about this Is You Can Reply that to a Point in Time Which Is Really Handy So if You Know Something Really Horrible Happened at 10 : 30 Last Night You Can Replay the Database to 10 : 29 and Bring It Up in that State Now that's Really Nice this Is Why for Disaster Recovery this Can Be Really Really Very Handy It Takes Much To Bring Up One of these That Takes To Promote a Secondary to Being a Primary but if this Is the Only Way You Can Get Your Data Back That Can Be Extremely Useful
22. 41:45This Is Why for Disaster Recovery this Can Be Really Really Very Handy It Takes Much To Bring Up One of these That Takes To Promote a Secondary to Being a Primary but if this Is the Only Way You Can Get Your Data Back That Can Be Extremely Useful It's Well Worth the Security Frequently this Server this Remote Server Is Set Up on It in a Different Data Center I Live in San Francisco so Data Center Disruption Is Not a Completely Academic Idea So Said You Know with since I'M in San Francisco Sometimes I'Ll Set to Something like Fresno Which Probably Will Not Get a Huge Earthquake at the Same Time We Will
23. 46:49You Then Add the Constraint Later once You'Ve Got You Populated that Field this Takes a Lock but It Takes a Faster Lock because It Only Takes a Lock Long Enough To Modify this the the Table Structure Not To Rewrite the Entire Table Also 92 Has a New Has a Not Checked a Feature for the Constraint Which Means You Can Add the Not Null as Not Checked and It Won't Do the Whole Rewrite because It Allowed no Values To Appear in It of Course Your Application Has To Realize that no Values Could Come Back from this Column that Thought Was Not Null
24. 47:10Which Means You Can Add the Not Null as Not Checked and It Won't Do the Whole Rewrite because It Allowed no Values To Appear in It of Course Your Application Has To Realize that no Values Could Come Back from this Column that Thought Was Not Null Now this Isn't Going To Be Super Fast It Still Has To Rewrite the Entire Table but It Can Rewrites the Entire Table All the System Is Still in Production and that Could Be Significantly Less Painful than Having To Lock the Entire Table and Have the Whole System Go down the Other Option Is You Can Create a New Table and Copy the Old Values into It of Course Then You Have To Figure Out What To Do About Update to that Table Wall but Pull that Copy Was It Operation
25. 47:31And that Could Be Significantly Less Painful than Having To Lock the Entire Table and Have the Whole System Go down the Other Option Is You Can Create a New Table and Copy the Old Values into It of Course Then You Have To Figure Out What To Do About Update to that Table Wall but Pull that Copy Was It Operation Occasional It So Now that You'Re Smart and You Go In and Look at Pg Sql
26. 48:38And One Thing That Pg Stat Activity Has in It Is How When the Backend Started Which Is They'Ll Be a Back-End Start Time Which Is When that Connection Opened and You Can See the Last Query Time So if these Are like Starting To Drift into the Past these Are Really Bad You Should Never See this State in a Postgres Database because It Potentially Means Somebody Could Be Holding Locks on Tables or on Tuples and Not Releasing Them So Kill Them Get Rid of Them There Find Out What's Causing Them and Get Rid of Them That Can Freeze Vacuum Freezes like this Evil Thing because It's So Surprising
27. 51:27It's Basically all It Does Is Multiplex a Lot of Client-Side Connections Down to a Small Number of Server-Side Connections That's all It Does It Does It Really Well It Does Not Have Failover It Does Not Have Load Balancing those Are those Are Problems Left to the Reader in the Case of Pg Bouncer Don't Automatically Rule It Out because You Think You Might at some Point Need that Stuff There Are Ways To Add It on Top of Pg Bouncer and Pg Bouncer Is a Good First Step for this and Really this Is another Thing That You Don't Need To Pay Us To Set Up It's Really Very It's Very Straightforward Virtually Self-Documenting Not every Application Can Take Advantage of It
28. 52:58It Is Sparkly It Does some Query Analysis It Will Look at the Queries as They'Re Going By and if It Thinks It's a Read Query It'Ll Send It to the Primary if It's if It Thinks It's a Write Query or I'M Sorry a Write Query It'Ll Send It to the Primary if It's a Require You Can Send It to the Secondary So Can Distribute those for You Makes Thousands of Julienne Fries I Mean You Know It's like Full of Features However It Is Complicated To Set Up and Quite Fiddly Especially if You'Re Running It on a on a High Availability Solution

" related talks

• 34mTuning PostgreSQL to work even betterKaren Jex · DjangoCon Europe 2023
• 45mProper PostgreSQL Parameters to Prevent Poor PerformanceGreg Dostatni · Postgres Conference Orlando 2025
• 33mTuning PostgreSQL to work even betterKaren Jex · PostgreSQL Conference Europe 2023
• 58mScaling PostgresDenish Patel · PostgresOpen 2013
• 43mPostgres Performance: From Slow to ProElizabeth Garrett Christensen · DjangoCon US 2023
• 52mPostgreSQL Configuration for HumansÁlvaro Hernández · HighLoad++ 2017
• 55mScaling PostgreSQL: Horizontal vs. VerticalIbrar Ahmed · Postgres Conference Orlando 2025
• 59mPostgreSQL Don'ts and DosMagnus Hagander · foss-north 2026