Cross-Database Queries in SQLite

Re:Cross-Database Queries in SQLite by codetrotter on Sunday February 21, 2021 @05:59PM (#26218218)

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.

[ Parent | on HN ]

Re:Cross-Database Queries in SQLite by mceachen on Sunday February 21, 2021 @11:11PM (#26220761)

> I don’t know if the sort of performance issues I was seeing was tied to SQLite or not though

It's certainly doable to serve million-asset libraries with SQLite (PhotoStructure is proof of this).

You need to be careful with indexed queries and make sure you've set up a large enough RAM cache, but for PhotoStructure, almost all queries are kept under 10-50ms.

[ Parent | on HN ]

Re:Cross-Database Queries in SQLite by tmpz22 on Sunday February 21, 2021 @06:22PM (#26218393)

Performance is an odd metric to judge a database, not because its unimportant but because its mostly in the developer's court if they are configuring and accessing data in a performant way. If the data is poorly indexed or queried its going to be slow no matter what database you use. Odds are the photos app is not configured well for your edgecase of having hundreds of thousands of photos.

For the record I dislike SQLite - especially the cargo culting of SQLite by people who aren't even using it in production.

[ Parent | on HN ]

Re:Cross-Database Queries in SQLite by blackrock on Sunday February 21, 2021 @10:41PM (#26220613)

Apple tries to force you to use the Photos app on the Mac to store your photos. But I never trusted it. Maybe because I didn’t understand what data model it was using behind the scenes. They don’t advertise that part. Even though for the most part it works pretty well. In fact, for most daily uses, the Photos app works almost magically.

But once, after a few years of perfect usage, the program crashed. Then it started crashing more regularly. Then finally it completely died. Data corrupted. Completely unusable. Major system malfunction.

I lost all my photos and videos in the Photos app.

But because I was always paranoid of it, I had the original pictures backed up elsewhere. So data saved. But I never trusted the Photos app again.

[ Parent | on HN ]