4 ms·

Things I learned while making a database of songs on Jango Radio: 1. Create covering indexes sorted by each column to be searched. 2. Enable sqlite_stat4 3.

Things I learned while making a database of songs on Jango Radio: 1. Create covering indexes sorted by each column to be searched. 2. Enable sqlite_stat4 3. Use WAL mode. 4. Set a busy_timeout 5. Vacuum and analyze daily. 6. Nobody has heard of Jango.

anamexis 6y ago
Sounds like a GIN index might be better suited for the search case.
- ZinniaZirconium 6y ago
  SQLite doesn't have GIN indexes.
munk-a 6y ago
Covering indexes are a really careful thing to investigate, sometimes you'll get big wins out of it but often times those indexes can easily be misaligned with really minor application changes so it's something I tend to approach in very limited situations. Vacuum & Analyze are two fun ones and I think postgres in particular would do well by investing a bit more into the default configuration of autovacuum since the out of the box settings are pretty conservative compared to the state most out of the box DBs are used - but I think that DB configuration is very much an unsolved problem in general and can be a quite surprising source of major performance gains (or losses!). I mentioned mat views elsewhere - but if you are really hitting quite predictable usage patterns (where covering indexes are stable enough) then some well defined mat views might go a long way to tuning performance that little bit more - just be aware of the fact that rematerialization isn't instantaneous and if you have a high rate of change in the data then you'll probably want time based re-materialization instead of trigger based which means you'll need to account for some inconsistent data (as is the case with caching in general).
- ZinniaZirconium 6y ago
  I wish there were different recommended configurations for SQLite when used as the backend of a web site versus the storage format for an embedded system. The defaults are really not suitable for concurrency or for tables with millions of rows. Also I wish I didn't have to recompile to enable sqlite_stat4 histograms. SQLite doesn't have materialized views.
RabbitmqGuy 6y ago
Damn you work at jango? I've been a happy customer since ~2009. It's the best kept secret on the internet. I wish you guys blogged about your engineering, I bet there's lots to say. Keep rocking
- ZinniaZirconium 6y ago
  Haha! No I don't work at Jango. Earlier this year I got tired of songs disappearing from Jango song search so I made my own search tool as a quarantine project. Like you say Jango is almost a secret these days and I've been looking for an appropriate forum to discuss it but there pretty much isn't any. I tried Show HN and nothing happened. So are you actually a paying customer with a Radio Airplay account or are you a free Jango listener? I never had and never needed an account. I don't know what kind of database Jango uses or what kind of work Jango people do but I sure hope they keep doing it.

anamexis 6y ago

Sounds like a GIN index might be better suited for the search case.

ZinniaZirconium 6y ago
SQLite doesn't have GIN indexes.

munk-a 6y ago

Covering indexes are a really careful thing to investigate, sometimes you'll get big wins out of it but often times those indexes can easily be misaligned with really minor application changes so it's something I tend to approach in very limited situations. Vacuum & Analyze are two fun ones and I think postgres in particular would do well by investing a bit more into the default configuration of autovacuum since the out of the box settings are pretty conservative compared to the state most out of the box DBs are used - but I think that DB configuration is very much an unsolved problem in general and can be a quite surprising source of major performance gains (or losses!). I mentioned mat views elsewhere - but if you are really hitting quite predictable usage patterns (where covering indexes are stable enough) then some well defined mat views might go a long way to tuning performance that little bit more - just be aware of the fact that rematerialization isn't instantaneous and if you have a high rate of change in the data then you'll probably want time based re-materialization instead of trigger based which means you'll need to account for some inconsistent data (as is the case with caching in general).

ZinniaZirconium 6y ago
I wish there were different recommended configurations for SQLite when used as the backend of a web site versus the storage format for an embedded system. The defaults are really not suitable for concurrency or for tables with millions of rows. Also I wish I didn't have to recompile to enable sqlite_stat4 histograms. SQLite doesn't have materialized views.

RabbitmqGuy 6y ago

Damn you work at jango? I've been a happy customer since ~2009. It's the best kept secret on the internet. I wish you guys blogged about your engineering, I bet there's lots to say. Keep rocking

ZinniaZirconium 6y ago
Haha! No I don't work at Jango. Earlier this year I got tired of songs disappearing from Jango song search so I made my own search tool as a quarantine project. Like you say Jango is almost a secret these days and I've been looking for an appropriate forum to discuss it but there pretty much isn't any. I tried Show HN and nothing happened. So are you actually a paying customer with a Radio Airplay account or are you a free Jango listener? I never had and never needed an account. I don't know what kind of database Jango uses or what kind of work Jango people do but I sure hope they keep doing it.