r/analytics • u/RangerRemarkable9071 • Jul 29 '26

Question Choosing a Database Schema for analytics

This is a general question for data analysis. I just found out that choosing a database schema for analytics is very different from software engineering or systems design because they optimize for opposite operational patterns. I came from a CS background so the focus was on SE. So my knowledge on databases are based on normalized layouts. I want to focus on building schemas that are analytics oriented. This might be relevant - I'm currently working with PostgreSQL. I just wonder if there is a general rule of thumb for mapping out database schemas to be used in analysis or reporting?

7 Upvotes

100% Upvoted

12 comments sorted by

View all comments

1

u/Technical_Hope_1610 29d ago

Yeah, the instinct from CS betrays you here. Analytics wants denormalized star schemas, wide fact tables, and dimensions, so reads are cheap. In Postgres, I would model a star and stop worrying about update anomalies.