Hacker News Reader

Comment by GuB-42 | Hacker News Reader

Maybe it is just a reflection on the state of filesystems vs databases.

There is no fundamental difference between a database and a flat file, it is all bytes on a disk/memory in the end. So it is mostly a question of balancing the roles of the hardware, OS, and application software.

For example, if the reason you are using a database is that it does a particularly good job at limiting disk IO, then it may not be necessary with fast SSDs. If your reason for splitting into small files is to save RAM, it may not be necessary if you have more RAM. If you want to do to a distributed architecture, maybe your filesystem is not up to the task and you may want a database server.

View on HN

For me it's about proprietary vs. readable - unless it's obviously necessary for performance I think I'd still prefer 'flat file' to sqlite, but basically I just want something that isn't an incomprehensible blob, that I can use with other tools etc.

Bonus points for some open standard, but some kind of understandable format even if proprietary is miles ahead of proprietary and cryptic.

I wish Fusion360 for example had a sanely git-able on-disk format like:

    line 0xdeadbeef (0,0) - (123,456)
    line 0x01234567
    constraint parallel 0xdeadbeef 0x01234567

Etc., Or something.

(Actually I suppose git - and incremental backups - is the main reason I say I'd still prefer plaintext to db.)

Perhaps it was deliberate, but your last paragraph (plaintext vs db) really just reinforces your point of standards. There's nothing particularly special about plaintext aside from its total ubiquity.

Everything today uses and understands ASCII/UTF-8 and we've used that, plus some handy human conventions like limited character count between newline characters, to build up tools like git to help manage the changes in smaller chunks.

There's nothing preventing us from doing the same for other formats if they become commonly supported or expected. Much like SQLite and some of the tools cropping up.

> There's nothing particularly special about plaintext aside from its total ubiquity.

But ubiquity is extremely special and useful and essential if you want to maintain ownership of your data.

> There's nothing preventing us from doing the same for other formats if they become commonly supported or expected. Much like SQLite and some of the tools cropping up.

True. I mean if it would be common to put source code and text into sqlite files, then most tools would probably also deal with it. However there is a case about simplicity. For instance writing a diff algorithm on plain text files is much simpler and easy to understand, than writing diff algorithms on text or binary structures.

> For instance writing a diff algorithm on plain text files is much simpler and easy to understand, than writing diff algorithms on text or binary structures.

I'd argue the opposite. Coming up with a good, human readable, diff on unstructured text within acceptable runtime is not that easy. LCS is NP-hard and the naive algorithm has unacceptable performance and memory footprint.

However diffing something structured, especially when it has unique keys like in most databases, is really easy. Structure gives you an easy way to compare data systematically and is handy to express differences.

> I think I'd still prefer 'flat file' to sqlite, but basically I just want something that isn't an incomprehensible blob, that I can use with other tools etc.

An Sqlite database may be a binary blob, but thanks to the Sqlite software being open source and ubiquitous, it's pretty easily comprehensible. Sure, not quite as accessible as plain text[1], but not far from it.

So when you need something just a little bit more advanced than plain text (i.e. SQL functionality) it seems like a good compromise that doesn't -- pun intended -- too much compromise openness and accessibility.

___

[1]: But, hey, does "plain" text include UTF-8 nowadays or does it still mean ASCII-only?

> I wish Fusion360 for example had a sanely git-able on-disk format like:

There are two standards for this common to CAD, called IGES and STEP. They are both plaintext, though their gittability is questionable.

They are structured text though so I don't think you can just do deltas like like with code. You'd have to treat them like binaries would you not? That's the way we always kept our XML files in git forced to be binaries.

I would really like to see smudge/clean like-filters for git which convert between git objects and work dir files in deal with structured data or binary files more efficiently. So that diffs are minimal and readable even with those formats.

Code is (generally) structured too?

> main reason I say I'd still prefer plaintext to db

Is there a non-proprietary somewhat sane way to do version control for spreadsheets (Excel and LibreOffice)?

Tell LibreOffice to save the file as Flat XML.

> For me it's about proprietary vs. readable - unless it's obviously necessary for performance I think I'd still prefer 'flat file' to sqlite, but basically I just want something that isn't an incomprehensible blob, that I can use with other tools etc.

That is not exactly what proprietary means. Though I feel the same - plain text can be versioned and managed by various tools and that is great!

I was not aware I gave a definition for it. I didn't mean it as an antonym for 'readable', if that's what you mean.

Plaintext could be in a proprietary format but that's far better than proprietary compressed database that's a job to work out what even made it (if that wasn't proprietary itself!). Proprietary plaintext might even be better than open standard blobby format, depending on what you want to do with it. (Better for git and backup, worse for opening in competitor tools.)

I think their point was that proprietary is not a synonym for bespoke.

If your code is Free and Open Source, then its file-format is presumably not proprietary, whether or not it makes use of an existing standard format (like JSON) or solution (like SQLite).

A database can be relational, it can make some guarantees, it can pass Acid. With a file you are responsible for performance, reliability, integrity and security of the data.

Do you believe that you can do a better job than those tens of guys who contributed to a DBMS? Go ahead.

Do you not need reliability, integrity, security, performance? Go ahead.

So use a file to store data instead of database but know the trade-offs.

Yeah, none of that may matter for a "brochure" type of website, or a blog where you are the only person updating it.

As soon as you start processing forms and doing anything transactional based on that, you'll be quickly reinventing a lot of wheels if you aren't using a DBMS of some sort.

> none of that may matter for a "brochure" type of website, or a blog where you are the only person updating it

Doesn't Hacker News run on flat files?

But only the wheels you need, when you need them. Don’t carry the overhead unless needed.

ACID comes with costs, those guys may be smart but they're not magicians. As a rule of thumb, if you never handle a transaction failure or deadlock with something that is not retrying or failing, you shouldn't be using an ACID-based system since you're not getting any benefit from it.

And if you e.g. have multiple dataflow paths with different priorities then you also probably shouldn't be using a DBMS, because they're too much of a closed world. Using database technology as a library would be a better approach; otherwise sooner or later you'll find you need to customize the internals of your DBMS, and with most extant products that's somewhere between hard and impossible.

> As a rule of thumb, if you never handle a transaction failure or deadlock with something that is not retrying or failing, you shouldn't be using an ACID-based system since you're not getting any benefit from it.

Could you expand? Does this mean you wouldn't use an ACID database for CRUD?

Depends how you're interpreting the "U" in CRUD - if you have some complex business logic to resolve conflicting updates to the same object then maybe an ACID database would make sense. But certainly if you're just doing last write wins like 99% of CRUD webapps then using an ACID database is wasteful and pointless. The whole point of ACID is to say that you need these globally isolated transactions and you're willing to pay for them, and while database engines are pretty smart with MVCC and so on there's fundamentally only so much they can do.

99% of the time all you want to do is a) commit input events to a durable store as quickly as possible b) read a state-of-the-world that's consistent in the sense that it represents all the consequences of all the inputs up until some (logical) time t, and IME an event sourcing model is if anything better at doing that than an RDBMS is.

