I really want to read it to learn the answer to the following:
why isn't sqlite able to handle full scale loads? Why do I have to swap it out? Can't someone change the implementation details so it can work at scale and leave the interface the same?
Where is sqlheavy?
From https://www.sqlite.org/about.html :
"SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine"
Zero-configuration is actually quite an onerous design requirement, as it immediately eliminates most features which shouldn't be on by default. It also eliminates tuning.
Serverless imposes most of the rest of the performance penalty. If you're accessing a sqlite database from a single process, it will most likely be limited by your storage speed. But if you're writing to it from multiple processes, lock contention will quickly become an issue. The obvious technique to improve lock contention is to separate the tables so they can be locked separately, but then you lose the single-file convenience.
That's why that page also says "Think of SQLite not as a replacement for Oracle but as a replacement for fopen()".
It also makes for a stupidly easy to use product. SQLite easily outperforms Postgres in default settings and any "optimized" snippets blog posts cough up for the workload generated by a project I'm working on.
I'm seriously tempted to drop support for non-sqlite DB backends, since it's unlikely that the majority our potential customers (SMB environment) could do a better job than that at configuring database servers.
True.
Point is, most software has only the need for fopen(), but instead uses something like Oracle.
Not quite, SQLite can be tuned using pragma's, https://www.sqlite.org/pragma.html which covers most of your tuning needs.
> (3) SQLite lets me insert a string into a database column of type integer!
> This is a feature, not a bug. SQLite uses dynamic typing. It does not enforce data type constraints. Data of any type can (usually) be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)
> But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is called type affinity.
I did not know that.
I am... incredibly horrified.
The code I wrote has already told the machine what I want it to do. I don't want to be involved in figuring out how to make the DB work on a larger load.
Being serverless isn't a feature for me. Zeo-configuration is. Can't it handle the server stuff without asking me to type more lines of code? Maybe on heroku?
You're basically bashing the Smartcar because it can't pull your 5th wheel heavy trailer.
I don't think its a failure.
> I don't want to be involved in figuring out how to make the DB work on a larger load.
Sqlite definitely wasn't made to fulfill that need of yours. Why do you think it's supposed to give you that?
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 ?
> 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.
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).
In postgresql, open a transaction, and run each test in a subtransaction, then abort the 'top' transaction. This should be ~fastest.
It actually can, it's just not ideal and leads to a lot of lock contention.
And doesn't a WAL help with that?
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.
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.
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.
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.
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.
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...
SQLite can easily scale out to read workloads that can consume an entire (large) machine, especially when using its mmap'd IO modes.
However, it doesn't have good concurrency for writers and concurrency for readers also drops when any writer is large enough to spill the cache.
Writer concurrency can be managed down by building a custom write pipeline in front of it but that is not trivial. ...and, when you do that, you then have to manage your own consistency and Read/Modify/Write hazards so you're no better off than any of the first generation NoSQL stores. If you think about your data model up front then that might be OK, otherwise it's a very difficult road.
An important other difference between SQLite and other database engines (RDBMS or otherwise) is that SQLite is in-process rather than client/server.
This has a lot of implications for the latency costs associated with data access. In particular, SQLite allows you to mostly ignore query round-trip times: because there aren't any. If you change your engine to something that's not in-process you suddenly start having to think about batching and prefetching for things that might initially look like a simple point query. This can have a serious performance impact and result in profound refactoring requirements for your app. Things that were previously running at several 10s of thousands of point queries per second and complete in nanoseconds now require 10s of thousands of sockets and take 10s of milliseconds.
For my part, I've built production web apps with lots of users on SQLite and the database engine was the smallest part of our performance work. Reducing garbage generation in the render pipeline and moving query generation out of the request path were where we got most of our performance gains.
The goal of sqlite is to be easily embeddable: that means not too much code, not too much resources used. It serves that purpose well: it's widely used in a lot of programs where you never even thought that they could have it.
https://www.sqlite.org/mostdeployed.html
So the goal was never to suit your "full scale loads" the way you probably define them. But I don't know what you actually understand under the "full scale loads" and once you state what you would exactly expect to achieve it can be discussed more. Until then, simply stated, there is a reason why the big server software is big and resource hungry, unlike sqlite.
The same reason an Access database doesn't scale. Because it's not designed to.
It doesn't have a complex query optimizer. It isn't designed to use tremendous amounts of memory to optimize execution speeds with a well-managed cache. It isn't designed to be clustered or sharded or partitioned or replicated. It doesn't have GIS or geometry functions. It doesn't support point-in-time recovery. It doesn't support many ANSI SQL functions and features. It's capable of being thread safe, but limited to a single process.
The real limitation that people tend to run into, however, is that writing to the database locks the whole database. You can switch to WAL mode which allows reading while writing, but you never get more than one writer and by default writing blocks reading. In other RDBMSs, you have table locks, page locks, or row locks. You can have multiple writers going on in multiple connections managed by multiple processes with multiple threads, and reading is (in general) not blocked.
Furthermore, databases for high load applications often support multiple, concurrent connections from multiple discrete applications. You might have 3 different web servers all connecting to your SQL servers in the background. You might have a main application and a reporting application. You may need to ETL data from the database to another database for another purpose. You can't do any of that with SQLite.
Hell, I support an app that has six different application servers that connect to it (two main app servers, one secondary app server, two "task" servers that run long execution processes, and a reporting server). And that's just the app itself. There's about a dozen ETL tasks that run against the DB for other applications.
Sure, there are some third party add-ons that can do some of these things (SQLitereplica, SQLitening) but I've never seen anybody implement them instead of moving to a heavier RDBMS, which is likely to be better supported.
> Can't someone change the implementation details so it can work at scale and leave the interface the same?
They could, but then it wouldn't be a lightweight RDBMS anymore. Making a system scale takes more complexity, and therefore more code.
> Where is sqlheavy?
Oracle, MySQL, PostgreSQL, MS SQL Server, DB2, Firebird, Cassandra, MongoDB, etc. With varying degrees of heaviness.
Is this a file system issue (because only one file is used) or a SQLite problem? Like, file systems only allow one writer to a file, even if they would write at different locations.
http://webcache.googleusercontent.com/search?q=cache:21EcxYD...
Some of advice within should be taken with a grain of salt. Exiting function as early as possible might be OK for you, but it goes against some coding standards that dictate only single return statement per function.
Secondly, sqlite is an embedded database and doesn't have a separate server process.