News for hackers. Stuff that matters.
Re:SQLite the only database you will ever need in most cases by andrewstuart on Thursday April 15, 2021 @03:21AM (#26817124)
Postgres is the database I use for everything.

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.

Re:SQLite the only database you will ever need in most cases by dijit on Thursday April 15, 2021 @03:46AM (#26817343)
it's important to note that the author of SQLite wasn't "competing" with RDMS engines -- it's competing with flat file access.

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.

Re:SQLite the only database you will ever need in most cases by crazypython on Thursday April 15, 2021 @11:44AM (#26821875)
Firefox uses SQLite for localStorage. When Firefox is force quit, the database becomes corrupted. https://bugzilla.mozilla.org/show_bug.cgi?id=1341070

This bug has occurred to me personally multiple times.

Does it really have advantages over a flat file?

Re:SQLite the only database you will ever need in most cases by NicoJuicy on Thursday April 15, 2021 @01:13PM (#26823315)
Sqlite is certified for use in planes. This requires a high level of tests.

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

Re:SQLite the only database you will ever need in most cases by easton on Thursday April 15, 2021 @03:07PM (#26824854)
"Valgrind is perhaps the most amazing and useful developer tool in the world."

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.

Re:SQLite the only database you will ever need in most cases by unmole on Thursday April 15, 2021 @11:25PM (#26829067)
Valgrind is fine if you can live with the false positives, limited stack validation and execution slowdown. In a development environment, AddressSanitizer is a far better choice.
Re:SQLite the only database you will ever need in most cases by throwaway2037 on Thursday April 15, 2021 @03:19PM (#26824967)
I am trolling on the person you to whom you replied...

<<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.

Re:SQLite the only database you will ever need in most cases by throwawayboise on Thursday April 15, 2021 @12:26PM (#26822584)
I use Firefox almost exclusively and have never encountered this.

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).

Re:SQLite the only database you will ever need in most cases by btilly on Thursday April 15, 2021 @12:39PM (#26822793)
Per that bug report, you are likely to encounter it at https://codesandbox.io/s/sentry-js-sdk-unhandled-promise-fuq....

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.

Re:SQLite the only database you will ever need in most cases by mcdevilkiller on Friday April 16, 2021 @11:49AM (#26835390)
What's getting corrupted is Firefox's own SQLite DB.
Re:SQLite the only database you will ever need in most cases by btilly on Friday April 16, 2021 @09:23PM (#26840837)
I'm perfectly aware of that.

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.

Re:SQLite the only database you will ever need in most cases by astrobe_ on Thursday April 15, 2021 @12:22PM (#26822493)
This is quite surprising. SQLite is also used in embedded where you don't have comparatively civil "forced quits" but also straight power offs. SQLite seems to do everything it can to handle this gracefully.
Re:SQLite the only database you will ever need in most cases by edoceo on Thursday April 15, 2021 @11:56AM (#26822062)
if you're corrupting sqlite files you're for sure going to corrupt other formats in a force quit situation.
Re:SQLite the only database you will ever need in most cases by ravenstine on Thursday April 15, 2021 @03:09PM (#26824868)
Why would that not happen to any other form of flat file storage? When you force quit a process, unless there's some nuance I'm unaware of, you're not only bypassing any graceful cleanup but there could be an FS operation in progress. Although I'm certain there are mitigation techniques, I would never expect a process to guarantee no file corruption when it's forcefully quit.

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.

Re:SQLite the only database you will ever need in most cases by asimpletune on Thursday April 15, 2021 @03:30PM (#26825104)
I believe for a proper implementation it would be done transactionally. So if the program crashed then that record wouldn’t be written but the DB wouldn’t be corrupted.
Re:SQLite the only database you will ever need in most cases by nix0n on Thursday April 15, 2021 @03:30PM (#26825101)
I force-quit Firefox regularly (easiest way to get my tabs to stay open), on both Linux and Windows.

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?

Re:SQLite the only database you will ever need in most cases by crazypython on Friday April 16, 2021 @01:48PM (#26836987)
Some sites break due to not expecting the exception being thrown– deviantart.com won't work, for instance.
Re:SQLite the only database you will ever need in most cases by mumblemumble on Thursday April 15, 2021 @09:30AM (#26820211)
Even when systems don't share a database, if operational concerns - disaster recovery, failover migrations, metrics and monitoring, anything like that - matter to me, I'm reaching for PostgreSQL or MS SQL Server. I hear I also need to get over my 20 year old grudge against MySQL.

SQLite is really only my first choice for hobby projects and desktop apps that aren't running on the JVM.

Re:SQLite the only database you will ever need in most cases by eric_h on Thursday April 15, 2021 @11:02AM (#26821320)
> I hear I also need to get over my 20 year old grudge against MySQL.

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.

Re:SQLite the only database you will ever need in most cases by johannes1234321 on Thursday April 15, 2021 @01:51PM (#26823858)
That was configurable for ages and default is on strict for a long time now ...

Compatibility with old decisions vs. doing the right thing™ is always a pain ...

Re:SQLite the only database you will ever need in most cases by eric_h on Thursday April 15, 2021 @07:04PM (#26827293)
> That was configurable for ages and default is on strict for a long time now

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.

Re:SQLite the only database you will ever need in most cases by Quarrelsome on Thursday April 15, 2021 @08:13AM (#26819470)
Ye I believe its best to think of Sqlite not as a database per se but more as a file format, kinda as you would a word doc but a file format that doesn't suck and is absolutely amazing at storing information.
Re:SQLite the only database you will ever need in most cases by hodgesrm on Thursday April 15, 2021 @12:23PM (#26822519)
And retrieving it, too.
Re:SQLite the only database you will ever need in most cases by lucioperca on Thursday April 15, 2021 @08:34AM (#26819661)
If it is mostly text, then I would say git is better suited.
Re:SQLite the only database you will ever need in most cases by Quarrelsome on Thursday April 15, 2021 @08:39AM (#26819701)
git is ACID?

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.

Re:SQLite the only database you will ever need in most cases by megous on Thursday April 15, 2021 @12:14PM (#26822377)
Let's see:

- 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

Re:SQLite the only database you will ever need in most cases by lbruder on Thursday April 15, 2021 @10:47AM (#26821157)
that's one of the reasons I love Fossil so much. Way better backend than Git.
Re:SQLite the only database you will ever need in most cases by utxaa on Thursday April 15, 2021 @11:22AM (#26821554)
one of my team members really pushed fossil, and we did like the integrated wiki/ticket system. not bad at all.

... but it was so slow compared to git! at everything. merging, pushing, pulling ...

you did not experience same?

Re:SQLite the only database you will ever need in most cases by remram on Thursday April 15, 2021 @12:46PM (#26822901)
Fossil has rebasing? I thought they felt strongly against rewriting history, which is the biggest reason I am not considering it.
Re:SQLite the only database you will ever need in most cases by utxaa on Thursday April 15, 2021 @12:55PM (#26823045)
oops. corrected. thanks.

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.

Re:SQLite the only database you will ever need in most cases by 0xEFF on Thursday April 15, 2021 @02:50PM (#26824659)
For context, before the rise of SQLite, BerkeleyDB was often used for local file databases.

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

Re:SQLite the only database you will ever need in most cases by setpatchaddress on Thursday April 15, 2021 @03:56PM (#26825405)
BerkeleyDB was a key/value store. I'm not sure how much uptake it actually had vs other solutions. Prior to widespread availability of SQLite3, it was not unheard of to write your bespoke format if you needed something sophisticated.
Re:SQLite the only database you will ever need in most cases by 0xEFF on Thursday April 15, 2021 @07:47PM (#26827579)
My exposure to BDB was through sendmail, spam assassin, and subversion. All three a big chunk of my job in 2006.
Re:SQLite the only database you will ever need in most cases by xdeepak81 on Thursday April 15, 2021 @06:26PM (#26826940)
BerkleyDB is NoSQL and is internally used in popular No-SQL solution like DynamoDB.
Re:SQLite the only database you will ever need in most cases by chris37879 on Thursday April 15, 2021 @10:44AM (#26821114)
I actually saw a really interesting article (probably on here) a while back about using SQLite files as your applications' document storage model, and I really like it. Like if you wrote a word processor, you could include a table of edits that would let you recreate Google Doc's timeline feature, among other things. I thought that was a really neat idea.
Re:SQLite the only database you will ever need in most cases by derefr on Thursday April 15, 2021 @03:20PM (#26824980)
Apple's Core Data persisted-object-graph engine (kind of like Hibernate, I guess?) uses SQLite as its default storage backend.

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.

Re:SQLite the only database you will ever need in most cases by simonw on Thursday April 15, 2021 @11:38AM (#26821766)
Re:SQLite the only database you will ever need in most cases by bhl on Thursday April 15, 2021 @06:22PM (#26826905)
Re:SQLite the only database you will ever need in most cases by jxi on Thursday April 15, 2021 @08:19AM (#26819521)
If competing with flat files, and assuming you don't need concurrency, I don't think it's better than just using one or more json or csv files. Those are much easier to manipulate and manage (same benefits in terms of admin and backup), and you don't need to use a database driver in your code.
Re:SQLite the only database you will ever need in most cases by eru on Thursday April 15, 2021 @03:42AM (#26817316)
> In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do.

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.

Re:SQLite the only database you will ever need in most cases by encryptluks2 on Thursday April 15, 2021 @04:02AM (#26817455)
Would love to be able to have a database file system and be able to use standard Linux tools to query and make changes, back up, etc.

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.

Re:SQLite the only database you will ever need in most cases by yason on Thursday April 15, 2021 @04:15AM (#26817538)
Try your nearest shell to experiment with this? In my scripts and programs I've stored data just in files many times: simple, transparent and effective. Works everywhere. There's so much in search that you can just brute-force through unless you have a huge amount of data or a lot of concurrent tasks that it doesn't matter much in today's hardware.
Re:SQLite the only database you will ever need in most cases by eru on Thursday April 15, 2021 @09:13AM (#26820042)
The maildir format does something like this.
Re:SQLite the only database you will ever need in most cases by nix23 on Thursday April 15, 2021 @04:08AM (#26817496)
Re:SQLite the only database you will ever need in most cases by thinker5555 on Thursday April 15, 2021 @08:49AM (#26819790)
This reminds me a little bit of the filesystem from BeOS (RIP) and Haiku. You cant attach any metadata to any file you want, and it has a system to create queries on that metadata.
Re:SQLite the only database you will ever need in most cases by blacktriangle on Thursday April 15, 2021 @08:58AM (#26819892)
Very much miss this. I love how they used this technique to build their email client. Emails were just files on the filesystem with a filesystem plugin that made their metadata queryable. So the email client was just a file browser window with some pre-saved queries that would let you find all emails by date, sender, etc.
Re:SQLite the only database you will ever need in most cases by megous on Thursday April 15, 2021 @12:23PM (#26822516)
A job for fuse. :) I'd like having a nice sysfs like view on all tables and other objects in a database. Even better if editable.

Or alternatively a midnight commander for databases. :)

Re:SQLite the only database you will ever need in most cases by encryptluks2 on Thursday April 15, 2021 @01:30PM (#26823562)
OMG yes. Now this would be perfect for SQLite.
Re:SQLite the only database you will ever need in most cases by pxndxx on Thursday April 15, 2021 @05:46AM (#26818298)
If you do this, you can add all the files to git and get historical views, changelogs... and if you distribute the "database", you even can know who made what changes!
Re:SQLite the only database you will ever need in most cases by encryptluks2 on Thursday April 15, 2021 @07:40AM (#26819163)
Along the lines of what I was thinking. Would be really neat! I already do this to an extent, but with a virtual file system it could automatically sort/categorize/etc like a regular database.
Re:SQLite the only database you will ever need in most cases by busymom0 on Thursday April 15, 2021 @05:21AM (#26818111)
This makes me wonder how efficient filesystems are with millions of file in a single directory? Do they create some sort of index? Are there limits to the number of files a directory can hold? Is that what inodes are for? I remember seeing “inode” limits on some VPS I was using a while back.
Re:SQLite the only database you will ever need in most cases by majewsky on Thursday April 15, 2021 @05:26AM (#26818156)
Indeed, putting too many files in a single directory is inefficient, both for read and write. When writing a file (or changing metadata like permissions), the entire directory inode may have to be rewritten. When searching for a file or opening it, the entire file list for the directory needs to be read (in the worst case where the file is at the end of the list).

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`.

Re:SQLite the only database you will ever need in most cases by loeg on Thursday April 15, 2021 @10:53AM (#26821203)
> When writing a file (or changing metadata like permissions), the entire directory inode may have to be rewritten.

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.

Re:SQLite the only database you will ever need in most cases by fnord123 on Thursday April 15, 2021 @12:19PM (#26822453)
Large directory sizes suck on NFS and parallel file systems like Panasas, Lustre, GPFS, and the like. Python and Rust's Cargo also suck on networked file systems and would be greatly improved by pushing things into a sqlite file.
Re:SQLite the only database you will ever need in most cases by deckard1 on Thursday April 15, 2021 @05:28PM (#26826340)
most things suck on networked file systems. I still have trauma from having to use NFS more than a decade ago, on systems that would freeze on boot because the network couldn't be reached or the NFS server was down.

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.

Re:SQLite the only database you will ever need in most cases by fnord123 on Thursday April 15, 2021 @05:42PM (#26826495)
Nfs locking broken.. excel? Sqlite ui on Windows? Are you sure you're not confusing SMB with nfs? I've had SMB cache files that ducks everything up. Not nfs.
Re:SQLite the only database you will ever need in most cases by simias on Thursday April 15, 2021 @05:41AM (#26818264)
That's sometimes true but it depends a lot on the filesystem used, and sometimes on the options used when building it. ReiserFS, ext4 and FAT 32 won't have the same performance profile.

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.

Re:SQLite the only database you will ever need in most cases by eru on Thursday April 15, 2021 @09:14AM (#26820051)
Yes, though I think there's some POSIX compatibility stuff that also makes big directories a hassle.
Re:SQLite the only database you will ever need in most cases by utxaa on Thursday April 15, 2021 @11:33AM (#26821695)
also ext4 (then 3 i think) used to look for dir entries linearly (it was a linked list of entries).
Re:SQLite the only database you will ever need in most cases by gugagore on Thursday April 15, 2021 @05:34AM (#26818218)
What about on the other end? Why not have each hex digit be a directory along a path? Then you have very, very few files per directory at the cost of deeper hierarchy. What's the practical downside?
Re:SQLite the only database you will ever need in most cases by pmiller2 on Thursday April 15, 2021 @07:36AM (#26819123)
Directories are just another type of file. So, if you do this, and your filenames are n characters long, you'll end up needing to do n file accesses just to find the file you're looking for. Unless the underlying file system does something to make that particular access pattern fast, well... it's going to be stupidly slow after a certain point.
Re:SQLite the only database you will ever need in most cases by loeg on Thursday April 15, 2021 @10:59AM (#26821269)
> Unless the underlying file system does something to make that particular access pattern fast

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.

Re:SQLite the only database you will ever need in most cases by eru on Thursday April 15, 2021 @11:46PM (#26829207)
I've played around with fuse a bit.

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?

Re:SQLite the only database you will ever need in most cases by ArneBab on Thursday April 15, 2021 @08:04AM (#26819379)
You then need to do more stat calls — one per hierarchy level — and you waste more inodes.
Re:SQLite the only database you will ever need in most cases by thewakalix on Thursday April 15, 2021 @06:37AM (#26818684)
[epistemic status: amateur speculation]

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.

Re:SQLite the only database you will ever need in most cases by loeg on Thursday April 15, 2021 @10:54AM (#26821217)
More overhead for each extra layer. Kernel serialization/locking, random seeks (dirent and inode per level), maybe other concerns.
Re:SQLite the only database you will ever need in most cases by eru on Thursday April 15, 2021 @11:48PM (#26829213)
The random seeks aren't a given: your filesystem could do something clever with memory or disk layout to avoid most of them.
Re:SQLite the only database you will ever need in most cases by loeg on Friday April 16, 2021 @10:27AM (#26834303)
Anything particular in mind? You could probably get a good academic paper or patent out of this.
Re:SQLite the only database you will ever need in most cases by utxaa on Thursday April 15, 2021 @11:26AM (#26821600)
if this was the case - even if rewriting the inode was O(n) - you would have noticed. try ls on a large directory, now touch a file on same directory.
Re:SQLite the only database you will ever need in most cases by xorcist on Thursday April 15, 2021 @06:32AM (#26818655)
"Modern" file systems (say, ext3 and onwards) scales pretty well.

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).

Re:SQLite the only database you will ever need in most cases by nh2 on Thursday April 15, 2021 @08:35AM (#26819667)
> Are there limits to the number of files a directory can hold?

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...

Re:SQLite the only database you will ever need in most cases by bentcorner on Thursday April 15, 2021 @01:55PM (#26823908)
WinFS was an attempt at this. It'd be interesting to learn why it was cancelled.

https://en.wikipedia.org/wiki/WinFS

Re:SQLite the only database you will ever need in most cases by tinus_hn on Thursday April 15, 2021 @06:43PM (#26827117)
Because it tries to make people work in a way they don’t want to work. People want to organize their files in folders. They don’t want to tag them all with labels or tags and they don’t want to input all that metadata.

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.

Re:SQLite the only database you will ever need in most cases by eru on Thursday April 15, 2021 @11:44PM (#26829200)
You are right that users don't want to do that busy work.

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'.)

Re:SQLite the only database you will ever need in most cases by tinus_hn on Friday April 16, 2021 @06:45AM (#26832192)
That’s all very nice but a filesystem needs to be able to deal with every type of file on the planet. Which means you can’t automatically detect the contents.

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.

Re:SQLite the only database you will ever need in most cases by utxaa on Thursday April 15, 2021 @11:23AM (#26821577)
you can :) even across nfs ... maildir etc ...
Re:SQLite the only database you will ever need in most cases by rurban on Saturday April 17, 2021 @10:20AM (#26844461)
But filesystems are secure (mostly, ignoring unicode issues) whilst sqlite is an insecure hack.

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...

Re:SQLite the only database you will ever need in most cases by bvm on Thursday April 15, 2021 @04:23AM (#26817612)
> inside of Google, they are using databases to store filesystems.

I'm struggling to picture this, do you have any links I could read?

Re:SQLite the only database you will ever need in most cases by marcyb5st on Thursday April 15, 2021 @05:19AM (#26818093)
Googler here, I think OP is referring to the relationship between colossus and bigtable [1]. Basically, what happens is that colossus uses bigtable for storing metadata. So, when you issue a command like `ls /some-cluster-directory/myfolder/mysubfolder` you are really querying bigtable instead of a distributed ls on a cluster fs.

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...

Re:SQLite the only database you will ever need in most cases by eru on Thursday April 15, 2021 @09:16AM (#26820077)
Yes, that's something like I was going for.

(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.

Re:SQLite the only database you will ever need in most cases by iudqnolq on Thursday April 15, 2021 @09:49AM (#26820458)
From the outside one clue is that you can have two files with the same name, another is the url to a file doesn't change when you move it. Also it's dog slow to list a directory, like 30sec for 50 files.
Re:SQLite the only database you will ever need in most cases by hansvm on Thursday April 15, 2021 @11:53AM (#26822012)
You can experiment with something kind of like this yourself pretty cheaply. Here's an example [0] user-space filesystem that stores its data in an in-memory sqlite file.

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...

Re:SQLite the only database you will ever need in most cases by c22 on Thursday April 15, 2021 @12:40PM (#26822809)
You used to be able to store your own filesystems on google's databases [0]. No idea if this still works, though.

[0] http://sr71.net/projects/gmailfs/

Re:SQLite the only database you will ever need in most cases by exyi on Thursday April 15, 2021 @11:47AM (#26821917)
Filesystem is like a database. Except for transactions, which is very important when multiple processes can access it.
Re:SQLite the only database you will ever need in most cases by nix23 on Thursday April 15, 2021 @04:05AM (#26817476)
I think the difference of a Database and a Filesystem is:

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.

Re:SQLite the only database you will ever need in most cases by eru on Thursday April 15, 2021 @04:17AM (#26817555)
What do you mean by `you know where'?

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.

Re:SQLite the only database you will ever need in most cases by hannofcart on Thursday April 15, 2021 @05:07AM (#26817997)
> 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

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.

Re:SQLite the only database you will ever need in most cases by nh2 on Thursday April 15, 2021 @08:29AM (#26819606)
Note that using SQLite over SSHFS is dangerous if you're not the only user of the DB, because SSHFS does not support file locking.

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.

Re:SQLite the only database you will ever need in most cases by loeg on Thursday April 15, 2021 @11:02AM (#26821315)
If you're just using sshfs for RO queries for debugging, it should be harmless.
Re:SQLite the only database you will ever need in most cases by tedunangst on Thursday April 15, 2021 @02:28PM (#26824382)
It will try to replay the journal on open, though, even without running a query if I'm not mistaken. (There's a read only flag for open, just have to be sure you're using it.)
Re:SQLite the only database you will ever need in most cases by hannofcart on Thursday April 15, 2021 @10:55AM (#26821227)
I wasn't aware of this. Thanks for the information. Is this true even when you use a read-only sshfs mount?
Re:SQLite the only database you will ever need in most cases by loeg on Thursday April 15, 2021 @11:00AM (#26821292)
No. Read-only cannot mutate the database. I don't know if Sqlite ensures you won't observe inconsistent results if there is a concurrent (single) writer.
Re:SQLite the only database you will ever need in most cases by nh2 on Thursday April 15, 2021 @10:16PM (#26828615)
The docs [1] and this [2] suggest that you'll probably observe inconsistent results.

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

Re:SQLite the only database you will ever need in most cases by justsomeuser on Thursday April 15, 2021 @07:36AM (#26819129)
I just scp that son of a b down and run all the queries on the SQLite file locally.
Re:SQLite the only database you will ever need in most cases by spicybright on Thursday April 15, 2021 @02:45PM (#26824583)
Sounds like a pain for larger databases.
Re:SQLite the only database you will ever need in most cases by justsomeuser on Thursday April 15, 2021 @03:23PM (#26825016)
Well it depends how large you are talking about. With todays internet speeds, anything under 10GB is a less than a few minutes.

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.

Re:SQLite the only database you will ever need in most cases by spicybright on Thursday April 15, 2021 @07:34PM (#26827494)
I guess there's a lot of cases I just want to peek into a table live without pulling it all down. I've only used postgres so it's weird to me you can't talk to any MySql style DB like it does.

Anything that needs to be coded I'll pull it down though, it's nice to have options.

Re:SQLite the only database you will ever need in most cases by tzs on Thursday April 15, 2021 @10:44AM (#26821115)
> In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do.

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.

Re:SQLite the only database you will ever need in most cases by exyi on Thursday April 15, 2021 @11:50AM (#26821958)
Depends on what you need. It does not seem like a great idea to run Postgres in a browser, but it is usually less resource hungry than the JVM-based app using it to return HTML or JSON, so I can call it lightweight for server use.
Re:SQLite the only database you will ever need in most cases by nawgz on Thursday April 15, 2021 @12:28PM (#26822620)
> 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

Re:SQLite the only database you will ever need in most cases by dragonwriter on Thursday April 15, 2021 @01:59PM (#26823979)
> With the deprecation of Web SQL, I highly doubt this is a use-case anyone cares for anymore.

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.

Re:SQLite the only database you will ever need in most cases by ptman on Friday April 16, 2021 @09:23AM (#26833552)
There are people compiling sqlite to webasm, so it's a use-case some care for.
Re:SQLite the only database you will ever need in most cases by derefr on Thursday April 15, 2021 @03:22PM (#26825005)
> This means it can even be used on many embedded systems far too small to run a Unix.

A good example is that SQLite is used inside many video games. You wouldn't put Postgres in your game engine.

Re:SQLite the only database you will ever need in most cases by spicybright on Thursday April 15, 2021 @07:36PM (#26827509)
Is using something with that small a foot print even worth it in today's age? Surely postgres and other don't have THAT big of an overhead, at least for the features they provide.
Re:SQLite the only database you will ever need in most cases by api on Thursday April 15, 2021 @01:26PM (#26823507)
I like everything about Postgres except how arcane it is. It feels like 1980s Unix software, and everything about it is manual. It seems like there are multiple ways to do many things and I never feel like I am sure I am doing it the "right" way (as in both correct and modern and what you should do).

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.

Re:SQLite the only database you will ever need in most cases by ellimilial on Thursday April 15, 2021 @03:27AM (#26817186)
It can’t run embedded. Having no live parts is a real paradigm shift for so many scenarios.
Re:SQLite the only database you will ever need in most cases by andrewstuart on Thursday April 15, 2021 @03:30AM (#26817203)
What does "can't run embedded" mean exactly?

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.

Re:SQLite the only database you will ever need in most cases by yaur on Thursday April 15, 2021 @03:33AM (#26817236)
"can't run embedded" means you have to have a dedicated DB server process. For anything I would use a database for this is a non issue, but if you want a DB in your mobile app (for example) it might be.
Re:SQLite the only database you will ever need in most cases by skohan on Thursday April 15, 2021 @04:10AM (#26817503)
So if SQLite is typically run embedded in the client application, does this mean it's not supported to have multiple clients operating on the same SQLite DB concurrently, or is there some support for this?
Re:SQLite the only database you will ever need in most cases by hypertele-Xii on Thursday April 15, 2021 @04:35AM (#26817707)
It uses filesystem locking mechanisms to synchronize concurrent access from multiple processes. One writer, multiple readers.
Re:SQLite the only database you will ever need in most cases by devin on Thursday April 15, 2021 @04:32AM (#26817676)
This is covered early on in the article.
Re:SQLite the only database you will ever need in most cases by thomascgalvin on Thursday April 15, 2021 @09:24AM (#26820151)
It's mentioned briefly in the article, but no instructions are given on how to properly configure busy_timeout and WAL to scale above more than a few concurrent writes per second.
Re:SQLite the only database you will ever need in most cases by millerm on Thursday April 15, 2021 @08:52AM (#26819826)
It’s sad when most people discuss things based on a one line title of something they haven’t read. Many questions here were answered in that article. I recently asked someone on a different site “didn’t you read the article?”. The obvious answer I alluded to was “No, I don’t have time.” Yet they managed to post about 15 different comments asking more questions about the article (title). I really wish there was a way to have a public forum with proper discourse with the caveat that people must read the material before they comment on it.
Re:SQLite the only database you will ever need in most cases by 5cents on Thursday April 15, 2021 @07:43PM (#26827549)
The technical blog of the public broadcaster of Norway has implemented exactly this: https://nrkbeta.no/2017/08/10/with-a-quiz-to-comment-readers...

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

Re:SQLite the only database you will ever need in most cases by millerm on Monday April 19, 2021 @02:07PM (#26866082)
Thanks for the links. :-)
Re:SQLite the only database you will ever need in most cases by webmobdev on Thursday April 15, 2021 @08:30AM (#26819613)
Kind of - your web application needs to partially handle that, and SQLite also depends on the OS filesystem to handle concurrent access.
Re:SQLite the only database you will ever need in most cases by mfontani on Thursday April 15, 2021 @03:32AM (#26817234)
Can it be contained within the application, like SQLite?

Or has it to run as a separate process?

Re:SQLite the only database you will ever need in most cases by aqme28 on Thursday April 15, 2021 @12:08PM (#26822271)
But if the metric is "need in most cases," then that's fine. You don't need an embedded database in most cases.
Re:SQLite the only database you will ever need in most cases by ellimilial on Thursday April 15, 2021 @04:19PM (#26825625)
The other way around. For most problems you don't need a DB. Only for the subset of those that do you need a dedicated process/container/machine.

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.

Re:SQLite the only database you will ever need in most cases by NicoJuicy on Thursday April 15, 2021 @03:31AM (#26817221)
And Postgress can be used as an event store + document store.

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)

Re:SQLite the only database you will ever need in most cases by atat7024 on Thursday April 15, 2021 @04:37AM (#26817721)
You can store files/documents within SQLite dbs as well.
Re:SQLite the only database you will ever need in most cases by NicoJuicy on Thursday April 15, 2021 @05:01AM (#26817938)
There is JSON support, yeah.

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.

Re:SQLite the only database you will ever need in most cases by ngrilly on Thursday April 15, 2021 @10:36AM (#26821039)
You don't really need v8 JavaScript functions built-in in the database when you use SQLite, because SQLite is running in the same process than your application. The round trips between your application and SQLite cost almost nothing.

Also see "Many Small Queries Are Efficient In SQLite": https://sqlite.org/np1queryprob.html

Re:SQLite the only database you will ever need in most cases by NicoJuicy on Thursday April 15, 2021 @11:00AM (#26821288)
I was literally talking about using v8 functions when managing data ( eg. updating event versions should be a clear use-case)

Round-trips and small queries don't have anything related to my comment?

Re:SQLite the only database you will ever need in most cases by ngrilly on Thursday April 15, 2021 @01:17PM (#26823381)
Since SQLite is embedded in your program, why not use your programming language directly. SQLite can even callback in custom functions you define. For example in Python: https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne....

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)

Re:SQLite the only database you will ever need in most cases by NicoJuicy on Thursday April 15, 2021 @01:33PM (#26823600)
I didn't consider it that way, thanks.
Re:SQLite the only database you will ever need in most cases by ngrilly on Thursday April 15, 2021 @01:54PM (#26823891)
My initial comment was a bit cryptic to be honest :)
Re:SQLite the only database you will ever need in most cases by NicoJuicy on Thursday April 15, 2021 @02:10PM (#26824140)
You resolved it perfectly.

My experience with flatfiles dbs was not enough to interpret it correctly :p

Re:SQLite the only database you will ever need in most cases by Annatar on Thursday April 15, 2021 @05:32AM (#26818198)
It's unbelievably powerful, lightweight and there's not much it can't do.

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.

Re:SQLite the only database you will ever need in most cases by TicklishTiger on Thursday April 15, 2021 @03:33AM (#26817240)
What makes multi-tenancy easier in Postgres than in any other DB?
Re:SQLite the only database you will ever need in most cases by andrewstuart on Thursday April 15, 2021 @03:45AM (#26817336)
I wrote a full multitenancy row level security system in 3 short Python functions in Django. Simple, complete, compatible with Django's architecture. All the other multitenancy solutions I researched were complex and fiddly and heavyweight and required all sorts of considerations and caveats to implement and run.
Re:SQLite the only database you will ever need in most cases by hardwaresofton on Thursday April 15, 2021 @04:05AM (#26817472)
One of postgres's most underrated features. RLS is amazing, can be unseen/basically work silently if your programming language-side tools are good enough, and is documented well (like everything else):

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.

Re:SQLite the only database you will ever need in most cases by switch007 on Thursday April 15, 2021 @03:48AM (#26817353)
I’d love to see the code if you can share.
Re:SQLite the only database you will ever need in most cases by andrewstuart on Thursday April 15, 2021 @04:04AM (#26817468)
I'm preparing it into a minimal open source project over the next couple of weeks. I'll post to HN when it's ready.

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.

Re:SQLite the only database you will ever need in most cases by arthurcolle on Thursday April 15, 2021 @03:57AM (#26817415)
if you dont mind sharing, I'm curious what the methods + method signatures are!
Re:SQLite the only database you will ever need in most cases by znpy on Thursday April 15, 2021 @12:24PM (#26822533)
I've seen mysql used in multi-master ha configuration (percona-based solution) successfully, do you have anything to recommend for a true multi-master postgresql solution (ideally open source)?
Re:SQLite the only database you will ever need in most cases by rattray on Thursday April 15, 2021 @03:37PM (#26825194)
Postgres-XL and Postgres-BDR are both multi-master. I don't think they're open-source.
Re:SQLite the only database you will ever need in most cases by jhgb on Thursday April 15, 2021 @10:00AM (#26820592)
> 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.

I found https://kristaps.bsd.lv/sqlbox/ to be an interesting approach.

Re:SQLite the only database you will ever need in most cases by bjacobt on Thursday April 15, 2021 @09:42AM (#26820379)
Can you share more on how you do multitenancy? Are you using it for web/mobile? What language/Library do you use to access the database?

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.

Re:SQLite the only database you will ever need in most cases by andrewstuart on Thursday April 15, 2021 @10:02AM (#26820614)
Refer to this comment elsewhere in this thread: https://news.ycombinator.com/item?id=26817468

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.

Re:SQLite the only database you will ever need in most cases by blacktriangle on Thursday April 15, 2021 @09:38AM (#26820328)
By multi tenancy is dead easy in Postgres, are you talking about schemas?

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.

Re:SQLite the only database you will ever need in most cases by andrewstuart on Thursday April 15, 2021 @10:04AM (#26820633)
Refer to this comment elsewhere in this thread: https://news.ycombinator.com/item?id=26817468

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.

Re:SQLite the only database you will ever need in most cases by 7thaccount on Thursday April 15, 2021 @08:41AM (#26819723)
A lot of IDEs and other graphical tools can point towards a SQLite database. Does it really have to be local though? I assumed you could hit it from any external file share? Or did you mean something else?
Re:SQLite the only database you will ever need in most cases by aabbcc1241 on Monday April 19, 2021 @08:52AM (#26862176)
You can remote access sqlite database with sshfs or curlftpfs. Or simply sqlite cli over ssh
Re:SQLite the only database you will ever need in most cases by gher-shyu3i on Thursday April 15, 2021 @08:59AM (#26819894)
What's wrong with MySQL? Genuine question.
Re:SQLite the only database you will ever need in most cases by relaxing on Thursday April 15, 2021 @09:07AM (#26819974)
There’s nothing “wrong” with MySQL/MariaDB - it powers huge portions of the web.

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.