reproduce_preparation_env.sql

DROP TYPE IF EXISTS evt_kind CASCADE;
CREATE TYPE evt_kind AS ENUM (
  'k_a',
  'k_f',
  'k_g',
  'k_h',
  'k_i',
  'k_d',
  'k_c',
  'k_j',
  'k_k',
  'k_l',
  'k_b',
  'k_e'
);

DROP TABLE IF EXISTS fact_events CASCADE;

CREATE TABLE fact_events (
    pk_id bigint NOT NULL,
    grp_id bigint NOT NULL,
    ref_id bigint NOT NULL,
    ref_kind varchar(255) NOT NULL,
    metric_a bigint NOT NULL,
    ts_col timestamp without time zone NOT NULL,
    aux_txt varchar,
    metric_b bigint,
    kind_col evt_kind NOT NULL,
    metric_c bigint,
    metric_d numeric(54,18),
    metric_e numeric(54,18) DEFAULT 0,
    metric_f numeric(54,18),
    aux_ts timestamp(6) without time zone,
    PRIMARY KEY (ts_col, pk_id)
) PARTITION BY RANGE (ts_col);

CREATE TABLE fact_events_p2026w13
PARTITION OF fact_events
FOR VALUES FROM ('2026-03-23 00:00:00') TO ('2026-03-30 00:00:00');

CREATE TABLE fact_events_p2026w14
PARTITION OF fact_events
FOR VALUES FROM ('2026-03-30 00:00:00') TO ('2026-04-06 00:00:00');

CREATE INDEX fact_events_p2026w13_ts_idx
  ON fact_events_p2026w13 (ts_col);
CREATE INDEX fact_events_p2026w13_kind_idx
  ON fact_events_p2026w13 (kind_col);

CREATE INDEX fact_events_p2026w14_ts_idx
  ON fact_events_p2026w14 (ts_col);
CREATE INDEX fact_events_p2026w14_kind_idx
  ON fact_events_p2026w14 (kind_col);

SET synchronous_commit = off;
SET maintenance_work_mem = '1GB';

WITH p AS (
  SELECT
    12000000::bigint AS p13_rows,
    6000000::bigint  AS p14_rows
)
INSERT INTO fact_events
SELECT
    gs AS pk_id,
    1 + (gs % 500000) AS grp_id,
    gs AS ref_id,
    'R'::varchar(255) AS ref_kind,
    100 AS metric_a,
    '2026-03-23 00:00:00'::timestamp
      + ((gs - 1)::numeric / (SELECT p13_rows FROM p)) * interval '7 days'
      + ((gs % 10) * interval '1 microsecond') AS ts_col,
    NULL::varchar AS aux_txt,
    NULL::bigint AS metric_b,
    CASE
      WHEN gs % 100000 < 81150 THEN 'k_a'::evt_kind
      WHEN gs % 100000 < 99563 THEN 'k_f'::evt_kind
      WHEN gs % 100000 < 99793 THEN 'k_g'::evt_kind
      WHEN gs % 100000 < 99883 THEN 'k_h'::evt_kind
      WHEN gs % 100000 < 99929 THEN 'k_i'::evt_kind
      WHEN gs % 100000 < 99956 THEN 'k_d'::evt_kind
      WHEN gs % 100000 < 99966 THEN 'k_c'::evt_kind
      WHEN gs % 100000 < 99976 THEN 'k_j'::evt_kind
      WHEN gs % 100000 < 99986 THEN 'k_k'::evt_kind
      WHEN gs % 100000 < 99993 THEN 'k_l'::evt_kind
      WHEN gs % 100000 < 100000 THEN 'k_b'::evt_kind
      ELSE 'k_e'::evt_kind
    END AS kind_col,
    NULL::bigint,
    NULL::numeric(54,18),
    0::numeric(54,18),
    NULL::numeric(54,18),
    NULL::timestamp(6)
FROM generate_series(1, (SELECT p13_rows FROM p)) AS gs;

WITH p AS (
  SELECT
    12000000::bigint AS p13_rows,
    6000000::bigint  AS p14_rows
)
INSERT INTO fact_events
SELECT
    (SELECT p13_rows FROM p) + gs AS pk_id,
    1 + (gs % 500000) AS grp_id,
    (SELECT p13_rows FROM p) + gs AS ref_id,
    'R'::varchar(255) AS ref_kind,
    100 AS metric_a,
    '2026-03-30 00:00:00'::timestamp
      + ((gs - 1)::numeric / (SELECT p14_rows FROM p)) * interval '7 days'
      + ((gs % 10) * interval '1 microsecond') AS ts_col,
    NULL::varchar AS aux_txt,
    NULL::bigint AS metric_b,
    CASE
      WHEN gs % 100000 < 81680 THEN 'k_a'::evt_kind
      WHEN gs % 100000 < 99543 THEN 'k_f'::evt_kind
      WHEN gs % 100000 < 99763 THEN 'k_g'::evt_kind
      WHEN gs % 100000 < 99809 THEN 'k_h'::evt_kind
      WHEN gs % 100000 < 99879 THEN 'k_i'::evt_kind
      WHEN gs % 100000 < 99906 THEN 'k_d'::evt_kind
      WHEN gs % 100000 < 99916 THEN 'k_c'::evt_kind
      WHEN gs % 100000 < 99926 THEN 'k_j'::evt_kind
      WHEN gs % 100000 < 99936 THEN 'k_k'::evt_kind
      WHEN gs % 100000 < 99943 THEN 'k_l'::evt_kind
      WHEN gs % 100000 < 100000 THEN 'k_b'::evt_kind
      ELSE 'k_e'::evt_kind
    END AS kind_col,
    NULL::bigint,
    NULL::numeric(54,18),
    0::numeric(54,18),
    NULL::numeric(54,18),
    NULL::timestamp(6)
FROM generate_series(1, (SELECT p14_rows FROM p)) AS gs;

VACUUM (ANALYZE) fact_events_p2026w13;
VACUUM (ANALYZE) fact_events_p2026w14;
ANALYZE fact_events;