load

#!/bin/bash
# Drop+create the test DB, install pg_deltax, set up the partitioned hits table,
# enable compression, and direct-backfill load the 100 hits_*.parquet files in
# a single COPY ... WITH (FORMAT deltax_compress).
set -eu

PARQUET_DIR=/tmp/hits_parquet

# Move parquet files into a path the postgres server user can read.
sudo rm -rf "$PARQUET_DIR"
sudo mkdir -p "$PARQUET_DIR"
sudo mv hits_*.parquet "$PARQUET_DIR/"
sudo chmod 644 "$PARQUET_DIR"/*.parquet

# Recreate the DB so this script is idempotent. DROP wipes any prior
# ALTER DATABASE settings so we start from postgresql.conf defaults
# (work_mem=64MB, set by ./install in conf.d/clickbench.conf).
sudo -u postgres psql -v ON_ERROR_STOP=1 -t -c "DROP DATABASE IF EXISTS test"
sudo -u postgres psql -v ON_ERROR_STOP=1 -t -c "CREATE DATABASE test"

# Bump work_mem for the load only. Direct backfill sorts each segment before
# compressing; 1GB keeps larger segments in memory and shaves load time.
# Reset before the query phase so the concurrent-QPS test (10 connections
# each spawning parallel-scan workers) doesn't multiply this up to OOM.
sudo -u postgres psql -v ON_ERROR_STOP=1 -t -c "ALTER DATABASE test SET work_mem TO '1GB'"

sudo -u postgres psql -v ON_ERROR_STOP=1 -t test -c "CREATE EXTENSION pg_deltax"

# Schema + partitioning + compression setup. mock_now pins the partition
# boundary calculation to the dataset's epoch (the hits data is from 2013).
sudo -u postgres psql -v ON_ERROR_STOP=1 -t test < create.sql
sudo -u postgres psql -v ON_ERROR_STOP=1 -t test -c "SET pg_deltax.mock_now = '2013-07-01 12:00:00'; SELECT deltax.deltax_create_table('hits', 'eventtime', '3 days'::interval, 15)"
sudo -u postgres psql -v ON_ERROR_STOP=1 -t test -c "SELECT deltax.deltax_enable_compression('hits', order_by => ARRAY['counterid', 'userid', 'eventtime'], segment_size => 30000)"

# Direct backfill: load and compress in a single pass using FORMAT deltax_compress.
sudo -u postgres psql -v ON_ERROR_STOP=1 test -c "COPY hits FROM '$PARQUET_DIR/hits_*.parquet' WITH (FORMAT deltax_compress)"

sudo -u postgres psql -v ON_ERROR_STOP=1 -t test -c "VACUUM FREEZE ANALYZE hits"

# Restore default work_mem (64MB) and disable JIT for the query phase.
sudo -u postgres psql -v ON_ERROR_STOP=1 -t -c "ALTER DATABASE test RESET work_mem"
sudo -u postgres psql -v ON_ERROR_STOP=1 -t -c "ALTER DATABASE test SET jit TO off"

# Free disk: the source parquet files are no longer needed.
sudo rm -rf "$PARQUET_DIR"
sync