[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)