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

SQLite is not a toy database

antonz.org

Post reply on HN
Print view View original

351–360 of 364 posts

Re: SQLite is not a toy database

#351

Post by kragen »

SQLite wrote: post #349
kragen wrote: post #348

Earlier quoted context omitted.

After discussing it with you, I think it would be bad to make SQLite's recursive CTE support more powerful; making SQL Turing-complete is a clear case of Turing overreach. I think it's still the case that SQLite doesn't permit the use of recursive CTEs in subqueries.

SQLite requires that the self-reference be in the top-level FROM clause of the recursive part of a recursive CTE. PG apparently allows the self-reference to be down inside of subqueries, as long as there is only one reference. I have make a copy of the Collatz Conjecture CTE that you linked to and was going to see if I could get it to work in SQLite for the next release cycle. I don't (yet) see any reason why it shou…

Oh dear. I was hoping you wouldn't notice this discussion. Another step off the cliff into the Turing Tarpit... well, I hope my twisted piece of appalling SQL brings joy to your heart, at least, even if supporting it makes SQLite worse. And you are of course in a much better position to judge what would make SQLite better or worse than I am.

Have you thought about offering an interface with an intentionally-Turing-incomplete subset of SQL in SQLite, for making queries that can be guaranteed to terminate? Because it doesn't sit right with me that SQL is Turing-complete now. https://news.ycombinator.com/item?id=26529789 goes into more detail on decidable query languages that can still accommodate transitive closure.

Re: SQLite is not a toy database

#352

Post by asalahli »

lmm wrote: post #309
asalahli wrote: post #304

Earlier quoted context omitted.

Most web servers support greaceful restarts, i.e. reloading code and configuration without downtime. Usually you just send a pre-specified and documented signal to the process to do it.

For a web server serving from the filesystem, sure. That doesn't help you deploy a new version of a web application you're making code changes to (which is the kind of thing that would need a database).

I was actually talking about both. For example for python, which is what I usually use, both uwsgi and gunicorn support graceful restarts. Admittedly I don't have much experience with other languages, but it's technically possible. If your application doesn't support it out of the box, you can still achieve it with something like systemd's socket activation.

Re: SQLite is not a toy database

#353

Post by lacker »

gwd wrote: post #335
lacker wrote: post #258

Earlier quoted context omitted.

When I was working on database services there were actually many applications with more writes than reads. A common example is applications where you want to keep the user's state saved frequently, but you only need to reload it when the application is restarted. A lot of games work this way. So there can be a write every few seconds or every minute for every active user, but you only need to do a read a few times at…

Right; lots of modern document software, for instance, basically saves continuously; so a Google Docs-style application could have such a load. But if you have a write transaction every second, but each transaction only takes 1ms, that's still ~1000 concurrent users before you start to get noticeable lag. Remember, the person I was replying to claimed SQLite was " not useable outside of the model... where you have a…

1 ms per transaction is quite optimistic! In practice occasionally you will have some sort of latency spike caused by an external event or a single extra-slow transaction, and then your single-threaded queue will get backed up and requests will fail, if your database is dependent on a single thread. SQLite is great for the right problem but it just isn't the right tool for a web service with even a small amount of traffic.

Re: SQLite is not a toy database

#355

Post by syzygyhack »

neerajdotname2 wrote: post #171
petercooper wrote: post #30

With no sense of overstatement here, SQLite is one of my favorite creations in the entire world, so I have a bunch of links some of you might find interesting if you want to dig further: https://github.com/sql-js/sql.js - SQL.js lets you run SQLite within a Web page as it's just SQLite compiled to JS with Emscripten. https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replica…

Using sql.js we have built online SQL course where the code is executed in the browser itself. https://academy.bigbinary.com/learn-sql

This is cool!

Re: SQLite is not a toy database

#356

Post by benbjohnson »

lacker wrote: post #353
gwd wrote: post #335

Earlier quoted context omitted.

Right; lots of modern document software, for instance, basically saves continuously; so a Google Docs-style application could have such a load. But if you have a write transaction every second, but each transaction only takes 1ms, that's still ~1000 concurrent users before you start to get noticeable lag. Remember, the person I was replying to claimed SQLite was " not useable outside of the model... where you have a…

1 ms per transaction is quite optimistic! In practice occasionally you will have some sort of latency spike caused by an external event or a single extra-slow transaction, and then your single-threaded queue will get backed up and requests will fail, if your database is dependent on a single thread. SQLite is great for the right problem but it just isn't the right tool for a web service with even a small amount of tr…

That's not been my experience. I write applications with in Go using a SQLite backend and it's not uncommon to see writes transactions that are in the 100s of microseconds. I typically see read transactions with multiple queries around ~50µs. That's all running on very modest $5/month DigitalOcean droplets. The vast majority of web applications do not see thousands of requests per second so I think SQLite is a great fit for most web apps.

