Case study: Write-heavy scalable and reliable inventory platform

https://doordash.engineering/2023/02/22/how-doordash-designed-a-successful-write-heavy-scalable-and-reliable-inventory-platform/ TLDR; Case study: Write-heavy scalable and reliable inventory platform

The technical requirements

• High Scalability
• High reliability
• Low latency
• High observability

Database table optimization

• Choose a natural primary key instead of auto-incrementing a primary key: combined primary key naturally constructed from business parameters -> reduce columns and query more efficiently because all our queries are mostly centered around those business parameters.
• Cleanup DB indexes:
  • Add missing indexes for all the queries
  • Remove unnecessary indexes
• Reduce the column count: put some of the frequently updated columns into one single JSONB column -> There are pros and cons based on use cases
• Configure TTL for fast-growing tables: add TTL for high-intensity-write tables which do not need to have data for too long
• Batch and rewrite query within one SQL request whenever possible in API and DB: this will save a lot of QPS for downstream services and databases.