0

I was wondering if it's good practice to have multiple Sqlite databases? My thinking is that I would keep all my relational data in one database and have non-relational data (data that are stored in tables with no-relationship with other tables) in separate Sqlite databases to reduce the amount of concurrency that each database encounters. Would this be a good idea?

Note: There isn't much non-relational data to store. To give an idea, maybe up to 10 records per table for the non-relational data. I'm just using it for configuration data within the application. One of the non-relational tables might have up to 10000 records.

Besides the configuration data, I use multiple processes because the front-end calls a backend process to retrieve non-relatable data from a Table to update the user on what's happening close to real-time, while I have a separate process that's doing a specific task that may update that same table

Zac Rougeau
  • 19
  • 1
  • 4
  • 1
    Not really. SQLite simply isn't a multi-user database. If you start it in WAL mode you can have multiple *writers*, but readers will read stale data until the database engine catches up. By splitting up one database into several you increase the chance of inconsistent data – Panagiotis Kanavos Jun 08 '23 at 14:00
  • 1
    `reduce the amount of concurrency that each database encounters` what concurrency do you refer to? What's the actual problem you want to solve and why do you think it involves concurrency? – Panagiotis Kanavos Jun 08 '23 at 14:02
  • how many users do you have? how many records -- just give us an idea of the scale. – Barry the Platipus Jun 08 '23 at 14:12
  • I have concurrency when I have two separate backend processes access the same database. I figured there might be less database requests coming from two or more processes if I split the nonrelational data into separate databases. – Zac Rougeau Jun 08 '23 at 14:12
  • I think your judgement on this is sound, I'm just not sure it's needed for 2 processes only -- that's why I asked you to give us a sense of user count, and rows count – Barry the Platipus Jun 08 '23 at 14:16
  • Thanks for your feedback. Besides the configuration data, I use multiple processes because the front-end calls a backend process to retrieve non-relatable data from a Table to update the user on what's happening close to real-time, while I have a separate process that's doing a specific task that may update that same table. – Zac Rougeau Jun 08 '23 at 14:19
  • You're asking for trouble. SQLite is a *simple* database, meant to be used by only one user at a time. Typically it's only used by a single *thread* at a time. The fact you can use WAL to avoid outright exceptions doesn't mean it's now a multi-user database. You definitely *don't* get real-time data as the "other" application will have to wait for your application to commit logged changes, even though your write code has moved on. That delay can be several seconds if not more – Panagiotis Kanavos Jun 08 '23 at 14:26
  • Sorry, I should of specified, close to real-time data. It doesn't have to be accurate but close enough to update the user a few seconds if something goes wrong. Sorry about the confusion. The sqlite library I used is synchronous so I believe it waits for the results from the database before moving to the next line of code. – Zac Rougeau Jun 08 '23 at 14:29

0 Answers0