Can somebody recommend a database suitable for an event sourced application:
* One series of events per user
* Each series grows at about 10 events/minute while the user is active
* Fancy queries are not required, typically a user's event series is consumed in order to update aggregate state for that user
* Either used online, adding events one at a time and needing to immediately update the aggregate state
* Used offline syncing a batch of hours or days at once. When a large time interval, eventually consistent state updates are acceptable
* It must be possible to delete a user's data, regardless how old it is (a nightly batch job deleting multiple users at once is fine, if it helps performance)
* Migrating old data should be possible with reasonable performance and without consuming excessive temporary memory
* Compact storage is important (simple zstd compression should suffice, though columnar compression might be slightly better)
* Being able to use a cheaper object store like S3 for old data would be nice
At a glance timescale community appears to meet most requirements. The userid can be used as `segmentby` key, and the data compressed via columnar compression. But it seems to have limitations with migration (sounds like it requires me to manually decompress and recompress chunks, instead of simply transforming one (chunk, segment) piece at a time) and deletion (I need to delete everything with a specific `segmentby` key).
Alternatively there is the DIY approach, of serializing each entry in a compact format, one file per user, and then once data is old enough compress it (e.g. with zstd) and upload it to S3.