News for hackers. Stuff that matters.
Re:SQLite Index Visualization by IX-103 on Thursday November 14, 2024 @09:19AM (#42136324)
> 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.

Re:SQLite Index Visualization by ngrilly on Thursday November 14, 2024 @12:01PM (#42138240)
SQLite is a real database engine. I guess what you mean is that SQLite is not competing with database servers.
Re:SQLite Index Visualization by ASalazarMX on Friday November 15, 2024 @11:29AM (#42148397)
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.
Re:SQLite Index Visualization by ngrilly on Friday November 15, 2024 @12:40PM (#42149065)
Agreed. SQLite is becoming popular on the server-side as well. The latest version of Rails making SQLite the default is particularly interesting.
Re:SQLite Index Visualization by baq on Friday November 15, 2024 @03:05PM (#42150363)
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?
Re:SQLite Index Visualization by ngrilly on Friday November 15, 2024 @03:18PM (#42150482)
Re:SQLite Index Visualization by cogman10 on Thursday November 14, 2024 @12:00PM (#42138218)
Meh, it isn't really too far off from the way other DBMS servers handle storage and indexes. The principles are pretty identical (especially when sqlite operates in WAL mode).
Re:SQLite Index Visualization by graemep on Thursday November 14, 2024 @12:39PM (#42138785)
> SQLite isn't competing with other databases. It's competing with JSON and XML files for persistent storage

It competes with both. its clearly used for local persistent storage. SO are quite a lot of other things. It also competes with other RDBMSes where a separate server process is not a requirement.

That does mean it serves very different requirements, its just that its use case are a lot wider than just replacing JSON and XML files and similar.

Re:SQLite Index Visualization by threatofrain on Thursday November 14, 2024 @03:42PM (#42140986)
> It also competes with other RDBMSes where a separate server process is not a requirement.

If you casually list off the top DB's either by usage or by recent hotness then almost all of them will have a server, but you'll also find they're basically all not embedded DB's with exception to RocksDB.

Re:SQLite Index Visualization by e28eta on Friday November 15, 2024 @10:14AM (#42147626)
I’m familiar with this embedded DB, used in Quickbooks desktop: https://en.m.wikipedia.org/wiki/SQL_Anywhere

So… large usage, but probably not very high on the hotness scale

Re:SQLite Index Visualization by akx on Tuesday November 26, 2024 @01:03PM (#42248153)
https://duckdb.org/ begs to differ.