We use similar setups but with ElasticSearch instead of an RDBMS and we use cluster connections to query against disparate environments at the same time. They are not allowed to communicate to each other, but a 'placeholder' or 'empty' cluster that is allowed to talk to the others can give you exactly what you want for analysis and auditing. Another big benefit is that environments and other 'boxing' formats are isolated and cannot influence another. Same with lifecycle management, they do their own hot/cold/archive/deletion.
We usually end up querying based on session signatures (which is something like user agent + JWT), on plain user ids or on trace ids. This gets you a pretty neat timeline with references for full records if you need to drill down on a specific event. Makes everything super fast and when you need more data or want to read PII you can drill down (depending on your access roles).
We log to SQL so that we can instantly obtain a full list of log entries that pertain to a specific user action trace id. We can go from a collection of user actions over a larger business process and then for each action we can pull all of the log entries that were generated. All of this is exposed through a nice web interface with full text search capability over the log messages. Without logging to SQL, we would have a hell of a time building something similar.
In order to keep this from exploding out of control, we have a strict 90 day expiration policy on all persisted business state and log entries. Our log table indicies are:
- User Action Trace Id (16 bytes)
- Timestamp (64 bits)
- Message (Variable - FTS)
We store all log entries in a single SQLite database (logs.db) contained in each environment. These are queried over HTTP from centralized management tools.