Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Appropriate Uses for SQLite

sqlite.org

Post reply on HN
Print view View original

31–39 of 39 posts

Re: Appropriate Uses for SQLite

#31

Post by tracker1 »

marcosdumay wrote: post #16
127001brewer wrote: post #4

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. "…

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…

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.

Re: Appropriate Uses for SQLite

#32

Post by mutagen »

pstadler wrote: post #6

"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 artic…

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.

Re: Appropriate Uses for SQLite

#33

Post by tracker1 »

rockdoe wrote: post #18
SchizoDuckie wrote: post #3

Earlier quoted context omitted.

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 Now I really hope that Spartan can implement WebSQL as well...

Now I really hope that Spartan can implement WebSQL as well... Sounds unlikely given that the standard was superseded by IndexedDB. http://www.html5rocks.com/en/tutorials/webdatabase/websql-in... Most browsers just use SQLite to back IndexedDB, though.

Which is kind of sad... I get the objections to "WebSQL" as it kind of lacked a standard to the support/syntax. But given that it was SQLite (v3 iirc) everywhere, it should have just specified SQLite v3's interface, and MS could have fallen into line with Firefox, Chrome and Safari.

IndexedDB is a better fit for no-sql style use in the browser, but sometimes you really need something closer to SQL.

Re: Appropriate Uses for SQLite

#34

Post by oliwarner »

127001brewer wrote: post #4

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. "…

Yeah, I use it for fair-traffic CMS sites and even webapps. The trick is exactly the same as any other RDBMS: cache everything as much as possible.

Django makes this really easy (it's the default). It's a shame other projects aren't on flexible ORMs. I'd love to be able to deploy WordPress and Drupal sites without dicking around creating databases.

Re: Appropriate Uses for SQLite

#36

Post by marcosdumay »

tracker1 wrote: post #31
marcosdumay wrote: post #16

Earlier quoted context omitted.

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…

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 expec…

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.

Re: Appropriate Uses for SQLite

#37

Post by baconner »

k2enemy wrote: post #28
baconner wrote: post #21

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?

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.

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

Re: Appropriate Uses for SQLite

#38

Post by tracker1 »

marcosdumay wrote: post #36
tracker1 wrote: post #31

Earlier quoted context omitted.

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 expec…

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.

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.

Re: Appropriate Uses for SQLite

#39

Post by rockdoe »

tracker1 wrote: post #33
rockdoe wrote: post #18

Earlier quoted context omitted.

Now I really hope that Spartan can implement WebSQL as well... Sounds unlikely given that the standard was superseded by IndexedDB. http://www.html5rocks.com/en/tutorials/webdatabase/websql-in... Most browsers just use SQLite to back IndexedDB, though.

Which is kind of sad... I get the objections to "WebSQL" as it kind of lacked a standard to the support/syntax. But given that it was SQLite (v3 iirc) everywhere, it should have just specified SQLite v3's interface, and MS could have fallen into line with Firefox, Chrome and Safari. IndexedDB is a better fit for no-sql style use in the browser, but sometimes you really need something closer to SQL.

That would have meant everyone would've been stuck with SQLite3's quirks forever. It may be good but it's not that good!
Post reply on HN