The filesystem provides its own ACID guarantees too. Well, some filesystems.

Portability to other systems.

Which platform do you miss, which SQLite does not support?

Portability is more important than guaranteed atomicity, consistency, isolation and durability?

I mean, not always, but I can certainly imagine cases where it might be.

Sometimes. It really depends on the use case. Use the right tool for the job.

What kind of portability? Why isn’t a db portable?

A SQL dump is incredibly portable.

> There is no fundamental difference between a database and a flat file, it is all bytes on a disk/memory in the end.

A database has a whole engine, which I'd argue is a pretty fundamental difference.

I think I get what you were trying to say, though: there's no fundamental difference between database tables and flat files, and there's no fundamental difference between a database and a filesystem.

> there's no fundamental difference between database tables and flat files

But "flat" essentially means "one table that doesn't reference (and isn't referenced by) other tables in a reliable way." It's a way of saying 2 dimensions (flat) versus 3+ dimensions (relational). The moment you start having multiple plain text files where columns act as primary and foreign keys, the system is no longer flat, despite using plain text files.

It seems to me that TFA and most comments here are really just talking about using a DBMS vs using plain text files, not using flat vs using relations.

File systems are also "whole engines". Otherwise we wouldn't have named files or directory structures.

How is a filesystem not a "whole engine"?

