Using a database in a data science project - Lessons learned in production
summary
System handles real traffic (up to five ticket sales per second), recalculates millions of predictions daily, and keeps data fresh by ingesting CDC into a Postgres replica via Kafka CDC. Performance wins come from postgres materialized views, unlogged tables and COPY or prepared statements (psycopg2/psycopg3) for bulk writes, and precomputed day‑wise snapshots stored as parquet+snappy files to avoid repeated heavy queries. Query profiling through a single SQL helper exposes the 20/80 hotspots so optimizations target the real bottlenecks.
AI-written summary · about
chapters (4)
- 0:00Start
- 6:43ETL steps using materialized views (based on normal views) A cronjob can refresh a materialized view the code is on the schema and easy to inspect. When based on a view it's trivial to switch to have fresh data and benchmark
- 13:34Ways to insert a lot of data quickly .execute_many is the bare minimum • COPY is the best way when upsert is not needed • Unlogged tables are faster to create for temporary data
- 24:49ETL steps using materialized views (based on normal views) A cronjob can refresh a materialized view, the code is on the schema and easy to inspect. When based on a view it's trivial to switch to have fresh data and benchmark
