Optimal Data Loads
summary
A 295 MB CSV of ~2 million rows and 10 fields loads row-by-row in ~9:15 locally and ~7:11 over the network, but switching to COPY/backslash COPY via psycopg drops local runs to ~51s and server runs to ~21s, a roughly 10x improvement; further tactics include using a staging table (all-text columns to avoid parse errors), dropping/recreating indexes for slightly faster loads, and using unlogged tables to shave time at the cost of WAL safety. Scripts and slides live in the GitHub repo.
AI-written summary · about
chapters (6)
- 0:00Introduction to optimal data loads
- 10:45Defining data loads and ETL vs ELT
- 23:55Don’s slow Python data load problem
- 31:17Using COPY command for fast data loading
- 40:15Advanced optimizations: indices and UNLOGGED tables
- 43:05Key takeaways and Q&A
related talks
46mETL Confessions
32mLoading at Hyperscale: Advanced Data Import Techniques for Citus
46mPostgreSQL Beyond Django: Strategies to Get Max Performance with Álvaro Justen
18mMaking Postgres inserts faster on Azure
29mMigrating Data Quickly and Safely
16mOnline Scale-Out and Cloud Data Ingestion
45mPerformance tricks you have never seen before 2.0
43mOptimize Python & Django apps with Postgres superpowers