Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite Index Visualization

mrsuh.com

Post reply on HN
Print view View original

21–28 of 28 posts

Re: SQLite Index Visualization

#21

Post by lyxell »

srcreigh wrote: post #5

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 “faste…

> 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

Re: SQLite Index Visualization

#22

Post by e28eta »

threatofrain wrote: post #17
graemep wrote: post #14

Earlier quoted context omitted.

> 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 replacin…

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

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

#23

Post by ASalazarMX »

ngrilly wrote: post #10
IX-103 wrote: post #3

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

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

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

#24

Post by ngrilly »

ASalazarMX wrote: post #23
ngrilly wrote: post #10

Earlier quoted context omitted.

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

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.

Re: SQLite Index Visualization

#25

Post by baq »

ngrilly wrote: post #24
ASalazarMX wrote: post #23

Earlier quoted context omitted.

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?

Re: SQLite Index Visualization

#26

Post by ngrilly »

baq wrote: post #25
ngrilly wrote: post #24

Earlier quoted context omitted.

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?

https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t...

Re: SQLite Index Visualization

#28

Post by akx »

threatofrain wrote: post #17
graemep wrote: post #14

Earlier quoted context omitted.

> 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 replacin…

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

https://duckdb.org/ begs to differ.
Post reply on HN