Breakout Session

on YouTube ↗53m

" summary

Claims an open‑source PostgreSQL extension pg_shard that manages placement distribution and replication of shards and plugs into the regular Postgres toolchain; uses Citus for distributed query planning, JSONB, CloudFormation and PG tooling (pgbench pg_stat_statements pgBadger). Shows a working system used in production (one customer hitting 30,000 inserts/sec and hundreds of GBs per month), explains logical shards and rebalancing, and notes limits such as no cross‑shard transactions.

AI-written summary · about

" chapters (17)

1. 0:00Intro
2. 2:15Horizontal partitioning [...] involves putting different rows into different tables. - Wikipedia, "Shard (database architecture)"
3. 5:16How does that scale?
4. 6:08What about load characteristics?
5. 9:45Logical!
6. 10:44Operations burden Network resiliency - ACID tradeoffs? - No return
7. 12:31Pyramids!
8. 14:251. Generate realistic load 2. Measure, measure, measure... 3. Change one thing 4. Determine the impact 5. GOTO the first step
9. 18:43Change and Compare
10. 19:28Denormalize where necessary. Add indexes to all commonly used columns. Locally partition tables if it makes sense. Move hot columns to separate tables.
11. 28:33Sharding principles
12. 29:40Pass into hash function (smooths out distribution) – Use contiguous range - Specify a list of columns - Generalize to any expression
13. 30:17Choosing a key
14. 30:46the correct shard key can have a great impact on [...] performance [and] capability... - ib., Considerations for Selecting Shard Keys
15. 31:23What is most important to your application? - Spreading incoming writes – Targeting reads to reduce latency - Consider key frequently in WHERE clauses - Use a hybrid approach when it makes sense (shard on customer, partition on time) Mind the "hot spots"
16. 41:13Locks enforce safe commutation - Replicas visited in predictable order – Per-session libpq connection pool – If replica errors out, mark as inactive
17. 48:03Questions

" related talks

• 38mPostgreSQL as a distributed computing platformMarco Slot · PostgresOpen Silicon Valley 2017
• 42mPostgreSQL Distributed & Secure Database Ecosystem BuildingJuan Pan · Big Data Conference Europe 2022
• 33mFive Sharding Data Models and Which is RightCraig Kerstiens · RailsConf 2018
• 31mPostgreSQL Distributed & Secure Database Ecosystem BuildingTrista Pan · Conf42 Cloud Native 2022
• 44mDistributed Postgres with CitusWill Leinweber · HighLoad++ 2017
• 45mThe Future of Postgres ShardingBruce Momjian · HighLoad++ 2015
• 34mPostgreSQL sharding, state of the artJulien Tachoires · PGConf NYC 2021
• 45mThe Future of Postgres ShardingBruce Momjian · HighLoad++ 2015