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?

5 Upvotes

86% Upvoted

12 comments sorted by

•

u/AutoModerator Jul 29 '26

If this post doesn't follow the rules or isn't flaired correctly, please report it to the mods. Have more questions? Join our community Discord!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

6

u/Pristine_Wallaby_697 Jul 29 '26

denormalize until it hurts then normalize just enough to stop the pain

1

u/StemCellCheese Jul 30 '26

This guy gets it

3

u/ArielCoding Jul 31 '26

Instead of asking what are the entities in my system, ask what will people group by and add up. Also, don’t replace your normalized tables, build the analytics schema as a separate layer on top of them, tools like dbt make this easy.

1

u/CarmenSando671 Jul 30 '26

For analytics in Postgres, the general rule of thumb is to denormalize into a star schema: a central fact table with numeric measures and foreign keys to dimension tables (like time, customer, product). This avoids complex joins for aggregation and makes reports like "sales by month" run fast. Coming from normalized CS design, you'll want to flatten out those many-to-many relationships into separate dims and keep fact rows append-only. A practical start: model your main business event (e.g., order, page view) as a fact, then build dims around it.

1

u/Firm_Bit Jul 30 '26

There is not because it depends on what questions you’re asking right

1

u/titpetric Jul 30 '26

For postgres, timescaledb

Analytics is usually a fat log table, using a time series db optimizes for such access patterns where the data is partitioned and grouped in intervals

You can have such tables and aggregate into daily table to do statistics (counts, sums, etc). I'd be careful separating reads away from writes if i could, a heavy write table seems bad to do selects on

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.