How to Choose Between Star and Snowflake Schemas in PostgreSQL

Galaxy Glossary

What is the difference between star and snowflake schemas in PostgreSQL?

A star schema keeps dimensions denormalized, while a snowflake schema normalizes them into sub-tables—affecting query speed, storage, and maintenance in PostgreSQL.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

What is a star schema in PostgreSQL?

A star schema stores one central fact table and several denormalized dimension tables. Each dimension sits "close" to the fact table, minimizing joins and boosting query speed.

What is a snowflake schema in PostgreSQL?

A snowflake schema normalizes dimension tables into multiple related sub-tables.This reduces redundancy and storage but increases join depth during queries.

When should I use a star schema?

Choose star when read performance is critical, dimensions change infrequently, and disk space is not a major constraint.

When is a snowflake schema better?

Use snowflake when dimension data is large, highly repetitive, or frequently updated—saving space and easing maintenance.

How do I create a star schema?

Example DDL

-- Fact table CREATE TABLE Orders ( id SERIAL PRIMARY KEY, customer_id INT, order_date DATE, total_amount NUMERIC(12,2) ); -- Denormalized dimension CREATE TABLE Customers ( id INT PRIMARY KEY, name TEXT, email TEXT, created_at TIMESTAMP );

How do I create a snowflake schema?

Example DDL

-- Dimension split into sub-tables CREATE TABLE CustomerCore ( id INT PRIMARY KEY, name TEXT ); CREATE TABLE CustomerContact ( customer_id INT REFERENCES CustomerCore(id), email TEXT, created_at TIMESTAMP );

How do queries differ?

Star schemas need fewer joins: FROM Orders JOIN Customers USING (id).Snowflake schemas add hops: Orders → CustomerCore → CustomerContact.

Best practices for schema choice

Benchmark both designs with representative workloads. Add indexes on foreign keys. Avoid partial snowflakes; stay consistent.

Common mistakes

See dedicated section below.

.

Why How to Choose Between Star and Snowflake Schemas in PostgreSQL is important

How to Choose Between Star and Snowflake Schemas in PostgreSQL Example Usage


-- Compare total revenue by product name using a star schema
SELECT p.name, SUM(oi.quantity * p.price) AS revenue
FROM Orders o
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products p ON p.id = oi.product_id
GROUP BY p.name
ORDER BY revenue DESC;

How to Choose Between Star and Snowflake Schemas in PostgreSQL Syntax


-- Star schema example
CREATE TABLE Products (
  id      INT PRIMARY KEY,
  name    TEXT,
  price   NUMERIC(10,2),
  stock   INT
);

CREATE TABLE OrderItems (
  id          SERIAL PRIMARY KEY,
  order_id    INT REFERENCES Orders(id),
  product_id  INT REFERENCES Products(id),
  quantity    INT
);
-- Snowflake variant breaks Products
CREATE TABLE ProductCore (
  id   INT PRIMARY KEY,
  name TEXT
);
CREATE TABLE ProductPricing (
  product_id INT PRIMARY KEY REFERENCES ProductCore(id),
  price      NUMERIC(10,2),
  stock      INT
);

Common Mistakes

Mixing both schemas in one area: combining denormalized and normalized dimensions increases complexity. Pick one design per data mart.
Ignoring indexing: extra joins in snowflake schemas slow queries without foreign-key indexes. Always index join columns.

Frequently Asked Questions (FAQs)

Is a star schema always faster?

Yes for read-heavy workloads, because it reduces joins. However, updates can be slower due to redundancy.

Does snowflake save significant storage?

For large, repetitive dimensions, splitting tables can cut storage by 30-70%.

Can I convert star to snowflake later?

Yes—normalize dimensions and update foreign keys. Migrate during low-traffic windows.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.