To that goal, I think it's wildly successful, instead of writing files I almost always reach for SQLite first.
When systems share a database, I, like you, reach straight for postgresql. Most people reach for mysql, which I do not prefer.
This bug has occurred to me personally multiple times.
Does it really have advantages over a flat file?
I think the odds are likely that Firefox is doing something unsuspected.
https://www.sqlite.org/testing.html
- Four independently developed test harnesses
- 100% branch test coverage in an as-deployed configuration
- Millions and millions of test cases
- Out-of-memory tests
- I/O error tests
- Crash and power loss tests
- Fuzz tests
- Boundary value tests
- Disabled optimization tests
- Regression tests
- Malformed database tests
- Extensive use of assert() and run-time checks
- Valgrind analysis
- Undefined behavior checks
- Checklists
It seems that about once a month I read something that makes me like SQLite even more. I guess that they like Valgrind as much as I do is the reason this month.
<<This bug has occurred to me personally multiple times.>>
Hello, I am AirBus engineer (not really, only pretend). Does my plane crash? No, it does not!
Dear Random Internet Person who complains that open source software is broken. Are you joking? We are talking about SQLite!!??
SQLite has whole pages dedicated to "why you should NOT use SQLite". How many open source projects are /so/ good they can do this? Incredible!
The test coverage feels like NASA.
Whatever capabilities SQLLite has to recover corrupted or inconsistent storage, it certainly exceeds what you get with flat files (none, unless you implement it yourself).
Which suggests an application error to me. I'd give decent odds to the real problem being some bounds check or pointer writing to the wrong place. To prove it, I'd instrument SQLite to log every single query that it was asked to do, then try to run those queries outside of the application. If the queries work fine, then the copy of SQLite in the application is being corrupted somehow.
In which case it really isn't fair to blame SQLite.
The question is how it is getting corrupted. Is it that SQLite has a bug? Or the copy of SQLite in Firefox is getting corrupted first?
Given the complexity and relative levels of testing of those two products, my bet is that the problem is Firefox.
Interestingly enough, I don't think I've ever had this issue. I've force quit Firefox many times in the past (not often, but I'm sure I've done it countless times in the 10+ years I've been using it) and I still have localStorage data intact. There's old crap in StackEdit.io, which uses localStorage, that I haven't touched in years. (no, I have neither that service or Firefox itself set up to sync with anything)
EDIT: Maybe it's OS specific? I've been on macOS, and maybe there's something about other file systems where corruption is more likely to happen with a force-quit? Just throwing darts here.
I've never seen any error messages from Firefox suggesting that anything has been corrupted.
Are there other symptoms that I would expect to see, if this corruption is happening?
SQLite is really only my first choice for hobby projects and desktop apps that aren't running on the JVM.
I hear this too - but silently truncating my data (causing a great deal of data loss and grief for me) is likely going to continue to bias me against MySQL for the foreseeable future.
Compatibility with old decisions vs. doing the right thing™ is always a pain ...
I'm not saying I'm being rational in holding my grudge. I got bit by that "bug" _bad_ early in my career and the scar tissue lingers.
I still believe Sqlite is the appropriate store. Unless.. does Git handle power issues elegantly? What happens if you yank power in the middle of a commit? Sqlite is designed to handle that.
- A - yes, update HEAD/branch text file is the key atomic update
- C - not sure how to apply this in the context of git, because commited files' content is kinda free form by design, no schema, no consistency problems
- I - there should not be concurrent use of commands, so no. practically this is mostly important for pushing to remote repository, and there it's handled by rejecting the push that comes second, if you make two commits from the same base and push them at the same time
- D - yes
... but it was so slow compared to git! at everything. merging, pushing, pulling ...
you did not experience same?
my main beef with fossil was performance. but i should clarify that this was 5 years ago or so. so maybe it's ok now.
I never programmed for berkelyDB but I don’t believe it was SQL, so I think that’s what made SQLite so successful. The combination of an SQL api backed by local files
There is a lot of macOS/iOS software using Core Data who already have "SQLite as an application document storage model" without even realizing it.
Well, depends on how wide your definition is:
Filesystems are also databases. (And eg inside of Google, they are using databases to store filesystems.)
These days, we often think of relational databases. Filesystems are more like the hierarchical databases of yore that Codd (also) talked about in his original papers.
For example, having a folder of contacts with each file named after the person and having key/value pairs. Similar to how static site generators use YAML/TOML/JSON.
Or alternatively a midnight commander for databases. :)
When you need to store lots of files on disk, it's a common pattern to spread them out in subdirectories. For instance, instead of `files/2d8af74bcb29ad84`, you would have `files/2d/8a/f74bcb29ad84`.
That isn't how inode filesystems work -- if you change a file's permissions, it's just an inode update on the file -- not the containing directory.
Even in DOS-type filesystems (FAT/exFAT), it's just a record update in the corresponding dirent for that file.
If you add a new file to a directory, that causes an mtime update on the directory's inode.
The rest is accurate -- many older filesystems have lookup performance that scales poorly with directory size (for DOS filesystems and BSD UFS, you have to do a full directory scan). Also ls defaults to sorting output, which is O(N log N) and can be slow in large directories.
I'd also never put sqlite on NFS. Locking is often broken on NFS. Unless you can guarantee a heterogeneous environment. I can imagine some Excel guy in marketing is going to launch his SQLite UI on Windows and completely hose it all.
I think breaking down large collections in subdirectories is mainly done in order to ensure that it'll work even with FS that don't deal well with very large directories and also because it makes inspecting the files manually a little more convenient given that many file explorers (and especially the GUI ones) can have trouble with large directories.
I don't know exactly how Linux does it. Windows hands off whole paths to the filesystem, so this idea is possible there.
In FreeBSD, there is a generic routine (lookup(9)) that goes component by component, so at each step the filesystem is only asked to resolve a single component to a vnode. I think a clever filesystem implementation (in FreeBSD) could look at the remaining path and kick off asynchronous prefetch... but I am not aware of anything doing this.
There are two modes you can implement for your filesystem: in the so-called 'high level' mode you get the whole path. In the 'low level' mode the filesystem asks you for one piece of the path at a time.
The low level mode seemed faster in my tests, and I think it's also closer to how Linux kernel works internally?
I’d expect it to take up slightly more disk space. More importantly, the more indirections, the longer it’ll take to get to the file.
The problem is that most tools that operate on file systems doesn't. Things like readdir() is a linear scan and takes a long time on a million files.
So in practice it's not optimal. A thousand files, no problem. A million, start looking at doing it in-memory (or use some database tool).
Yes, depending on the file system.
For example, ext4 with default settings uses 32-bit hashes. Upon the first collision, you can no longer add more files to the directory (ENOSPC error).
Source: https://blog.merovius.de/2013/10/20/ext4-mysterious-no-space...
Perhaps they want to tag a few, and it’s useful to have some autodetected metadata but they don’t want to tag them all and they don’t want a gigantic ‘Untagged files’ list. They want folders and they want more than a flat folder list, they want nested folders.
You can implement it, it’s not hard and most modern file systems have all the features you need. But users will hate it and won’t use it the way you want.
But I am less sure users actually want folders.
Some power-users, sure. But most normal people don't want to deal with folders, either.
For evidence: look at the guy who saves everything on his overflowing desktop.
Any system that allows people to find their stuff, and perhaps make a few annotations, will be good for them.
Google Photos is almost a good example: I don't have to do annotate anything, yet I can search for eg pictures of snow or by location.
(I say only 'almost', because while impressive, that system isn't good enough yet to find obscure stuff or to work on contextual cues like 'those pictures I took at home after we came back from shopping sometime in the last few months'.)
And really, a lot of users don’t want an interface that doesn’t allow them to do what they want just because someone else just dumps all their files on the desktop.
Apple tried this on iCloud and had to go back. Because, while it makes for nice presentation and usability, there’s a lot of users that it can’t cater for.
You'll have hard time to harden sqlite, removing all the insecure defaults, fix the broken and exploitable full text search apis, but esp. its built-in hacks. Like explained here https://github.com/rurban/hardsqlite or here https://research.checkpoint.com/2019/select-code_execution-f...
I'm struggling to picture this, do you have any links I could read?
It is much more complicated than that, but the idea is that using bigtable you create a resemblance of a fs that feels like a fs to use for the most part.
[1] http://www.pdsw.org/pdsw-discs17/slides/PDSW-DISCS-Google-Ke...
(I'm an ex-Googler, but I am glad that my spotty memory gives me ample protection against accidentally giving out company secrets.)
I suspect Google Drive is also backed by something that's not a traditional file system, even thought Google Drive tries to look a bit like a file system to the user.
You could just as easily replace those in-memory calls with a networked DB (perhaps with speculative pre-fetching or something, I dunno, I probably wouldn't try to make a python filesystem too performant).
The salient detail here is that as far as your kernel is concerned a filesystem is an API for interacting with data (whether that's with a daemon process like the linked example or with raw function calls built into the kernel). Those APIs can and often do interact with structures physically stored on a local disk, but that isn't a requirement.
[0] http://www.rath.org/pyfuse3-docs/example.html#in-memory-file...
In a Filesystem you know where the Data lives, in a Database you know that it lives...but yeah with stuff like Ceph that view gets a little bit foggy.
For me, all I know is that my filesystem store its data on disk somewhere, I don't really have any clue how that's organised. That's pretty much exactly the same level of knowledge I have about my db.
(And yes, I could look into both of them to learn more. And yes, we also have databases and filesystems that get accessed over the network..)
File system paths perhaps sound like a location, but there are no more and no less a location than eg tablenames to me. Or URLs.
Not sure exactly what you mean by 'remote access', but if it's to debug something on a remote DB from an SQL IDE, I do this quite often. I use sshfs to mount the remote filesystem and open the SQLITE file in DBeaver.
Even in case of Postgres, you would have to connect to a remote DB via SSH or via VPN. It's just that you will be using sshfs to connect to a remote SQLITE file.
According to: https://www.fossil-scm.org/forum/forumpost/8749496886
> There's a very real possibility that you could corrupt the SQLite DB by running it over SSHFS.
WAL mode supports it ("reading and writing can proceed concurrently"), but "does not work over a network filesystem" like SSHFS [3] because it either requires shared memory on the same machine, or exclusive locking [4] (which SSHFS does not provide, and which again does not allow readers concurrent with a writer).
Thus overall, you can only rely on your read queries over SSHFS if you know that there is no concurrent writer.
[1]: https://www.sqlite.org/lockingv3.html [2]: https://stackoverflow.com/questions/1005206/does-sqlite-lock... [3]: https://www.sqlite.org/wal.html [4]: https://www.sqlite.org/wal.html#noshm
I once accidentally locked a production MySQL database with some kind of recursive sub query.
Running queries locally on a copy avoids lock/mutation issues, and I have a snapshot to re-run queries on if I need to go back and see the source of the data.
Anything that needs to be coded I'll pull it down though, it's nice to have options.
How lightweight?
SQLite itself is around 600 KB. That's with all features enabled. I believe you can get it it down to about half that if you disable features you don't need. RAM usage is typically under a dozen or so KB.
It doesn't even require that you have an operating system. All you need for a minimal build is a C runtime that includes memcmp, memcpy, memmove, memset, strcmp, strlen, and strncmp, and by default also malloc, realloc, and free although it has provisions for providing different memory allocators, and you have to provide some code to access storage if you are running on a system that doesn't have something close to open, read, write, and the like.
This means it can even be used on many embedded systems far too small to run a Unix.
Heck, it is not even too unreasonable to compile SQLite to webasm and include it on a web page if for some reason you need client side SQL on the web.
With the deprecation of Web SQL, I highly doubt this is a use-case anyone cares for anymore.
Anyhow, I see your main point: SQLite is indeed "Lite", so it makes sense to use in embedded or small single-consumer cases. I just don't think those are very interesting tools to make in 2021, we expect collaboration and concurrency, so PostgreSQL wins in my imagination-space. I am a web developer though, so I am inherently biased.
I also am not the person you were just discussing with
WebSQL didn’t die as a standard because noone wanted client SQL on the web, it died because SQLite was the only reasonable way of providing it, preventing multiple really-independent implementations.
A good example is that SQLite is used inside many video games. You wouldn't put Postgres in your game engine.
I've been watching CockroachDB as a more modern alternative that can auto-scale easily, do master/master easily, etc. Those are all painful on Postgres to the point that hosted and managed pgsql is a cash cow for cloud vendors.
I've run Postgres in a pure RAM only configuration, never touching the disk except to start.
If you mean "it can't be compiled in to another application as a library", then yes that's true - it's not an embedded database.
The quiz module is open-source (https://nrkbeta.no/2017/03/06/our-comment-quiz-module-is-now...) and available on github https://github.com/nrkbeta/nrkbetaquiz
Or has it to run as a separate process?
We have learnt to use full-fledged RDBMSs as a default because they proved really flexible and powerful over the last 30+ years. But they do have limitations and cost.
It also has javascript V8 functions to update data for migrations.
( eg. migrating your events to a new model, instead of keeping the different versions)
But while you need to do everything with a JSON1 extension for managing the json ( https://www.sqlite.org/json1.html ).
Postgress has build-in v8 javascript functions to manage this ( as stated before). Which makes it much more useable.
There's a good library for .net that handles postgress documents, which has an insane amount of usefull functions ( https://martendb.io/documentation/documents/ ). I think halve of those wouldn't be possible on another database.
Also see "Many Small Queries Are Efficient In SQLite": https://sqlite.org/np1queryprob.html
Round-trips and small queries don't have anything related to my comment?
The main reason why main client-server databases have so many built-in functions is because of the round-trips. Let's say you want to filter rows based on the result of some function, if your function is implemented client-side, then the database would have to send each row to the client.
"Relational database engines act as bandwidth-reducing data filters." (https://www.sqlite.org/whentouse.html)
My experience with flatfiles dbs was not enough to interpret it correctly :p
I agree, but there is one thing that it cannot do, one very important, critical thing: synchronous multimaster replication.
Well, vanilla PostgreSQL cannot. Vertica can.
https://www.postgresql.org/docs/current/ddl-rowsecurity.html
But the power of PG is that it doesn't stop there, if you combine this with a plugin like temporal_tables and you can segment by user and time:
https://github.com/arkhipov/temporal_tables
All of this mostly unknown to the thing that's accessing the DB. If that's not enough for you, why not add some auditing with pgaudit:
https://www.pgaudit.org/#section_three
All this value is just out there. There's even more if you can stomach browsing "ugly" sites like PGXN. Most of it works out of the box, though you may need to tinker for performance and some edge cases but it's there.
I think it might not actually be hyperbole to say that Postgres is the greatest RDBMS database that has ever existed.
The simple explanation is that you set a postgres environment variable prior to each query. The Postgres row level security system looks at this variable and returns nothing if the ID in the variable does not match an ID in the table.
So I wrote a function in Django which intercepts every database query and prefixes each query with a postgres environment variable set command.
That's pretty much it.
I found https://kristaps.bsd.lv/sqlbox/ to be an interesting approach.
I’m building a multi tenant web application on node/sequlize/feathers and implementing logic in code. I’ve seen table row level security but haven’t looked deeply.
I use Python/Django.
I use Postgres row level security.
Read this entire comment thread for more info from other posters on row level security.
If so, have you run into any scaling issues with the number of tenants you can support. I looked into using schemas for multi tenancy but the various reports I found said around 50 tenants or so things started breaking down very quickly.
No I am not using schemas - I found them to be brittle and complex as a solution for multitenancy.
I use Postgres row level security.
I use Python/Django.
Read this entire comment thread for more info from other posters on row level security.
Postgres has a larger feature set. Whether or not you need it depends on your use case. There are ample pages out there that will give you a side by side comparison if you’re curious.
I find databases of all sorts extremely interesting and I've tried many of them, of all flavors.
In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do.
Why not use sqlite? Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite.
Another example - multitenancy is dead easy in postgres, makes things more secure and makes coding easier.
Another is that Postgres had an extremely rich set of data types, including arrays and json.
Also Postgres is designed for symmetric multiprocessing which matters in today's multicore world.