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

Consider SQLite

blog.wesleyac.com

Post reply on HN
Print view View original

21–30 of 274 posts

Re: Consider SQLite

#21

Post by teraflop »

chasil wrote: post #4

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…

> NFS and SMB are options that can convey access to remote systems, but performance will not likely be good.

And the safety of your data depends on the quality of your network filesystem's locking implementation. It's not too difficult to design a locking method that works most of the time, but it's a lot harder to build something that guarantees mutual exclusion over an imperfect network.

On a single machine, file locking is generally reliable (mediated by the kernel). Multiple clients can access the same database simultaneously, with readers and writers taking turns, and all that happens is you pay the performance penalty of serialized access.

But if your client thinks it has a lock on a remotely-stored database file, but the server-side lock has actually expired and another client is writing to the database, your data is likely to get hosed.

https://www.sqlite.org/howtocorrupt.html

Re: Consider SQLite

#22

Post by copperx »

catillac wrote: post #11
greatjack613 wrote: post #5

I use SQLite exclusively on a high performance crypto sniper project - https://bsctrader.app and I could not be happier with it. Performs much better then postgres in terms of query latency which is ultra important for the domain we operate in. I take machine level backups every 2 hours, so in the event of an outage, just boot the disk image on a new vm and it's off. I would never do this on my professional job due t…

The stigma of using the most popular database in existence?

For the domain of webapps, where multiple concurrent writers are often expected, yes, I would say it's a stigma.

Re: Consider SQLite

#23

Post by pgwhalen »

SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is.

My team at work has adopted it and generally likes it, but the biggest hurdle we've found is that it's not easy to inspect or fix data in production the way we would with postgres.

Re: Consider SQLite

#24

Post by cperciva »

lrem wrote: post #18
chasil wrote: post #4

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…

I don't know what backup tools you have in mind... But since a SQLite database is a single file (modulo write ahead journal and whatnot), making whatever you need is trivial.

Be careful! If you copy a database file while it is being modified, you may end up with an inconsistent view of the database depending on the order in which different parts of the file are modified.

There are ways around this (filesystem snapshots and potentially file locking) but it's not trivial and failing to handle this correctly is a very good way to build a system which passes tests under low load (when the file-copying operation will win all the relevant races) but fail catastrophically under heavy load.

Re: Consider SQLite

#25

Post by chasil »

lrem wrote: post #18
chasil wrote: post #4

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…

I don't know what backup tools you have in mind... But since a SQLite database is a single file (modulo write ahead journal and whatnot), making whatever you need is trivial.

In Oracle, I can do this:

    RECOVER DATABASE UNTIL TIME '2021-10-01 02:00:00' USING BACKUP CONTROLFILE;
SQLite does not implement such a feature.

Re: Consider SQLite

#27

Post by samwillis »

I believe SQLite is about to explode in usage into areas it’s not been used before.

SQL.js[0] and the incredible “Absurd SQL”[1] are making it possible to build PWAs and hybrid mobile apps with a local SQL db. Absurd SQL uses IndexedDB as a block store fs for SQLite so you don’t have to load the whole db into memory and get atomic writes.

Also I recently discovered the Session Extension[2] which would potentially enable offline distributed updates with eventual consistency!

I can imagine building a SAAS app where each customer has a “workspace” each as a single SQLite db, and a hybrid/PWA app which either uses a local copy of the SQLite db synced with the session extension or uses a serveless backend (like CloudFlare workers) where a lightweight function performs the db operations. I haven’t yet found a nice way to run SQLite on CloudFlare workers, it need some sort of block storage, but it can’t be far off.

0: https://sql.js.org/

1: https://github.com/jlongster/absurd-sql

2: https://www.sqlite.org/sessionintro.html

Re: Consider SQLite

#28

Post by DasIch »

lrem wrote: post #18
chasil wrote: post #4

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…

I don't know what backup tools you have in mind... But since a SQLite database is a single file (modulo write ahead journal and whatnot), making whatever you need is trivial.

The state of the art is that you click a few buttons in the UI of the cloud provider of your choice. That takes a few seconds.

What you are proposing may be trivial but in comparison it might just as well be rocket science. Doing more work to use a simpler database that is harder to setup and has less features is not exactly compelling.

Re: Consider SQLite

#29

Post by masklinn »

lrem wrote: post #18
chasil wrote: post #4

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…

I don't know what backup tools you have in mind... But since a SQLite database is a single file (modulo write ahead journal and whatnot), making whatever you need is trivial.

> modulo write ahead journal and whatnot

That's like saying immortality is trivial modulo death and whatnot. If you don't integrate with sqlite's locking system, you can easily "backup" a file mid-write, corrupted and unusable.

That's why sqlite has a built-in backup command.

Re: Consider SQLite

#30

Post by srcreigh »

chasil wrote: post #4

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…

In WAL mode, a write can mean writing some data in memory on the local machine. With Postgres/MySQL/etc, it has to go over the network.

I can't parse [0] right now about the overhead of TCP and round trips with the database server, but it's basically a question of whether one SQLite write is 10x, 100x, 1000x, or more faster than a database server write. That should make a lot of difference.

[0] https://gist.github.com/jboner/2841832

Post reply on HN