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

Ask HN: Have you used SQLite as a primary database?

news.ycombinator.com

Post reply on HN
Print view View original

61–70 of 330 posts

Re: Ask HN: Have you used SQLite as a primary database?

#61

Post by bsenftner »

barryhennessy wrote: post #40
bsenftner wrote: post #23

I no longer work there, but an enterprise facial recognition system used by NGOs, and 3-lettered government agencies has SQLite as the sole datastore. I wrote a portion of the SQLite runtime logic, a simply key/value store used all over the software. SQLite proved to be phenomenal. We spec'ed hardware with enough RAM to hold the FR DB in memory, and damn SQLite is fast enough to keep up with the optimized FR system p…

Impressive numbers, thanks for sharing. Out of interest, were you running on bare metal/cloud? And what kind of CPU was behind those 24M face compares per second?

Running on bare metal, and those numbers come from a 3.4 GHz i9. The system is a fully integrated single executable, with embedded SQLite. Since I left the firm a year ago, new optimizations have the facial compares down to 40 nanoseconds per face.

Re: Ask HN: Have you used SQLite as a primary database?

#62

Post by qwerty456127 »

syntaxfree wrote: post #57
qwerty456127 wrote: post #44

Earlier quoted context omitted.

> For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better. Isn't MySQL MyISAM faster and this way constitute a better choice for a scientific number crunching application? I mean near 4GB DB, very simple schema, heavy reading load, little/no inserts and no updates.

Why?

I dunno, just felt like conventional (since long ago) knowledge that MyISAM is the fastest of all SQL DBs in simplistic non-RAM scenarios. I'm not sure this is true so I ask.

Re: Ask HN: Have you used SQLite as a primary database?

#63

Post by samuel »

qwerty456127 wrote: post #44
the__prestige wrote: post #22

The sqlite docs page has a nice article [1] on when to use an embedded database such as sqlite and when to go with a client/server model (postgres, mysql or others) When not to use sqlite: - Is the data separated from the application by a network? - Many concurrent writers? - Data size > 280 TB For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better. [1…

> For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better. Isn't MySQL MyISAM faster and this way constitute a better choice for a scientific number crunching application? I mean near 4GB DB, very simple schema, heavy reading load, little/no inserts and no updates.

DuckDB is the OLAP equivalent of SQLite, as far as I know.

Re: Ask HN: Have you used SQLite as a primary database?

#64

Post by dangerface »

qwerty456127 wrote: post #44
the__prestige wrote: post #22

The sqlite docs page has a nice article [1] on when to use an embedded database such as sqlite and when to go with a client/server model (postgres, mysql or others) When not to use sqlite: - Is the data separated from the application by a network? - Many concurrent writers? - Data size > 280 TB For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better. [1…

> For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better. Isn't MySQL MyISAM faster and this way constitute a better choice for a scientific number crunching application? I mean near 4GB DB, very simple schema, heavy reading load, little/no inserts and no updates.

> Isn't MySQL MyISAM faster

I think the performance MySQL has over sqlite comes from its multithreading more than the storage engine.

In my experience sqlite is just as fast as MyISAM for single threaded work.

Re: Ask HN: Have you used SQLite as a primary database?

#65

Post by lep »

ajani wrote: post #17

Yes. For http://ht3.org which is a search engine I wrote for tech related articles. It works really well. It uses the fts5 extension, that allows full text searching. There are over a million indexed pages and it’s no trouble.

I'm also using fts5 for some small projects but i haven't looked too deeply into it so i'm wondering if you have any interesting insights. Like what kind of index/options do you use? Maybe the trigram index? And your "across boundaries" mode is just word* in fts syntax?

Re: Ask HN: Have you used SQLite as a primary database?

#66

Post by dotancohen »

marginalia_nu wrote: post #53
qwerty456127 wrote: post #44

Earlier quoted context omitted.

> For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better. Isn't MySQL MyISAM faster and this way constitute a better choice for a scientific number crunching application? I mean near 4GB DB, very simple schema, heavy reading load, little/no inserts and no updates.

With 4 Gb you might as well just load the data into RAM.

But then you have to implement all the SELECT and DML logic yourself. SQL makes this a breeze with JOIN, ON UPDATE CASCADE, etc. And being SQL, it is very easy to maintain, even by the PFY that replaces you.

Re: Ask HN: Have you used SQLite as a primary database?

#70

Post by qwerty456127 »

dotancohen wrote: post #66
marginalia_nu wrote: post #53

Earlier quoted context omitted.

With 4 Gb you might as well just load the data into RAM.

But then you have to implement all the SELECT and DML logic yourself. SQL makes this a breeze with JOIN, ON UPDATE CASCADE, etc. And being SQL, it is very easy to maintain, even by the PFY that replaces you.

SQLite (also H2 and some other embedded SQL databases) can be used entirely in-memory, one can also drop an SQLite file on a RAM-hosted filesystem (tmpfs/ramdrive). You really can put everything into RAM (and still enjoy SQL) if you have enough, don't mind long cold-load and potential data loss.
Post reply on HN