hot_test.sql

application/octet-stream

Download

-- ================================================================
-- COMPREHENSIVE HOT UPDATE TEST WORKLOAD
-- Run with pgbench: pgbench -f hot_test.sql -T <seconds> <database>
-- ================================================================

\set iterations 1000
\set phase1_btree_id random(1, 1000000)
\set phase1_hash_id random(1, 1000000)
\set phase1_gin_id random(1, 1000000)
\set phase1_brin_id random(1, 1000000)

\set phase2_expr_id random(1, 500000)
\set phase2_partial_id random(1, 500000)
\set phase2_expr_partial_id random(1, 500000)

\set phase3_light_id random(1, 1000000)
\set phase3_heavy_id random(1, 500000)
\set phase3_gin_id random(1, 500000)

\set phase4_wide_id random(1, 500000)
\set phase4_narrow_id random(1, 2000000)

\set phase5_churn_id random(1, 1000000)
\set phase5_selective_id random(1, 500000)

\set phase6_ecom_id random(1, 1000000)
\set phase6_ts_id random(1, 2000000)

\set workload_type random(1, 100)

-- ================================================================
-- PHASE 1: INDEX TYPE FUNDAMENTALS - Random Updates
-- ================================================================

\if :workload_type <= 10
UPDATE phase1_btree_simple
SET status = CASE
        WHEN status = 'active' THEN 'ACTIVE'
        WHEN status = 'ACTIVE' THEN 'active'
        ELSE status
    END,
    counter = counter + 1
WHERE id = :phase1_btree_id;
\endif

\if :workload_type = 11
UPDATE phase1_btree_simple
SET user_id = user_id + 1,
    counter = counter + 1
WHERE id = :phase1_btree_id;
\endif

\if :workload_type = 12
UPDATE phase1_hash_simple
SET quantity = quantity + 1,
    notes = notes || '.',
    counter = counter + 1
WHERE id = :phase1_hash_id;
\endif

\if :workload_type = 13
UPDATE phase1_hash_simple
SET product_code = product_code || '_updated',
    counter = counter + 1
WHERE id = :phase1_hash_id;
\endif

\if :workload_type = 14
UPDATE phase1_gin_simple
SET description = description || 'x',
    counter = counter + 1
WHERE id = :phase1_gin_id;
\endif

\if :workload_type = 15
UPDATE phase1_gin_simple
SET config = jsonb_set(
        config,
        '{setting2}',
        to_jsonb((config->>'setting2')::int + 1)
    ),
    counter = counter + 1
WHERE id = :phase1_gin_id;
\endif

\if :workload_type = 16
UPDATE phase1_brin_simple
SET measurement = measurement + 0.1,
    raw_data = raw_data || 'x',
    counter = counter + 1
WHERE id = :phase1_brin_id;
\endif

-- ================================================================
-- PHASE 2: INDEX FEATURES - Expression + Partial
-- ================================================================

\if :workload_type = 17
UPDATE phase2_expr_btree
SET email = CASE
        WHEN email = lower(email) THEN upper(email)
        ELSE lower(email)
    END,
    counter = counter + 1
WHERE id = :phase2_expr_id;
\endif

\if :workload_type = 18
UPDATE phase2_expr_btree
SET status = CASE WHEN status = 'active' THEN 'inactive' ELSE 'active' END,
    counter = counter + 1
WHERE id = :phase2_expr_id;
\endif

\if :workload_type = 19
UPDATE phase2_partial_btree
SET value = value + 1.0,
    counter = counter + 1
WHERE id = :phase2_partial_id
  AND status = 'active'
  AND priority > 5;
\endif

\if :workload_type = 20
UPDATE phase2_partial_btree
SET status = CASE WHEN status = 'active' THEN 'inactive' ELSE 'active' END,
    counter = counter + 1
WHERE id = :phase2_partial_id;
\endif

\if :workload_type = 21
UPDATE phase2_expr_partial_combined
SET last_login = last_login + interval '1 day',
    counter = counter + 1
WHERE id = :phase2_expr_partial_id
  AND account_status = 'active';
\endif

-- ================================================================
-- PHASE 3: MULTI-INDEX COMPLEXITY
-- ================================================================

\if :workload_type = 22
UPDATE phase3_multi_index_light
SET notes = notes || '.',
    counter = counter + 1
WHERE id = :phase3_light_id;
\endif

\if :workload_type = 23
UPDATE phase3_multi_index_light
SET status = CASE (counter % 4)
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'processing'
        WHEN 2 THEN 'complete'
        ELSE 'failed'
    END,
    counter = counter + 1
