7 ms·

Even with the inclusion of JSONB, I think Postgres is still lagging behind MongoDB by enforcing schema. After so many years doing web apps, I am seeing very lit

• cygned 10y ago
  Always wondering about constraints, relationships, entity/data reusablity and things like calculations, aggregations, ...; typical "SQL tasks" - how does one handle these in a document-oriented schemaless database like mongodb?
  • throwanem 10y ago
    Via corollary with Greenspun's tenth law, or not at all.
    • cygned 10y ago
      Thanks, didn't knew about that.
  • hartator 10y ago
    Shouldn't the web app itself enforce constraints? I don't think it's reasonable to enforce password strenght for example on the DB level.
    • wedowhatwedo 10y ago
      If your database knows a user's password, you are doing it wrong no matter what persistence layer you are using.
      • hartator 10y ago
        Why the DB coudln't do the hashing itself? Storing is the actual dangerous from a security perspective, plain passwords themselves just hitting your db or your web apps is fine as long you don't keep a log anywhere in plain.
        • danneu 10y ago
          You'd want to distribute the load of your slow password-hashing functions across your application servers rather than centralizing it in your single database.
          • icebraining 10y ago
            You can have more than on database node as well.
          • rpedela 10y ago
            Postgres bcrypt is wicked fast. You need some crazy load before it makes sense to move hashing out of the DB.
            • Xylakant 10y ago
              If your PW hashing function is wicked fast, you need to increase the work factor. The whole idea of a paw hash is to be comparatively slow, because that's one property that makes it harder to crack. Otherwise we could all go back to salted md5
            • jeltz 10y ago
              Please change to a higher factor. PostgreSQL's default is a bit outdated and too low for modern computers.
    • deleted 10y ago
      [deleted]
    • cygned 10y ago
      You are right, password strength is an example for an application layer constraint. However, by constraints I was more talking about things like "these properties need to be unique", "this column here must reference that column over there", "this column's values must be in this set" and so on. I know I could enforce these things in the application layer, but I don't think that's feasible.
      • hartator 10y ago
        Got you. Uniq indexes are a thing in MongoDB as well, but I can see that just another schema-like behavior and doesn't fit what I was saying earlier.
    • deleted 10y ago
      [deleted]
    • bsg75 10y ago
      What happens when your platform grows and a different app needs to use the same data? Enforce constraints in two places instead of one?
      • hartator 10y ago
        You still need your DB to be correctly sharded plus app constrains correctly distributed. It still more complicated than to enforce only in one place.
    • anarazel 10y ago
      If your constraints are about more than one row (e.g uniqueness, non overlapping ranges, foreign keys) it's very hard to do so clientside in a concurrent setup - unless you're ok with very coarse locking. Edit: Typo
    • garysieling 10y ago
      Database constraints are intended for use in distributed systems problems (e.g. two people create accounts with the same email, buy the last item in the store, etc). Whether you enforce password complexity in the database is a question of where your team is more comfortable working, and what the business requirements are.
      • hartator 10y ago
        Uniq indexes in MongoDB has been a thing since forever. You can enforce complex constrains like this in `>3.0`: db.createCollection( "contacts", { validator: { $or: [ { phone: { $type: "string" } }, { email: { $regex: /@mongodb\.com$/ } }, { status: { $in: [ "Unknown", "Incomplete" ] } } ] } } ) While still not having to define the rest of the schema that you don't want too. It feels more flexible.
        • icebraining 10y ago
          More flexible than what? You can do the same in Postgres, just use a single JSON field (which lets you store any structure) and add constraints on the JSON key(s).
        • garysieling 10y ago
          You can do things like this in Postgres as well - my point was that the database are designed to solve distributed systems problems, so your database of choice is a good place to solve those problems. I couldn't make a universal statement about where the constraints you're talking about should go without knowing the business rules for the application.
• narrator 10y ago
  The schema in the database can generate most of the ORM layer. If you're using a strongly typed language and ORM like Slick/Scala or C#/LINQ the compiler will actually prevent you from writing invalid queries or putting invalid data into the database. This will eliminate most runtime database errors. This can make development go really fast once you get the hang of it all.
  • hartator 10y ago
    Yeah, it's what Rails is doing. However, if you are doing an app like this, you are building logic around the db mainly. If you are that DB centric, why not having Postgres itself serve web requests? There is no consistency with the double approach of having a logic rich app and a logic rich db.
    • jnbiche 10y ago
      You mean "building logic around the db" by using ORMs as opposed to SQL queries?
    • Scarbutt 10y ago
      why not having Postgres itself serve web requests? Primarily, security.
      • ruslan_talpa 10y ago
        If by security you mean protection against DOS then yes. If you mean users accessing private data then you are wrong. Postgres (and all the other dbs) have the tools necessary to specify who can access what (one just has to use them).
    • kuschku 10y ago
      > why not having Postgres itself serve web requests? Good question! Why not have all the logic in the database? The answer is, you can actually do that. And it’s popular enough that someone founded a company on that concept, although they didn’t use Postgres, but their own self-built database. You might have heard of it: https://firebase.google.com/ https://firebase.google.com/ (They were acquired later, and are now Google’s top database offering).
