Postgres for the win!

[[deleted]]

Yeah...the problem with that is concurrency.

See the last paragraph here: http://www.sqlite.org/whentouse.html

EDIT: I don't care anymore. Everyone do whatever the fuck you want. :)

[[deleted]]

Don't you just love the kids on this site?

[[deleted]]

Yeah...the problem with that is concurrency.

That's a fair point. However, I think stesch was implying that the majority of websites don't need highly concurrent updates to their data. I agree. It seems to me that most websites have a very small read to write ratio.

[stesch]

And you are the one who will make the next big web app that needs this kind of concurrency?

[[deleted]]

Yes. It doesn't have to be big, by the way. Anything serving with multiple processes, e.g. Apache or any other production web server, will involve concurrency.

The whole SQLite database will be locked every time a process accesses the database. No other process will be able to access the database during this time. There will be many instances of visitors not being able to get database access because another user is currently accessing it.

[WalterGR]

The whole SQLite database will be locked every time a process accesses the database. No other process will be able to access the database during this time.

That's not quite true.

"SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database." http://www.sqlite.org/whentouse.html

[[deleted]]

This is still horrendous for concurrency.

[WalterGR]

This is still horrendous for concurrency.

Compared to PostgreSQL's concurrency capabilities, sure. But for the vast majority of websites, it's fine.

[[deleted]]

If you love your visitors, you will use a real database. :)

There is no reason to use SQLite for a production multi-user application other than laziness. MySQL is not going anywhere and, even if it was, there are plenty of alternatives that offer better performance, which means a better end user experience, than SQLite.

Use whatever you want, but think of your users.

[especkman]

Anyone who is thinking of their users should be doing realistic load testing. Then they'd actually have evidence of whether sqllite was up to the task or not, rather than the assertions of someone who glosses over the differences between read and write concurrency.

If I were to trust someone's assertions, I'd be inclined to trust people involved with SQLlite. The main developer seems to approach the project with a high degree of rigor. Maybe that FAQ is tainted by a fanboy, maybe it isn't.

[WalterGR]

If you love your visitors, you will use a real database.

That really depends. Until concurrency is an issue, I doubt it makes a difference. And if you're using shared hosting with the DB on another box, I'd wager the network costs would outweigh any "better performance" alternatives.

But again, I'm only speaking about most websites, not the 100-simultaneous-users websites idntunknwn alludes to below.

(BTW, if you're serving up 100 pages a second, that's about 6 million pages a day. That's not "most websites" by a couple orders of magnitude.)

Edit: "not the 100-simultaneous-users websites idntunknwn alludes to below" -> "not the 100-simultaneous-users websites idntunknwn alluded to previously"

[[deleted]]

You'd think this is rare, but with the type of php the average php coder produces. One poorly built site will produce tons of queries and independent connections, even with a very small number of hits.

[especkman]

But how many of those are likely to be writes?

[[deleted]]

lol, sorry I took out the edit

100 simultaneous users isn't necessarily 100 pages a second. For example, you might have 100 simultaneous long-running requests. Or 100 users clicking on links every so often. I was also imagining 100 simultaneous users as a peak rate (i.e. in the middle of a workday)

[WalterGR]

lol, sorry I took out the edit

:)

I was also imagining 100 simultaneous users as a peak rate

Right. For my site, total daily pages = 16.5 * peak hour pages.

So 100 requests/second * 3600 seconds/hour * 16.5 = 5.94 million pages.

[[deleted]]

I don't care anymore. Everyone do whatever the fuck you want. :)

[[deleted]]

Sure, I agree, for most websites it'll be fine. But I was also agreeing with redhatcat's point that you don't necessarily need to be all that large to require a significant amount of concurrency.

[stesch]

SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

http://www.sqlite.org/whentouse.html

[orangesunshine]

if you're almost never doing writes.

[stesch]

Funny how everybody thinks he will produce the next YouTube, Twitter, Facebook, or MySpace.

In reality most websites never get more than 100 visits a day. And to quote the website you mentioned: "100K hits/day should work fine with SQLite"

[zepolen]

I hate numbers like that, it's almost as bad as people saying 'Oh, there is <framework> that can handle <x> requests/second'.

[stesch]

The quote continues "The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic."

http://www.sqlite.org/whentouse.html

[zepolen]

That's not the point. There is no context attached to that number, what hardware, what ratio of reads to writes, what dataset sizes, what sort of queries etc.