CODEWITHSUNDEEP
sqlite
Dan Goodspeed
Dan Goodspeed

Reputation: 3580

Should I make multiple SQLite databases for better concurrency?

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!

Upvotes: 4

Views: 3651

Answers (1)

mvp
mvp

Reputation: 116357

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.

Upvotes: 1

Related Questions