7 ms·
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 fewe
by Lxr 10y ago
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/ https://docs.djangoproject.com/en/1.10/intro/tutorial02/
- keithnz 10y agoIf 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
- bpicolo 10y agoMoving 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
- nostrademons 10y agoGetting 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.
- TylerE 10y agoWith Django it really isn't, as all of the differences are (in theory at least...) abstracted away behind a common API.
- collyw 10y agoThats 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.
- bpicolo 10y agoUntil you start usings database-specific types. JSONB, hstore, etc :) Django isn't really the limiting factor.
- TylerE 10y agoThe biggest limitation is it's complete lack of proper type support.
- qwertyuiop924 10y ago...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?
- technomancy 10y agoI wouldn't say it's a frequent problem, but it's much more likely to be a problem than performance.
- hug 10y agoThe 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.
- pbhjpbhj 10y ago>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?
- watermoose 10y ago> 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.
- qwertyuiop924 10y agoOTOH, it is a Real DB, if a small one. And corruptions, while obviously not unheard of, aren't very common. Even in power failure.
- watermoose 10y agoYeah- 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.
- qwertyuiop924 10y agoIt can have concurrent reads, and even concurrent read and write, but it doesn't support concurrent writes.
- mappu 10y ago>it will require a port to be open for the DB, even if only locally Surely it supports AF_UNIX sockets?
- 10y ago
- blihp 10y agoSure. 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.
- ufmace 10y agoI'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.
- jstimpfle 10y agoThe 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.
- audleman 10y agoNo, 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!
- Biganon 10y agoOne 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.
- majewsky 10y agoDoes your programming language or framework not offer an unaccent (aka ASCII fold) function?
- elmigranto 10y agoI 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))`.
- majewsky 10y agoOh I see. That makes sense.
- marktangotango 10y agoWe 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 https://www.lite-engine.com