4 ms·
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 whi
by CodesInChaos 5y ago
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.
- ryanbooz 5y ago(post author and Timescaler) What do you mean by "migrating old data"? Don't want to make assumptions before answering further.
- CodesInChaos 5y agoUpdating many/all rows as a rare maintenance task, typically as part of deploying a new version of the application. I know timescale has native support for the most common cases (adding nullable columns/renaming columns). But sometimes the transformation is more complex. Sometimes an sql update statement would suffice, sometimes streaming each segment in chronological order to the application returning the updated row might be required.
- smarx007 5y ago10 events/minute - I would guess SQLite/DuckDB can fit the bill for many years?
- deleted 5y ago[deleted]
- CodesInChaos 5y agoI don't think performance is the issue, but I'd like to keep the storage small/cheap via compression, since this is a hobby project. (though admittedly it's probably premature optimization)
- ryanbooz 5y agoLooks like you edited this with some more detail, so I'll answer higher. Compression in TimescaleDB used to mean all compressed data was immutable and the table schema couldn't be altered. Since TimescaleDB 2.1, and 2.3 that has changed in a few ways. - Schema can now have columns added or renamed - Compressed chunks can now have rows inserted into them (partially compressed, the background job will fully compress when it runs) Row deletion is not possible yet, but I've personally been having some internal conversations around ways to do exactly as you're suggesting in the near-term; deleting rows based on a "segmentby" column. I have some testing to do... but my focus has been taken up by a certain, 33-minute long read, blog post. Feel free to join our Slack and DM me if you want to talk about it further. slack.timescale.com