How to Choose Between Star and Snowflake Schemas in PostgreSQL

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.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

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.