dbt2

Overview

Package/source version 0.61.7; SQL extension version 0.45.0. This package contains the PostgreSQL stored-function extension, not the full DBT-2 benchmark toolchain.

Version

Build

You can build the DEB packages for dbt2 using pig build:

pig build pkg dbt2         # build DEB packages

Install

You can install dbt2 directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install dbt2;          # Install for current active PG version

pig ext install -y dbt2 -v 18  # PG 18
pig ext install -y dbt2 -v 17  # PG 17
pig ext install -y dbt2 -v 16  # PG 16
pig ext install -y dbt2 -v 15  # PG 15
pig ext install -y dbt2 -v 14  # PG 14

dnf install -y dbt2-pg18-extensions       # PG 18
dnf install -y dbt2-pg17-extensions       # PG 17
dnf install -y dbt2-pg16-extensions       # PG 16
dnf install -y dbt2-pg15-extensions       # PG 15
dnf install -y dbt2-pg14-extensions       # PG 14

apt install -y postgresql-18-dbt2   # PG 18
apt install -y postgresql-17-dbt2   # PG 17
apt install -y postgresql-16-dbt2   # PG 16
apt install -y postgresql-15-dbt2   # PG 15
apt install -y postgresql-14-dbt2   # PG 14

Create Extension:

CREATE EXTENSION dbt2;

Usage

dbt2: OSDL-DBT-2 test kit

dbt2 is a TPC-C benchmark implementation for PostgreSQL. The extension provides stored procedures that implement the five standard TPC-C transaction types.

CREATE EXTENSION dbt2;

TPC-C Transaction Types

The extension provides stored procedures for the five standard TPC-C transactions:

• New Order: Creates a new order with multiple line items, updating stock levels
• Payment: Processes a customer payment, updating warehouse and district balances
• Order Status: Retrieves the status of a customer’s most recent order
• Delivery: Processes pending orders for delivery across all districts
• Stock Level: Checks the count of recently sold items with low stock

Benchmark Workflow

The dbt2 system consists of:

1. Database extension (dbt2): Stored procedures for TPC-C transactions
2. Data loader: Populates the benchmark tables with TPC-C data
3. Driver: Generates transaction workloads simulating terminal users
4. Client: Manages connections between the driver and database

Running Benchmarks

The benchmark is typically run using the dbt2 command-line tools (separate from the extension):

# Build the benchmark database
dbt2 build --dbms pgsql --warehouses 10

# Run the benchmark
dbt2 run --dbms pgsql --warehouses 10 --duration 300 --connections 10

# Generate report
dbt2 report --dbms pgsql

TPC-C Schema

The benchmark uses these standard tables: warehouse, district, customer, history, new_order, orders, order_line, item, and stock.

Refer to the doc/ directory in the repository for detailed configuration and tuning options.