WHERE id = :phase3_light_id;
\endif

\if :workload_type = 24
UPDATE phase3_multi_index_heavy
SET counter = counter + 1
WHERE id = :phase3_heavy_id;
\endif

\if :workload_type = 25
UPDATE phase3_multi_index_heavy
SET email = CASE
        WHEN email = lower(email) THEN upper(substring(email, 1, 1)) || substring(email, 2)
        ELSE lower(email)
    END,
    counter = counter + 1
WHERE id = :phase3_heavy_id;
\endif

\if :workload_type = 26
UPDATE phase3_multi_index_heavy
SET metadata = jsonb_set(
        metadata,
        '{version}',
        to_jsonb(((metadata->>'version')::int) + 1)
    ),
    counter = counter + 1
WHERE id = :phase3_heavy_id;
\endif

\if :workload_type = 27
UPDATE phase3_jsonb_gin_mixed
SET config = jsonb_set(config, '{option1}', to_jsonb((config->>'option1')::int + 1)),
    settings = jsonb_set(settings, '{timeout}', to_jsonb(30 + random()::int * 60)),
    counter = counter + 1
WHERE id = :phase3_gin_id;
\endif

-- ================================================================
-- PHASE 4: BULK OPERATIONS
-- ================================================================

\if :workload_type = 28
UPDATE phase4_bulk_wide_table
SET col1 = col1 || '_u',
    col6 = col6 + 1,
    col11 = col11 + 10,
    counter = counter + 1
WHERE id = :phase4_wide_id;
\endif

\if :workload_type = 29
UPDATE phase4_bulk_narrow_table
SET value = value + 0.5,
    counter = counter + 1
WHERE indexed_id = (:phase4_narrow_id % 100000);
\endif

\if :workload_type = 30
DELETE FROM phase4_bulk_wide_table
WHERE id = :phase4_wide_id;

INSERT INTO phase4_bulk_wide_table (id, col1, col6, col11, indexed_col)
VALUES (:phase4_wide_id, 'new', 0, 100.00, 'key_' || (:phase4_wide_id % 10000));
\endif

-- ================================================================
-- PHASE 5: VACUUM STRESS TESTING
-- ================================================================

\if :workload_type = 31
UPDATE phase5_vacuum_stress_high_churn
SET event_data = jsonb_set(
        event_data,
        '{timestamp}',
        to_jsonb(now()::text)
    ),
    counter = counter + 1
WHERE id = :phase5_churn_id;

DELETE FROM phase5_vacuum_stress_high_churn
WHERE id = :phase5_churn_id + 1000 AND id % 10 = 0;
\endif

\if :workload_type = 32
UPDATE phase5_vacuum_stress_selective
SET attempts = attempts + 1,
    last_attempt = now(),
    counter = counter + 1
WHERE category = 'hot'
  AND id = :phase5_selective_id;
\endif

-- ================================================================
-- PHASE 6: REALISTIC WORKLOADS
-- ================================================================

\if :workload_type = 33
UPDATE phase6_realistic_ecommerce
SET status = CASE status
        WHEN 'pending' THEN 'processing'
        WHEN 'processing' THEN 'shipped'
        WHEN 'shipped' THEN 'delivered'
        ELSE status
    END,
    updated_at = now(),
    counter = counter + 1
WHERE id = :phase6_ecom_id;
\endif

\if :workload_type = 34
UPDATE phase6_realistic_ecommerce
SET order_metadata = jsonb_set(
        order_metadata,
        '{updated_at}',
        to_jsonb(now()::text)
    ),
    counter = counter + 1
WHERE id = :phase6_ecom_id;
\endif

\if :workload_type = 35
DELETE FROM phase6_realistic_ecommerce
WHERE created_at < '2024-03-01'::timestamp
  AND status = 'delivered'
  AND id % 1000 = 0;
\endif

\if :workload_type = 36
UPDATE phase6_realistic_timeseries
SET derived_values = jsonb_set(
        derived_values,
        '{max}',
        to_jsonb(value + (random() * 5)::numeric(10,2))
    ),
    counter = counter + 1
WHERE sensor_id = (:phase6_ts_id % 1000)
  AND measurement_time > now() - interval '1 day';
\endif

\if :workload_type = 37
UPDATE phase6_realistic_timeseries
SET quality_flags = CASE WHEN random() < 0.05 THEN 1 ELSE 0 END,
    counter = counter + 1
WHERE id = :phase6_ts_id;
\endif