Bulk data processing and PostgreSQL thingy
summary
Shows a working set of tactics for PostgreSQL at scale: use Hibernate StatelessSession and JDBC batching to cut write time (batch sizes ~20–100, experiments show big gains on 400–400k inserts), avoid IDENTITY generators because they disable batching and prefer SEQUENCE (or time-ordered UUIDv6) with aligned allocation, never leave fetch_size = 0 (setting fetch_size = 100 drops memory from ~1.7GB to ~64MB), offload large reads to Chronicle Map to close DB connections, and avoid long-running transactions that stop autovacuum and cause disk bloat.
AI-written summary · about
chapters (18)
- 0:00Introduction
- 1:50Bulk data in banking
- 3:59Use case and architecture
- 6:06Why PostgreSQL
- 7:49PostgreSQL features
- 11:43MVCC tradeoffs
- 14:11Vacuum role
- 15:37Batch JDBC
- 19:37Hibernate batching
- 23:57Stateless session
- 27:1110M entries performance
- 29:24Long transactions dangers
- 30:32ID generation best practices
- 34:30Stream fetching pitfalls
- 39:13Streaming performance fixes
- 41:20Chronicle Map offloading
- 46:34Chronicle Map demo
- 51:11Key takeaways and closing remarks
related talks
57mPostgreSQL When It's Not Your Job
30mPuny to Powerful PostgreSQL Rails Apps
37mPostgres at Any Scale
26mBiggish Data With Rails and Postgresql
48mHow to Tame A Mastodon: Lessons for PostgreSQL at Scale
31mBiggish Data With Rails and Postgresql
47mPostgreSQL worst practices
45mHibernate: Mapping Strategies and Their Database Performance Impact