Schema and SQL statements

Our starting point is the schema and the SQL statements we want to eventually emulate.

DDL

For this tutorial, the DDL is very simple, just 2 tables.

-- file: bank.ddl

CREATE TABLE ref_data (
    acc_no BIGINT PRIMARY KEY,
    external_ref_id UUID,
    created_time TIMESTAMPTZ,
    acc_details VARCHAR
);

CREATE TABLE orders (
    acc_no BIGINT NOT NULL,
    id UUID NOT NULL default gen_random_uuid(),
    status VARCHAR NOT NULL,
    amount DECIMAL(15, 2),
    ts TIMESTAMPTZ default now(),
    CONSTRAINT pk PRIMARY KEY (acc_no, id)
);

Note

PostgreSQL and CockroachDB share the same SQL syntax as CockroachDB has adopted the postgres wire API protocol. This means the above CREATE TABLE statements work for both technologies 🚀

Save the DDL to a file bank.ddl.

Statements

This below is the list of statements that constitute our workload. Bind parameters, denoted with %s, will vary, and we are expected to simulate them.

-- file: bank.sql

-- read operation, executed 50% of the time
SELECT * FROM orders WHERE acc_no = %s AND id = %s;

-- below 2 transactions constitute a complete order execution

-- new_order
INSERT INTO orders (acc_no, status, amount) VALUES (%s, 'Pending', %s) RETURNING id;

-- execute order - this is an explicit transaction
SELECT * FROM ref_data WHERE acc_no = %s;
UPDATE orders SET status = 'Complete' WHERE (acc_no, id) = (%s, %s);

Save this to file bank.sql

A quick note

For this exercise, we will not spend too much time caring about what the statements do. The statements in this example will not make much sense, so just focus on dbworkload 🙂