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

161–170 of 364 posts

Re: SQLite is not a toy database

#161

Post by dbattaglia »

crazygringo wrote: post #146
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.

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…

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.

Re: SQLite is not a toy database

#162

Post by renewiltord »

Toutouxc wrote: post #45

> not bothering with optimization (≈200 requests per page) What amount of SQL queries per page render is considered sensible? When I run more than 20 queries per request in my Rails apps (smallish internal tools for different companies) I get uneasy. I usually deploy the app on the same machine where the DB (not SQLite) runs, but I imagine if that weren't the case the app-DB roundtrips could soon dominate the whole t…

The sqlite position on this is articulated here https://sqlite.org/np1queryprob.html

> N+1 Queries Are Not A Problem With SQLite

>

> The SQLite database runs in the same process address space as the application. Queries do not involve message round-trips, only a function call. The latency of a single SQL query is far less in SQLite. Hence, using a large number of queries with SQLite is not the problem.

Re: SQLite is not a toy database

#163

Post by ericlewis »

finiteseries wrote: post #143
mrweasel wrote: post #136

Earlier quoted context omitted.

Many banks still “shutdown” for hours every night to do backups.

I’m not anywhere near the banking industry but from HN alone I’ve been led to believe dailyish huge file transfers are also the norm in a variety of situations (aka SQLite’s backup strategy).

ftp or sftp if you're lucky - upload a giant CSV or receive one. It is crazy to me it still works this way.

Re: SQLite is not a toy database

#164

Post by benbjohnson »

hinkley wrote: post #135
benbjohnson wrote: post #88

Earlier quoted context omitted.

I agree that high availability features are outside of the goals of an embedded database. There's an ecosystem of tools SQLite to provide these benefits though. There's dqlite & rqlite for providing HA over SQLite. I'm the author of Litestream[1] which provides streaming replication to S3 for SQLite databases so folks can safely run a single node instance. [1]: https://litestream.io/

One could, for instance, run “static” sites with the data stored in a SQLite database. Update on write apps often can do okay with 90% uptime. It’s the “generate everything in every request” crowd that needs an HA solution and why do we keep doing this to ourselves ?

Yes, static generation is a great way to go if that works for your use case. Throw it up on a CDN and you'll likely have five 9s of uptime. I don't think that's a viable solution for a lot of applications though.

Re: SQLite is not a toy database

#165

Post by incanus77 »

I had seen Dr. Hipp speak at a conference[1] around 2009 and it really put SQLite on my radar as a (then) Mac developer. It ended up surfacing from the back of my mind around then at Mapbox when I put together the prototype of the MBTiles file format[2] which is still in use today. The idea of an on-disk relational database was new to me and just a perfect fit for where mobile devices were at that point in time, and for our use case of offline maps.

[1] https://en.wikipedia.org/wiki/C4_(conference)#C4[2]

[2] https://medium.com/devseed/portable-map-tiles-format-release...

Re: SQLite is not a toy database

#166

Post by lsb »

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…

If you are in WAL mode, you can have unlimited readers as one writer is writing.

Re: SQLite is not a toy database

#167

Post by bob1029 »

hnrodey wrote: post #69
bob1029 wrote: post #4

Don't forget about User-Defined Functions. https://www.sqlite.org/appfunc.html We just started enhancing our SQL dialect with new functions which are implemented in C# code. One of them is an aggregate and it is really incredible to see how it simplifies projections involving multiple rows. One huge benefit of SQLite's idea of UDFs is that you can actually set breakpoints and debug them as SQL is executing.

Sounds very interesting. Can you elaborate on how you're leveraging C# for this?

Directly through Microsoft's SQLite provider - Microsoft.Data.Sqlite.

See: https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite...

Re: SQLite is not a toy database

#168

Post by yellowapple »

crazygringo wrote: post #146
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.

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 servers, one database), and I haven't used RQLite enough (or at all; I should fix that) to be comfortable with it in production. Because of that, I'm more likely to reach for / recommend PostgreSQL instead.

That being said, if your website has crazy "web scale" FAANGesque needs and you're at the point where you need to write your own replicated datastore, using SQLite as a base and building your own replication layer on top of it (or using RQLite and maybe adjusting it for your needs) seems like a reasonable way to go.

Re: SQLite is not a toy database

#170

Post by bob1029 »

Thaxll wrote: post #20
andrewmcwatters wrote: post #13

SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html

SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html

With a single thread & SQLite connection instance, I have been able to insert hundreds of thousands of rows per second when using NVMe drives.

Note that WAL and synchronous flags must be set appropriately. Out of the box and using the standard "one connection per query" meme will handicap you to The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you.

The whole point of an embedded database is that the application should have exclusive control over it, so you don't have to worry about the kinds of things that SQL Server needs to worry about.

SQLite is not a direct replacement for SQL Server, but with enough effort it can theoretically handle even more traffic in your traditional one-database-per-app setup, because it's not worrying about multiple users, replication, et. al.

Post reply on HN