A filesystem is, but a flat file alone is not. That was the only point of my comment - I was just being pedantic.

Some databases also have the ability to be instantly portable, like a sqlite file.

You can tar up your flat files as well, but that is a separate process and can be time consuming for larger directories.

Perhaps there is a use case for a file system backed by portable database like sqlite, such that you can just mount the volume and send it around without pain, but can prefer to use normal command line utils (ls, rm, grep, etc) instead of interacting via SQL.

I’ve had this research paper on my reading list for a while (but haven’t gotten to reading the full thing)[1]. Not necessarily just a file system but It lays out an entire operating system backed by a database and OS state interactions are done through SQL.

1. https://vldb.org/pvldb/vol15/p21-skiadopoulos.pdf

If you're running Postgres in Docker, you can just stop the container and tar up the data volume and start it somewhere else

(This also works without Docker of course, but Docker makes me feel safer when I do stuff like this)

This procedure is officially documented (1).

Since we're talking about copying a folder, I'm not sure why you'd feel safer doing it via an additional layer of abstraction.

(1) https://www.postgresql.org/docs/14/backup-file.html

Why does Docker makes you feel safer here? Is it because you feel more confident there is no database process still running?

It's more about, I don't like having a global directory namespace that everything gets dumped into.

I know that PG data is usually in /var/lib/postgresql/data, but I like Docker because if I ever need a 2nd or 3rd database on one system, I can give them more meaningful names and not feel like I'm touching Important System-Wide Stuff.

If you're an ignorant fool such as myself, your knowledge of a database's internals is sufficient only to inspire fear. With Docker I know that whatever black magic the database uses is contained within a black box I can copy wholesale. Anything important must be in there, since I didn't give it anything else!

Does this work even when the Postgres is updated to a newer version? This would make me want to use docker as well.

I'd have to read the Postgres docs to be sure. Postgres has really good docs.

My intuition is that you're expected to do some kind of migration or export/import, because the on-disk format probably changes between major versions.

> You can tar up your flat files as well, but that is a separate process and can be time consuming for larger directories.

We have file formats like ODF which are just zip packages with regular files inside

I believe hdf5 is the format that can serve that purpose.

This opinion is quite popular nowadays but IMHO it is caused by misunderstanding what a database and database management system is and what it is for.

DBMS (and in particular) RDBMS is not a merely persistence medium for an application - DBMS main role is to _share_ data between applications.

I don’t understand this as distinction with file systems. Networked file systems exist and I have a lot of production code that shares data as files in either some networked file/blob system. Often databases even just store a reference to a file path for stuff like images/other large artifacts that are shared from some file system.

Indeed - a filesystem is a form of DBMS and provides data sharing capabilities. These capabilities are very poor though - especially its data model (or rather lack of one) makes data sharing difficult as applications cannot rely on it and implement their own proprietary data model instead.

Read-only sharing is trivial. Concurrent write accesses, not so much.

> There is no fundamental difference between a database and a flat file, it is all bytes on a disk/memory in the end.

Curious as to what is not just "all bytes on a disk/memory in the end"? Also can we please stop calling datastores databases?

I think that the direction is moving torward SQLite which is a flat file database that has the most installs from apps in general.

A flat file database? It is a single file. This means you need specialized tools to read it, and if you want to sync it a lot of cloud storage solutions will force you to resync every change. This is why a transparent file system db is great for integration into native storage. Databases don't really optimize for the file system layer. I am hopeful one-day we'll have an auto organizing flat file system that auto sorts and sections data in a predictable manner.

I love this point. I had honestly never thought of the question this way before.

Reply on news.ycombinator.com