mirror of
https://github.com/dredx/prole.git
synced 2026-09-23 11:03:59 +00:00
66 lines
3.1 KiB
SQL
66 lines
3.1 KiB
SQL
-- 02_generate_data.sql
|
||
-- Populates demo.ci_job_artifacts_legacy with ~5 million rows (~1 GB of data).
|
||
-- Also seeds demo.ci_build_status for the OLTP workload.
|
||
--
|
||
-- Runtime estimate: 3–6 minutes on the CNPG cluster.
|
||
-- Scale: represents 1 GB out of the real 10 TB (1:10000 ratio).
|
||
--
|
||
-- Data distribution:
|
||
-- - Rows span the last 180 days (6 months of history)
|
||
-- - 70% of expire_at values are in the past (already expired)
|
||
-- - 5% of rows are locked (should be skipped by cleanup)
|
||
-- - project_id values spread across 50,000 "projects"
|
||
-- - job_id values spread across 1,000,000 "jobs"
|
||
|
||
\echo 'Generating 5,000,000 rows in demo.ci_job_artifacts_legacy...'
|
||
\echo 'This will take 3-6 minutes. Watch progress with: SELECT count(*) FROM demo.ci_job_artifacts_legacy;'
|
||
|
||
INSERT INTO demo.ci_job_artifacts_legacy
|
||
(job_id, project_id, file_type, size_bytes, file_store,
|
||
checksum, created_at, expire_at, locked)
|
||
SELECT
|
||
(random() * 999999 + 1)::BIGINT AS job_id,
|
||
(random() * 49999 + 1)::BIGINT AS project_id,
|
||
(random() * 9)::SMALLINT AS file_type,
|
||
(random() * 104857600)::BIGINT AS size_bytes, -- up to 100MB per artifact
|
||
1 AS file_store,
|
||
encode(sha256((random()::TEXT || i::TEXT)::BYTEA), 'hex') AS checksum,
|
||
NOW() - (random() * INTERVAL '180 days') AS created_at,
|
||
CASE
|
||
WHEN random() < 0.70
|
||
THEN NOW() - (random() * INTERVAL '90 days') -- 70% already expired
|
||
ELSE NOW() + (random() * INTERVAL '30 days') -- 30% not yet expired
|
||
END AS expire_at,
|
||
random() < 0.05 AS locked -- 5% locked
|
||
FROM generate_series(1, 5000000) AS gs(i);
|
||
|
||
\echo 'Legacy table populated. Running ANALYZE...'
|
||
ANALYZE demo.ci_job_artifacts_legacy;
|
||
|
||
-- Seed the OLTP workload table
|
||
\echo 'Seeding OLTP workload table (100,000 rows)...'
|
||
INSERT INTO demo.ci_build_status (job_id, status)
|
||
SELECT
|
||
(random() * 999999 + 1)::BIGINT,
|
||
CASE (random() * 2)::INT
|
||
WHEN 0 THEN 'pending'
|
||
WHEN 1 THEN 'running'
|
||
ELSE 'created'
|
||
END
|
||
FROM generate_series(1, 100000);
|
||
|
||
ANALYZE demo.ci_build_status;
|
||
|
||
\echo ''
|
||
\echo 'Data generation complete. Verify with:'
|
||
\echo ' SELECT count(*), pg_size_pretty(sum(pg_column_size(t.*))) FROM demo.ci_job_artifacts_legacy t;'
|
||
|
||
SELECT
|
||
count(*) AS row_count,
|
||
pg_size_pretty(pg_total_relation_size('demo.ci_job_artifacts_legacy')) AS table_size,
|
||
min(created_at)::DATE AS earliest_row,
|
||
max(created_at)::DATE AS latest_row,
|
||
count(*) FILTER (WHERE expire_at < NOW()) AS expired_rows,
|
||
count(*) FILTER (WHERE locked = TRUE) AS locked_rows
|
||
FROM demo.ci_job_artifacts_legacy;
|