

Appropriate Uses for SQLite - vince_refiti
https://www.sqlite.org/whentouse.html

======
pstadler

      "The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course,
      and as of this writing (2015) it handles about 400K to 500K HTTP requests
      per day, about 15-20% of which are dynamic pages touching the database.
      Each dynamic page does roughly 200 SQL statements. [...]"
    

Wait... What? They're saying that their dynamic pages are executing 200 SQL
statements per request?

Off-topic: I enjoyed reading this article which was posted here a while ago
[http://www.sqlite.org/testing.html](http://www.sqlite.org/testing.html)

~~~
mutagen
200+ queries for a simple dynamic page doesn't surprise me. Once you add some
dynamic features it's easy to start sticking all kinds of stuff in the
database. Let me check some sites I run:

    
    
      Medium complexity Drupal 6 site: 200-300 queries
      Wordpress site with accretion of plugins over the years: 100+ queries
      Drupal 7 site: 100+ queries
    

Those totals might be inflated due to having administrative menus active.

Many of these are key/value lookups which can be accelerated by serving from
Memcached or Redis, although I imagine SQLite dishes them out pretty quickly
as well.

------
127001brewer
_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._

I am curious to know how many people here solely use SQLite to power the back-
end of their web application(s), especially when the page states, " _SQLite
does not compete with client /server databases._"

(Or is the page referring to content-management-system-type websites?)

~~~
marcosdumay
On work, my area has a conflicting relationship with IT, thus we are often
required to use unusual setups...

Well, we've tried distributing applications that needed to share a DB backend.
We tried a MS Access backend first, but it stopped working after about 5
people were using it. Then we migrated to SQLite, it handled well up to near
50 people, then its over-restrictive locking become a problem. Luckly by that
time we got hold of an Postgres server.

Low to medium traffic websites don't usualy have much more than 10 working
threads, so yes, I've used a lousely similar setup, and it worked. It'll
depend on how much time is spent on DB access vs. local processing, and how
fast the DB is accessed by those servers, so YMMV.

~~~
tracker1
It's worth noting that you can split your data among multiple database files
(effectively on-disk sharding) to alleviate contention... in-memory record
caching and mostly read scenarios will also reduce contention.

There's a _LOT_ you can do with SQLite... not to mention that with simple read
caching and reduced writes you can get a lot of performance out of SQLite.
Highly interactive web applications, I wouldn't expect to handle more than 50
users on a single system backed by SQLite, as you mention... with SSD, you may
get a couple more.

~~~
marcosdumay
Yes, we start splitting the data at one point. But don't take those 50 users
limit too seriously, it was more a consequence of bad networking
infrastructure than of SQLite.

I have no idea how far SQLite would go on a nice network with a sane file
sharing protocol.

~~~
tracker1
If you aren't having to do many writes, it will fly for thousands of users...
when you have to do a lot of writes, then it will slow to a crawl. I've seen
distributed Access based database apps that handled several hundred
simultaneous users before.

------
heyalexej
I was fooled by the name for a long time. "SQLite? That's what you use to
store 160 phone book entries or something." How wrong I was.

~~~
SchizoDuckie
I'm using WebSQL / Sqlite to store TV show data on the user's system. works
like a charm, databases regularly grow to 10+mb, _never_ had a problem with
crashes, speed or anything like it. Plus, everything runs locally <3

Now I really hope that Spartan can implement WebSQL as well...

~~~
bengali3
I look forward to using SQLite again. In 2010ish I used it for an airport and
aircraft database to be installed with our iOS app for 'find near you'
feature. lookup performance was perfect. but did have some performance snags
on application start, a server call retrieved any DB records needing to be
updated. I found SQLite update statements were slow (10mb DB of 10,000
records, less than 100 updates took 30s on first gen iphones), so instead of
retrieving the records that were changed, we modified it to retrieve the
latest 3mb (compressed) of the 10mb DB from the server, decompress and replace
the single file which turned out to be simpler, more atomic and required much
less code.

I really enjoyed the simplicity of it all.

~~~
trevyn
Actually, all you needed to do was wrap that batch update in a transaction; by
default, SQLite autocommits and writes to disk after each update/insert, which
is a bit slow.

------
AdrianRossouw
There's also mbtiles, which is a standard way of storing map tiles in an
sqlite database in blob fields.

[https://www.mapbox.com/guides/an-open-platform/#storing-
tile...](https://www.mapbox.com/guides/an-open-platform/#storing-tiles)

you can have sqlite databases that are gigs in size and perform great.

------
baconner
Can anybody speak to their experience using sqlite for data analysis purposes?

Am I wrong in thinking it's just plain old row oriented storage and not
something more aggregate oriented?

~~~
k2enemy
I use sqlite to store all of my small datasets. Minimally, I think of it as a
replacement for zipped CSV files. But it also has the added benefit of a
relational structure and SQL.

It is super easy to access from julia, R, python, etc, so instead of importing
a CSV and manipulating the data, I find it a lot easier to connect to the
sqlite database and use SQL for the a lot of the joining and manipulating.

~~~
baconner
I see. So the benefit is mainly in having more relational structure than in
actually crunching numbers. Makes sense.

------
zachrose
Neat. I've also used an in-memory SQLite db for testing ActiveRecord models.

Also, the same list if uses can be applied to nedb as a lightweight
alternative to MongoDB.

------
mike_r_parsons
Don't get mislead by the "lite" in sqlite. I can do about 200k sql requests
per second with a sqlite inmemory db and node.js

------
tootie
How does SQLite handle replication? Can I have 10 app server nodes reading
from one SQLite DB? NFS?

~~~
lukaslalinsky
I'm not sure if SQLite writes over NFS work, I wouldn't trust it. Previously I
have done SQLite replication by simply rsyncing the database to application
servers at certain times.

~~~
vamin
Writes definitely work, but concurrent writes will hose the database. SQLite
relies on filesystem locking (which NFS does not handle properly) to handle
write concurrency.

------
micro-ram
sqlite.org SSL Cert just expired

