[HN Gopher] SQLite Index Visualization
___________________________________________________________________
SQLite Index Visualization
Author : mrsuh
Score : 280 points
Date : 2024-11-14 10:51 UTC (1 days 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.
| ASalazarMX wrote:
| 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.
| ngrilly wrote:
| Agreed. SQLite is becoming popular on the server-side as
| well. The latest version of Rails making SQLite the default
| is particularly interesting.
| baq wrote:
| 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?
| ngrilly wrote:
| https://fractaledmind.github.io/2024/04/15/sqlite-on-
| rails-t...
| 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.
| e28eta wrote:
| 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
| 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.
| lyxell wrote:
| > 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
| 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.
| srcreigh wrote:
| Nope. Academics prefer "indexes" when discussing databases.
| 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.
| Terr_ wrote:
| I wonder if one could make a grammar-argument that it's like
| "Attorneys General." :p
| 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-15 23:01 UTC)