4 ms·
Paul Ford said something about SQLite a few years ago which really stuck with me: https://simonwillison.net/2018/Jan/29/paul-ford/ https://simonwillison.net/201
by simonw 5y ago
Paul Ford said something about SQLite a few years ago which really stuck with me: https://simonwillison.net/2018/Jan/29/paul-ford/ https://simonwillison.net/2018/Jan/29/paul-ford/
> The JSON interface is like, “we save the text and when you retrieve it we parse the JSON at several hundred MB/s and let you do path queries against it please stop overthinking it, this is filing cabinet.”
- rockostrich 5y agoThe "this'll work for most use cases" approach to solving problems is great until it isn't, but you most likely have a "good problem" on your hands if you hit that point.
- tiborsaas 5y agoOh, how many times I hoped for having a scaling issue :)
- deleted 5y ago[deleted]
- nalgeon 5y agoNo matter how fast the parsing is — queries are going to be slow. For example, on 1M rows table with very simple flat JSON values like these: {"object": "user", "object_id": 10, "action": "login"} Without index, this query is going to take 500ms or more: select * from events where json_extract(value, '$.object_id') = ?; With index on json_extract(value, '$.object_id'), it is going to take 1ms. See for yourself: https://sqlime.org/#gist:c452550ae694382f32188bc29412b24a https://sqlime.org/#gist:c452550ae694382f32188bc29412b24a
- simonw 5y agoNot disagreeing with you that indexes are a great idea here - but 500ms is fast enough for a LOT of cases.
- naikrovek 5y agoand it's too slow for all others. I learned a lot from that interactive SQLite thing and I'm glad I didn't just let myself believe that 500ms was ok. (I'm not saying that you just default to a "slow is ok" stance, I'm just saying I'm glad I didn't. this is the internet, where everyone misunderstands me and pretends I say things I don't, so I need to be explicit, sometimes.)
- Too 5y agoSimilar mindset as Grafana Loki took for log search, rather than indexing each field like many others do. https://grafana.com/docs/loki/latest/best-practices/ https://grafana.com/docs/loki/latest/best-practices/ ”don’t add a label for something until you know you need it! Use filter expressions and brute force those logs. It works – and it’s fast.“ Now I don’t advocate brute forcing text as a replacement for a database. But for filing cabinets, like logs with very unpredictable formats, that some admin will look at every once in a blue moon, those 500ms extra per query are nothing. Put it in relation to someone doing it completely manually.
- remram 5y agoIf you can already subset the logs by date and component, scanning the few thousands entries of interest is probably cheaper than maintaining indexes on many fields for all kind of imagined queries. The average-case algorithmic complexity doesn't matter if you can keep N low, even if you run the algorithm a lot.