[HN Gopher] Cross-Database Queries in SQLite ___________________________________________________________________ Cross-Database Queries in SQLite Author : chmaynard Score : 12 points Date : 2021-02-21 22:08 UTC (51 minutes ago) (HTM) web link (simonwillison.net) (TXT) w3m dump (simonwillison.net) | mlthoughts2018 wrote: | Can anyone share some cool applications or bigger production | systems built with sqlite? I love virtually everything I read | about it, but I have never encountered a single use case where I | am not better served by storing the data in some flat file | (parquet, csv, etc.) and running a pandas program or simple Spark | program to analyze it (locally on my laptop, or through a managed | service like Dataproc or Databricks - it's _exceedingly_ cheap | for any data size conceivably processable with sqlite). | | I am asking sincerely, what is the comparative advantage of | sqlite or some example scenarios or trade off circumstances where | sqlite is a comparatively more effective solution? | duskwuff wrote: | Most flat-file formats I'm aware of can't be updated in place. | SQLite databases can. | | SQLite is used throughout Chrome and Firefox as a storage | format, e.g. for browser history, bookmarks, cookies, _etc_. It | 's also commonly used as a storage format on macOS, iOS, and | Android. | jpau wrote: | SQLite isn't competing for analytical use-cases. SQLite is a | wonderfully simple transactional database; it compares more to | e.g. MySQL than with OLAP systems like Spark. You use it to | back and operate an application. | | In the broader when-to-OLTP-when-to-OLAP discussion: Think | about how inserts, updates, single-point lookups, indexes etc. | work with your analytical system. | codetrotter wrote: | Apple use SQLite in a lot of places, both in macOS and in iOS, | and both in their applications and in their frameworks. | | For example, they use it in the Photos app on macOS and in the | Photos app on iOS. Their Core Data framework is an abstraction | that is built on top of SQLite, and is available for | application developers on iOS, macOS, etc. | | Specifically about the Photos apps on macOS and iOS though, I | was underwhelmed by the performance when you deal with 100,000+ | pictures and videos. I don't know if the sort of performance | issues I was seeing was tied to SQLite or not though. My own | solution to this has been to move away from using the Photos | app on macOS all together, and to store my photos and videos in | directory hierarchies instead. And to move photos and videos | off of my phone as well, into the directory hierarchies on my | hard drive. ___________________________________________________________________ (page generated 2021-02-21 23:00 UTC)