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

231–240 of 330 posts

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

#231

Post by rograndom »

I've used in "production" and as the "primary datastore", but not in the ways those terms are normally used.

1. PHP web development for the client of a client. They needed persistent data and MySQL was not available. Moving to a different webhost was straight up rejected. Used sqlite with Idiorm and it worked just fine.

2. As the local datastore for a cross platform mobile application. The sqlite DB was unique on each device. Libraries were available and worked well.

3. This is a large one. Several 10's of thousands of installs that query the filesystem, but filesystem access is throttled by the vendor. We're using sqlite to store the state of the filesystem as it doesn't really change that much. If the db is damaged or whatever, it can be wiped as it isn't the final source of truth.

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

#232

Post by pphysch »

I think SQLite vs. PostgreSQL is similar to Flask+SQLAlchemy vs. Django, or similar debates.

Yeah, you probably can do everything with the "simpler" stack. It might even be nominally faster in many cases. But if there's any chance you're going end up rolling your own type-validation or ORM or admin interface or GIS... Just use the battle-tested kitchen sink from the get go.

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

#233

Post by divbzero »

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…

The Consider SQLite post mentions that one of SQLite’s in the past decade as “WAL mode (enabling concurrent reads and writes)”. Does this mean that the official advice to avoid SQLite for concurrent writes [1] is no longer a big concern?

[1]: https://www.sqlite.org/whentouse.html

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

#234

Post by giantrobot »

matthewaveryusa wrote: post #87

I’ve worked on several projects with sqlite, both read and write heavy, all with high concurrency, with databases in the few hundred MB with 400k server clients, and 100 bare-metal servers running at capacity. The sqlite part of our system is never the problem. In our case sqlite has been an alternative to custom files on disk or replacing a spaghetti of hashmaps in memory. we also replaced a single postgresql instan…

I'm also in the SQLite is a data structure camp. You get fast access and persistence for free. You can also fix a lot of issues just exploring the database offline.

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

#235

Post by dgb23 »

mekster wrote: post #160
lm28469 wrote: post #144

Earlier quoted context omitted.

> So, why not use a safer choice to begin with? I know several people who build projects like that. It take them months to get a working product, just to discover it doesn't interest people or doesn't work like they expected. If for every piece of tooling you go for the "safe" and most performant one you gain bloat and complexity real quick. People underestimate "simple" tech performance, in 99% of projects by the ti…

Which part of running MySQL instead SQLite is over engineering?

Running MySQL/Postgres over SQLite:

- needs to be provisioned and configured

- needs additional tooling and operational overhead

- comes with a _large_ performance overhead that is only won back if you have quite a significant load - especially writes, which means the vast majority of web projects are slower and require more resources than they should.

- it makes the whole system more complex by definition

It is a cost-benefit thing that tilts towards RDBMS as soon as you need to sustain very high transactional loads and want a managed, individually accessible server that you can query and interact with while it's running in production.

But if it is just "a website that needs durability" then you haven't yet shown how that tradeoff is worth it.

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

#236

Post by simonw »

divbzero wrote: post #233
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…

The Consider SQLite post mentions that one of SQLite’s in the past decade as “WAL mode (enabling concurrent reads and writes)”. Does this mean that the official advice to avoid SQLite for concurrent writes [1] is no longer a big concern? [1]: https://www.sqlite.org/whentouse.html

I think the way it's worded in that SQLite documentation page is still accurate:

> SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

If your application needs to support hundreds of concurrent writes a second you shouldn't use SQLite. But that's a pretty high bar!

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

#237

Post by simonw »

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…

I would expect the performance of SQLite for queries against an index to outperform MySQL and PostgreSQL in all cases - because SQLite eliminates the need for network overhead by essentially executing those queries directly as a C function call.

So no matter how optimized MySQL and PostgreSQL are, SQLite will run rings around them for basic SELECT queries.

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

#238

Post by woah »

divbzero wrote: post #233
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…

The Consider SQLite post mentions that one of SQLite’s in the past decade as “WAL mode (enabling concurrent reads and writes)”. Does this mean that the official advice to avoid SQLite for concurrent writes [1] is no longer a big concern? [1]: https://www.sqlite.org/whentouse.html

Take this with a huge grain of salt because I am by no means an expert, but I currently am working on some scripts that import a few million rows into SQLite. I am using bash and the sqlite command line. I was getting a lot of concurrent write errors from sqlite (bear in mind i am only doing inserts of separate rows so in theory there is never an actual conflict), so I tried using WAL mode. It actually resulted in more contention. I ended up just going back to non-WAL mode and implementing an exponential backoff in bash to retry writes.

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

#239

Post by loxs »

I tried doing this a bunch of times and most of them ended up requiring a migration, most often to postgresql. I can only remember one such case that still uses sqlite for a web service.

It always goes like this:

1. I start a new "lean" web service and decide to use sqlite.

2. Some months down the road I figure I need some slightly more advanced db feature. The ones I can remember are postgresql numeric arrays (for performance where I can test for membership in a where clause) and jsonb (again with its special syntax for querying and its performance implications).

3. For some time I postpone the inevitable and do various hacks until I fully hate myself.

4. Suddenly realize that migration to postgresql will reduce the complexity, even with regards of infrastructure, as I usually have redis et al. in the game (which I wouldn't have to use had I started with postgresql initially).

3. I waste several days migrating and wondering was it (my initial stupidity) worth it...

My advise is - if it's going to be accessed via the network (and you'll have to operate a server either way), make it two servers and go with postgresql. If you are not 100% sure about the opposite (no chance of it becoming a web service), go with postgresql. Is it a desktop app? Postgresql (just slightly joking here). Mobile app? OK, I guess you have no real choice here, go with sqlite.

And no, you can't "just use an ORM", because when the day comes, you will need to migrate because of features sqlite does not support and you will have made mistakes. If you used an ORM, now you'll have to migrate off both sqlite and the ORM.

PS: Ah, yeah, and now I remember one other instance where I had to migrate off sqlite solely because I needed to provide an admin interface (think PGAdmin) to the production system.

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

#240

Post by deckard1 »

TheRealPomax wrote: post #182
mekster wrote: post #172

Earlier quoted context omitted.

So you trade for some risk for an hour.

Please do explain what risk you're thinking of, as anyone smart enough to write their own SaaS would not put resources in the web server's file system tree? You stick your db file in an normal secure location outside the server's root, chmodded appropriately so that it suffers the exact same risks as any other file on the OS. It's no more or less risky than /etc/shadow, while being considerably easier to work with (a…

your app has full access to the SQLite file. MySQL/PostgreSQL have users and permissions. Security is about layers, and SQLite is removing one layer of security. You can, for example, put DELETEs or access to certain tables on a separate user that your web app has no access to. With SQLite, if your app gets hacked then they can do anything with the whole DB they want to. In addition, with a separate DB process you get audit logs. If someone hacks your SQLite app they may have access for months before you realize it, if you ever do. Especially if they are doing something subtle like UPDATEs on specific tables/fields that may go unnoticed but provide the hacker some benefit. This is why you can't simply rely on the idea of using a backup. That's only going to help if the hacker totally trashes your DB.

With a separate DB you may have a hope of detecting when someone hacked your app. But without that firewall, the question becomes: how much of the data in my SQLite can now be trusted? If you don't know what backup is safe to restore, then you can't trust any of it.

Again, this is about layers. Not saying MySQL/Postgres will save you. But they can increase the odds.

Post reply on HN