4

I'm very new to SQL and relational databases (just started learning last week) and I'm in the process of upgrading my website and currently keep all my data in XML files. It works, but the new site would be better suited from what I hear a relational database can do, and it looks like SQLite is best for me. One of my concerns is concurrency, even though 99% of the data will be read-only (which I understand SQLite is pretty good at) 99% of the time. Other things, like page view counters for certain pages will constantly require small writes. I'm still learning database design and want to do it right. Would it make sense to make separate databases for things that get written to a lot, that way making the main database far less susceptible to concurrency issues? Is it possible to do a "foreign key" type reference (I still haven't used foreign keys yet, but think I understand them) across databases? As each view count would point to some primary key in the main database. Thanks for any help!

Dan Goodspeed
  • 3,484
  • 4
  • 26
  • 35
  • The answer you got below was more focused on whether SQLite is suited or not for web development. Now 10 years later, what was your experience? Were there improvements in concurrency splitting up a SQLite database into multiple? – ecoe Aug 29 '22 at 01:51

1 Answers1

1

SQLite is good to use in embedded systems (like mobile phones and tablets) and small desktop applications (Chrome, Firefox, Thunderbird, etc). However, when you need to have many concurrent readers and writers (typical for websites), you should not use it.

Even if you split your data in many databases, it has a lot of operational overhead. For example, it will be difficult to join data from different databases - you must use ATTACH, and by default you can only ATTACH up to 10 databases. And concurrency issues will still not go away 100%.

Instead, use real database like PostgreSQL or MySQL. Not only it will be faster, these databases provide real concurrent access to your data over the network, which SQLite cannot do.

My personal preference is PostgreSQL, but if your web hosting does not provide PostgreSQL, you can use MySQL, but then please use fully transactional engine like InnoDB.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • I don't really have many concurrent users. The [SQLite website](http://www.sqlite.org/whentouse.html) says that it can easily handle 100,000 hits/day. I'd be surprised if I ever hit 50,000. [This guy goes into detail](http://www.bookgoldmine.com/Blog/powered-by-sqlite/5) about how good SQLite is for his web server, which seems to get similar traffic to my site. He also points out that SQLite can be better than "real databases" on shared servers like mine. Then [this guy](http://stackoverflow.com/questions/14217249/sqlite-and-concurrency/17132843#17132843) sold SQLite for me. – Dan Goodspeed Sep 01 '13 at 06:49
  • Don't get me wrong - [I love SQLite for what it does](http://stackoverflow.com/a/14700226/1734130). However, for me, biggest issue with SQLite for web servers is inability to access data over network. For example, I could access my host data from my desktop box using SQL tool like PgAdmin3 or MySQL Workbench. You cannot do that with SQLite without copying whole database, or accessing it locally. – mvp Sep 01 '13 at 06:57
  • As far as the question itself... you say it's difficult to join data from different databases. Why do I need to join them? I think they'd work just fine as separate databases. There may be some redundancy with the key names if I can't use foreign keys, but that's not too big of a deal. I have crazy amounts of redundancy with my current XML set up. I'd say 99.9% of the writes to the database will be page view counts. – Dan Goodspeed Sep 01 '13 at 06:58
  • If I separate them from the main database, and have one for article view counts, and one for message board post view counts, etc etc... that should cut the need to write to the main database by 99%, and each of the "view count" databases would only be read/written to maybe 10% of the amount of saving everything in one big database... increasing my concurrency ability by like 10x. If I'm understanding it correctly. – Dan Goodspeed Sep 01 '13 at 06:58
  • It is extremely rare that you can totally separate your data into self-contained "islands". Almost always you need to join data. For example, if you have users, articles, comments, etc - you want to join them, just to know which user article or comments belongs to, etc. – mvp Sep 01 '13 at 07:01
  • To reply to your comment- I use [phpLiteAdmin](https://code.google.com/p/phpliteadmin/) to access my data over the web. I just installed it so I don't have much experience with it, but it came highly recommended, and it's working fine for me so far. – Dan Goodspeed Sep 01 '13 at 07:01
  • Again - it _may_ work for you. But, you will be better off using full-fledged database now. – mvp Sep 01 '13 at 07:03
  • And yes, users, articles, comments... they would all be in the same single main database that doesn't get updated all that often. On my current site, the last comment posted was two weeks ago. When the new site goes live, I expect a handful of comments every day, but nothing that would make me concerned about concurrency... except for those view counts which would be in the hundreds every day. – Dan Goodspeed Sep 01 '13 at 07:04
  • Are you able to find any articles where SQLite didn't work? Everyone who says not to use SQLite on their web server, don't use it on their web server. But everyone who does use it on their web server, says it's great. I tend to value the opinion of those actually using it more than those who aren't. – Dan Goodspeed Sep 01 '13 at 07:06
  • I DO use SQLite a LOT in my work, and love it. But, again, I DO NOT recommend using it on website. Reasons are many, I just warn you that if you go live, you will have to reconsider. – mvp Sep 01 '13 at 07:09
  • Appreciated. I just really like the file-based convenience. It's closer to the XML-based system I've used for the past 10 years. And when I FTP in and download a copy of my web directory, my whole database will come with it. I did some research and it doesn't look _that_ difficult to move to MySQL (especially with PHP's PDO) if I feel it's needed later on. I'm just trying to make sure I'm doing it in the way that's best for my site and get some input as to whether or not it's a good idea to split the database. – Dan Goodspeed Sep 01 '13 at 07:13