Ask HN: Why Databases Instead of Filesystem?

[dead]

A database is a file system when you get down to it. The reason people use them is to abstract up a layer so you can query the data and get the results you want instead of having to iterate through direct reads of a disk, then having to read, parse, and filter what you want from those reads. You could always write code to help do those things direct from disk, but you know what you have just written if you do so? A database!

More of a sidenote than an answer but a database system can be faster than using the disk directly: https://sqlite.org/fasterthanfs.html#approx.

It turns out having a defined abstraction like a database makes things faster than having to rely on a rawer interface like filesystems because you can then reduce the number of system calls and context switches necessary. If you wanted to optimize that in your own code rather than relying on a database, you'd end up reinventing a database system of sorts, when (probably) better solutions already exist.

It turns out to be quite unreasonably difficult to put something aside for a time -- from milliseconds to decades -- and then go back and find it again. The difficulty is all in the "find", because things that anyone might care about finding have various different aspects that different people may care about. There is no middle ground between the electronic equivalent of a pile of papers on one's desk and the full capabilities of the relational model. Filesystems are somewhere in between; but there is no useful place in between.

[dead]

[dead]

Because you'll slowly start building the individual pieces of the database over the file system, until you've just recreated a database. Database didn't spawn out of nothing, people were writing to raw files on disk, and kept on solving the same issues over and over (data definitions, indexes, relations, cache/memory management, locks, et al).

So your question is: Why does the industry focus on reusable solutions to hard problems, over piece-meal recreating it every project? And when phased in that way, the answer is self-evident. Productivity/cost/ease.

Just like any other abstraction that helps you do things more efficiently. Database is an abstraction over the more crude file system. It is similar to asking the question "why not write direct assembly code instead of a programming language". The answer is the same.

I think there are a lot of good answers here, but it really comes down to the type of content being stored and access patterns.

A database is a data structure with (generally) many small items that need to be precisely updated, read and manipulated.

A lot of files don't necessarily have this access pattern (for instance rendering a large video file) ... a filesystem has a generic access pattern and is a lower level primitive than a database.

For this same reason you even have different kinds of database for different types of access patterns and data types (e.g Elasticsearch for full text search, MongoDB for JSON, Postgres for SQL)

Filesystem is generic and low-level, database is a higher order abstraction.

I do that a little bit, for simple cache where I don't need it to be always up to date but close enough and on a single instance only.

Some of my horizontally scaled services have like 500mb disk.

Historically, Unix (and many other operating systems) stored file names as an unsorted list so using the FS as KV store had O(N) lookup times whereas a single-file hashed database like dbm, ndbm, gdbm, bdb, etc gave you O(1) access.

If you're using a relational DB, like SQL, as a relational database, then it gives you a lot the FS doesn't give you. If you're using a relational database as a key-value store, SQLite is 35% than the filesystem [1]

Perhaps one of the biggest users of the filesystem as a KV store is git -- (not an llm, I just wanted to use --) .git/objects/xx/xxxxx maps the sha1 file hash to the compressed data, splayed by the first 2 bytes. However git also uses a database of sorts (.git/objects/pack/....). To sum up the git pack-objects man page, it's more efficient.

1. https://www.sqlite.org/fasterthanfs.html

The tabular SQL database generally regarded as the fastest, SQLite, is measured as only averaging about 3x faster than the file system assuming you are using well optimized approaches in each system. That said the file system could be faster depending upon the operation.

The advantages of a database:

* Locking - Tables applying locking conventions to prevent race conditions from multiple operations writing competing changes to the same data repository. If its only a single application that has access to the data this can be solved with queues, but locking is necessary when multiple applications write to the same data at the same time.

* API - SQL provides a grammar that many people are familiar with. In practice this all goes to shit when you have to write a bunch of stored procedures, SQL functions, or table triggers. I really don't like SQL.

* References - In RDBMSs records of tables can reference records of other tables using secondary keys that point to unique identifiers of the given other table. This is typically employed as secondary keys. This is also solved auto-magically in languages where objects are passed by reference, but that isn't the file system.

---

If your database, data system, whatever is in memory only there are very few real advantages to using something like SQL. If the data is on disk the file system is the lower level and is designed in such a way as to optimize access to the file system by something like SQL, such as with Logical Volume Manager that can create data volumes that span different hardware.

You might enjoy the FOSDEM talk I did about half a dozen years ago:

https://archive.fosdem.org/2021/schedule/event/new_type_of_c...

I adapted most of it into an article for The Register:

https://www.theregister.com/2024/02/26/starting_over_rebooti...

BTW: some early filesystems were more database-like:

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

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

They were more like BerkeleyDB and lacked Query Planner.

I think Oracle internally using something similar, i.e. a native filesystem optimized for an RDBMS.

There is no reason why a single data management system cannot be built that can do everything that either a file system or a database can do (I have been building one).

It is an object store called Didgets (i.e. Data Widgets). Each Didget has a specific type. One type is used to hold unstructured data like a file does. These Didgets are unsurprisingly called File Didgets. Other types of Didgets can hold lists (used to create hierarchical folders, music play lists, photo albums, etc.).

Others hold sets of Key/Value pairs which are used to create a tagging system for other Didgets or columns in a relational table.

Using a variety of Didgets, I have been able to create hierarchical file systems where a simple query can find one or thousands of files instantly out of 200 million+ based on the values of any tags attached.

In the same container (called a pod), it can store tens of thousands of relational tables; each one capable of having 100,000+ columns and billions of rows.

The system is 'multi-model' so it could manage hierarchical data, relational data, graph data, or anything managed by a NoSQL system.

It is not only versatile, but is incredibly fast.

[dead]

A lot of early games were more like file systems and they worked. I'm surprised nobody had mentioned ACID yet.

The API revolution might be another thing - you were able to swap out a database with any other. Risky decisions are fine when they're reversible. Databases were a more reversible way to deal with scaling and architecture.

databases solves coordination problems that filesystems couldnt. concurrent writes, atomic transactions, and structured querying across millions of records.

But, filesystem-databases idea is still exists. SQLite is essentially this. and I wrote an adapter for this. from experience, It look like capable of doing everything that other adapters do. but really slowly :D

For example; datrix.update('comment',/where/{ author(another file): { group(another file): { tag: 'abc'} } },/data*/ { name: 'new name', anotherRelation(another file): { connect:[123] } })

to find correct entry, this query has to check 3 file. that It replace all the results name with 'new name' and also connects another file's entry for them. what happens if another user wants to write authors same time. or names updated but while connecting another relation exception happened. I have to get back all updates. how I solved this. I lock filesystem while this query (all other requests blocked) I updated all data in memory and if anything happens on the way I revert. this is how slow this solution. what should I do to fix this. I must create another application. It has to manage all request. without blocking. and this is also we called database engine.

[flagged]

They're not exclusive. Quite often developers use a filesystem with a database.

Store the file on the filesystem with a unique name. Store the original name, the unique name, the owner, tags, a description, locking, auth, enforce uniqueness, and track access with the database.

Then try and keep things performant and handle concurrency!

Try doing all of the above just using a filesystem and you'll either:

1. Waste years making a rubbish database. 2. Do a bad job trying to do everything with flat files.

PostgreSQL/MySQL or SQLite are easy wins.