Comment by freedmand
2 years ago
I recently had the idea to record every note coming out of my digital piano in real-time. That way if I come up with a good idea when noodling around I don’t have to hope I can remember it later.
I was debating what storage layer to use and decided to try SQLite because of its speed claims — essentially a single table where each row is a MIDI event from the piano (note on, note off, control pedal, velocity, timestamp). No transactions, just raw inserts on every possible event. It so far has worked beautifully: it’s performant AND I can do fun analysis later on, e.g. to see what keys I hit more than others or what my average note velocity is.
I wouldn't expect performance of pretty much any plausible approach to matter much. The notes just aren't going to be coming very quickly.
If you play ten note chords — one for each finger — in quick succession, that can rack up a lot of inserts in short time period (say, medium-worst case, 100Hz, for playing a chord like that five times per second, counting both “on” and “off” events).
It’s also worth taking into consideration damper pedal velocity changes. When you go from “off” (velocity 0) to fully “on” and depressed (velocity 127), a lot of intermediate values will get fired off at high frequency.
Ultimately though you are right; it’s not enough frequency of information to overload SQLite (or a file system), probably by several orders of magnitude.
A 1/100th of a second is still an eternity for a modern computer. It's easy to forget just how insanely fast computers are when user-facing software still lags all the time. You really do need to go out of your way to make performance an issue here (or stack too many abstractions).
What counts as plausible nowadays may surprise you given your grasp on true performance. Observe these professionals. https://www.primevideotech.com/video-streaming/scaling-up-th...
Insertion times are not a problem but maybe after a few years of playing you will be managing millions of records
Would you expect SQLite performance to degrade per insert once a table is very large (in a way that, say, an append-only log file wouldn’t)?