7 ms·

You mean heavy concurrent writes? I have no special insight in SQLite, but I think because it would need to be multiprocess, which it cannot as an embedded lib

You mean heavy concurrent writes? I have no special insight in SQLite, but I think because it would need to be multiprocess, which it cannot as an embedded library. Have you read https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html ? > Situations Where A Client/Server RDBMS May Work Bette > • High-volume Websites > • High Concurrency Anecdotal Observation: I like to use SQLite when running tests etc... because it's easy to make the data make sense, and easy to ship the resulting file, but recently I had to run a high number of concurrent writes and it slowed down my tests and made my laptop unusable. I re-wired my scripts to use PostgreSQL and it moved the bottleneck to what I was actually trying to test, and even though my disk led was blinking like crazy, my laptop was still usable. I checked what was going on and saw that postgres had spawn a whole bunch of processes... On other workload, especially with low-concurrency, I have found SQLite actually performing really well, and comparably to postgres. You do have to tweek a few things depending on your workload, but there are a lot of very informative blogs and SO pages on this subject out there.

projectramo 10y ago
Yes. But the whole point of sqlite is that i don't have to type in more code. Can't it just do all that for me? I should add that I know it is a minimal lightweight database, and that there are other ones that are much more feature rich and more complicated.
- dorfsmay 10y ago
  No, the whole point of sqlite is to be able to use a relational data model instead of a text file. It sounds like you are needing a Relational Database server. PosgreSQL, DB2 and Oracle aren't that difficult to install on a laptop. I highly recommend using the same product in dev and prod.
- mikeash 10y ago
  I think you're looking at the wrong layer. SQLite is a specific database implementation, there's no reason to expect it to provide a wrapper around some other implementation too. One potential problem is that not all databases are created equal. SQLite may support features that other databases don't (or vice versa), or they may both support the same feature but with different syntax, and so code that works with one backend may not work with another. In any case, it sounds like you're after some sort of wrapper that can provide access to different database backends without your code needing to know or care what the current backend is. Something like ODBC would seem to fit the bill, and there is a SQLite driver for it.
- jimbokun 10y ago
  You keep asking this and keep getting the same answer. Why do you expect the answer to change if you ask more times?
  - projectramo 10y ago
    Because the answer is not the answer to the question I asked, so if you think it is, I didn't ask it the right way. If I ask: -- why can't sqlite just scale for me. And you say: -- Well, its meant to be lightweight. There are other more powerful databases, or you can add more code to sqlite to make it scale. Its not that I didn't know all those things. Those were the premise to the question I asked.
    - Kalium 10y ago
      The things that make a database scale are fundamentally at odds with the things that make a database zero-configuration. You can get one or the other. The thing you want, a zero-configuration self-tuning automagical smart RDBMS, is not considered feasible. There. An answer to the question you asked. Incidentally, if you think that's unsatisfactory answer, I remind you that you're on a website linked to a startup accelerator.
    - dorfsmay 10y ago
      > why can't sqlite just scale for me. Because it's not a client/server implementation, it's a single embedded library, and because part of the requirements were to use a single data file. Therefore, each process (all completely independent) accessing that file: -needs to lock the entire file (rather than tables/rows) -needs to flush and close the file after each write -cannot prioritize I/O across processes, one file open the file, read or write, close the file (as opposed to do all the writes at once for example) -cannot cache very effectively, every time a process writes, all the other processes have to re-load the file etc...
monocasa 10y ago
> need to be multiprocess, which it cannot as an embedded library It actually can, it's just not ideal and leads to a lot of lock contention.
- k__ 10y ago
  Isn't this just an issue if there are too much writes? And doesn't a WAL help with that?
ddorian43 10y ago
Run the db in-memory when doing tests with sqlite. In postgresql, open a transaction, and run each test in a subtransaction, then abort the 'top' transaction. This should be ~fastest.
- dorfsmay 10y ago
  One of the reason I use a DB is to be able to recover most of the data in case of a crash, met disconnection etc... So memory isn't good for me here.
  - ddorian43 10y ago
    I meant only for testing.
