Rudder

some metrics

rudder=> SELECT
   relname AS objectname,
   relkind AS objecttype,
   reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
   FROM pg_class
   WHERE relpages >= 8
   ORDER BY relpages DESC;
                objectname                | objecttype |  #entries   |  size   
------------------------------------------+------------+-------------+---------
 ruddersysevents                          | r          | 2.56554e+08 | 86 GB
 composite_node_execution_idx             | i          | 2.56554e+08 | 42 GB
 ruleid_idx                               | i          | 2.56554e+08 | 34 GB
 keyvalue_idx                             | i          | 2.56554e+08 | 19 GB
 executiontimestamp_idx                   | i          | 2.56554e+08 | 15 GB
 ruddersysevents_pkey                     | i          | 2.56554e+08 | 11 GB
 changes_executiontimestamp_idx           | i          | 1.27593e+07 | 751 MB
 pg_toast_16522                           | t          |      262164 | 510 MB
 reportsexecution_nodeid_nodeconfigid_idx | i          |      691807 | 281 MB

creating a table with all the repairs of ruddersysevents:

CREATE SEQUENCE changeserial START 101;

CREATE TABLE ChangeSysEvents (
  id                 bigint PRIMARY KEY default nextval('changeserial')
, executionDate      timestamp with time zone NOT NULL
, nodeId             text NOT NULL CHECK (nodeId <> '')
, directiveId        text NOT NULL CHECK (directiveId <> '')
, ruleId             text NOT NULL CHECK (ruleId <> '')
, serial             integer NOT NULL
, component          text NOT NULL CHECK (component <> '')
, keyValue           text
, executionTimeStamp timestamp with time zone NOT NULL
, eventType          text
, policy             text
, msg                text
);

CREATE INDEX changes_executionTimeStamp_changes_idx ON ChangeSysEvents (executionTimeStamp) ;

insert into ChangeSysEvents select * from ruddersysevents where eventType = 'result_repaired';
INSERT 0 12997914

SELECT
   relname AS objectname,
   relkind AS objecttype,
   reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
   FROM pg_class
   WHERE relpages >= 8
   ORDER BY relpages DESC;
                objectname                | objecttype |  #entries   |  size   
------------------------------------------+------------+-------------+---------
 ruddersysevents                          | r          | 2.56554e+08 | 86 GB
 composite_node_execution_idx             | i          | 2.56554e+08 | 42 GB
 ruleid_idx                               | i          | 2.56554e+08 | 34 GB
 keyvalue_idx                             | i          | 2.56554e+08 | 19 GB
 executiontimestamp_idx                   | i          | 2.56554e+08 | 15 GB
 ruddersysevents_pkey                     | i          | 2.56554e+08 | 11 GB
 changesysevents                          | r          | 1.29989e+07 | 4491 MB
 changes_executiontimestamp_idx           | i          | 1.27593e+07 | 751 MB
 pg_toast_16522                           | t          |      262164 | 510 MB
 changesysevents_pkey                     | i          | 1.29989e+07 | 373 MB
 changes_executiontimestamp_changes_idx   | i          | 1.29989e+07 | 284 MB

so the table is much larger than the previous index

Stripping useless content

alter table ChangeSysEvents drop column eventType;
alter table ChangeSysEvents drop column policy;

saves only 300 MB on the table

So, as a result, having an external table "costs" 4,1GB+278MB+278MB (compared to the 751MB of the index in ruddersysevents)
Perf might be better, but this needs disks space