0

I'm familiar with the process of setting up Postgresql. And I'm also thinking of using SQLite for some of my websites for I don't need that much functionality that Postgresql offers. And I also want to avoid having a database demond working all the time in the background even when there's no need -- no requests to my database.

I think that SQLite will suit me better on some of my small websites. My questions are:

1) Is it worth it to use on relatively small websites?

2) On such websites, does it consume less resources of a server?

3) When in general is it recommended or ok to use SQLite in regards to websites or web services?

Improve this question
6
  • Please update your question to define what kind of small websites do you consider...
    – Basile Starynkevitch
    Dec 13, 2017 at 17:44
  • @BasileStarynkevitch, no.
    – Kakki
    Dec 13, 2017 at 18:36
  • Why not? IMHO "small websites" don't really mean much.... and your question is then too broad, unclear, and probably off-topic; and is likely to be closed soon
    – Basile Starynkevitch
    Dec 13, 2017 at 18:37
  • @BasileStarynkevitch, because I don't want to be that much precise.
    – Kakki
    Dec 14, 2017 at 2:52
  • Hence your question has been closed...
    – Basile Starynkevitch
    Dec 14, 2017 at 5:33

3 Answers 3

Reset to default
7

Sqlite is an in-process database, which means that its performance characteristic is going to be similar to a well optimized application that uses a file to store data.

In an IaaS or PaaS cluster, an Sqlite website can be infinitely scaled out extremely easily for small, read-only sites. Simply deploy a new application server with a copy of the sqlite file, and when the workload dissipates, simply dispose the server. Updating such site can be done by applying a simple rolling update to the server cluster. In this kind of scenario, and especially when the sites handles lots of complex read-only queries, sqlite can be much faster and simpler to scale than traditional database as there's no serialization/inter process communication involved in each query.

This also makes sqlite very well suited as a secondary database for a stateless application server to store the mostly static configuration, historical data, and pages that are rarely changed on a whim.

Sqlite can handle multiple writers very well, much better than most other in process database, but as a fundamental limit to in process database, all the writers must be in a single machine. This limits your application to write workloads that can be handled comfortably on a single machine. If your application has fairly small number of writers, sqlite can usually handle them well. While it is possible to deploy an sqlite site with the static file in a networked file system, networked filesystem often don't have correct implementation of more advanced filesystem features like flock, flush, or shared memory that are necessary to ensure transactional correctness and good performance, which makes deploying sqlite site with multi-machine writers on a networked filesystem a rather tricky business.

Most smaller and medium business web sites which acts simply as an online presence/business card, containing little more than some information about the company's people, product, and contact info and probably small contact form are very well suited to sqlite site. These sites typically have very low frequency of updates and a single server can comfortably handle 10k page views per day which is way more than enough for these type of sites, so simplicity of maintenance is a very important consideration. Sqlite can serve these scenarios very well, as you can update the site simply by copying the sqlite file over from development and backup the site by scp-ing the sqlite file to somewhere else.

Improve this answer
5
  • too difficult. I need a simpler answer to my question.
    – Kakki
    Dec 13, 2017 at 14:52
  • 3
    @Kakki: Your question is already answered on the SQLite site, here: sqlite.org/whentouse.html. However, if you need the short version, here it is: "SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite."
    – Robert Harvey
    Dec 13, 2017 at 17:33
  • @Kakki: there is no simple answer, except "it depends", since you did not give your definition of "small website"....
    – Basile Starynkevitch
    Dec 13, 2017 at 17:42
  • 4
    "Too difficult, need a simpler answer" - Thanks for the laugh.
    – jleach
    Dec 13, 2017 at 20:46
  • that's it, thx. and it can also handle the situation when several people/users are writing something to a db at the same time, right?
    – Kakki
    Dec 14, 2017 at 2:50
4

SQLite is an in-process database. If you are only ever going to have a single process running your website, then it is fine. If you ever need to have more than one process (and likely even more than one thread) writing to the same database, SQLite is not for you. It's that simple.

This basically means that you can only be handling one request at a time [1], which is probably only tens of humans on your site before they start thinking it is slow.

  1. Unless your operation is a pure read, i.e. no session tracing, no analytics, no logging, none of the many similar things that modern websites want to be doing
Improve this answer
4
  • 1
    The SQLite FAQ says that it supports multiple processes, and multiple simultaneous reads. If every request involves multiple writes to the database then it's not going to scale, but that's very different to "you can only be handling one request at a time".
    – Peter Taylor
    Dec 13, 2017 at 12:16
  • @PeterTaylor I have walked back the absolute, but a modern website will normally be doing a number of things that involve database writes, even for nominally "read" requests
    – Caleth
    Dec 13, 2017 at 12:27
  • 1
    I think that's quite dependent on other design decisions. E.g. most PHP sites handle sessions using a file per session in a dedicated directory, not using their SQL database; analytics is often done using third party (e.g. Google) tools which just require the page to include some JavaScript and work perfectly well with static sites; logging may be handled by Apache rather than by the web application software...
    – Peter Taylor
    Dec 13, 2017 at 12:34
  • 1
    Note that with a web framework that can do async IO, a single thread can still realistically serve 20–100 requests per second without special configuration when the requests are IO-bound. That's not “web-scale”, but it's good enough for small and medium sites. For a database that has rare updates (e.g. blog posts, user accounts), the limitation of one writing process is not a bottleneck. So there's a huge space where single process server + SQLite can be used, and this space likely extends well above your first 10k visitors per day.
    – amon
    Dec 13, 2017 at 14:13
1

I think that SQLite will suit me better on some of my small websites. My questions are:

Good thinking.

1) Is it worth it to use on relatively small websites?

Yes, see below why....

2) On such websites, does it consume less resources of a server?

Of course, since you don't need any PostGreSQL server (either a process running on the same machine, or a process running on a different machine).

3) When in general is it recommended or ok to use SQLite in regards to websites or web services?

First, if you think that your website might later have a big enough audience to require larger computing resources (in particular, running an RDBMS on some other machine, or load-balancing on several computers), you may want to start with PostGreSQL.

But you could design your web application to be more or less easily portable from SQLite to PostGreSQL. With care, that should be fairly easy; and you may want to think about such a migration (and document something about it), even if it would happen in a few years.

Notice that you'll probably need to dump your database in SQL format (at least for backup purposes). If the database is large enough (even on website having a few hits per day) -e.g. many gigabytes- that might be a concern (because it could be unpractical -perhaps too slow- to dump an SQLite database while your web application is frequently updating it). But it is probably not.

So you need to consider several factors: how would the web application be updated? how small is the website (in terms of traffic, in terms of data update rate, in terms of data volume)? How is it important to be able to migrate it (more or less easily) later to some RDBMS server?

See also this answer to a quite related question about SQLite database size.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.