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

221–230 of 364 posts

Re: SQLite is not a toy database

#221

Post by zie »

doomleika wrote: post #148

Two major gripe I had with SQlite 1. SQLite doesn't really enforce column types[0], the choice is really puzzling to me. Since schema enforced type check is one of the strong suit of SQL/RDMBS based data solution. 2. Whole database lock on write, this make it unsuitable to high write usages like logging and metric recording. WAL mode will help but it will only alleviate the issue, you will need row based lock solutio…

1: Actually this is a feature, It's awesome and easy to map typing to your language types. In python see: https://docs.python.org/3/library/sqlite3.html#using-adapter... specifically the DECLTYPES option.

Other language bindings do things like this also, and makes it pretty idiot proof. you `create table test (mydict dict);` so your tables know their types, and then at bind time you say a sqlite column type of dict == a python dictionary.

Obviously python is sort of a terrible example, because python typing is somewhat non-existent in many ways, but you see the point here.

2: There are definitely cases where it won't work out well, high-concurrent write load is definitely it's big weak spot, but those are usually fairly rare use cases.

Re: SQLite is not a toy database

#222

Post by snicker7 »

code-scope wrote: post #18
snicker7 wrote: post #2

> SQLite is serverless. Maybe if you use SQLite as a file format. But if you use it like an actual database (e.g. in a web application), I find that one is best off setting up a daemon thread to queue/batch transactions.

I use a different pattern. A lot sqlite files for diff purposes (UserSession, User Files, etc each store in separate files) This way diff threads of webserver can open/query/read/write a lot of files concurrently without any issue.

> without any issue

It's easier than you think to corrupt SQLite if you access from multiple threads and especially from multiple processes (yup, I've done this before).

Also, there are no concurrent transactions in SQLite. The entire db file gets locked (using POSIX locking, which is known to be broken [0]). Better to queue/batch transactions on a single connection. If your web server consists of multiple processes, then this requires a separate daemon.

[0]: http://0pointer.de/blog/projects/locking.html

Re: SQLite is not a toy database

#223

Post by kroggen »

pbowyer wrote: post #200
kroggen wrote: post #172

Earlier quoted context omitted.

For replication of SQLite there are some options: http://litereplica.io - single-master replication http://litesync.io - multi-master replication https://aergolite.aergo.io - highest security replication, using a small footprint blockchain

Have you used litereplica or litesync? It doesn't inspire confidence that their websites haven't been updated since 2016 and 2017 respectively.

Most things happen in the forums:

http://litesync.io/forum/

http://litereplica.io/forum/

Re: SQLite is not a toy database

#224

Post by benbjohnson »

danenania wrote: post #204
dbattaglia wrote: post #161

Earlier quoted context omitted.

I think one valid fear is that a web application might, for multiple reasons, scale beyond a single process on a single server. This is common enough that using an embedded database can be problematic, compared to a separate RDBMS process that can be shared between processes. Some web applications are just never going to scale out for any reason, and for those SQLite might be appropriate.

Agreed, but it’s not only a question of scaling. Any app with high uptime requirements will need more than a single process from day 1.

It depends on what you consider high uptime. You can achieve 99.95% uptime with 4h of downtime a year. A lot of downtime occurs because of overly complicated systems so running a single process on a single server can give you relatively high uptime.

Re: SQLite is not a toy database

#226

Post by setr »

haberman wrote: post #178
doomleika wrote: post #148

Two major gripe I had with SQlite 1. SQLite doesn't really enforce column types[0], the choice is really puzzling to me. Since schema enforced type check is one of the strong suit of SQL/RDMBS based data solution. 2. Whole database lock on write, this make it unsuitable to high write usages like logging and metric recording. WAL mode will help but it will only alleviate the issue, you will need row based lock solutio…

> SQLite doesn't really enforce column types[0], the choice is really puzzling to me. This is acknowledged as a likely mistake, but one that will never be fixed due to backward compatibility: > Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more…

SQLite already has different operating modes, right? e.g. WAL is turned on and stays on, I think; It seems like you could at least make type-checking an opt-in mode

Re: SQLite is not a toy database

#227

Post by benbjohnson »

yellowapple wrote: post #168
crazygringo wrote: post #146

Earlier quoted context omitted.

So if you're saying everything does support a web database... then what's the reason people aren't using it for websites? Why do you say it works for "small" websites but presumably not large ones? If it's not transactions, concurrent reading, backups, or administrative tasks... then what's the issue you run into? Genuinely curious... I'm wondering if everything I've heard about "don't use SQLite for websites" is wro…

> then what's the reason people aren't using it for websites? I'd guess the reason to be that people keep hearing things like "don't use SQLite for websites" and thus don't even try. > Why do you say it works for "small" websites but presumably not large ones? Not the GP, but the main reason I wouldn't use SQLite for a large website is that SQLite itself doesn't offer much re: failover/replication (i.e. multiple serv…

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/

Re: SQLite is not a toy database

#228

Post by nijave »

unnouinceput wrote: post #24

SQLite is definitely not a toy DB when it comes to its features, none will deny that. However, when you start to deal with hundreds of millions of records in a table, you kinda consider it a toy.

Sqlite can handle that https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

I've been toying around with it locally for playing with big data sets (50-100s GB) uncompressed and it works pretty well. It's much easier than postgres and mysql which have a lot more knobs and tuning required)

Re: SQLite is not a toy database

#229

Post by nijave »

tmountain wrote: post #103
nalgeon wrote: post #51

Earlier quoted context omitted.

I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. Backups and administrative tasks are super-easy.

How do backups work? Is there a locking mechanism to prevent file corruption if the file is copied during writing?

You can also combine file system snapshots to get versioning which would allow you to pull the db file out of the latest snapshot (right after taking it) and send it somewhere

Re: SQLite is not a toy database

#230

Post by nijave »

ben509 wrote: post #16

> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…

I think the biggest issue can be distilled to "lack of concurrent network access". You start getting into a lot hairy management problems with distributed file systems/copying files around.

Imo part of that is just web applications tend to be not very efficient when compared to something like unix cli utilities so to get performance you end up with potentially massive amounts of horizontal scaling

On the other hand, the lack of performance /usually/ buys you higher productivity so you can make product changes faster (you let a GC manage the memory to save time coding but introduce GC overhead)

Post reply on HN