I'm thinking about storing history of a workflow engine. There are hundreds of different pipelines defined.
The goal is to track "life cycle" of inbound data. Thus execution of each pipeline step should be logged along with pipeline&step specific metadata.
Example of simple pipelines:
Pipeline A = read JSON from FS | validate | split JSON array | send parts via JMS
Pipeline B = ingest XML via JMS | validate | store to a batch | check stored batch size | if reached batch size merge XMLs | send via HTTP
We currently got ~200k different stage executions/events per day. It would be sufficient to set event TTL to a month or so.
So far the model correlates all steps of a pipeline execution by inbound data UUID. Results of splits, batches, merges, etc. are assigned different UUIDs and the parent relation is stored.
- Pipeline A
- Pipeline B
We need to perform listing queries like: "give me last N events" or "give me last N unique UUIDs sorted by newest events". Also queries like: "give me all related UUIDs to UUID A".
A brief experimenting with graph DBs or RDF DBs shows that they are rather slow with the listing queries - ordering the whole data set, etc.
So we will probably use classic Postgres due it's speed. Any alternatives?