Performance tips for PostgreSQL OLAP applications

Question

I have a PostgreSQL OLAP cube with 5 joined tables with about 1 million rows. A lot of users could filter dimensions via web interface that creates high workload on the server. Web application is an analytics tool so users could change dimensions constantly to analyze their business. Could you please give some tips how to improve performance? For example, it's a good idea to use a materialized view with 5 joined tables which is cached by Postgres and a user queries this view and doesn't do the joins again and again. Or use indexes on most frequently used columns. Please help! ::-)

Answer 1

You can pre-aggregate your fact table(s). For example, if people often look at month roll-ups, then make a new fact table rolled up by month.

Partition your fact table(s), by say year

Make sure you have primary keys on your facts and dims, as well as indexes on your keys to dimension tables. Index columns that are often used for ORDER BY, WHERE and GROUP BY

Are you using an actual OLAP server front of PostgreSQL? Make sure caching is on and optimized

Use RAID 10 SSDs, and make sure your machine has plenty of cores and RAM

Use browser caching, proxy caching, web server caching, ORM caching