dfox 10y ago
There is no reason why you cannot do concurrent transactional writes in embedded database library and there are many embedded databases that can do that. MySQL is probably only opensource client-server RDBMS whose server is based on relatively tightly coupled threads. PostgreSQL uses separate processes with well defined IPC interface between them, for long time the postmaster process mostly served to initialize and teardown this IPC interface (apart from spawning postgres processes for incomming connections), which was mostly needed because SysV IPC is brain-dead, hacking PostgreSQL into embedded library is probably not advisable, but perfectly doable. Firebird's architecture is similar, but in addition explicitly supports use as embedded library and stores whole database in single file, that can be used by both server daemon and embedded library at the same time (and the file can even reside on windows share without compromising transactional semantics). Main problem with doing fine grained locking in embedded library (apart from complexity of fine grained locking itself) is with various non-standard situations like crash recovery. Another problem is that when processes that access the database data run under different users, there is real possibility of data corruption caused by wrong permissions or some similar misconfiguration (perfect example of this were first versions of subversion with Berkeley DB backend).

projectramo 10y ago

Yes. But the whole point of sqlite is that i don't have to type in more code. Can't it just do all that for me? I should add that I know it is a minimal lightweight database, and that there are other ones that are much more feature rich and more complicated.

dorfsmay 10y ago
No, the whole point of sqlite is to be able to use a relational data model instead of a text file. It sounds like you are needing a Relational Database server. PosgreSQL, DB2 and Oracle aren't that difficult to install on a laptop. I highly recommend using the same product in dev and prod.
mikeash 10y ago
I think you're looking at the wrong layer. SQLite is a specific database implementation, there's no reason to expect it to provide a wrapper around some other implementation too. One potential problem is that not all databases are created equal. SQLite may support features that other databases don't (or vice versa), or they may both support the same feature but with different syntax, and so code that works with one backend may not work with another. In any case, it sounds like you're after some sort of wrapper that can provide access to different database backends without your code needing to know or care what the current backend is. Something like ODBC would seem to fit the bill, and there is a SQLite driver for it.
jimbokun 10y ago
You keep asking this and keep getting the same answer. Why do you expect the answer to change if you ask more times?
- projectramo 10y ago
  Because the answer is not the answer to the question I asked, so if you think it is, I didn't ask it the right way. If I ask: -- why can't sqlite just scale for me. And you say: -- Well, its meant to be lightweight. There are other more powerful databases, or you can add more code to sqlite to make it scale. Its not that I didn't know all those things. Those were the premise to the question I asked.
  - Kalium 10y ago
    The things that make a database scale are fundamentally at odds with the things that make a database zero-configuration. You can get one or the other. The thing you want, a zero-configuration self-tuning automagical smart RDBMS, is not considered feasible. There. An answer to the question you asked. Incidentally, if you think that's unsatisfactory answer, I remind you that you're on a website linked to a startup accelerator.
  - dorfsmay 10y ago
    > why can't sqlite just scale for me. Because it's not a client/server implementation, it's a single embedded library, and because part of the requirements were to use a single data file. Therefore, each process (all completely independent) accessing that file: -needs to lock the entire file (rather than tables/rows) -needs to flush and close the file after each write -cannot prioritize I/O across processes, one file open the file, read or write, close the file (as opposed to do all the writes at once for example) -cannot cache very effectively, every time a process writes, all the other processes have to re-load the file etc...

monocasa 10y ago

> need to be multiprocess, which it cannot as an embedded library It actually can, it's just not ideal and leads to a lot of lock contention.

k__ 10y ago
Isn't this just an issue if there are too much writes? And doesn't a WAL help with that?

ddorian43 10y ago

Run the db in-memory when doing tests with sqlite. In postgresql, open a transaction, and run each test in a subtransaction, then abort the 'top' transaction. This should be ~fastest.

dorfsmay 10y ago
One of the reason I use a DB is to be able to recover most of the data in case of a crash, met disconnection etc... So memory isn't good for me here.
- ddorian43 10y ago
  I meant only for testing.

dfox 10y ago

There is no reason why you cannot do concurrent transactional writes in embedded database library and there are many embedded databases that can do that. MySQL is probably only opensource client-server RDBMS whose server is based on relatively tightly coupled threads. PostgreSQL uses separate processes with well defined IPC interface between them, for long time the postmaster process mostly served to initialize and teardown this IPC interface (apart from spawning postgres processes for incomming connections), which was mostly needed because SysV IPC is brain-dead, hacking PostgreSQL into embedded library is probably not advisable, but perfectly doable. Firebird's architecture is similar, but in addition explicitly supports use as embedded library and stores whole database in single file, that can be used by both server daemon and embedded library at the same time (and the file can even reside on windows share without compromising transactional semantics). Main problem with doing fine grained locking in embedded library (apart from complexity of fine grained locking itself) is with various non-standard situations like crash recovery. Another problem is that when processes that access the database data run under different users, there is real possibility of data corruption caused by wrong permissions or some similar misconfiguration (perfect example of this were first versions of subversion with Berkeley DB backend).