←back to thread

SQLite Index Visualization

(mrsuh.com)
295 points mrsuh | 5 comments | 14 Nov 24 10:51 UTC | HN request time: 1.23s | source
Show context
IX-103 ◴[14 Nov 24 14:19 UTC] No.42136324[source]▶
> I wanted to see how a database management system (DBMS) stores an index in both disk and memory, and how it searches through an Index...I chose SQLite for my experiments

SQLite is a bit of an outlier in how it handles...everything, but even more so in query processing. SQLite tends to favor simplicity over performance, which causes it to implement things differently than every other DB I've worked with. You have to understand - SQLite isn't competing with other databases. It's competing with JSON and XML files for persistent storage. This means that how it implements anything tells you practically nothing about how a real database would do something.

replies(3): >>42138218 #>>42138240 #>>42138785 #
1. ngrilly ◴[14 Nov 24 17:01 UTC] No.42138240[source]▶
SQLite is a real database engine. I guess what you mean is that SQLite is not competing with database servers.
replies(1): >>42148397 #
2. ASalazarMX ◴[15 Nov 24 16:29 UTC] No.42148397[source]▶
And even that is questionable, since many web applications offer SQLite as another DB back end, and it works just fine for a wider range of workloads than one would expect.
replies(1): >>42149065 #
3. ngrilly ◴[15 Nov 24 17:40 UTC] No.42149065[source]▶
Agreed. SQLite is becoming popular on the server-side as well. The latest version of Rails making SQLite the default is particularly interesting.
replies(1): >>42150363 #
4. baq ◴[15 Nov 24 20:05 UTC] No.42150363{3}[source]▶
the problem with sqlite has never been performance, it's always been extreme (dead)locking when writing concurrently - how does Rails get around that assuming this is actually recommended for prod deployments?
replies(1): >>42150482 #
5. ngrilly ◴[15 Nov 24 20:18 UTC] No.42150482{4}[source]▶
https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t...