Star Schema vs. Snowflake Schema in PostgreSQL for Analytics
When deciding between a star schema and a snowflake schema for a PostgreSQL analytics database, a useful starting rule is this: choose a star schema when query performance and straightforward ETL processes are the main priorities, and choose a snowflake schema when efficient storage, referential integrity, and frequently changing dimension attributes are more important.
Both approaches use dimensional modeling for OLAP workloads. PostgreSQL supports either design without requiring extensions, and both can operate on managed PostgreSQL services. When comparing PostgreSQL with other database platforms, its relational capabilities are one reason it is frequently selected for analytical workloads.
The main distinction between the two designs is the balance between denormalized and normalized dimension tables surrounding a central fact table. This tutorial demonstrates DDL examples for both approaches with the same retail sales scenario, compares equivalent queries with EXPLAIN ANALYZE, and explains indexing and configuration considerations for PostgreSQL analytics workloads.
This tutorial explains how to build star and snowflake schemas in PostgreSQL, compare query performance with EXPLAIN ANALYZE, configure a managed PostgreSQL environment for analytical workloads, and select between the two models by following a reusable decision process.
Key Takeaways
- A star schema places dimension attributes in denormalized, flat tables. This reduces the number of joins and usually speeds up aggregation queries, but it introduces redundant storage.
- A snowflake schema separates dimension hierarchies into normalized lookup tables. This lowers storage requirements and reduces update anomalies, but queries require additional joins.
- PostgreSQL can use hash joins for dimension lookups in either design. The
work_memsetting has a direct effect on performance when multi-join snowflake queries require more memory than is available. - Equivalent star schema queries typically need fewer hash joins than snowflake queries. The performance difference becomes more noticeable as the fact table grows and available
work_membecomes small relative to intermediate result sets. - BRIN indexes on sequential date columns can provide smaller indexes and lower maintenance costs than B-tree indexes for large, append-only analytical fact tables when values correlate with insertion order.
- Managed PostgreSQL environments commonly provide configurable settings such as
work_memandmax_parallel_workers_per_gather, which influence planning and execution of multi-join analytical queries. - The decision framework later in this tutorial can be used to select a schema based on fact-table size, query behavior, and ETL maturity.
Prerequisites
Before working through this tutorial, you need:
- A managed or self-hosted PostgreSQL environment running PostgreSQL 15 or newer. PostgreSQL 14 can also be used where it remains supported.
psqlinstalled and connected to the PostgreSQL server.- A database called
analyticscreated on the server:CREATE DATABASE analytics; - Basic knowledge of SQL
SELECT,JOIN, andGROUP BYsyntax.
What Is a Star Schema in PostgreSQL?
A star schema arranges analytical information around a central fact table with flat dimension tables placed around it. Its name comes from the appearance of an entity-relationship diagram: the fact table sits in the middle while dimension tables extend outward.
Star schema ER diagram: fact_order_items at the center connected to dim_dates, dim_customers, and dim_products as flat surrounding dimension tables through many-to-one crow’s-foot relationships.
Core Components: Fact Tables and Flat Dimension Tables
A star schema contains one central fact table together with flat dimension tables. Fact tables contain measurable events such as order line items, page views, or sensor measurements. Dimension tables contain descriptive information associated with those events, including product properties, customer information, and date hierarchies.
Dimension tables in a star schema are denormalized. All properties belonging to a concept such as a product are kept in one table, including both subcategory_name and category_name. A query that calculates revenue by product category therefore joins the fact table to only one product dimension table. The term “star schema” reflects the diagram shape created by a fact table in the center and dimension tables extending around it.
Star Schema DDL Example for a Retail Sales Scenario
CREATE TABLE dim_dates (
date_key INTEGER PRIMARY KEY,
full_date DATE NOT NULL,
day_of_week VARCHAR(10) NOT NULL,
month INTEGER NOT NULL,
quarter INTEGER NOT NULL,
year INTEGER NOT NULL
);
CREATE TABLE dim_customers (
customer_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id INTEGER NOT NULL UNIQUE,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
city VARCHAR(100),
country VARCHAR(100)
);
CREATE TABLE dim_products (
product_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
product_id INTEGER NOT NULL UNIQUE,
product_name VARCHAR(255) NOT NULL,
subcategory_name VARCHAR(100) NOT NULL,
category_name VARCHAR(100) NOT NULL
);
CREATE TABLE fact_order_items (
order_item_key BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
order_id INTEGER NOT NULL,
date_key INTEGER NOT NULL REFERENCES dim_dates(date_key),
customer_key INTEGER NOT NULL REFERENCES dim_customers(customer_key),
product_key INTEGER NOT NULL REFERENCES dim_products(product_key),
quantity INTEGER NOT NULL,
unit_price NUMERIC(10,2) NOT NULL,
discount NUMERIC(5,2) NOT NULL DEFAULT 0,
revenue NUMERIC(12,2) NOT NULL
);
The dim_products table keeps both subcategory_name and category_name directly in each product row. If the name of a category changes, every relevant record in dim_products needs to be updated.
When a Star Schema Is the Right Choice
A star schema is an appropriate default for many analytics workloads. When a BI application mainly performs aggregations against one dimension at a time, the flat design enables analysts to create simple queries without navigating additional hierarchy tables. SQL-generating BI tools such as Metabase or Looker also tend to create simpler query plans against star schemas than highly normalized structures.
ETL maturity is another factor that can favor a star schema. Data must be denormalized before it is loaded, so the pipeline responsible for this work should be tested and dependable. If the pipeline is stable and dimension properties in source systems rarely change, the easier update behavior of a snowflake schema may provide little benefit. A star schema is therefore a strong starting point when simplicity is more valuable than minimizing storage.
What Is a Snowflake Schema in PostgreSQL?
A snowflake schema begins with the same type of fact table but normalizes dimensions into additional lookup tables. As a result, fewer repeated strings are stored, while analytical queries require more joins.
Snowflake schema ER diagram: fact_order_items connects to dim_products, which connects to product_subcategory, which then connects to product_category, creating a normalized three-level dimension hierarchy.
How Normalization Extends Dimension Tables
A snowflake design applies normalization to dimensions. Instead of keeping category_name directly in dim_products, the product table points to product_subcategory, and that table references product_category. The dependency between subcategory_name and category_name is therefore removed from dim_products.
The cost of this design is additional query complexity. To aggregate information at category level, PostgreSQL must now traverse fact_order_items, dim_products, product_subcategory, and product_category rather than joining directly to a single product dimension.
Snowflake Schema DDL Example Using the Same Retail Sales Scenario
CREATE TABLE product_category (
category_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
category_name VARCHAR(100) NOT NULL
);
CREATE TABLE product_subcategory (
subcategory_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
subcategory_name VARCHAR(100) NOT NULL,
category_key INTEGER NOT NULL REFERENCES product_category(category_key)
);
CREATE TABLE dim_dates (
date_key INTEGER PRIMARY KEY,
full_date DATE NOT NULL,
day_of_week VARCHAR(10) NOT NULL,
month INTEGER NOT NULL,
quarter INTEGER NOT NULL,
year INTEGER NOT NULL
);
CREATE TABLE dim_customers (
customer_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id INTEGER NOT NULL UNIQUE,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
city VARCHAR(100),
country VARCHAR(100)
);
CREATE TABLE dim_products (
product_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
product_id INTEGER NOT NULL UNIQUE,
product_name VARCHAR(255) NOT NULL,
subcategory_key INTEGER NOT NULL REFERENCES product_subcategory(subcategory_key)
);
CREATE TABLE fact_order_items (
order_item_key BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
order_id INTEGER NOT NULL,
date_key INTEGER NOT NULL REFERENCES dim_dates(date_key),
customer_key INTEGER NOT NULL REFERENCES dim_customers(customer_key),
product_key INTEGER NOT NULL REFERENCES dim_products(product_key),
quantity INTEGER NOT NULL,
unit_price NUMERIC(10,2) NOT NULL,
discount NUMERIC(5,2) NOT NULL DEFAULT 0,
revenue NUMERIC(12,2) NOT NULL
);
When a Snowflake Schema Is the Right Choice
The added complexity of a snowflake schema can be worthwhile when dimension values change regularly. For example, when a product catalog is reorganized every quarter, changing one record in product_category is significantly cheaper than executing a batch update against 20,000 product rows. At larger scales, this difference in maintenance cost becomes substantial.
Shared dimensions and data-governance requirements are two additional situations that favor snowflaking. If the same product_category table is used by sales and inventory fact tables, a single normalized lookup prevents different fact areas from developing inconsistent category values. Where referential integrity must be controlled within the database instead of only through application logic, the foreign-key hierarchy provides that enforcement automatically.
Star Schema vs. Snowflake Schema: Direct Comparison
The practical distinction between the models is the point at which complexity is absorbed. A snowflake schema generally adds cost while queries are executed, whereas a star schema shifts more work into ETL processing. The following sections compare these trade-offs across important production considerations.
Side-by-side ER diagram comparing a star schema on the left with a snowflake schema on the right using the same retail sales example. The star design contains flat dimensions, while the snowflake version divides the product dimension into a three-level normalized hierarchy.
Query Complexity and Number of Joins
In the star design, a query joining fact_order_items with dim_products and dim_dates requires two joins to calculate revenue by category and quarter. The corresponding snowflake query needs four joins: fact_order_items to dim_products, dim_products to product_subcategory, product_subcategory to product_category, and fact_order_items to dim_dates.
PostgreSQL can process extra hash joins effectively when smaller dimension tables remain in memory. However, every added join increases planning requirements and raises the possibility of an inefficient join order as row counts increase.
Storage Footprint and Data Redundancy
The storage difference can be demonstrated with a simple example. If dim_products contains 20,000 rows and stores both category_name and subcategory_name as VARCHAR(100) columns in every record, those fields alone require approximately 4 MB. Moving the same information into a five-row product_category table and a 2,800-row product_subcategory table reduces the requirement to less than 50 KB. For those two attributes, that represents roughly a 98% reduction.
In many practical systems, this alone does not determine the schema choice. The fact table contains integer foreign keys in either design, so most of the difference exists in dimensions rather than fact_order_items. Storage improvements become more relevant when dimensions contain hundreds of thousands of records together with several low-cardinality VARCHAR fields, as frequently occurs with product and geographic hierarchies.
ETL Pipelines and Data Loading Complexity
Loading a star schema places responsibility for denormalization on the ETL process. Before a record reaches dim_products, the pipeline must join the source product data with category and subcategory information and flatten the result into a single row. With a reliable, thoroughly tested pipeline, this cost is handled during loading. With an unreliable process, category inconsistencies can be written silently into the dimension table.
A snowflake schema moves part of that responsibility into the data model. Its normalized tables often resemble source-system structures more closely, which can reduce transformations during dimension loading. Incremental loads, however, must keep product_category, product_subcategory, and dim_products synchronized and load them in the proper sequence. Foreign-key constraints identify invalid relationships, but the coordination effort still exists.
Maintenance and Update Anomalies
A star design can experience update anomalies. When a category name changes, every dim_products row containing that category_name must also change. Under slowly changing dimension Type 1 patterns, this can mean running large batch updates against extensive dimension tables.
Normalized snowflake dimensions avoid this issue for separated attributes. Changing category_name in a single product_category record effectively changes the category value associated with every linked product through the foreign-key relationships.
Comparison Table
| Schema | Query Speed | Storage Efficiency | Join Complexity | ETL Complexity | Best-Fit Use Case |
|---|---|---|---|---|---|
| Star schema | Usually faster for aggregation because fewer joins are required | Lower efficiency because dimension rows contain repeated attribute strings | Low; typical analytical queries commonly use 1-2 joins | Higher; the loading pipeline must denormalize information before insertion | BI reports, dashboard workloads, and dimensions that rarely change |
| Snowflake schema | Can become slower at scale because each hierarchy introduces additional joins | Higher efficiency because normalized values are stored once | Higher; hierarchy queries can require 3-5 joins | Lower transformation requirements because the structure more closely resembles normalized sources | Data-governance environments, frequently changing dimensions, and shared lookup tables |
Generating Test Data for Reproducible Benchmarks
The EXPLAIN ANALYZE results in the next section were produced from a production-representative dataset that used a larger date range than the generator shown below. Run these generate_series statements against the analytics database to build a functionally comparable dataset for schema testing and performance comparisons. Exact execution times and row estimates will vary, but the relative behavior of the star and snowflake designs remains comparable at this scale.
Populate dim_dates with one row for every calendar date from 2020 through 2025, resulting in 2,192 rows:
INSERT INTO dim_dates (date_key, full_date, day_of_week, month, quarter, year)
SELECT
TO_CHAR(d, 'YYYYMMDD')::INTEGER,
d::DATE,
TO_CHAR(d, 'FMDay'),
EXTRACT(MONTH FROM d)::INTEGER,
EXTRACT(QUARTER FROM d)::INTEGER,
EXTRACT(YEAR FROM d)::INTEGER
FROM generate_series('2020-01-01'::DATE, '2025-12-31'::DATE, '1 day') d;
Populate dim_customers with 50,000 records:
INSERT INTO dim_customers (customer_id, first_name, last_name, email, city, country)
SELECT
i,
'First' || i,
'Last' || i,
'customer' || i || '@example.com',
(ARRAY['New York','San Francisco','Chicago','Austin','Seattle'])[1 + (i % 5)],
'US'
FROM generate_series(1, 50000) i;
Populate dim_products with 20,000 records for the star schema:
INSERT INTO dim_products (product_id, product_name, subcategory_name, category_name)
SELECT
i,
'Product ' || i,
(ARRAY['Laptops','Tablets','Phones','Monitors','Accessories',
'Chairs','Desks','Shelves','Lamps','Rugs',
'Jackets','Shirts','Pants','Shoes','Hats',
'Bats','Balls','Nets','Gloves','Helmets',
'Pans','Knives','Bowls','Plates','Cups'])[1 + (i % 25)],
(ARRAY['Electronics','Furniture','Clothing','Sports','Kitchen'])[1 + (i % 5)]
FROM generate_series(1, 20000) i;
Populate fact_order_items with approximately 2.7 million records:
INSERT INTO fact_order_items (order_id, date_key, product_key, customer_key, quantity, unit_price, discount, revenue)
SELECT
(random() * 500000 + 1)::INTEGER,
TO_CHAR('2020-01-01'::DATE + floor(random() * 2192)::INTEGER, 'YYYYMMDD')::INTEGER,
(random() * 19999 + 1)::INTEGER,
(random() * 49999 + 1)::INTEGER,
v.quantity,
v.unit_price,
v.discount,
ROUND((v.quantity * v.unit_price) * (1 - v.discount), 2) AS revenue
FROM generate_series(1, 2700000)
CROSS JOIN LATERAL (
SELECT
(random() * 10 + 1)::INTEGER AS quantity,
(random() * 500 + 10)::NUMERIC(10,2) AS unit_price,
0::NUMERIC(5,2) AS discount
) v;
After loading the data, execute ANALYZE so PostgreSQL refreshes planner statistics before benchmarking:
psql -d analytics -c "ANALYZE dim_dates, dim_customers, dim_products, fact_order_items;"
The star and snowflake definitions of dim_products use incompatible columns, while fact_order_items references whichever version currently exists. Benchmark the two designs in separate databases, or remove and recreate fact_order_items and dim_products with the snowflake definitions before executing the following statements. Defining both variants in the same database without recreating the tables causes the snowflake insert to fail because it does not include the subcategory_name and category_name fields expected by the star-schema table.
For a snowflake benchmark, populate the normalized tables with the same structure of five categories and 2,800 subcategories, using 560 subcategories for each category:
INSERT INTO product_category (category_name)
VALUES ('Electronics'), ('Furniture'), ('Clothing'), ('Sports'), ('Kitchen');
INSERT INTO product_subcategory (subcategory_name, category_key)
SELECT
pc.category_name || ' Sub ' || s,
pc.category_key
FROM product_category pc
CROSS JOIN generate_series(1, 560) s;
INSERT INTO dim_products (product_id, product_name, subcategory_key)
SELECT
i,
'Product ' || i,
(SELECT subcategory_key
FROM product_subcategory
ORDER BY subcategory_key
OFFSET (i % 2800) LIMIT 1)
Run ANALYZE for the normalized dimension tables after loading:
psql -d analytics -c "ANALYZE product_category, product_subcategory;"
PostgreSQL Query Performance: What the Numbers Show
Schema design affects execution plans in two directly measurable areas: the number of joins and the amount of memory used by hash tables. The EXPLAIN ANALYZE examples below use the same fact table containing approximately 2.7 million rows so that the cost difference can be compared directly.
EXPLAIN ANALYZE Output for a Star Schema Aggregation Query
The following EXPLAIN ANALYZE results were produced on a managed PostgreSQL 15 environment with 4 vCPUs, 8 GB of RAM, and work_mem configured to 64 MB. Exact row estimates and execution times vary with database statistics, PostgreSQL version, available memory, hardware, and configuration. The relative difference illustrates the typical behavior of denormalized and normalized dimension hierarchies at this data volume.
The query calculates revenue grouped by product category and quarter using a fact_order_items table with about 2.7 million rows.
EXPLAIN ANALYZE
SELECT
dp.category_name,
dd.year,
dd.quarter,
SUM(foi.revenue) AS total_revenue,
COUNT(DISTINCT foi.order_id) AS order_count
FROM fact_order_items foi
JOIN dim_products dp ON foi.product_key = dp.product_key
JOIN dim_dates dd ON foi.date_key = dd.date_key
WHERE dd.year = 2023
AND dp.category_name = 'Electronics'
GROUP BY dp.category_name, dd.year, dd.quarter
ORDER BY dd.quarter;
HashAggregate (cost=84321.50..84325.80 rows=16 width=48)
(actual time=412.344..412.591 rows=4 loops=1)
Group Key: dp.category_name, dd.year, dd.quarter
-> Hash Join (cost=1628.90..82944.30 rows=89918 width=32)
(actual time=20.344..387.801 rows=89918 loops=1)
Hash Cond: (foi.date_key = dd.date_key)
-> Hash Join (cost=1592.00..71308.20 rows=540000 width=28)
(actual time=18.211..298.112 rows=540000 loops=1)
Hash Cond: (foi.product_key = dp.product_key)
-> Seq Scan on fact_order_items foi
(cost=0.00..52130.00 rows=2700000 width=24)
(actual time=0.021..142.430 rows=2700000 loops=1)
-> Hash (cost=1592.00..1592.00 rows=4000 width=20)
(actual time=10.411..10.412 rows=4000 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 309kB
-> Seq Scan on dim_products dp
(cost=0.00..1592.00 rows=4000 width=20)
(actual time=0.011..6.322 rows=4000 loops=1)
Filter: (category_name = 'Electronics')
Rows Removed by Filter: 16000
-> Hash (cost=36.90..36.90 rows=365 width=12)
(actual time=2.511..2.512 rows=365 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 26kB
-> Seq Scan on dim_dates dd
(cost=0.00..36.90 rows=365 width=12)
(actual time=0.009..1.234 rows=365 loops=1)
Filter: (year = 2023)
Rows Removed by Filter: 1827
Planning Time: 2.341 ms
Execution Time: 413.019 ms
PostgreSQL selected hash joins for both dimension lookups. The category_name condition is evaluated against the in-memory hash for dim_products, while all 2.7 million records in the fact table are scanned once.
EXPLAIN ANALYZE Output for the Equivalent Snowflake Schema Query
EXPLAIN ANALYZE
SELECT
pc.category_name,
dd.year,
dd.quarter,
SUM(foi.revenue) AS total_revenue,
COUNT(DISTINCT foi.order_id) AS order_count
FROM fact_order_items foi
JOIN dim_products dp ON foi.product_key = dp.product_key
JOIN product_subcategory ps ON dp.subcategory_key = ps.subcategory_key
JOIN product_category pc ON ps.category_key = pc.category_key
JOIN dim_dates dd ON foi.date_key = dd.date_key
WHERE dd.year = 2023
AND pc.category_name = 'Electronics'
GROUP BY pc.category_name, dd.year, dd.quarter
ORDER BY dd.quarter;
HashAggregate (cost=99812.40..99816.70 rows=16 width=48)
(actual time=498.712..498.981 rows=4 loops=1)
Group Key: pc.category_name, dd.year, dd.quarter
-> Hash Join (cost=505.95..98180.20 rows=89918 width=32)
(actual time=22.341..471.229 rows=89918 loops=1)
Hash Cond: (foi.date_key = dd.date_key)
-> Hash Join (cost=469.05..95100.40 rows=540000 width=28)
(actual time=20.114..421.902 rows=540000 loops=1)
Hash Cond: (ps.category_key = pc.category_key)
-> Hash Join (cost=468.00..88444.30 rows=2700000 width=32)
(actual time=12.123..360.112 rows=2700000 loops=1)
Hash Cond: (dp.subcategory_key = ps.subcategory_key)
-> Hash Join (cost=412.00..80305.60 rows=2700000 width=28)
(actual time=8.344..288.112 rows=2700000 loops=1)
Hash Cond: (foi.product_key = dp.product_key)
-> Seq Scan on fact_order_items foi
(cost=0.00..52130.00 rows=2700000 width=24)
(actual time=0.021..142.430 rows=2700000 loops=1)
-> Hash (cost=412.00..412.00 rows=20000 width=8)
(actual time=8.111..8.112 rows=20000 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 940kB
-> Seq Scan on dim_products dp
(cost=0.00..412.00 rows=20000 width=8)
(actual time=0.011..3.902 rows=20000 loops=1)
-> Hash (cost=56.00..56.00 rows=2800 width=8)
(actual time=3.211..3.212 rows=2800 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 142kB
-> Seq Scan on product_subcategory ps
(cost=0.00..56.00 rows=2800 width=8)
(actual time=0.009..1.512 rows=2800 loops=1)
-> Hash (cost=1.05..1.05 rows=1 width=12)
(actual time=0.018..0.019 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on product_category pc
(cost=0.00..1.05 rows=1 width=12)
(actual time=0.007..0.011 rows=1 loops=1)
Filter: (category_name = 'Electronics')
Rows Removed by Filter: 4
-> Hash (cost=36.90..36.90 rows=365 width=12)
(actual time=2.511..2.512 rows=365 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 26kB
-> Seq Scan on dim_dates dd
(cost=0.00..36.90 rows=365 width=12)
(actual time=0.009..1.234 rows=365 loops=1)
Filter: (year = 2023)
Rows Removed by Filter: 1827
Planning Time: 3.812 ms
Execution Time: 499.621 ms
The snowflake query completed in about 499 ms compared with approximately 413 ms for the star schema, producing a difference of roughly 21% over the same 2.7 million fact-table records. In this execution plan, PostgreSQL joins dim_products against the complete set of 2,800 product_subcategory records before the pc.category_name = 'Electronics' restriction is applied during the category join. Depending on statistics and database configuration, PostgreSQL may instead select an execution order that applies the category filter earlier.
Index Strategies for PostgreSQL Fact Tables
Three indexing approaches can address common analytical bottlenecks on fact_order_items.
BRIN, or Block Range Index, works efficiently for columns whose values correlate with physical row order. This is commonly the case with append-only fact tables loaded chronologically:
CREATE INDEX idx_fact_order_items_date_brin
ON fact_order_items USING BRIN (date_key);
A partial index can focus only on recent periods, reducing both index size and maintenance when reporting primarily targets current information:
CREATE INDEX idx_fact_order_items_recent
ON fact_order_items (date_key, product_key)
WHERE date_key >= 20240101;
A covering index can allow PostgreSQL to answer a frequently used aggregation directly from index data without reading the heap:
CREATE INDEX idx_fact_order_items_covering
ON fact_order_items (date_key, product_key)
INCLUDE (revenue, order_id);
Execute ANALYZE fact_order_items; following bulk loads to update optimizer statistics. Outdated statistics can lead the planner toward inefficient join orders and are a frequent source of unexpected analytical performance regressions. Run VACUUM fact_order_items; after bulk loading as well so the visibility map remains current and index-only scans can use the covering index efficiently. Without an up-to-date VACUUM operation, PostgreSQL may still perform heap fetches even when all requested columns exist in the index.
Setting Up an Analytics Schema on Managed PostgreSQL
The following steps cover provisioning a PostgreSQL environment, configuring analytics-related parameters, applying either schema, and using connection pooling with both designs.
Provisioning PostgreSQL for Analytics Workloads
Choose a PostgreSQL server or managed database plan with at least 4 GB of RAM. Analytical queries involving snowflake schemas with several joins benefit from additional memory because PostgreSQL can allocate work_mem for each hash or sort operation during query execution. For environments dominated by read-heavy reporting, a read replica can be used to separate analytical queries from transactional activity on the primary database.
Configuring work_mem and enable_hashjoin for Multi-Join Queries
In managed PostgreSQL environments, analytics-related parameters are generally configured using the database service’s configuration interface or supported management API. Important parameters for multi-join analytical workloads include work_mem, max_parallel_workers_per_gather, and enable_hashjoin.
Suggested starting values for an analytics-oriented environment:
| Parameter | Recommended Value | Effect |
|---|---|---|
work_mem |
256MB | Provides memory for individual hash and sort operations and can reduce disk spilling in snowflake queries with multiple joins |
max_parallel_workers_per_gather |
4 | Allows parallel sequential scans on large fact tables |
enable_hashjoin |
on | Keeps hash-join execution plans available and should normally remain enabled for analytical workloads |
Some managed PostgreSQL services restrict ALTER SYSTEM. In those cases, change supported parameters through the service configuration interface or its management API. Setting work_mem too high in a shared environment can exhaust available memory when many queries execute concurrently. Test a higher value through a session-specific override first with SET work_mem = '256MB';.
To override the value for only the current analytical session:
SET work_mem = '256MB';
This value affects only the active connection and can be used with PgBouncer when session pooling is enabled.
Applying a Star or Snowflake Schema with psql or pgAdmin
After connecting to the PostgreSQL environment, apply the DDL from the previous sections through psql. A PostgreSQL client must already be installed and configured to connect to the server.
psql "postgresql://<username>:<password>@<database-host>:<port>/analytics?sslmode=verify-full&sslrootcert=/path/to/ca-certificate.crt" \
-f star_schema.sql
With pgAdmin, open the Query Tool, paste each DDL statement, and execute it. Create a separate analytics database before applying the schema definitions so that analytical tables remain isolated from application databases hosted on the same PostgreSQL environment.
Table Partitioning and Schema Interaction
Range partitioning by date_key works with both star and snowflake models and is commonly used for fact tables that exceed 50 million rows. The fact table can be partitioned by year or quarter with PARTITION BY RANGE:
CREATE TABLE fact_order_items (
order_item_key BIGINT GENERATED BY DEFAULT AS IDENTITY,
order_id INTEGER NOT NULL,
date_key INTEGER NOT NULL,
product_key INTEGER NOT NULL,
customer_key INTEGER NOT NULL,
quantity INTEGER NOT NULL,
unit_price NUMERIC(10,2) NOT NULL,
discount NUMERIC(5,2) NOT NULL DEFAULT 0,
revenue NUMERIC(12,2) NOT NULL
) PARTITION BY RANGE (date_key);
CREATE TABLE fact_order_items_2024
PARTITION OF fact_order_items
FOR VALUES FROM (20240101) TO (20250101);
CREATE TABLE fact_order_items_2025
PARTITION OF fact_order_items
FOR VALUES FROM (20250101) TO (20260101);
A BRIN index on date_key inside each partition can reduce index size even further because every partition receives its own index and contains a narrower date range than the complete table. Partitioning benefits star and snowflake designs equally because both use the same fact-table structure. Dimension tables are not partitioned in this example.
PostgreSQL supports declarative partitioning directly. On managed PostgreSQL services that permit standard table creation, partitions can be created through psql or pgAdmin using an account with sufficient database privileges.
Connection Pooling Considerations with PgBouncer
PgBouncer can be used as a PostgreSQL connection pooler. For analytical connections that depend on session-level settings, session pooling is preferable to transaction pooling.
Transaction pooling releases a connection back to the pool after each transaction, which can conflict with session-specific settings such as SET work_mem and with prepared-statement behavior used by certain BI applications. A dedicated connection pool for analytical traffic can instead use session mode with a pool size selected according to the expected number of concurrent analytical queries.
Normalization vs. Denormalization: The Core Trade-Off
Neither design is universally superior. The best choice depends on how frequently dimension values change, how many fact tables use the same dimensions, and how reliably the ETL pipeline can produce the desired structure at load time.
When Denormalization Benefits Analytics Workloads
Denormalization decreases the number of tables that must participate in an analytical query. For read-heavy OLAP environments where information is loaded in hourly or nightly batches and rarely changed in place, repeating dimension attribute values is often a reasonable cost in exchange for simpler queries.
A flat dim_products table can also work better with BI software that has limited SQL optimization capabilities. Some spreadsheet-based integrations and embedded analytics tools create simple aggregation queries and cannot automatically navigate multi-table hierarchies without additional configuration.
When Normalization Reduces Long-Term Storage and Update Costs
Normalization is particularly useful when dimension attributes have low cardinality but appear repeatedly. For example, if dim_products contains 100,000 records while category_name has only five possible values, storing each full category string 100,000 times is less efficient than keeping five records in product_category and using integer foreign keys.
The update advantage is also important under SCD Type 1 behavior, where new attribute values overwrite existing ones. Updating one row in product_category is less expensive and less error-prone than modifying 10,000 product records containing the same category.
Hybrid Approaches with Partially Normalized Dimensions
A hybrid schema can normalize dimension properties that change regularly or need to be reused while leaving stable attributes in flat dimensions. For example, product_category and product_subcategory can be separated because they may occasionally change and can be shared by several fact tables. Customer address fields can remain directly in dim_customers because individual addresses are mostly unique and may rarely be used as grouped analytical hierarchies.
This mixed model is frequently used in production PostgreSQL warehouses where individual dimensions have different update rates and operational requirements.
Choosing Between Star and Snowflake Schemas: A Decision Framework
Use the following criteria when beginning a new analytical project or determining whether an existing dimensional design is creating unnecessary operational complexity.
The decision can be summarized as follows: when frequently executed BI queries traverse a dimension hierarchy against a fact table larger than 50 million records, a star schema will often show measurable performance benefits compared with an equivalent snowflake schema on the same PostgreSQL environment. Below approximately 10 million rows, differences are more commonly dominated by indexing and work_mem configuration than by the dimensional structure itself.
Decision Criteria Checklist
- Dimension update frequency: When dimension properties change more often than monthly, a snowflake structure can reduce update anomalies.
- Fact-table size: Below 10 million records, differences in query performance are often minor with appropriate indexing. Beyond 50 million rows, benchmark both models with representative queries before committing to either structure.
- BI-generated SQL: When a BI platform automatically generates SQL, confirm that it handles multi-table hierarchies efficiently. Star schemas are generally less risky for tools with limited query optimization.
- Shared dimensions: When identical lookup information is needed by several fact tables, normalized shared dimensions reduce the risk of attribute values diverging.
- ETL pipeline maturity: Denormalization depends on a reliable ETL process. If the loading pipeline is still developing, a snowflake model can be used initially and denormalized later after the process becomes stable.
- Team SQL experience: Snowflake designs require more complicated joins. When analysts have less experience with multi-table SQL, a star schema can reduce query-writing complexity.
Signals That Suggest Migrating from One Schema to the Other
Consider moving from star to snowflake when dimension-table updates become costly because many rows repeat the same attributes, or when several fact tables need shared dimensional information and synchronization is becoming unreliable.
Consider moving from snowflake to star when EXPLAIN ANALYZE repeatedly identifies join processing as a major bottleneck for frequently used BI reports and the ETL pipeline can reliably flatten dimensions before loading.
Migrating Between Schemas Without Downtime
A view-swap approach can be used when migrating from a snowflake structure to a star structure without taking the database offline or stopping read activity. The operational process is:
- Create a denormalized view over the snowflake dimension hierarchy.
- Confirm that the view returns the same expected row counts and values used by existing queries.
- Create a new physical star-schema table using column names and types that match the view.
- Backfill the new table from the view inside a transaction.
- Within one transaction, rename the original tables and replace them with the new star-schema table.
- Remove the normalized lookup tables after verifying that every query is using the new structure correctly.
The view provides a stable contract between the old and new structures. Queries can continue using the view during migration, while the final physical replacement can occur through a DDL-level table swap.
Frequently Asked Questions
Is a Star Schema Always Faster Than a Snowflake Schema in PostgreSQL?
No. Performance depends on factors including join count, table size, index coverage, and work_mem. For an equivalent fact table, a star schema requires fewer hash joins than a snowflake design and therefore often performs better for aggregations that traverse dimension hierarchies. At small data volumes, however, the difference may be insignificant. PostgreSQL selects hash-join orders according to statistics maintained by ANALYZE. When these statistics become stale, the resulting performance loss can exceed the effect of the schema itself, which means running ANALYZE after bulk loads can matter more than schema selection at smaller scales.
Does PostgreSQL Handle Snowflake Schema Joins Efficiently at Scale?
At large row counts, insufficient work_mem can cause intermediate hash tables to spill to disk. Because a snowflake query usually includes more joins, it has more opportunities for this to occur and can develop a larger performance gap relative to a star-schema equivalent. Increasing work_mem to approximately 128-256 MB can reduce disk spilling where enough total memory is available, although the actual improvement depends on fact-table size, concurrency, and join count. Run ANALYZE whenever new dimension data is loaded. Outdated statistics can cause PostgreSQL to choose an inefficient join order, which has a greater effect on snowflake queries because there are more joins whose ordering can influence performance.
Can Star and Snowflake Patterns Be Used in the Same PostgreSQL Database?
Yes. Separate subject areas inside one database can use different dimensional models. A sales area could use a star design with a flat dim_products table, while an inventory area uses a normalized snowflake model with product_subcategory and product_category. A normalized product_category table can also serve as a shared dimension by introducing a foreign key from a partially denormalized product table, resulting in a hybrid design. This is useful when subject areas have different query patterns and update requirements.
How Does Storage Usage Differ Between Star and Snowflake Schemas on PostgreSQL?
As an example, a dim_products table containing 20,000 records and two repeated VARCHAR(100) attributes consumes approximately 4 MB for those columns. Normalizing the same values into a five-row product_category table and a 2,800-row product_subcategory table can reduce the requirement to below 50 KB. Since both models store integer foreign keys in the fact table, the difference is concentrated in dimensions rather than fact_order_items. In many analytical schemas, normalizing dimensions changes total database storage by less than 1% when the fact table is properly designed. Savings become more substantial when dimension tables exceed roughly 500,000 records and contain several repeated low-cardinality attributes.
Which PostgreSQL Index Types Work Best for Star-Schema Fact Tables?
BRIN indexes are effective for date_key fields in append-only fact tables where values correlate with insertion order. A BRIN index can be dramatically smaller than a B-tree index on the same column and requires less maintenance during large inserts. B-tree indexes on foreign keys such as product_key and customer_key are useful when queries selectively filter those columns and the optimizer chooses nested-loop or merge joins. Covering indexes using INCLUDE can also benefit queries that aggregate revenue by date_key and product_key. When every required value exists in the index, PostgreSQL can avoid accessing the heap and reduce I/O for large fact tables.
How Can a Snowflake Schema Be Migrated to a Star Schema in PostgreSQL Without Downtime?
Use the following sequence:
- Create a flattened view of the normalized snowflake dimension.
- Validate the new view against existing queries.
- Create the physical table for the new star dimension.
- Populate the new table using data from the view.
- Rename the previous table and replace it with the new one inside a single transaction.
- Delete the normalized tables only after confirming that all queries operate correctly against the new design.
For the first step, create a flattened product-dimension view:
CREATE VIEW v_dim_products_flat AS
SELECT dp.product_key, dp.product_id, dp.product_name,
ps.subcategory_name, pc.category_name
FROM dim_products dp
JOIN product_subcategory ps ON dp.subcategory_key = ps.subcategory_key
JOIN product_category pc ON ps.category_key = pc.category_key;
For the third step, create the physical star-schema dimension table:
CREATE TABLE dim_products_star (
product_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
product_id INTEGER NOT NULL UNIQUE,
product_name VARCHAR(255) NOT NULL,
subcategory_name VARCHAR(100) NOT NULL,
category_name VARCHAR(100) NOT NULL
);
For the fourth step, populate the new table from the view:
INSERT INTO dim_products_star
SELECT product_key, product_id, product_name, subcategory_name, category_name
FROM v_dim_products_flat;
After the backfill is complete, compare the row count in v_dim_products_flat with the count in dim_products_star before performing the table replacement in the fifth step.
Do Managed PostgreSQL Services Support the Configuration Changes Needed for Analytics Workloads?
Many managed PostgreSQL platforms allow selected PostgreSQL parameters to be changed through a configuration interface or management API. Commonly available settings include:
work_mem: determines the amount of memory available to individual sorting or hashing operations. Increasing it can reduce disk spills in snowflake queries containing multiple joins.max_parallel_workers_per_gather: controls the number of parallel workers available to supported scans and aggregations. Increasing the value from 2 to 4 or 8 on sufficiently large database servers can reduce scan times for large fact tables.enable_hashjoin: normally enabled by default and determines whether PostgreSQL can select hash-join execution plans.
Some managed PostgreSQL environments restrict parameters such as shared_buffers because they are automatically selected according to server size, while other settings require elevated privileges. The maximum number of connections can also be subject to limits imposed by the available database resources or service plan.
What Is the Difference Between a Snowflake Schema and Third Normal Form in a Data Warehouse?
Third normal form, or 3NF, is a normalization standard commonly applied to transactional systems to eliminate update anomalies. A snowflake schema uses the same general normalization principle, but applies it selectively to analytical dimension tables. In a strict 3NF transactional model, each non-key field must depend only on the primary key of its table.
A snowflake analytical model does not place the entire warehouse into 3NF. The fact table deliberately stores additive measures such as revenue and quantity together with several foreign keys because those measures depend on combinations of dimensions rather than on one individual key. Dimension hierarchies may be normalized, but the complete dimensional model is not a strict 3NF schema. Snowflaking is therefore intended to normalize selected attribute hierarchies where update anomalies or repeated storage justify the added structure, rather than to fully normalize the complete analytical database.
Conclusion
Star and snowflake schemas are better understood as different positions on a trade-off between straightforward analytical queries and stronger normalization of the data model rather than as competing standards. This tutorial covered both approaches from table definitions through EXPLAIN ANALYZE results using approximately 2.7 million fact records. It also demonstrated how the additional hash joins required by a snowflake design affect query-plan structure, how indexing and configuration can address performance concerns, and which workload characteristics favor one design over the other.
Using these DDL examples together with the decision framework makes it possible to prepare a PostgreSQL analytics environment, apply the model that matches the workload, and configure work_mem, connection pooling, and indexes for analytical query performance. The comparison table and checklist provide a repeatable way to reevaluate the schema as data volume and organizational requirements change.
As a practical next step, execute the EXPLAIN ANALYZE examples from this tutorial against both schema designs and then test two additional representative reports: one aggregation using a single dimension and one filtered GROUP BY involving several dimensions together with a date-range condition. Compare both planning and execution times for each pair. Those results will show whether schema design is the primary performance factor for the workload or whether index quality and work_mem configuration have a greater influence. A natural-language query layer can also be placed above a PostgreSQL analytics schema to generate text-to-SQL queries for connected databases, allowing analysts to query either dimensional model without writing SQL manually.
