9 ms·

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

by GuB-42 4y ago

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.

tarr11 4y ago
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.
- ReactiveJelly 4y ago
  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)
  - latch 4y ago
    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 https://www.postgresql.org/docs/14/backup-file.html
    - deleted 4y ago
      [deleted]
  - bombela 4y ago
    Why does Docker makes you feel safer here? Is it because you feel more confident there is no database process still running?
    - ravi-delia 4y ago
      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!
    - ReactiveJelly 4y ago
      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.
  - hda111 4y ago
    Does this work even when the Postgres is updated to a newer version? This would make me want to use docker as well.
    - ReactiveJelly 4y ago
      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.
- 42jd 4y ago
  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 https://vldb.org/pvldb/vol15/p21-skiadopoulos.pdf
- kuboble 4y ago
  I believe hdf5 is the format that can serve that purpose.
- zokier 4y ago
  > 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
rrrrrrrrrrrryan 4y ago
> 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.
- timClicks 4y ago
  File systems are also "whole engines". Otherwise we wouldn't have named files or directory structures.
- hunter2_ 4y ago
  > 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.
- djrobstep 4y ago
  How is a filesystem not a "whole engine"?
  - rrrrrrrrrrrryan 4y ago
    A filesystem is, but a flat file alone is not. That was the only point of my comment - I was just being pedantic.
OJFord 4y ago
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.)
- funstuff007 4y ago
  > 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)?
  - notriddle 4y ago
    Tell LibreOffice to save the file as Flat XML.
- CRConrad 4y ago
  > 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?
- andrewstuart2 4y ago
  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.
DeathArrow 4y ago
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.
- pixl97 4y ago
  Portability to other systems.
  - malux85 4y ago
    Portability is more important than guaranteed atomicity, consistency, isolation and durability?
    - joebob42 4y ago
      I mean, not always, but I can certainly imagine cases where it might be.
    - irrational 4y ago
      Sometimes. It really depends on the use case. Use the right tool for the job.
  - therealdrag0 4y ago
    What kind of portability? Why isn’t a db portable?
  - cortesoft 4y ago
    A SQL dump is incredibly portable.
  - The_Colonel 4y ago
    Which platform do you miss, which SQLite does not support?
zitterbewegung 4y ago
I think that the direction is moving torward SQLite which is a flat file database that has the most installs from apps in general.
- encryptluks2 4y ago
  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.
eternalban 4y ago
> 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?
mkleczek 4y ago
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.
- Mehdi2277 4y ago
  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.
  - macintux 4y ago
    Read-only sharing is trivial. Concurrent write accesses, not so much.
  - mkleczek 4y ago
    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.
ntnsndr 4y ago
I love this point. I had honestly never thought of the question this way before.

by GuB-42 4y ago

tarr11 4y ago

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.

ReactiveJelly 4y ago
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)
- latch 4y ago
  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 https://www.postgresql.org/docs/14/backup-file.html
  - deleted 4y ago
    [deleted]
- bombela 4y ago
  Why does Docker makes you feel safer here? Is it because you feel more confident there is no database process still running?
  - ravi-delia 4y ago
    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!
  - ReactiveJelly 4y ago
    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.
- hda111 4y ago
  Does this work even when the Postgres is updated to a newer version? This would make me want to use docker as well.
  - ReactiveJelly 4y ago
    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.
42jd 4y ago
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 https://vldb.org/pvldb/vol15/p21-skiadopoulos.pdf
kuboble 4y ago
I believe hdf5 is the format that can serve that purpose.
zokier 4y ago
> 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

rrrrrrrrrrrryan 4y ago

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

timClicks 4y ago
File systems are also "whole engines". Otherwise we wouldn't have named files or directory structures.
hunter2_ 4y ago
> 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.
djrobstep 4y ago
How is a filesystem not a "whole engine"?
- rrrrrrrrrrrryan 4y ago
  A filesystem is, but a flat file alone is not. That was the only point of my comment - I was just being pedantic.

OJFord 4y ago

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

funstuff007 4y ago
> 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)?
- notriddle 4y ago
  Tell LibreOffice to save the file as Flat XML.
CRConrad 4y ago
> 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?
andrewstuart2 4y ago
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.

DeathArrow 4y ago

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.

pixl97 4y ago
Portability to other systems.
- malux85 4y ago
  Portability is more important than guaranteed atomicity, consistency, isolation and durability?
  - joebob42 4y ago
    I mean, not always, but I can certainly imagine cases where it might be.
  - irrational 4y ago
    Sometimes. It really depends on the use case. Use the right tool for the job.
- therealdrag0 4y ago
  What kind of portability? Why isn’t a db portable?
- cortesoft 4y ago
  A SQL dump is incredibly portable.
- The_Colonel 4y ago
  Which platform do you miss, which SQLite does not support?

zitterbewegung 4y ago

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

encryptluks2 4y ago
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.

eternalban 4y ago

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

mkleczek 4y ago

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.

Mehdi2277 4y ago
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.
- macintux 4y ago
  Read-only sharing is trivial. Concurrent write accesses, not so much.
- mkleczek 4y ago
  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.

ntnsndr 4y ago

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