Hacker News Reader

Comment by Lxr | Hacker News Reader

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites)...Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.

Do people agree with this? I was under the impression you should not use SQLite for production websites for some reason. Django has this to say, for instance [1]:

When starting your first real project, however, you may want to use a more robust database like PostgreSQL, to avoid database-switching headaches down the road.

[1] https://docs.djangoproject.com/en/1.10/intro/tutorial02/

View on HN

I'm rather skeptical, because I've found Postgres to be pretty simple to run on a project of this size as well. Takes like 10 minutes to install it. Maybe bump it up to 30 minutes if you want to do something a little tricky with user accounts, like run the web server with a DB account that has limited permissions, which SQLite can't do at all anyways.

On the small projects, DB admin doesn't seem to be much more complex than using a SQLite DB. By the time you get the DB load high enough that you really want to pay attention to administrating it, SQLite has probably given up the ghost long ago.

Don't get me wrong, SQLite is great for what it does. I don't see the upside to it on this though. Even if you know for sure your site will never hit high traffic, it just isn't that hard to run a conventional DB. And if it does, it's a lot easier to pay somebody to set up your DB server right than to convert over to a conventional DB and then get it set up right.

The upside is it doesn't need a central server. And 10 minutes to install the server (if you know how to do it) vs 0 minutes is a pretty big deal.

A project running on sqlite can be quickly taken to just about any box and run without any infrastructure dependencies.

You can easily run a hundred instances on a single machine, for dozens of simultaneous users, without any setup or coordination. Computing resources are only required for access, not for availability.

The only possible answer here is "it depends".

As SQLite themselves state, they're running a 500K hits/day site on SQLite just fine. They also point out that their site is not particularly write heavy, which is a somewhat important point to be making with SQLite specifically.

What do Django mean by a "real project"? If this is a project you intend to scale to beyond the scope of SQLite, then starting from something that will scale that way in the first place will alleviate later growing pains. If it's your personal site and will always and forever be run on a VPS with 1GB of RAM? There's no reason not to just stick with SQLite -- and you get the benefits of not having to maintain a "real" db service.

>not having to maintain a "real" db service.//

Is there more to do for a small site to maintain postgres relative to SQLite?

Run backups and updates, de-lint occasionally; what else?

We built a SQL database backend as a service based on SQLite, we like it so much. The typeless schema and lack of some constraints can be a challenge, but there are work arounds (check constraints). High write uses are the only problem with SQLite, our service provides application level caching via a X-Query-Cache header to provide caching. In that case you're basically serving from redis.

Our goal is to provide a SQL backend to everyone or device on the planet. To get that level of scalability and manageability (ie disk usage and cpu usage) you pretty much have to use an in process database. SQLite is the best there is as others in this thread have noted.

Full disclosure, I'm the founder of Lite Engine:

https://www.lite-engine.com

Sure. I think the warning about not using SQLite in production is more of a rule of thumb (and a good one) for those who don't really understand the tradeoffs they're making when using it. If one takes the time to both understand the needs of their application and the limits of SQLite, there's no reason not to use it in production where it is appropriate.

Moving to an entirely different database is a monumental task, so if you start with the right tool you won't have to deal with that

Getting a site from zero to 100K hits/day is also a fairly monumental task, and if you don't succeed at that, you don't have to move to a different database.

With Django it really isn't, as all of the differences are (in theory at least...) abstracted away behind a common API.

Thats the theory but in practice its not always so good.

I tried moving from from MySQL to Postgress. Somehow my unique constrains in Django weren't unique in the database so it threw errors when I tried dumping to Django fixtures.

Until you start usings database-specific types. JSONB, hstore, etc :) Django isn't really the limiting factor.

> Do people agree with this?

I don't. I've corrupted SQLite DBs enough to not have warm and fuzzy feelings about it like I used to have.

I think it's only a good choice when you just need a database for your app that will barely be using it, and if you didn't use it you'd be writing to a file instead. And, that's basically what the SQLite docs say.

However, even then, I think it can be short-sighted. I've used webapps before that used SQLite and I thought to myself: if they'd only used MySQL or PostgreSQL and then provided access to it, I could have used it.

Be aware though, if you decide to use a scalable DB like PostgreSQL, it will require a port to be open for the DB, even if only locally. If you're trying to minimize how people can access your data, you don't want a port open/an extra port open, and you're not going to hit it very hard, SQLite's probably your best choice.

OTOH, it is a Real DB, if a small one.

And corruptions, while obviously not unheard of, aren't very common. Even in power failure.

Yeah- I changed my wording to "scalable". And I appreciate the developers and community around SQLite. It has its uses, and I appreciate it. However, I think it could be better with concurrency.

It can have concurrent reads, and even concurrent read and write, but it doesn't support concurrent writes.

>it will require a port to be open for the DB, even if only locally

Surely it supports AF_UNIX sockets?

It does. The default PostgreSQL installation binds to port 5432 (I can't recall if that's loopback only, or global), but it can easily be disabled and use AF_UNIX sockets only.

I wasn't aware of that. Thanks!

Can you talk at all about how the SQlite databases got corrupted? I'd be interested to know the circumstances. (The answer 'I have no idea, it was just one of those things' is perfectly acceptable.)

No, I tried to run a site which much less traffic than this on sqlite. It threw database lock exceptions all the time. My writes must have been throwing it off? It was pretty frustrating, I should have just used Postgres from the start.

Still love sqlite for what it is though!

Did you use WAL mode?

You probably were holding write transactions open for the duration of the request/response cycle, which is guaranteed to get you in trouble.

One of the reasons I don't use sqlite in my projects is the lack of an easy unaccent solution, to make queries that automatically suppress accents. All my projects are in French, and if you want to make a search function in French you absolutely need that. Almost nobody will search for "éléphant" with the accents, especially now that Google, Facebook etc. do not require it.

Does your programming language or framework not offer an unaccent (aka ASCII fold) function?

I guess stored rows contain accents, not just query. So you run something along the lines of `unaccent(text) like unaccent(query)`; or, more realistically, you `create index on table(unaccent(text))`.

Oh I see. That makes sense.

The biggest limitation is it's complete lack of proper type support.

...Which is pretty hard to notice unless you really screw up. It's never been a problem for me, but I only work with SQLite infrequently. Does it come up often in your work?

I wouldn't say it's a frequent problem, but it's much more likely to be a problem than performance.

If you just look at its capability, yes, it can work great for a website.

However, sqlite is limited in what it can do, so when you need to go beyond what it can do then it can be a pain.

I'd say sqlite is a DB choice for people who understand what each DB option gives them. If you were new and needed a default choice, then pg, or mysql, or sqlserver are going to be pretty flexible long term. You also are going to get a lot more technical info on the web about how to use it with whatever web framework you have chosen. However, I have used it for websites where I have a pretty good idea about my data needs. works fine.

I use it more in the "competes with fopen" case though. Super great as a settings / info / persistence store

Reply on news.ycombinator.com