Skip to content

Latest commit

 

History

2 Commits

Folders and files

NameName
Last commit message
Last commit date
 
 
 
 
 
 
 
 

Repository files navigation

PostgreSQL Mini Data Warehouse with Query Optimization

Project Overview

This project demonstrates the design and optimization of a small-scale data warehouse using PostgreSQL and Python.

It includes:

  • Star schema data modeling (Fact + Dimensions)
  • Python-based ETL pipeline (Faker + Pandas + bulk inserts)
  • 300k+ transactional records
  • Query performance benchmarking using EXPLAIN ANALYZE
  • Indexing strategy evaluation
  • Join strategy analysis (Hash Join vs Nested Loop)

Architecture

Data Model: Star Schema

  • fact_sales → transactional data (quantity, total_amount)
  • dim_customer → customer attributes
  • dim_product → product attributes
  • dim_date → time dimension (month, year, quarter precomputed)
erDiagram
    DIM_CUSTOMERS {
        int customer_id PK
        string name
        string city
        string segment
    }

    DIM_PRODUCTS {
        int product_id PK
        string category
        float price
    }

    DIM_DATES {
        int date_id PK
        date full_date
        int month
        int year
        int quarter
    }

    FACT_SALES {
        int sale_id PK
        int customer_id FK
        int product_id FK
        int date_id FK
        int quantity
        float total_amount
    }

    DIM_CUSTOMERS ||--o{ FACT_SALES : has
    DIM_PRODUCTS  ||--o{ FACT_SALES : has
    DIM_DATES     ||--o{ FACT_SALES : has
Loading

Why star schema?

  • Optimized for analytics queries
  • Reduced redundancy
  • Improved aggregation performance
  • Clear separation of facts and dimensions

ETL Pipeline

  • Data generated using Faker
  • 10k customers
  • 1k products
  • 365 dates
  • 300k sales records
  • Bulk insertion using psycopg2 execute_values
  • Batched inserts (10k per batch)

Total load time: < 1 minute


Performance Experiments

Baseline Query (No Index)

Query: Revenue for Q1 2023

Result:

  • Parallel Sequential Scan
  • Execution Time: ~33 ms

Observation: Full table scan performed due to absence of index on date_id.


Index on date_id

CREATE INDEX idx_sales_date ON fact_sales(date_id);

Query: Revenue for first week of Jan 2023

Result:

  • Bitmap Index Scan
  • Execution Time: ~3.9 ms
  • ~10x improvement

Key Insight: Indexes significantly improve performance for selective filters.


Join Strategy Analysis

Query: Total revenue from customers in selected city.

Observed:

  • Hash Join when no index on fact_sales(customer_id)
  • Parallel Seq Scan on fact table

Explanation: Planner selected hash join due to lack of supporting index and moderate dataset size.


Key Learnings

  • Indexes do not always improve performance (depends on selectivity)
  • PostgreSQL uses cost-based query planner
  • Parallel sequential scan can outperform index scan on small datasets
  • Nested Loop joins become efficient when outer relation is small and inner relation is indexed
  • Star schema improves clarity and scalability of analytics systems

Tech Stack

  • PostgreSQL (Dockerized)
  • Python
  • Pandas
  • Faker
  • psycopg2

Screenshots

Screenshot from 2026-02-16 23-03-04 Screenshot from 2026-02-16 23-04-30 Screenshot from 2026-02-16 23-00-51 image

About

This project demonstrates the design and optimization of a small-scale data warehouse using PostgreSQL and Python.

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages