Making Postgres inserts faster on Azure

on YouTube ↗18m

" summary

Practical recipes and numbers: using unlogged tables gives about 5× faster inserts and 10× faster COPY for staging loads; switch heavy imports to the COPY command or micro-batch multi-row INSERTs (send 10–100s of rows per transaction instead of 100K single commits); drop or disable indexes, unique/foreign constraints and triggers for bulk loads; delay HA/read-replica creation until after load; tune wal_buffers (e.g. up to 128MB), shared_buffers (~40% RAM), max_wal_size (up to 64GB) and turn off autovacuum during the load.

AI-written summary · about

" chapters (9)

1. 0:00Intro
2. 2:02Key Scenarios
3. 2:28Creating unclogged tables
4. 5:15Using COPY command
5. 6:48Batch inserts vs Single commits
6. 8:27Removing/Disabling contraints, triggers, & indexes
7. 10:26Partitioning the table
8. 11:42HA & Read Replica
9. 13:08Tuning server parameters

" related talks

• 53mOptimizing your Azure SQL Database workloadsSanjay Mishra, Denzil Ribeiro, Dimitri Furman · SQLBits 2020
• 57mPostgreSQL When It's Not Your JobChristophe Pettus · Postgres Open 2012
• 34mData modeling for those who want to save time and money!Danielle Monteiro · Citus Con: An Event for Postgres 2022
• 32mLoading at Hyperscale: Advanced Data Import Techniques for CitusColton Shepard · Citus Con: An Event for Postgres 2022
• 52mBulk data processing and PostgreSQL thingyYingkun Bai · Voxxed Days Ticino 2025
• 48mOptimal Data LoadsBen Lis · PGConf NYC 2022
• 37m​Postgres at Any Scale​Will Leinweber · RailsConf 2017
• 30mOvercoming Performance Hurdles in Postgres Partitioned TablesSarat Balijepalli · POSETTE: An Event for Postgres 2025