[HN Gopher] SQLite Index Visualization
___________________________________________________________________
SQLite Index Visualization
Author : mrsuh
Score : 161 points
Date : 2024-11-14 10:51 UTC (12 hours ago)
(HTM) web link (mrsuh.com)
(TXT) w3m dump (mrsuh.com)
| edweis wrote:
| The website is so legible I want to read it.
| saurik wrote:
| 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.
| bgalbs wrote:
| Yeah, such a relief to see content w/o super dense ad loads,
| etc. Very cool article.
| IX-103 wrote:
| > 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.
| cogman10 wrote:
| 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).
| ngrilly wrote:
| SQLite is a real database engine. I guess what you mean is that
| SQLite is not competing with database servers.
| graemep wrote:
| > 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.
| threatofrain wrote:
| > 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.
| srcreigh wrote:
| 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)
| kevincox wrote:
| > 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.
| salviati wrote:
| 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.
| gloflo wrote:
| Says who with what authority?
|
| All major RDBMS use the term "indexes".
| CharlesW wrote:
| 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".
| orthecreedence wrote:
| 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.
| euroderf wrote:
| 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.
| vivzkestrel wrote:
| would be real nice to see how postgres does the same thing,
| compare and take notes
| w10-1 wrote:
| or emit tgf for yEd, for more layout variants with less work
___________________________________________________________________
(page generated 2024-11-14 23:00 UTC)