We are logging and permanently storing all the queries performed about private persons in our database. Due to GDPR we need to be able to show all the entities who have requested personal data and the timestamps. The queries can be single or batch involving hundreds of thousands of individuals.

What is the most cost-effective way of keeping such logs? The primary index should be the unique personal code which allows efficient retrieval of query information. For analytics purposes we sometimes need to index by entity and/or query date, but this doesn't need to be optimized. The number of unique personal codes is about 1.5 million and the number of entities is ~300,000 and growing.

I have tried InfluxDB, but the limitation is that the time makes up a part of the key and batch queries will contain the same timestamp. I have tried storing the data as JSON in a relational database but it takes too long to update both single and batch entries. With DynamoDB the problem was batch write efficiency. It took 2 hrs to batch write million items at 500 WCU.

The data could look like this:

        'query_type_1':['2018-09-25 12:00:00.000000',...],
        'query_type_2':['2018-10-02 12:00:00.000000',...]
        'query_type_1':['2018-09-25 12:00:00.000000',...],
        'query_type_2':['2018-10-02 12:00:00.000000',...]

And the goal is to use as little hardware resources as needed to achieve <2 sec latencies retrieving the data by personal code and appending new timestamps to the arrays.

  • So on the relational database side, why would you need to do updates? Most logs I have used are insert-only. Does the data need to be fully stored in JSON format? I know SQL Server and PostgreSQL (probably others too) have functions to shred and build JSON. I'm imagining a design where you have entity, query type, query text and timestamp as individual columns. Insert a new record for each time the user runs a query. That should make it relatively simple and efficient.
    – Jacob H
    Commented Nov 8, 2018 at 15:34
  • You probably want to decouple your application queries from writing the audit trail -- send log records to some durable message queue like Kafka, then write them out asynchronously.
    – mustaccio
    Commented Nov 8, 2018 at 16:17
  • @mustaccio The idea is indeed to log asynchronously, but the problem is how to store this data efficiently. Commented Nov 9, 2018 at 8:17
  • @JacobH It's not feasible to log every query as a new row. The table would grow to billions of rows rather fast. JSON is not mandatory, I just used it as an example of how the data could look like. The arrays with the timestamps can grow arbitrarily long. Commented Nov 9, 2018 at 8:17


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.