Rudder

Work in progess here: https://github.com/fanf/rudder/commit/77cc2de9f83e5c401c6d1fb5c87c896dcbd55d63

So, I explored two alternatives to see what could float.

1/ The first one is just a variation of JSON, and would allow to perhaps keep just one table. In place of JSON details, I used a new data type:

```
CREATE TYPE Compliance AS (
ruleId text
, directiveId text
, complianceLevel int[]
);

CREATE TABLE nodeComplianceComposite (
nodeId text NOT NULL CHECK (nodeId <> '')
, runTimestamp timestamp with time zone NOT NULL
, details Compliance[] NOT NULL
, PRIMARY KEY (nodeId, runTimestamp)
);
```
So here, we have an array of [ruleId, directiveId, [compliance level in an int[]] for details. It's much more compact than JSON, and the perf are in the domain of "not amazing but we will be able to do something".
But there is some caveat:

- JDBC does not know about array of custom type. So we need to expand the custom type (with `unnest`) to make data go throught JDBC driver. That's cumbersome and remove a lot of the appealing simplicity of the solution.
- I wasn't able to unnest a data with a custom data in it, meaning I was forced to used a int[] where a `ComplianceLevel` data type would have been better.
- Everything in SQL request, even with postgresql private functions, cries "don't do things like that". Any request grows quickly quite complicated, with a lot of subqueries.

2/ The second solution just used a ol'normalized table, with everything expanded:

```
CREATE TABLE nodecompliancedata (
nodeId text NOT NULL CHECK (nodeId <> '')
, runTimestamp timestamp with time zone NOT NULL
, ruleId text NOT NULL CHECK (nodeId <> '')
, directiveId text NOT NULL CHECK (nodeId <> '')
, pending int DEFAULT 0
, success int DEFAULT 0
, repaired int DEFAULT 0
, error int DEFAULT 0
, unexpected int DEFAULT 0
, missing int DEFAULT 0
, noAnswer int DEFAULT 0
, notApplicable int DEFAULT 0
, reportsDisabled int DEFAULT 0
, compliant int DEFAULT 0
, auditNotApplicable int DEFAULT 0
, nonCompliant int DEFAULT 0
, auditError int DEFAULT 0
, badPolicyMode int DEFAULT 0
, PRIMARY KEY (nodeId, runTimestamp, ruleId, directiveId)
);
```

That not pretty, and any query/update is an horror in the number of fields. Plus, we are going to have a lot of lines - again. Update for one node compliance are slower, to.
On the bright side, that format is very well suited to be able to do the whole calculus postgre-side, and so we just get back in Rudder 30 Compliance level. That's quick, even in a slow network. And the whole query is quite fast (in the order of 100 ~ 400 ms for 100 runs on 100 nodes).

With that solution, we will also need for sure a dedicated table for that. We could keep the other (current) nodecompliance one to get detailled information of for a given node/run (ie to "zoom in" to understand some compliance reported in the aggregation graphe).

First remark, in both ideas, I think you can remove the directiveId since we only filter on Rules for compliance graph (this would reduce the amount of array to n Rules instead of n Rules * x directives)

In first solution, where we would replace current details with a more efficient one, you lose the details of the compliance which goes to component and value ( Rule > Directive > Component > value ), so i don't think we should choose that solution.

I would be ok to do this if this was a another column ( "details" would still be a huge json, and we would have a new column "summary" which contains only the compliance level with an array of type Compliance.

But as you said above, this solution has quite some drawbacks and may not be very interesting.

Second solution is fine to be, but I would not make a column for each compliance level but either an array of int like in solution one, or keeping a text containing the json array of compliance we could directly sent to the interface.

So i would say a table like this:

```
CREATE TABLE nodecompliancedata (
nodeId text NOT NULL CHECK (nodeId <> '')
, runTimestamp timestamp with time zone NOT NULL
, ruleId text NOT NULL CHECK (nodeId <> '')
, compliance int[] or text
, PRIMARY KEY (nodeId, runTimestamp, ruleId, directiveId)
);
```

One other question, should we have a batch that initialize the table by translating data from nodeCompliance to that new table

Vincent MEMBRÉ wrote:

First remark, in both ideas, I think you can remove the directiveId since we only filter on Rules for compliance graph (this would reduce the amount of array to n Rules instead of n Rules * x directives)

I don't think so. Wanting a report on a specific directive seems something everybody would like to have, because for most people, especially with technique from technique editor, a unit of "work" is the directive. Rule is a little too big in most cases.
As you said, there is a tradeoff, and we are growing the table by a heuge factor. So I'm interested by feedbacks here (@Benoit ?)

In first solution, where we would replace current details with a more efficient one, you lose the details of the compliance which goes to component and value ( Rule > Directive > Component > value ), so i don't think we should choose that solution.

I would be ok to do this if this was a another column ( "details" would still be a huge json, and we would have a new column "summary" which contains only the compliance level with an array of type Compliance.

Do you see a reason to keep that level of details? In the general case and in reporting plugin case? For now, I'm not I know appart for the specific case where someone want to look at the details of a past compliance result (for debug most likely). But perhaps you see use case in reporting plugin ? Again, Benoit I welcome feedbacks here :)

But as you said above, this solution has quite some drawbacks and may not be very interesting.

Second solution is fine to be, but I would not make a column for each compliance level but either an array of int like in solution one, or keeping a text containing the json array of compliance we could directly sent to the interface.

So i would say a table like this: [...]

I did that at first (the array), but I thought that it will becomes quite hard to add/remove a field at some point without messing up things (quite easy to switch the order of elements for ex).
Moreover, it becomes harder to run aggregate function on the array. It may possible to do it (either via native postgres function or unnest(array)), but the it's just more tedious.
I also though to use a custom data type (as long as it's not in a array, it should be ok for jdbc driver).

But in fact, in each cases, I'm not sure of the gain compared to plain old standard columns. OK, that's a lot of columns, but internally that doesn't change anything for postgres (it's really just the same thing for custom data type, and almost the same thing for array, but with less optimisation because of the dynamic lenght that we don't even use). So why not sticking with SQL conventionnal way?

The text / json solution is not possible, because it forbids any postgres-side processing before at least 9.4, so not before centos8.

With some more discussion on the subject, we will:

- try to minimize change in 4.1 (of course)
- keep nodecompliance table as it is, with the same logic and all,
- create a new table, nodecomplianceSOMETHING (data? levels?) with the interesting data in it,
- create a dedicated cleaner for that table appart from the other compliance one, so that people are able to keep 100 days of nodecomplianceSOMETHING but only 1 of details.

For now, we don't aggregate anything.

I'm a bit concerned by the size of the entry you mention: 100-500kB per node per run
It seems too big; postgresql compress these entries (in the table size, it appears as TOAST, and should not be very big). If compression cannot really compress the compliance, then we have a real issue: this table is significantly larger than ruddersysevents (which is around 150kB/node/directive), and we should fix this

Nicolas CHARLES wrote:

I'm a bit concerned by the size of the entry you mention: 100-500kB per node per run
It seems too big; postgresql compress these entries (in the table size, it appears as TOAST, and should not be very big).

It's not the size on disk, it's the size that goes throught the network and arrive as text to Rudder to be parsed as JSON. I don't know the size on disk (but we already know it's big).

François ARMAND wrote:

Nicolas CHARLES wrote:

I'm a bit concerned by the size of the entry you mention: 100-500kB per node per run
It seems too big; postgresql compress these entries (in the table size, it appears as TOAST, and should not be very big).

It's not the size on disk, it's the size that goes throught the network and arrive as text to Rudder to be parsed as JSON. I don't know the size on disk (but we already know it's big).

Ha, yes, the data over network is awfully large
poor man solution: we could reduce the text size of the json
Excerpt from an existing entry

   "rules":[
     {
       "ruleId":"hasPolicyServer-root",
       "serial":13,
       "compliance":{
         "success":87.5,
         "notApplicable":12.5
       },
       "numberReports":8,
       "directives":[
         {
           "directiveId":"common-root",
           "compliance":{
             "success":87.5,
             "notApplicable":12.5
           },
           "numberReports":8,
           "components":[
             {
               "componentName":"Log system for reports",
               "compliance":{
                 "success":100.0
               },
               "numberReports":1,
               "values":[
                 {
                   "value":"None",
                   "compliance":{
                     "success":100.0
                   },
                   "numberReports":1,
                   "unexpanded":"None",
                   "messages":[
                     {
                       "message":"Logging system for report centralization is already correctly configured",
                       "type":"Success" 
                     }
                   ]
                 }
               ]
             },
             {
               "componentName":"CRON Daemon",
               "compliance":{
                 "success":100.0
               },
               "numberReports":1,
               "values":[
                 {
                   "value":"None",
                   "compliance":{
                     "success":100.0
                   },
                   "numberReports":1,
                   "unexpanded":"None",
                   "messages":[
                     {
                       "message":"Cron daemon status was correct",
                       "type":"Success" 
                     }
                   ]
                 }

For reference, on network compression for pgsql: https://stackoverflow.com/questions/38384731/efficiently-retrieving-large-data-objects-from-a-postgresql-database

Some quantification of the table:

- (3 uuids + date + 14 int) * 20 runs by hours * 24 hours = 60kB per day.
- for 100 nodes * 15 rules with 10 directives = 900MB
- for 1 month = 26GB

If we aggregate by hour, with need ~1.3GB by month. And by day, 55MB per month.

If we had decided to not keep directive level (only rule granularity), all numbers would be divised by 10 (so 90MB per day for 100 nodes, 2.6GB for a month).