Long version: I couldn't get bulk insert performance above absolutely miserable levels. I tried tricks like deleting and recreating indices but without luck. The perf tooling wasn't there to quickly figure out where the problem was (this was 10 years ago, not sure if things have improved) so I wound up building a version of SQLite with debug symbols and profiling it with a C profiler. The problem turned out to be a default setting that made spill-to-disk very aggressive and basically guaranteed that any workflow like mine would grind along with miserable slowness and no outward indication of what to do about it. I found an email thread where someone in effectively the same situation made some constructive suggestions and got turned away on the principle that even casual users ought to just know performance knobs like this one. Yikes. I am probably munging some of the details, but it made me angry enough to learn postgres and port my code over despite having a fix for my immediate problem.
I'm surprised more people don't use transactions in sqlite given transactions are a staple of using any enterprise RDBMS.
That's just my experience so I guess it may be meaningless but I'm surprised to hear it may not be often used.
Consider yourself lucky then. I know of a place that doesn’t use transactions in a homegrown ERP solution, of all things.
Suppose T1 has a 1-many relationship with T2. Declare in your assumptions that any rows in T2 with no corresponding valid row in T1 are not valid.
Additionally, have an is_valid field on T1 so selecting all valid data from T2 is done with "select * from T2 inner join T1 on T2.t1id = T1.id where T1.is_valid".
To insert data, insert a row into T1 first but initially have is_valid be false. Then insert all necessary data into T2. Finally, do an update and change the original row in T1 to have is_valid be true.
For deletions to T1, just do an update and set is_valid to false. Thanks to the validity logic, this has the effect of also invalidating all T2 rows.
Updates are trickier, but you can allow them to work without transactions by having two ids for for every table. The first id is the one we worked with before which is used for joins. The second id is used by applications to look for explicit records. Therefore, just never do any updates aside from the one setting is_valid to true (which is really storage logic and not application logic). Instead, just insert a new row into T1 whenever you want to update something in T1. The final update now just needs to flip the is_valid bit for the old row and the new row and will also need to verify that the old row is valid as well as any other rows the current update relies on (basically need to turn it into complex CAS).
All of this is pretty messy, but it does let you have CRUD without any transaction support from your DB. Also, even if your DB has transactions, this scheme has the advantage of being lock-free so your application cannot deadlock.
If you have many updates/deletes, you can do garbage collection either by allowing the GC to use a transaction or by changing adding in a check for insertions to T1 that verify the number of associated rows in T2 before setting is_valid.
Unfortunately, while updates and inserts with GC can still be lock-free, they are not wait-free since an insert or update can fail. If you never do updates or GC though, this is actually wait-free and guarantees that every create, read, and delete operation will succeed in the absence of hardware/network failures.
Still, this overhead probably isn't worth it unless you already need to track the history explicitly for auditing or something. At the company where we used this, we didn't have an is_valid row, we had valid_from and valid_to which were timestamps.
That said, it's still an interesting thought experiment so glad you shared.
The question "prove this program reliably closes the transaction I started" starts to become equivalent to "prove this program halts"
Obviously they are useful tools and heavily used, but it's not like they are a zero-overhead feature.
Many languages have some sort of `finally` or `with` construct tailored for this use case.
Remember, we’re code writers, not arbitrary discriminators.
The only thing that should ever stop you from returning a resource is a malicious scheduler.
You might want to take a look at rust.
To span a transaction over two functions, it has to be assigned to some variable and the lifetime of this variable is tracked by the compiler.
Where you know code boundaries are an issue I've found functional designs tend to work a little better than OOP with regards to managing transactions but a lot of that could just be down to how my brain is wired (while I'm not a FP evangelist I do tend to favour breaking code down to stateless functions rather than stateful classes).
It's fair to say spaghetti code will be a problem on most reasonably mature code bases but there are approaches and frameworks that help somewhat with managing transactions across code boundaries -- just as there are tools that make working with transactions harder. But in my experience there are much harder problems to solve than working with transactions.
> but it's not like they are a zero-overhead feature.
Is there such thing as a zero overhead feature? (I say this semi-flippantly).
Also have WAL mode on to get multi-user access going.
In my application that uses SQLite (Snebu backup), as data comes in (as a TAR format stream) I have one process extracting the data and metadata, then serializing the metadata to another process that owns the DB connection. This process dumps the metadata to a temp table, then every 10 seconds "flushes" the metadata to the various tables that it needs to go to. This way I can easily have multiple backups going simultaneously, as each process spends a small amount of time (relatively) flushing the data to the permanent tables, and a greater part of the time compressing and writing backup data to the disk vault directory.
I've been working with this for the past 8 years or so, and have picked up a few tricks on keeping as much as possible batched up in transactions, but also keeping the transaction times short relative to other operations. So far seems to work out fairly well.
Note, that in addition to journal_mode=wal, you need to have a busy handler defined that infinitely retries transactions with a 250 ms delay between each retry.
Edit: On further review of the docs, I'm not sure if wal mode enables table-level locking, it may be that when writing to a temp table, that temp tables are part of a separate schema (or are otherwise separate from the main DB) -- which makes sense, as temp tables are only visible to the process that owns them. So a temp table can be locked in a transaction, while the rest of the DB is writable.
But specifically, look in "snebu-main.c" that is where the opendb function is (so you can see the pragma statements), and there is a busy_retry function that gets referenced (all it does is sleep for .1 seconds). I believe that you don't need the busy-retry function, if you use the built-in busy handler, but I'm not really sure and don't want to take a chance and break working code.
For the temp tables, look in snebu-submitfiles.c -- the function at the top handles the DB operations, one towards the bottom handles the non-DB tar file consumption operations, and there is a circular queue in the middle to handle buffering so the data ingestion can keep going while the data is getting flushed (these three run as separate processes). I should learn threads, as there may be more flexibility in that, but not comfortable enough with thread programming yet.
$ f=$(mktemp)
$ sqlite3 $f "pragma journal_mode"
delete
$ sqlite3 $f "pragma journal_mode=wal
wal
$ sqlite3 $f "pragma journal_mode"
wal
> Usually, SQLite allows at most one writer to proceed concurrently. The BEGIN CONCURRENT enhancement allows multiple writers to process write transactions simultanously if the database is in "wal" or "wal2" mode, although the system still serializes COMMIT commands.
> When a write-transaction is opened with "BEGIN CONCURRENT", actually locking the database is deferred until a COMMIT is executed. This means that any number of transactions started with BEGIN CONCURRENT may proceed concurrently. The system uses optimistic page-level-locking to prevent conflicting concurrent transactions from being committed.
> When a BEGIN CONCURRENT transaction is committed, the system checks whether or not any of the database pages that the transaction has read have been modified since the BEGIN CONCURRENT was opened. In other words - it asks if the transaction being committed operates on a different set of data than all other concurrently executing transactions. If the answer is "yes, this transaction did not read or modify any data modified by any concurrent transaction", then the transaction is committed as normal. Otherwise, if the transaction does conflict, it cannot be committed and an SQLITE_BUSY_SNAPSHOT error is returned. At this point, all the client can do is ROLLBACK the transaction.
The page also mentions:
> The key to maximizing concurrency using BEGIN CONCURRENT is to ensure that there are a large number of non-conflicting transactions. In SQLite, each table and each index is stored as a separate b-tree, each of which is distributed over a discrete set of database pages. This means that:
> Two transactions that write to different sets of tables never conflict
Source: https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...
That is exactly what the parent comment already said:
> if you do them in one transaction
The commands you stated are exactly how to do (multiple) things in a transaction.
If you need to do a lot of inserts (or updates, etc), the slowest possible way to do them is to do them outside of a transaction. The fastest way to do them is to wrap them all into a single transaction.
For serialized writers in any system I'm sure keeping a transaction open as long as possible is the ideal case for throughput, but there's other problems with that in a CRUD app no?
This doesn't seem to contradict the comment you're replying to. They're suggesting wrapping operations into transactions in batches e.g. (just making some numbers up) if you have 100,000 inserts maybe you'd do 100 transactions of 1000 inserts each. I wouldn't call that "the opposite" of your one mega-transaction suggestion. In fact I'd expect it to still have most or all of the speed benefit of using one single transaction, or potentially even be slightly faster.
But to answer your question at face value, one common use for SQLite is as a file format for complex desktop apps, or perhaps a game save format for certain types of games (mostly the non-realtime types). One great advantage to this is that if you use a DB migration library, you ensure backwards compatibility with previous versions of your saved files. However, it's easy to imagine a fair bit of data getting inserted into such a new file each time it is created. It might only be 5000 rows, but I'd prefer to not have to wait 5 seconds just to persist it, if a single change can make it 0.05 seconds.
Then it wouldn't actually haven been a case of "bulk insert" at all.
What you are describing sounds almost exactly like PRAMGA synchronous = FULL [1] (which is the default). That pragma controls when fsync occurs. Depending on your application, you might have got away with NORMAL or even OFF. Again depending on your application, you could have set journal_mode = OFF or increased the mmap_size (both also discussed on that page). Yes those are fairly magical hacks, but synchronous and journal_mode at least are things are always worth considering for a new SQLite database (and before building it with debugging symbols!).
Even without those tweaks, the really key thing is to use fairly large transactions. I'm surprised that alone wouldn't have got you decent performance.
One option you didn't have at the time but might help today is write-ahead mode [2] with journal_mode = WAL (but still presumably not as fast as journal_mode = OFF!). I believe the only reason it isn't enabled by default is for backwards compatibility. According to that article, it was introduced in 2010-07-21, and improved to better handle large transactions (>100MB) in 2016-02-15.
Sqlite is full of locks, and any writes are single-threaded stop-the-world, and there's no way around it. It's Sqlite's philosophy. Write-heavy databases just need something like MVCC, and Sqlite won't have that.
You are talking about multiple different processes/threads heavily and concurrently writing to a database. In that case, you're absolutely right, the point has come to switch to a client/server database like PostgreSQL or MySQL.
But the parent comment was not about that (or at least they didn't explicitly mention concurrency, and their mention of "a default setting that made spill-to-disk very aggressive" rather than locks suggested that concurrency wasn't the problem). They seemed to be talking about a single writer inserting at a high rate, which is something I'd expect to cope with very well with the right tricks (mostly batching multiple inserts in transactions - I acknowledge their comment that the defaults are unfortunate though). Yes even with a single process there are locks, but if a lock is uncontended then it is not normally a problem.
Windows has an embedded NoSQL DB engine which is fine with concurrent writes and multi-versioning: https://en.wikipedia.org/wiki/Extensible_Storage_Engine
There’re disadvantages too. It does not implement SQL, the queries need to be done manually on top of various indices in these tables. The DB has much more than a single file. The API is way more complicated than sqlight. The databases are portable from older to newer versions of Windows with automatic upgrades, but not the other way.
The Windows search functionality uses this database and the only way Microsoft was able to make this feature somewhat reliable is by having it run very thorough checks at startup and tossing the database and reindexing at the first hint of trouble.
I don’t know why it is so terribly unreliable but I know it is.
Are you sure these issues are caused by the DB engine? Other possibilities include your PC (like interference with crappy AV software), or Microsoft’s code of these search indexing services.
I think the difference is that Windows Search is always running, including when the computer crashes, is turned off unexpectedly or doesn’t complete waking up from sleep or hibernation. I think JET just isn’t that robust against that. It’s quite conceivable people manually closed your software when they shut down their computer.
I remember Exchange failing the same way if its database disks suddenly disappear due to network issues.
Microsoft is large and software quality varies. For instance look a Skype, they failed to use their own GUI frameworks and are using Electron i.e. Google Chrome to paint a few controls.
> I think JET just isn’t that robust against that.
I think it is, it's mentioned everywhere:
https://docs.microsoft.com/en-us/windows/win32/extensible-st...
Like they say,
In theory, there’s no difference between theory and practice. In practice, there is.
Here’s a list of things that can go wrong (here in the context of domain controllers):
https://docs.microsoft.com/en-us/troubleshoot/windows-server...
Some of them are just the unavoidable hardware failures, for others they suggest ‘Deploy the OS on server-class hardware’. Or the always helpful ‘restore from backup’. Not quite reasonable for a database containing a search index on a consumer device.
That article was written because DC is a business-critical infrastructure. Here’s a comparable one about Oracle: https://docs.oracle.com/cd/A87860_01/doc/server.817/a76965/c...
NTFS: https://docs.microsoft.com/en-us/previous-versions/windows/i...
> Not quite reasonable for a database containing a search index on a consumer device.
Before I switched to MS Outlook, I was using Windows live mail (now discontinued) as an e-mail client for a decade or so, it used ESENT for everything.
When I run process explorer and search for “esent.dll”, it finds a dozen of system services using ESENT databases, many of them critical like CryptSvc.
I try to buy good hardware, but that’s not server-grade components. I don’t use ECC RAM nor a UPS, and I suffer from brief power outages couple times a year. If ESENT would be corrupting databases when the power is turned off suddenly, I would have noticed.
Experiments with a larger sample size show a different result.
Obviously for your SQL queries we crank up a large SparkSQL cluster.
- AWS architects
You can thank me later.
Stopping the database is even a common trick when bulk inserting into "real" databases.
You are right that the Sqlite approach actually works quite well for bulk operations since you only require a single lock. However, it's usually still better for bulk inserts without updates to use fine grained locking or MVCC since you can often avoid acquiring any locks at all beyond the basic ones guarding fundamental DB data structures (these aren't locks as far as SQL is concerned since they cannot cause deadlock, it's a big pet peeve of mine when people think lock-free = no use of mutexes).
As a side note, don't do the following pattern: "BEGIN TRAN; INSERT INTO T ...; SELECT max(id) FROM T; COMMIT;". I used to do this, but this is a very bad habit that may be incorrect (assuming that id is an autoincrementing column). It's only correct on systems with true serializability, when you have opted into full serializability, and where such systems consider autoincrementing IDs to be part of serializability. When I tested this, Postgres and MSSQL handled this as expected while MySQL allowed the select to return a different row. I just tested Sqlite, and it does seem to work there regardless of WAL since it only allows concurrent readers plus a single writer. Use last_insert_rowid() or the equivalent for your database[1].
[1]: https://sqlite.org/lang_corefunc.html#last_insert_rowid
And even when it work, it'll create a lot of unnecessary conflicts.
> Use last_insert_rowid() or the equivalent for your database[1].
RETURNING is the best approach for that in postgres (although lastval() also works). https://www.postgresql.org/docs/devel/sql-insert.html
https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co...
SQLite is not the silver bullet of the DB world, but it's extremely useful in certain scenarios. Will you need a DB for a desktop app? Ditto. Want a portable file to carry some complex data structure? Ditto. Want to test your webapp during development, or provide a single-user version for users? Ditto.
Concurrent writing, multiple user, multiple producer scenarios need something bigger. MySQL Embedded, MySQL, Posgres, MSSQL, Oracle... List goes on.
SQLite makes databases accessible and useful in much more scenarios. I've hated databases until I've found SQLite, because I simply didn't see the point of running a big server which is designed to handle much bigger data sets to store 250KB of text tables.
SQLite is extremely underrated IMHO.
My main app sync data across ERPs and their main case is batch loading of data. This mean that I need to nearly mirror a SQL Server/Oracle/Cobol/Firebase/Etc database into sqlite, clean it, then upload to postgresql.
I have more troubles fast loading into PG than sqlite (not saying I don't have them in the past!) and sqlite is very very fast to me.
A single insert of an array using an unnest can insert many rows. The performance is worse than copy to, but in the same ballpark. But there are use cases where you'd like to bulk load through a stored procedure for a variety of reasons, and now calling the procedure with an array and using unnest internally is a straight win.
I was building a small Rails project that has mostly reads, but here and there it has inserts that can have 1000+ entries with related models. Things seem fine when clicking around, but when I used jMeter to test what is the capability of the server, I found it terrible with simultaneous requests.
Adding two workers made things even worse, and this is with ~10% writes and the remaining going to reads.
I quickly changed the db to postgres for comparison purposes, and from 3-4 requests per second with sqlite, it jumped to 60-70 as it did scale linearly with the number of workers.
I am by no means expert in database optimisations, but out of the box this behaviour was somewhat limiting the usability of sqlite for a webapp.
I'm not currently using any of those, mind you, still on a pandas/dask* dataframe basis, but I'm trying to wrap my head around where the ecosystem is moving
*I know Dask is already using Arrow behind the scenes
My use case for DuckDB is effectively querying R dataframes with SQL. DuckDB has the functionality to register virtual tables, with data from existing dataframes.
As I know SQL reasonably well, using DuckDB to query dataframes means I don't need to learn a bunch of new dplyr verbs or data.table constructs.There are some other R packages which also support this use case - sqldf and tidyquery are two I am aware of. Both of these follow a different approach, where they parse the SQL query. Using a DuckDB virtual table lets the database handle all of the SQL.
I've found so far that through using DuckDB, performance is much better than sqldf and tidyquery, nowhere near as quick as data.table and can be quicker than dplyr, depending on query complexity. I haven't really looked at anything approaching big data sizes though.
> Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more judgmental with regard to data types. In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature.
With this limited set of datatypes it really makes you think harder about the data you are processing, because in the end all your data is one of these types anyway.
Those type names are just hints, they don't constrain the valid values in any way.
https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed...
It won't throw but it will set an "invalid" / "NaN" / etc. value.
SQLite made a design choice in favor of simplicity. It's also missing basic date functions all together. The only way to compare dates is by using Unix Epoch.
They were carefully designed so that collation order is identical to temporal order. Which is convenient!
If you need interval logic, though, SQLite won't help you, and epoch is the better choice. It's possible to solve some queries with a regex, but you won't love it.
If you really care about this, adding strongly typed columns is trivial: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=9baffa184672a7...
If you have strongly-type business objects then why not have a strongly typed storage? If you code can control the constraints that a correct data type ensures, then why have "strongly type business objects" to begin with? Why not store everything in your code as strings as well?
I see this misconception all the time. The database (and its data) lives way longer than most applications. And it's also a wrong to assume that there is always only one application accessing the database. Bulk loads are a typical case of secondary applications.
Not choosing the proper data type in a relational database is a really bad decision and we see question on stackoverflow and similar sites on a weekly (if not daily) basis asking how to fix invalid data in those "un-typed" columns.
In the end there will always be some business rules that are not constrained by the database. So, you always have to be a bit careful about what you store in it. Indeed not being careful and hoping that your types, constraints and triggers are going to save you is more risky
> Not choosing the proper data type in a relational database is a really bad decision
Well, then rejoice, you can't make this bad decision in SQLite because everything is +/- a number or a string
I'm sure we could get it to work in SQLite, but it sounds like we would have to have some layer of manual fudging that we couldn't forget about.
In our current database we just use "numeric(16, 3)" and no worries.
Having a fixed-point decimal type allows us to not think about once the table is created.
Same issue with money. Most of the time we need to store monetary values with two decimal places (cents).
Sure we could deal with it, but there are quite a number of tables due to different forms and messages, and then there's all the reports. Many custom ones thanks to to local officials wanting data from a certain customer in a certain way...
And yes, a fatter middle layer would be nice. Our next generation software will probably have more of that, this code base is over 20 years old at this point...
Yeah who cares about fractions of pennies anyway. Just makes things complicated.
Like, total invoice value can only be specified with two decimal digits, typically in foreign currency. Yet we also have to specify per-line value in local currency, also with only two digits. And then the per-line values are used to calculate taxes and whatnot...
At some point, you actually like for the software you use to actually have meaningful features.
The problem with reserving specialized logic for the application layer is that it limits you to simplistic indexing schemes and you end up doing excessive IO and filtering in memory to get what you actually want.
The idea that databases shouldn't have specialized datatypes is really only an idea that works in simplistic crud apps. The world is much bigger than that.
But data representation isn't data. You can't look at a serialized sequence of bytes and know what it represents without context - without a serialization scheme. For relational databases, the table definition is the serialization scheme - it is the context. For example, you can't know what date an integer is representing without contextual information like the offset. By storing dates as dates in your database, that context is baked in.
It is helpful to have that full context inside your database because it allows you to operate that database more efficiently (by carefully indexing on the properties of the data, not the data representation), but it also allows you to use that database in ways that are not tightly coupled to your application, such as analytics, because you are storing data itself and not just the bare minimum required to represent that data.
This is a show-stopper for me because the problem happens very rarely, only once every couple of days, but it's catastrophic: when this happens, the incoming message is not bounced, it is actually lost. The only reason I even realized it was happening is because I noticed there were emails in the root account, which is the error-reporting mechanism of last resort.
I've searched the web in vain for a solution. If you have any suggestions, I would love to be able to stick with sqlite, but at the moment I am about to begin migrating back to MySQL. :-(
That being said there are many use cases where having high availability is critical and in the face of multiple writers SQLite isn't the best option for that.
I was hoping to find some kind of global switch that would make sqlite always wait for locks rather than throwing an error. But I've scoured the web for such a solution without success :-(
What can be done is registering a busy callback:
https://www.sqlite.org/c3ref/busy_handler.html
I don't understand enough about your specific problem to know if this will actually help you, just sharing a tidbit I encountered working on a comparable issue.
why incorporate its 200k SLOC when the alternatives are a fraction of the size?
Performance, ACID and a superior declarative query language.
SQLite positions itself as an improvement over ZIP for application file formats: https://www.sqlite.org/appfileformat.html . But minzip is so much smaller, easier to understand, debug and ship. So why use SQLite for an app if ZIP suffices?
If you’re not reading and writing out application state, then yes, you don’t need something to manage your non-existent state
I actually consider that a good thing. Doing everything in volatile memory until user asks otherwise is a relic from diskette era.
For example: cut some content from a file to paste it somewhere else. Now the program saves and system crashes.
Well, it depends what you mean by “need”. But continuous, incremental updates generally provide a much better user experience, either instead of or in addition to active “save” actions.
So, yeah, I think its exactly something that is commonly desirable in a file format for maintaining application state, even if there is a different interchange format that the application produces/consumes as a static input or output.
If you just need a config file or only have a small amount of data you can use XML/JSON files that you parse yourself. If you are going to have loads of data that needs some structure (for example messages in a messaging app) i would use SQLite.
neither XML, JSON nor zip solve the problems SQLite does, though; if you use plain old files, you need to make sure any changes you make actually end up on the disk, consistently. This is not easy to do. It also solves any consistency issues that might stem from someone reading the data while you're writing it.
On top of being just better, having a relational model for your data gives you much more freedom to use said data; you'll be able to do things efficiently that might require restructuring your JSON or XML format. Personally, I love SQLite-based application formats because I can explore them with SQL, which is often much easier than trying to make sense of a custom JSON or XML schema.
Yes 200k SLOC is huge (modern development practices notwithstanding). SQLite creates temporary files at whim - nine different kinds! https://sqlite.org/tempfiles.html
I know how to atomically write a JSON file. But when I read, for example:
"The temporary files associated with transaction control, namely the rollback journal, super-journal, write-ahead log (WAL) files, and shared-memory files, are always written to disk. But the other kinds of temporary files might be stored in memory only and never written to disk. Whether or not temporary files other than the rollback, super, and statement journals are written to disk or stored only in memory depends on the SQLITE_TEMP_STORE compile-time parameter, the temp_store pragma, and on the size of the temporary file..."
My eyes have completely glazed over. If I add this to my app, what will it actually do? How can I even know?
Are you sure? I’ve had a lot of trouble getting that to work reliably myself across multiple OSes. (In hindsight I wish I’d used SQLite!) This article gives a good explanation of the many difficulties:
https://danluu.com/deconstruct-files/
My eyes have completely glazed over. If I add this to my app, what will it actually do? How can I even know?
Well, fundamentally it’s very hard to get it exactly right, and I imagine that’s why the implementation is a little involved.
But you could a) read through those docs, lengthy though they are, and/or b) trust the many testimonials saying SQLite is very, very robust and reliable.
No, and anyone who says yes is lying. (Lockless NFS exists and is no fun.)
> Well, fundamentally it’s very hard to get it exactly right, and I imagine that’s why the implementation is a little involved
SQLite has set itself the horrible task of updating files in-place. I know of two reliable, simpler alternatives:
1. Appending to files through O_APPEND
2. Rewriting files through rename()
If SQLite has different magic syscalls then I would very much like to learn.
(Haven’t googled, but if that’s possible, I don’t see why write would have that limitation)
Do you have any other resources regarding these types of low level "gotchas"?
I remember PostgreSQL having such an issue two years ago for example.
(SQLite does this for you automatically, BTW.)
"Ensuring data reaches disk"
Unless you're on nfs. Remote file locking is hard, and I don't think that any nfs implementation has gotten to the point where you can trust SQLite on it.
SQLite does updates in place, which I would trust far less than a rename call.
Sure. But that forces you to rewrite all the data at once. Once it becomes large or you require more frequent changes, that will impact performance.
Be really, really, *really*, unambiguously sure about whether your data was written or not, AND have high confidence that I/O errors (eg, power loss) in the middle of does of deletes won't scramble (or truncate) existing data.
What you're looking at is the complexity required to solve for the wonderful tornado of "but it's my data really written???". But you don't have to deal with SQLite's implementation details in order for it to do its thing, which is what makes it so awesome (given is public domain status, what's more!).
The complexity of including SQLite is trivial for practical purposes; it's already available on many systems, and if not you can include it by adding a single C file to your project.
Setting up a workflow for Google Protocol Buffers (another popular alternative for document file formats) is a lot more complex than building or linking with SQLite, and it doesn't stop people from using them.
One thing that speaks for SQLite is the quality of the project; it's one of the best maintained Open Source projects with fantastic quality assurance and support for almost every OS. This means that you are unlikely to run into issues compiling or working with SQLite, like you might have with alternative libraries like libxml2 or jsonc (which are still great libraries!!).
EDIT: The big downside of SQLite is that it's unsuitable for documents that are exposed to the user because of the temporary files (like the WAL). If you have a ZIP based file format that you atomically rewrite from scratch on every save, it's almost impossible to corrupt. Your users can just take the file and email it and nothing bad will happen. I'm not sure what happens if you email an SQLite database file that is currently being used. I've done that in the past and have been surprised that some data seemed to be missing, but I don't recall the details. Hence SQLite is often used for application data files that are not directly exposed to the user.
[1]: https://www.sqlite.org/sessionintro.html#:~:text=1%20Introdu...
ZIP isn’t a format alternative, its just a compression and/or packaging technique for files which you still need to choose a format for.
JSON/YAML/XML are great for input and output formats, but not great for continuous, random read/write access.
Well this is in a context that rejects zip as being a format. Do you do that? If the answer is no then skip the rest of my post and just note that they're talking about a different definition of 'format'.
-
But in that context:
The amount of structure imposed on you by the sqlite database format is not much more than the structure imposed on you by a zip. I think it's fair to rate them similarly as formats. A zip file is basically a key-value store.
"Zip full of csvs", while awful to use, would impose about the same amount of structure as sqlite does: not much. And zip+csv is not much more elaborate than zip on its own.
Surely this is more structure than a ZIP file, which is merely a way of compressing a directory of files into a single entity, can provide?
Sure the INTEGER part doesn't really do anything...
A configuration like that comes from the program using sqlite. Just adding sqlite into a system doesn't set up any data formatting like that. Sqlite itself gives you a blank canvas. And a blank canvas is not much of a data format.
The SQLite data format includes the schema, in plain ASCII. This self-documenting nature makes it an excellent data format, I've taken advantage of it numerous times in making use of SQLite-based application file formats.
SQLite is put forth as a basis for an application file format, and by definition it must be sufficiently flexible to accommodate any application. But by including the schema, it is self-documenting as to what the structure is, which ZIP isn't and can't be. QED.
MyCoolSQLApp may read and write a SQLite file with its own schema, but it can't handle an arbitrary SQLite file. Likewise MyCoolZipApp can't handle an arbitrary zip file.
If not, what do you call the specification of how data is stored in a SQLite file besides a 'file format'?
That's true that you can stuff any kind of string/blob data into any column of any table, so, yes, you still have to determine the data schema with sqlite much as you do with JSON, XML, or even CSV. I mean, I could have a CSV where each element is a base64-encoded ZIP containing sqlite database files that are each a single table with a single column of JSON files, each of which contains a JSON array of strings with XML documents in them.
But that's usually not something people would mean if they said their app was using CSV as it's data storage format, nor is the version stripping out CSV on the top what people would mean if they say they are using SQLite.
With ZIP, you have to decide the format(s) for the file(s) in the ZIP, their hierarchical structure, and, if the files aren't themselves the atomic data elements, the schema applicable to each file.
Furthermore, in discussion of performance characteristics and other aspects of suitability, ZIP adds overhead, but you still also need to consider the access properties of the contained files.
Some random modder basically just put multiple chunks into one file so that each file is 2MB. If Notch had just put the game world into a SQLite database he wouldn't have had to reinvent the wheel. There are games that did that, such as the alpha of Cube World and they work just fine.
Heck, notch went one step further and invented NBT aka named binary tag which is basically a weirdo binary file format that stores JSON like data.
It was using subdirs for the chunks, two levels iirc, one was chunkX % 36, the next level chunkY % 36. So there weren't that many files per directory. The slowness came from the overhead of opening, read/write and closing so many files all the time.
> Some random modder basically just put multiple chunks into one file so that each file is 2MB.
Almost, it wasn't limited by file size, it was putting 32*32 chunks into one file that was similar to a simple file system. The format of the individual chunks within that file stayed almost the same. Yet it performed much better.
NBT is indeed a little weird but fairly straight forward overall, I guess designing and implementing it just scratched an itch. It was a hobby project after all.
I remembered this post. https://github.com/microsoft/WSL/issues/873#issuecomment-425...
Also, did you know you can use in-memory instances (and share them across threads!) with the right incantation? And that you can backup your on-disk instance to an in-memory one, do your expensive transactions without hitting the disk then backup the modified instance right back to disk, even in-place if you want!
Sqlite is amazing when you don't expect the DB to do replication or failover on its own.
I've been doing a handrolled in memory cache layer to speed data access, but with this, I can just call the db directly and then periodically sync to disk, redis rdb style. Sqlite is a staggeringly good piece of technology!
I don't agree or disagree but it's something I've heard.
I've seen sqlite used as a cross language data frame solution. Store it in s3 and it's resilient if you are read only.
SQLite works really well for static or semi-static data. For example, a blog where you have a small number of users writing and many users reading from the DB. If the authors are content to use one server to edit the DB then you can easily push that DB to the servers handling the reads.
One of the cool things about it, is that SQLite is very lax about what it accepts (mostly in the datatype area). You can write your SQL statements targeting whatever database you think you'll move to later and they'll work while you're still on SQLite. I believe having this migration work seamlessly towards PostreSQL is one of the advertised features.
I'm rebuilding the application in a modern tech stack, still using SQLite but properly this time, along with Go and React. API requests take 20-40ms instead of 300-1500ms, and there's much less of them.
The main downside to using SQLite is that it does not support "proper" database migrations; you cannot alter a column. You can add columns to an existing table, but you can't change existing columns. The database abstraction I'm using at the moment, Gorm (a different subject entirely) work around this by moving stuff to a temp table, recreating the table with the updated columns and moving stuff back, I believe.
Anyway TL;DR sqlite is not the bottleneck.
Anyways, the process you describe is also used in MySQL for doing online schema migrations. [2] "proper" database migrations cause downtime
[1]: https://stackoverflow.com/questions/805363/how-do-i-rename-a... [2]: https://github.com/github/gh-ost
Postgres is basically just as easy to use and backup.
A lot of it comes from what TFA says: there's no network roundtrip, but a function call. Even in a local machine, a unix socket query will carry at least a couple of system calls with potential context switches, and that makes regular RDBMS lag behind when you do tons of sequential and small queries.
Of course, when you have large results or complex queries that eat a bigger chunk of the time cake and that technical advantage wanes. After that, which RDBMS has the performance lead is largely workload-dependent.
SQLite is way ahead on this: no daemon to run, no user / database to create, manage and administrate, no authentication to set, no socket connection to manage… backup is as easy as it gets: (copy one or two files).
Postgres is still largely manageable of course.
If copying the entire database is faster than your average update right, this will converge very quickly and will deliver a consistent copy.
For many small applications, this is perfectly fine. It's not much harder to just "sqlite3 $file ".backup $backupfile"' (that's literally all it takes, and what you should do) and guarantee consistency. But it's nice to know that a simple "rsync" is sufficient for slowly-updating uses - e.g.
And as for the other side of backup, you know -- restore -- sqlite shines brighter than everything else. You can just take a good copy and put it back. You can examine the file everywhere, on a read only system, etc - without configuring anything if needed.
However if you have frequent writes I would be careful.