TPC-H PostgreSQL Data Manipulation & Partitioning

A hands-on lab project exploring TPC-H benchmark data in PostgreSQL, covering setup, data generation, schema creation, and multiple table partitioning strategies (list, range, hash, multi-column, and composite), run on Ubuntu inside Oracle VirtualBox.

What is TPC-H?

TPC-H is a standard decision-support benchmark developed by the Transaction Processing Performance Council (TPC). It simulates a wholesale business environment using a set of relational tables (customers, orders, suppliers, parts, etc.) generated synthetically with the dbgen tool. Dataset size is controlled by a scale factor, allowing performance testing at different data volumes. It's widely used to evaluate database systems under analytical (OLAP) workloads — complex SQL queries involving joins, aggregations, and filters.

Environment

• OS: Ubuntu (running in Oracle VirtualBox)
• Database: PostgreSQL
• Data generator: TPC-H dbgen

Project Steps

1. PostgreSQL Setup

• Installed PostgreSQL and postgresql-contrib on Ubuntu
• Verified the service was running via systemctl
• Created a dedicated role/user (tpchuser) with database creation privileges
• Created a database (tpch_web) owned by that user

2. TPC-H Data Generation

• Compiled dbgen from source using gcc/make
• Configured makefile.suite for the target database and platform
• Generated TPC-H flat files at scale factor 1 and 5 (customer.tbl, orders.tbl, lineitem.tbl, part.tbl, partsupp.tbl, supplier.tbl, region.tbl, nation.tbl)
• Cleaned trailing pipe delimiters from generated files before loading

3. Schema Creation & Data Loading

• Created the 8 core TPC-H tables (Region, Nation, Part, Supplier, Customer, PartSupp, Orders, LineItem) with primary/foreign key constraints — see sql/01_create_tables.sql
• Loaded the generated .tbl files into PostgreSQL using \copy

4. Table Partitioning

Backed up the original tables, then recreated and partitioned them using four different strategies:

Strategy	Table	File
List	region, split by continent group	sql/02_partition_by_list.sql
Range	orders, split by order date ranges	sql/03_partition_by_range.sql
Hash	customer, split into 4 hash buckets on customer key	sql/04_partition_by_hash.sql
Multi-column range	orders, split on date + shippriority + totalprice	sql/05_partition_multi_column.sql
Composite (range + list)	orders, range by date, then sub-partitioned by list on shippriority	sql/06_partition_composite.sql

5. Query Benchmarking

Ran the standard TPC-H query set (query1_pg.sql ... query22_pg.sql) against the database and timed each one using a shell loop — see scripts/benchmark_queries.sh.

Key Takeaways

• Partitioning strategy should match query patterns: range partitioning suits time-series filters (e.g. queries filtering by o_orderdate), while hash partitioning is useful for evenly distributing load when there's no natural range key.
• Composite partitioning (range + list) allows combining coarse time-based partitioning with finer-grained categorical splits, which can help highly selective queries prune more partitions.
• Removing trailing delimiters and correctly matching column types/order between .tbl files and table DDL is critical before bulk loading with \copy.

Repository Structure

.
├── README.md
├── docs/
│   └── report.docx            # Original full lab report
├── sql/
│   ├── 01_create_tables.sql
│   ├── 02_partition_by_list.sql
│   ├── 03_partition_by_range.sql
│   ├── 04_partition_by_hash.sql
│   ├── 05_partition_multi_column.sql
│   └── 06_partition_composite.sql
└── scripts/
    └── benchmark_queries.sh

Author

Dalache Aya