22
In the defence of Object-Relational Mappers (event-driven.io)
submitted 2 years ago by to c/programming@beehaw.org
25 comments fedilink hide all child comments
 

I’m an accidental DBA, but I still never quite got the hate for ORMs. I thought this article does a good job explaining the issue, and why they aren’t so bad.

you are viewing a single comment's thread
view the rest of the comments
[–] [S] 7 points 2 years ago (1 child)

I’d actually say most data is suited for relational DBs, and that was pretty much what people realized after a few years of the NOSQL hypetrain.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 2 child comments
    •
  • [–] 3 points 2 years ago* (3 children)

    The article you linked disagrees - they said it pretty well:

    Of course, some issues come from the fact that people are trying to use the Relational model where it doesn’t suit their use case. That’s why I prefer a document model instead of a tabular one as the default choice. Most of our applications are more suitable for it, as we’re still moving the regular physical world (so documents) into computers. (Read also more in General strategy for migrating relational data to document-based).

    I never joined the NOSQL hype-train so I can't comment on that. However I will point out storing documents on a disk is a very well established and proven approach... and it's even how relational databases work under the hood. They generally persist data on the filesystem as documents.

    Where I find relational data really falls over is at the conversion point between relational document representation. That typically happens multiple times in a single operation - for example when I hit the reply button on this comment (I assume, haven't read the source code) this is what will happen:

    1. my reply will be sent to the server as a document, in body of a HTTP request
    2. beehaw's server will convert that document into relational data (with a considerable performance penalty and large surface are for bugs)
    3. PostgreSQL is going to convert that relational data back into a document format and write it to the filesystem (more performance issues, more opportunities for bugs)

    And every time the comment is loaded (or sent to other servers in the fediverse) that silly "document to relational to document" translation process is repeated over and over and over.

    I'd argue it's better, more efficient, to just store this comment as a document because over and over and over it's going to be needed in that format and anyway you ultimately need to write it to disk as a document.

    Yes - you should also have a relational index containing critical metadata in the document. The relationship linking that document to the comment that I replied to. The number of upvotes it has received. Etc Etc... but that should be a secondary database, not the primary one. Things like an individual upvote should also be a document, stored as a file on disk (in the format specified by AcitivtyStreams 2.0).

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 6 child comments
    •
  • [–] 4 points 2 years ago

    I'd argue it's better, more efficient, to just store this comment as a document because over and over and over it's going to be needed in that format and anyway you ultimately need to write it to disk as a document.

    You're assuming the file system overhead is smaller than the overhead of a database stored on the file system, and that's a pretty bold statement.

    The issue isn't storage and retrieval, it's search, it always has been. Your comment being a document or not isn't the hard part or the expensive part. Associating your comment with the post, comments, and likes is where the hard parts come in.

    Yeah, you could make every comment a document of sorts and store each one as a file and add an index that points to the files, but you'd be introducing a bunch of overhead caused by the file system.

    It's kind of a similar idea for why you don't store a movie as a directory with a picture for each frame. You absolutely could, but there are very good reasons not to do so.

    Database are just hyper optimized versions of "storing documents." I think you're severely overestimating the cost of moving a few bits around to change how data is represented.

  • permalink
  • fedilink
  • source
  • parent
  • [–] [S] 3 points 2 years ago* (1 child)

    and it’s even how relational databases work under the hood. They generally persist data on the filesystem as documents.

    I’m 99% certain this is wrong, which leads to a lot of your follow-up being wrong. Persisting data as documents would be atrocious for performance.

    I also disagree with the quoted part of the article.

    And for your case, sure, you could save it as document, maybe improve performance of a very light operation by 2X, just to have far worse performance for querying that data.

    edit: And in case you don’t mind the extra storage, and don’t care about correctness guarantees, you can just have a table that has all the metadata, but then have the comment also as a JSON blob which every modern db supports.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 2 child comments
    •
  • [–] 1 point 2 years ago* (last edited 2 years ago) (1 child)

    I’m 99% certain this is wrong

    ? This is how Postgres stores data, as documents, on the local filesystem:

    There are hundreds, even thousands, of documents in a typical Postgres database. And similar for other databases.

    But anyway, the other side of the issue is more problematic. Converting relational data to, for example, a HTTP response.

    Persisting data as documents would be atrocious for performance.

    Yep... it's pretty easy to write a query on a moderately large database that returns 1kb of data and takes five minutes to execute. You won't have that issue if your 1kb is a simple file on disk. It'll read in a millisecond.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 2 child comments
    •
  • [–] [S] 4 points 2 years ago (1 child)

    I’m 99% certain this is wrong

    ? This is how Postgres stores data, as documents, on the local filesystem:

    Those are not documents for a definition of document that works with the rest of your comment. If by document you mean "any kind of data structure", then yes, those are documents. But then the term becomes meaningless, as literally anything is a document.

    Yep… it’s pretty easy to write a query on a moderately large database that returns 1kb of data and takes five minutes to execute. You won’t have that issue if your 1kb is a simple file on disk. It’ll read in a millisecond.

    Sure, but then finding that document takes 5 minutes because you need to read a few million files first.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 2 child comments
    •
  • [–] 1 point 2 years ago* (last edited 2 years ago) (1 child)

    If by document you mean “any kind of data structure”, then yes, those are documents

    Yep — that is what I mean by documents, and it's what I meant all along. The beauty of documents is how simple and flexible they are. Here's a URL (or path), and here's the contents of that URL. Done.

    But then the term becomes meaningless, as literally anything is a document.

    No, because you can't store "literally anything" in a Postgres database. You can only store data that matches the structure of the database. And the structure is also limited, it has to be carefully designed or else it will fall over (e.g. if you put an index on this column, inserts will be too slow, if you don't have an index on that column selects will be too slow, if you join these two tables the server will run out of memory, if you store these columns redundantly to avoid a join the server will run out of disk space...)

    Sure, but then finding that document takes 5 minutes

    Sure - you can absolutely screw up and design a system where you need to read millions of files to find the one you're looking for, but because it's so flexible you should be able to design something efficient easily.

    I'm definitely not saying documents should be used for everything. But I am saying they should be used a lot more than they are now. It's so easy to just write up the schema for a few tables and columns, hit migrate, and presto! You've got a data storage system that works well. Often it stops working well a year later when users have spent every day filling it with data.

    What I'm advocating is stop, and think, should this be in a relational database or would a document work better? A document is always more work in the short term, you need to carefully design every step of the process... but in the long term it's often less work.

    Almost everything I work with these days is a hybrid - with a combination of relational and document storage. And often the data started in the relational database and had to be moved out because we couldn't figure out how to make it performant with large data sets.

    Also, I'm leaning more and more into using sqlite, with multiple relational databases instead of just a single database. Often I'm treating that database as a document. And I'm not alone, Sqlite is very widely used. Document storage is very widely used. They're popular because they work and if you are never using them, then I'd suggest you're probably compromising the quality of your software.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 2 child comments
    •
  • [–] [S] 2 points 2 years ago

    Yep — that is what I mean by documents, and it’s what I meant all along. The beauty of documents is how simple and flexible they are. Here’s a URL (or path), and here’s the contents of that URL. Done.

    But that content is meaningless, because you just saved an arbitrary data structure. It’s not as if you can do anything with those postgres files. Or those possibly multi GB MSSQL .mdf, .ldf, .ndf documents. That’s data (a word that’s imo far clearer than document) stored in a very specific way that you need to know the exact structure of to make any sense of. It’s not usable directly in any way. Not "Done."

    No, because you can’t store “literally anything” in a Postgres database.

    Yes you can. You can either add space for what you need to store, or you can, again, store e.g. a JSON blob.

    if you put an index on this column, inserts will be too slow, if you don’t have an index on that column selects will be too slow

    Or don’t, and it will only be as slow ass a NoSQL Database …

    A document is always more work in the short term

    It’s the opposite, a document db is far easier in the short term, that’s why everyone jumped on them before seeing the limitations.

    Yeah, a relational DB is harder because you have to have a good design, that allows you to do what you actually want to do. And if you none of your devs are good at SQL, then probably a document db is better. And yes, sometimes, you need nothing but a document DB. But I still heavily disagree that most of the time you want one.

  • permalink
  • fedilink
  • source
  • parent
  • [–] 1 point 2 years ago

    As long as you never store the same data in two places, documents are fine. But if you’re gonna store my name along with my id on the post you’re describing, then forget it. Your entire life is pain from now on and you will not give a single shit about join efficiencies, or converting between docs and relational, or maybe even your family, pets or home.

  • permalink
  • fedilink
  • source
  • parent