SQLite Index Visualization

The website is so legible I want to read it.

> 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.

FWIW, I find the font size (I am on an iPhone) way too large, particularly as there is also important text in the diagrams and that text is much smaller, so while I feel a need to shove my phone away from my face to deal with the overly large body text I then have to keep pulling it back in to feel comfortable reading the diagrams, which feel out of place.

Great effort!

> By default, each SQLite table row has a unique rowId, which works like a primary key if one isn’t explicitly defined.

It actually uses rowid even if you have a primary key.

You should try visualizing the primary key index for a WITHOUT ROWID table. Those indexes are my favourite

> Both Indexes look similar, but the second Index, with fewer Pages, should be faster.

Less nodes doesn’t really mean “faster”. The most important is the height of the tree.

The second most important is what happens when you find your value in the index. Do you need to load the rest from a separate table(rowid)? Or is the data just there for you (without rowid)? Especially range queries (aka where 50<= col <=100)

> Less nodes doesn’t really mean “faster”. The most important is the height of the tree.

In isolation of a single access yes. But when frequently accessing an index overall size can be very important for cache hit rate.

Yeah, such a relief to see content w/o super dense ad loads, etc. Very cool article.

The term "indexes" serves both as the third-person singular present tense of the verb "to index" and as a plural noun form of "index." In contrast, "indices" is the traditional plural form of "index," particularly prevalent in mathematical and scientific contexts. While "indexes" is commonly used in general English, "indices" is often preferred in technical fields to maintain linguistic precision. Employing "indices" in such contexts helps distinguish between the action of indexing and the plural form of index, thereby enhancing clarity.

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

SQLite is a real database engine. I guess what you mean is that SQLite is not competing with database servers.

Says who with what authority?

All major RDBMS use the term "indexes".

FWIW, both are fine (https://www.nasdaq.com/articles/indexes-or-indices-whats-the...), and SQLite and PostgreSQL documentation (as two popular examples) use "indexes".

It depends on your audience. If you're catering to academics, use "indices." If you're catering to the general person, "indices" comes off as pompous.

> 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.

would be real nice to see how postgres does the same thing, compare and take notes

Try pluralizing "time series". You won't get far.

So what I've seen in Finland is people using "time series" for the plural and "time serie" for the singular.

> 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.

or emit tgf for yEd, for more layout variants with less work

Nope. Academics prefer “indexes” when discussing databases.

I wonder if one could make a grammar-argument that it's like "Attorneys General." :p

> It actually uses rowid even if you have a primary key.

This is true with one exception, if you create an INTEGER PRIMARY KEY, SQLite will use this instead [1].

[1]: https://sqlite.org/rowidtable.html

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

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.

Agreed. SQLite is becoming popular on the server-side as well. The latest version of Rails making SQLite the default is particularly interesting.

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?