Cons of using a star schema in a columnar database

Question

My organisation is implementing a new data warehouse using a columnar data store (Redshift) and the performance is currently abysmal. There are many causes for this but I think the key reason is because of the way we have our data structured, we're implementing a classic star schema design, which surely is optimal for a traditional RDBMS (OLTP) and not necessarily for OLAP?

I'm looking for someone with experience on this to briefly explain the pros and cons of structuring large quantities of data in a star schema on amazon redshift.

I'm obviously no expert, but I'm looking for some advice and guidance on this.

thanks, Matt

Answer 1

You are mixing up three orthogonal concepts: data model (star schema), workload characteristics (OLTP vs. OLAP), and physical data organisation (columnar).

Your data model has no bearing on whether column-organised tables are appropriate for you; however, data organisation must reflect the nature of your workload (i.e. queries). For example, SELECT * FROM... cannot possibly perform well in a columnar database.

Answer 2

Mustaccio's answer is good. In addition, every star schema I have ever seen was implemented on top of a relational DBMS, often Oracle. Also, the data in it was downstream from some kind or operational database, also on a relational DBMS. The operational database was mostly normalized and used for OLTP.

I have never seen a star schema used for OLTP. I can't imagine anybody ever doing that. It's not what star schema is for.