• orf 10y ago
  Your tools are bad so postgres is bad? Use a ORM that handles migrations and queries.
  • hartator 10y ago
    If you are using something like Mongoid, you are declaring the schema only once directly in your rails app. How having your schema burried in migrations - however clean they are - is superior?
    • orf 10y ago
      That's not what I'm saying, I'm saying use an ORM that does not rely on migrations to form the schema. I can't think of a single one that does.
      • hartator 10y ago
        I don't get it. Isn't any good SQL ORM provide their own DSLs for migrations?
        • orf 10y ago
          Yes. Look, take for example the Django ORM. You specify the models in a python file. These are the central source of truth for what your database tables look like. If you want to add a column or change something in the schema you then generate the migrations from that. The migrations don't drive your schema, the schema drives the migrations. Proper tools don't make you have a bunch of .SQL files that organise your database.
          • pekk 10y ago
            Devil's advocate - how is it really better to have a bunch of autogenerated Django migration files?
            • orf 10y ago
• inglor 10y ago
  The downvoting is because you can have tables with single jsonb columns without schemas - call them "collections" and pretty much end up with a faster mongo up to having to fiddle a bit more with replication/sharding. No one is making you enforce schemas with PostgreSQL if you work this way. As for enforcements - there are things that are _impossible_ to enforce at an application level for atomicity reasons - for example - if you have a `users` table and you can't have duplicate email addresses - you'd need the operation "check if a user with the said email exists, if it does - return it, if it doesn't create it and return it" - PostgreSQL can do this easily, which prevents duplicate entries - Mongo - not so easily (see the "two phase commit" docs for how to simulate transactions).
  • hartator 10y ago
    Uniq indexes in MongoDB has been a thing since forever. You can enforce complex constrains like this in `>3.0`: db.createCollection( "contacts", { validator: { $or: [ { phone: { $type: "string" } }, { email: { $regex: /@mongodb\.com$/ } }, { status: { $in: [ "Unknown", "Incomplete" ] } } ] } } ) While still not having to define the rest of the schema that you don't want too. It feels more flexible.
  • orless 10y ago
    > check if a user with the said email exists, if it does - return it, if it doesn't create it and return it" ... Mongo - not so easily Can't you easily do it with upsert in Mongo?
    • edoceo 10y ago
      Yep. Put an unique index on the collection too.
      • petergeoghegan 10y ago
        I'm not sure that it's that simple in practice. MongoDB may fail to provide the same guarantees that Postgres UPSERT does, if not in theory then at least in practice. In other words, it seems like retrying within client code is necessary when the Wired Tiger storage engine happens to be in use: https://stackoverflow.com/questions/29305405/mongodb-impossible-e11000-duplicate-key-error-dup-key-when-upserting https://stackoverflow.com/questions/29305405/mongodb-impossi...
• lohengramm 10y ago
  The database schema serves as a single source of truth documentation. It is valuable by itself and not replaced by application level enforcement.
• vasachi 10y ago
  If you think of your database as a loosely constrained document storage - then yes, postgres is worse than mongo. But, if you think of your database as a single source of truth, then I almost can't imagine any reason to use mongo[1]. Without ACID transactions it is your problem to enforce all those pesky constraints across your whole codebase and data storage. And that work is HARD. For instance. I once had to implement a simple scheduling app. Basically, if worker A was scheduled to work from 2017-04-30T18:00:00 to 2017-04-30T18:30:00, then there can't be any overlapping schedule item for said worker. I guess you can do something like a two-phase commit with mongo, but I used a tsrange[1] and an exclusion constraint, and got everything almost for free. [1] Well, AFAIK mongo has better sharding. For now :) [2] https://www.postgresql.org/docs/9.6/static/rangetypes.html https://www.postgresql.org/docs/9.6/static/rangetypes.html [3] https://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION https://www.postgresql.org/docs/9.0/static/ddl-constraints.h...
