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

121–130 of 330 posts

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

#121

Post by sicp-enjoyer »

I have used SQLite for Django applications with a few thousand users. It has had no problems. However, I just use the ORM and never configure the SQL directly. The vast majority of LAMP stack style web applications would be an ideal use case.

However, I would consider how important RDMS features are to you which are not available in SQLite:

- less sophisticated type and constraint system.

- a severely limited ALTER TABLE.

- No stored procedures.

- limited selection of math and statistical functions.

- no permission and user model, not to mention row-level security.

To be clear, I don't think it's bad the SQLIte doesn't try to be an RDMS, but I would consider this perspective when making a decision, not performance which is great, and difficult to max out.

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

#122

Post by settrans »

justsomeuser wrote: post #114
settrans wrote: post #109

Things worked well at the outset, especially in local development against my NVMe drive for my small CRUD application. Then, with a little traffic, things continued to go well in production. But as traffic scaled up (to 1-5 QPS, roughly 25% writes), they fell apart. Hard. Because my production environment was spinning rust, IO contention was a real issue and totally absent from development. This manifested as frequen…

So you were using an HDD, not an SSD? Would an SSD in production have solved the timeouts by increasing your write throughput?

According to my testing, SSDs would greatly reduce the contention but were not a complete fix.

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

#123

Post by Karunamon »

marginalia_nu wrote: post #73
dotancohen wrote: post #66

Earlier quoted context omitted.

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.

I'd argue adding SQL into the mix makes it difficult to maintain, mixed-language codebases are almost by definition complex, and you get significant chafing when mixing a declarative language like SQL and OOP. Since this is a no-update and no live-insert scenario we're talking about, it's fairly easy to produce code that is an order of magnitude faster than a DBMS, since they're not only primarily optimized for effic…

Maybe this is a failure of imagination on my part, but won't most people be using ORMs? Again, talking about the use case of the average application that's light enough to get away with SQLite, it doesn't seem like you would need to be hand writing queries.

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

#125

Post by Cthulhu_ »

I maintain an 'older' codebase (2012) and am rebuilding it to a new version, but both use SQLite. It's a configuration management web application installed onto either bare metal or virtual machines. Generally only a handful of simultaneous users; I want to say performance isn't much of an issue or concern, but I've had to fix a bug that was caused by too many writes to the database where the system ran into IOPS limits (traditional hard drives or constrained VMs at 100 IOPS).

There is a hacky solution for redundancy; at certain events, a copy of the .db file is made and rsynced to a secondary node. This will probably fall apart if the file ever goes above a few MB in size.

Pros / reasons to use it: Self-contained, just a single file to transfer, no drivers needed, no servers running other than my own application.

Cons: No good support for ALTER TABLE queries, so things like changing the name, datatype, or default value of a column isn't happening. The workaround is to create a new table and transfer rows over, then drop the old table and rename the new table. Also the aforementioned issue if you want redundancy.

So basically, if redundancy isn't a requirement for you, sqlite is fine. It's probably ideal for single user applications, like your browser or apps (iirc sqlite is used a lot for those purposes).

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

#126

Post by bob1029 »

One other reason sqlite is great is the reduced latency. When you can satisfy queries in microseconds vs milliseconds, there is a fundamental shift in certain things you might try or not try.

We've been using this stuff in production for over half a decade now. Multi-user, heavily-concurrent systems too. The biggest cost savings so far has been the lack of having to screw with a separate database server per customer install (we do B2B software).

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

#127

Post by Glench »

mekster wrote: post #118
Glench wrote: post #32

Here's an all-time great post about why you might consider SQLite in production with data about performance: https://blog.wesleyac.com/posts/consider-sqlite I use SQLite in production for my SaaS[1]. It's really great — saves me money, required basically no setup/configuration/management, and has had no scaling issues whatsoever with a few million hits a month. SQLite is really blazing fast for typical SaaS workloads…

What is the point of using SQLite under a web service? I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant. (Things seem to be getting better with strict column types these days.) I've recently migrated a smallish service from MySQL to PostgreSQL and figured it's quite a work if you're not careful writing by the SQL standard which mean…

> What is the point of using SQLite under a web service?

Take a look at the Consider SQLite post I linked. They address your performance questions too.

For me, SQLite was a nice way to simplify launching and running my SaaS business and has had no downsides.

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

#128

Post by barryhennessy »

WA wrote: post #46
pieterhg wrote: post #6

Yes for all my sites: Nomad List, Remote OK, Hoodmaps, Rebase etc. No real issues at all.

I read in one of your Tweets that you use one database file per (unrelated) table to avoid corruption. Why did you move to this model? Are multiple tables per file really more easy to corrupt?

I'd be interested to know what kind of corruption you were facing.

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

#129

Post by chasil »

sicp-enjoyer wrote: post #113
zoomablemind wrote: post #103

> ...I periodically hear about projects that use/have used sqlite as their sole datastore. SQLite==exclusive access, no sharing, unless read-only. Basically, it provides a SQL convenience for local usage.

If you need to safely have multiple processes read and write to the same data, it does that great. The writes are serialized, but that's typically how an in-memory shared resource would be implemented as well. Do you mean shared across networks?

I have seen SQLite disconnect random sessions of multiple writers, rather than blocking them. Setting WAL mode made no difference.

This database can safely be used by a single writer at all times.

To implement the serialization that SQLite does not, do this in the POSIX shell:

  until mkdir ~/.dpapplock 2> /dev/null
  do sleep 3
  done

  trap "rmdir ~/.dbapplock" EXIT
  sqlite3_app ...
The mkdir() system call is defined as atomic by POSIX, so the shell can safely serialize for you, assuming there are never more than a handful.

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

#130

Post by Cthulhu_ »

memset wrote: post #42

Question for people using SQLite in prod: how do you cope if your app is running on a platform like Heroku or Cloud Run, rather than a proper server or VM? Have you found a solution for the fact that those environments, and disk, is ephemeral?

I'd always opt to use their hosted database in those constraints. IMO SQLite only works well if you own the storage, so bare metal (or VMs) only.
Post reply on HN