Table Inheritance in PostgreSQL
summary
PostgreSQL table inheritance gets applied to Forge of Empires to reduce storage and speed common operations: tests on 1 million rows show disk use falling from ~150MB to ~90MB (about 40% less space), inserts/updates save small amounts but deletes collapse from ~70 ms to 0.2 ms delete for child-row removals, and per-child selects drop from ~570 ms to ~75 ms. Practical downsides: unique constraints not inherited (primary and foreign keys behave per-table), migrations and constraint workarounds are manual.
AI-written summary · about
chapters (18)
- 0:00Start
- 2:45DIFFERENT DESIGNS IN RELATION DBS
- 6:02TABLE INHERITANCE
- 7:58QUERY EXAMPLES
- 12:07TESTING SCENARIO
- 19:07DRAWBACKS
- 19:44FOREIGN KEY CONSTRAINT
- 21:00PRIMARY KEY CONSTRAINT
- 22:43UNIQUE CONSTRAINT
- 24:13FIRST IMPLEMENTATION
- 25:33DB DESIGN CHANGES
- 27:40INSIDE THE CODE
- 29:34ADDING NEW CHILDREN AND PARENTS
- 30:59RECURRENT MANUAL STEP
- 31:56OTHER FEATURES
- 33:56COMPARISON BETWEEN DESIGNS
- 35:29CONCLUSION
- 36:43END OF PRESENTATION
related talks
25mData modeling with PostgreSQL at the core of Software Development
26mPartitioning your Postgres tables for 20x better performance
30mPuny to Powerful PostgreSQL Rails Apps
46mUse PostgreSQL, not Rails, to make Rails Faster!
28mPostgres Storytelling: Cunning Schema Design with Creative Data Modeling
43mJust Use Postgres.
25m500X Memory Reduction In Rails With Postgres Schemas And Apartment
54mReducing Costs and Improving Performance With Data Modeling in Postgres