News for hackers. Stuff that matters.
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.