• mamcx 10y ago
  I upvote you because that is a common sentiment, and is better to provide some reason why is better the way of RDBMS. --- A schema-less storage design have some valid uses cases. But even them leak a undeniable truth: All schema-less design are already a enforced schema, but more general. Even a KV store is a enforced schema. You have key, and values, and specific operations on them. Eventually, you will note that that schema-"less" design is too constrained / liberal. For the same reason you note that use all the time hash tables is not as good. You need arrays, and objects, and list, and records, etc. And you noted that some operations start to repeat themselves. Will be nice if that become abstracted? Right? And you will noted later that provide reliable results is very hard, specially if things are out-of-order, multi-thread, multi-process, etc.. Will be nice that that become abstracted, right? And then you can do all that yourself, in your language of choice. And is fine! But later, you will need to use another language. Now, you need to repeat all that effort AGAIN. Will be nice if you use something like all that about micro-services and stuff, and put the hard-part isolated and caring about that, and you can freely mix-match tech as you see fit? But then, you do all this, and you MISS THE DAMM SIMPLICITY OF A KV STORE!. How nice will be if exist a model, let's call it relational, that not only can model a KV store, but much MUCH MUCH more! TADA! You have reinvented a RDBMS!
• hibikir 10y ago
  There's a wide variety of reasons to want the schema in the database, and schemas are so valuable that even our good friends at Google decoded to add database level schemas to their massively distributed data stores. The strongest reason is that the database will complain loudly if you want to make a change that breaks existing constraints. An application, no matter how simple, will probably change, and you don't want to leave data out there that is unreadable and that will break your application's expectations. The schema minimizes the chances of garbage historical data that have been, in my experience the plague of NoSQL-backed apps. Often you find people that end up writing ad hoc constraint checkers, creating a poor imitation of what a database that understands schemas does well. Another big reason is that there's rarely just one application, and the applications could (and very often should) be written in different languages. A schema at that point becomes a service which just happens to not use Json or Thrift, but a far more complicated, sturdier one. You could still argue that databases are too complicated to expose very widely, but I'd much rather have all my data sitting in the very schema-heavy redshift to do reporting than to rely on application logic, or use most business insight tools out there. Also, let's not forget that schemas let databases do a lot of complex operations rather cheaply, because they can have very optimized code of very specific shapes next to the data. In the cases where 'first, I will take 50K rows out of the db and into my application' is just not acceptable, you have to let the DB do the work. In those cases, the interface to use a windowing function in a SQL database is IMO far more convenient than having to go into the classic remote map reduce function in erlang. I think there were plenty of gains from the NoSQL movement, especially as far as to start writing database where high availability and horizontally scalable loads are concerned. But outside of those realms, where few companies really are, I would not recommend a schemaless database today.
  • ianamartin 10y ago
    Good points all around, and I fully agree. Just to elaborate a little on your third paragraph, this is incredibly important as an app matures. You might start out thinking you're only ever going to have one interface to a datastore, but the reality is that you always have at least two. Someone has the ability to go directly to the store itself and make changes. That's life. Someone at some point in the lifetime of your app or company is going to go in and have to fix things by hand. If you have no schema and no constraints in the datastore itself, the odds of an error make things worse are far, far greater. We all know that's bad practice, but we all also know that sometimes you just have to do it for some reason. We also know that there will be times when someone is testing something on what they think is the local testing db but are actually connected to prod, and they do something experimental, and it screws things up. That's also a terrible thing to let happen, but we all know that it does. The second thing I'd like to point out that, much like sending email, any sufficiently useful application will, at some point, provide an API. And you're going to have to re-implement the backend validation again for that. So you know from the outset that if you are working on something you expect to be successful, you're going to have 3 access points. You're going to have to do the same work 3 times in 3 different places, unless you just decide to take the risk and use a db that doesn't have schema or constraints--which is a big risk. Doesn't make sense to me. Finally, I'd like to add to this good post that it's pretty easy to work in either direction with many ORMs. If you're comfortable with DBs, you can write out your migration scripts and use the ORM to generate models from it. If you prefer to work with an ORM, you can write the model and manage the migrations in the ORM. Either way, there's really not that much overhead compared to what you gain in safety and security and DRY. I'm not a DRY nazi, and when I'm prototyping, I'll repeat code anywhere I suspect a later iteration to diverge from the other instances of that code, and then refactor when I get closer to production. But when you're talking about database structure, I don't see how that can diverge, so it makes sense to me that you should never repeat yourself there. That's just asking for trouble. Do it once in the database, and do it right. Then all you have to do is trap database errors in your backend code and validate on the front end. That will be different for every interface, but it's a lot simpler and cleaner and safer than enforcing schemata and constraints in every codebase that accesses the data store.
• pbreit 10y ago
  I always wondered the opposite: what's the point of schemaless DB when you're going to have schema in the app? Also, joins.