Re: SQLite is not a toy database

#357

Post by benbjohnson »

yellowapple wrote: post #334
benbjohnson wrote: post #227

Earlier quoted context omitted.

I'm in the process of adding read replication to Litestream[1] so folks can scale out the read-side of their SQLite applications to multiple nodes (or replicate to edge nodes for low-latency). [1]: https://litestream.io/

Neat. I'll have to give that a whirl. Would this enable any node to be a writer (i.e. would it lock the DB across all nodes)? Or would I have to designate some "master" server with exclusive write access and have any other servers forward write requests to that server?

It would be the latter. You'd need to have a single primary and replicas would need to redirect writes to that node.

Re: SQLite is not a toy database

#358

Post by TheRealPomax »

emodendroket wrote: post #158
TheRealPomax wrote: post #117

Earlier quoted context omitted.

Not sure I understand: sqlite is file based, so snapshots and concurrent backups are literally just file copies/backups. I'd much rather SQLite not waste its time on implementing features they're not good at, leaving that to the tools we already have available for rolling file backups, instead spending their time and effort on offering the best file-based database system they can. (Heck, even failover is just a file…

Every database is ultimately on the file system but there’s a reason that method of backup is rarely used.

Except not every database was designed from the ground up to BE a file, that's one of Sqlite's benefits. A database is by definition a single, fully self-contained file. Unless you go out of your way to make Postgres, MySQL, MariaDb, etc. do this, that is very much not the case.

For SQLite, "copying files", rolling file backups, shadow volumes etc. are by definition valid strategies when it comes to SQLite.

Re: SQLite is not a toy database

#359

Post by TheRealPomax »

simonw wrote: post #145
TheRealPomax wrote: post #117

Earlier quoted context omitted.

Not sure I understand: sqlite is file based, so snapshots and concurrent backups are literally just file copies/backups. I'd much rather SQLite not waste its time on implementing features they're not good at, leaving that to the tools we already have available for rolling file backups, instead spending their time and effort on offering the best file-based database system they can. (Heck, even failover is just a file…

Unfortunately backing up with a simply file copy operation isn't guaranteed to work if you have write traffic at the same time. Instead you need to use the .backup mechanism or the VACCUM INTO command, both of which safely create a backup copy of your database in another file - which you can then move anywhere you like.

Not really, it still works exactly as you should expect: writes are handled atomically, using a separate file, so if you copy the database file "mid-write" you're not actually copying a database in flux, you're copying the database before the write operation gets applied, and SQLite applies the write after the copy lock is gone.

The main issue here is time: because copying temporarily locks the db out of further changes, it will be "out of sync" if you naively believe that performing a write means your next call will see that data, and you bake that assumption into your code. While there's a wait, on modern hardware using SQLite for its intended purposes (any volume of reads, but low volume of writes), that's just not an issue.

There are other problems associated with file-based backup, of course, such as missing out on in-memory data, or corruptions caused by power outages, but those problems only exist if we were to copy a db once. Backups run regularly, and any data we miss out on the current pass, we'll get on the next pass.

Basically: when used for the purpose that SQLite was created for, file copies are a perfectly fine backup strategy that really only shows its limitations when you start to push your project into "SQLite isn't really appropriate here anymore" territory.

(having said that: the fact that VACUUM can be run into a new file is super nice, and everyone should know that it exists)

Re: SQLite is not a toy database

#360

Post by benbjohnson »

TheRealPomax wrote: post #359
simonw wrote: post #145

Earlier quoted context omitted.

Unfortunately backing up with a simply file copy operation isn't guaranteed to work if you have write traffic at the same time. Instead you need to use the .backup mechanism or the VACCUM INTO command, both of which safely create a backup copy of your database in another file - which you can then move anywhere you like.

Not really, it still works exactly as you should expect: writes are handled atomically, using a separate file, so if you copy the database file "mid-write" you're not actually copying a database in flux, you're copying the database before the write operation gets applied, and SQLite applies the write after the copy lock is gone. The main issue here is time: because copying temporarily locks the db out of further chan…

SQLite uses advisory locks but the `cp` command won't adhere to those so you can get a copy that contains only some of the writes from a transaction. If you're lucky, you just get some wonky data. If you're unlucky, you can get a corrupted copy.

Transactions are only atomic from the perspective of other transactions. Other processes or commands on the system can see partial state. This applies to both the rollback journal & the WAL modes.

File copies are also covered in their "How to Corrupt a SQLite Database File" web page[1].

[1]: https://www.sqlite.org/howtocorrupt.html#_backup_or_restore_...

Post reply on HN