Modern SQL features in PostgreSQL
summary
PGBench shows tiny network delays crush throughput — 0.01 ms yields ~60,000 TPS, 0.28 ms drops to ~35,000 and 0.48 ms to ~2,000 — so reducing round trips is the biggest win. Uses include replacing chatty app logic with single SQL statements, measuring via pgbench and lock timing, cutting rollback storms (examples report ~95,000 aborts/day and terabytes of temp files). Practical fixes demonstrated: CTE to turn hours into minutes, ON CONFLICT and temporary statement locks to avoid rollbacks, and MERGE plus FDW for intermittent replication.
AI-written summary · about
chapters (15)
- 0:00Introduction to modern SQL
- 1:06Why learn modern SQL
- 2:10Network latency impact on performance
- 9:45Poor SQL practices and issues
- 18:29Modern SQL features overview
- 23:40Sequences vs business logic for IDs
- 29:43Handling conflicts and rollbacks
- 33:41Merge statement benefits
- 37:01Data replication with MERGE
- 38:49Unnest for array conversion
- 40:12Complex business logic in SQL
- 42:42Data retention and archiving
- 45:12Atomicity in SQL statements
- 46:15Optimizing database performance
- 47:00Conclusion and resources
related talks
36mBest Practices in PostgreSQL Tuning: Navigating Key Performance Bottlenecks in the Cloud
25mPostgreSQL performance tips you have never seen before
50mPerformance Archaeology
56mPerformance Improvements in PostgreSQL9 2
31mDemanding the impossible rigorous database benchm
31mPostgres Performance for Humans
41mUsing Databases to Pull Your Application Weight
50mImproving Postgres' Efficiency