5 ms·

I've ran into similar issue as you described. Something that can be done so simple and quickly in SQL, was bewilderingly difficult to do in mongo. The schema-l

I've ran into similar issue as you described. Something that can be done so simple and quickly in SQL, was bewilderingly difficult to do in mongo. The schema-less database approach also seems attractive at first but updating your data whenever your "app schema" changes starts to become a pain real quick. Now I can't really live w/o having a schema first, it actually saves you a lot more time in the long run (even short run), being schema-less means you can't really do anything too fancy w/ your data (generate reports, advanced search, etc...)

lmm 14y ago
You still need to update your data when doing schema changes under SQL, and you have a lot less control over the process. And you can do anything to your data without a schema, you just need to build your app as a service that provides access to it. IME SQL schemas do more harm than good; usually you end up with a schema that's subtly weaker than what's actually valid for your application, and the difference between the two models will trip you up at the worst possible time. Have a small, distinct set of classes that you store, enforce that you store only those (and don't access the storage layer any other way), enforce that they remain backwards compatible, and enforce that you can't create invalid instances. But application code is the best place to do all these things.
- einhverfr 14y ago
  IME SQL schemas do more harm than good Completely disagree here. The basic tradeoff is between flexible input and flexible output. Without a rigid schema, ad hoc reporting is impossible because you don't have an ability to articulate reporting criteria. I.e. no declarative schema means no declarative reporting query. I suppose that's ok as long as you never need to report on anything..... Might work....
  - lmm 14y ago
    I'm not saying don't have a rigid schema, just don't enforce it at the storage layer. If you're doing an ad-hoc report then you wouldn't have indexes in place for it in the SQL case, so I don't see how it's any worse or harder in mongodb.
    - einhverfr 14y ago
      >If you're doing an ad-hoc report then you wouldn't have indexes in place for it in the SQL case, so I don't see how it's any worse or harder in mongodb. Only true in a case where you ahve to index everything you might want to search on, like with InnoDB. In PostgreSQL all you really need are your foreign key indexes and a couple (if that) of criteria indexes and you are good. That's more of an InnoDB limitation than a relational limitation. Basically InnoDB tables are primary key indexes and they can only be traversed in key order, not physical order, so sequential scans are painful....
      - lmm 14y ago
        If that level of indexing is sufficient I don't see why you can't just do the same thing in mongodb.
        einhverfr 14y ago
        >"If that level of indexing is sufficient I don't see why you can't just do the same thing in mongodb." What level of indexing is sufficient depends a great deal on the specifics of the database layout on disk. In InnoDB for example, sequential scans are very costly, and primary key lookups are very cheap. This is because the table is more or less contained in the primary key index and this must be traversed in key order since physical order is not supported. This means a sequential scan of a table means lots of random disk I/O and OS prefetching is useless. So to address this you end up indexing everything you want to search on later. Note that non-pk indexes are a little slower in InnoDB because you have to traverse the index to find the primary key value, then you have to traverse the primary key index to retrieve the table info. In PostgreSQL things work differently. The table is a series of pages on disk and rows are allocated from these as a heap. You can scan a table, but not an index, in physical order in PostgreSQL. Therefore typically PostgreSQL sequential scans on tables are lot faster than on MySQL because it is sequential, rather than random, page reads. Indexes point at the tuple ID which stores the page number and row number within a page. An index scan is a tree traversal followed by processing pages indicated in the tuple ID. This leads to a bunch of interesting things: Adding indexes is usually a performance win with InnoDB. However for PostgreSQL, it will typically look up what indexes it has and balance index scans against sequential scans of tables. Unlike InnoDB, sequential scans sometimes win out planner-wise, esp. on small tables. So what indexes you need depends quite highly on how things are organized.

lmm 14y ago

You still need to update your data when doing schema changes under SQL, and you have a lot less control over the process. And you can do anything to your data without a schema, you just need to build your app as a service that provides access to it. IME SQL schemas do more harm than good; usually you end up with a schema that's subtly weaker than what's actually valid for your application, and the difference between the two models will trip you up at the worst possible time. Have a small, distinct set of classes that you store, enforce that you store only those (and don't access the storage layer any other way), enforce that they remain backwards compatible, and enforce that you can't create invalid instances. But application code is the best place to do all these things.

einhverfr 14y ago
IME SQL schemas do more harm than good Completely disagree here. The basic tradeoff is between flexible input and flexible output. Without a rigid schema, ad hoc reporting is impossible because you don't have an ability to articulate reporting criteria. I.e. no declarative schema means no declarative reporting query. I suppose that's ok as long as you never need to report on anything..... Might work....
- lmm 14y ago
  I'm not saying don't have a rigid schema, just don't enforce it at the storage layer. If you're doing an ad-hoc report then you wouldn't have indexes in place for it in the SQL case, so I don't see how it's any worse or harder in mongodb.
  - einhverfr 14y ago
    >If you're doing an ad-hoc report then you wouldn't have indexes in place for it in the SQL case, so I don't see how it's any worse or harder in mongodb. Only true in a case where you ahve to index everything you might want to search on, like with InnoDB. In PostgreSQL all you really need are your foreign key indexes and a couple (if that) of criteria indexes and you are good. That's more of an InnoDB limitation than a relational limitation. Basically InnoDB tables are primary key indexes and they can only be traversed in key order, not physical order, so sequential scans are painful....
    - lmm 14y ago
      If that level of indexing is sufficient I don't see why you can't just do the same thing in mongodb.
      - einhverfr 14y ago
        >"If that level of indexing is sufficient I don't see why you can't just do the same thing in mongodb." What level of indexing is sufficient depends a great deal on the specifics of the database layout on disk. In InnoDB for example, sequential scans are very costly, and primary key lookups are very cheap. This is because the table is more or less contained in the primary key index and this must be traversed in key order since physical order is not supported. This means a sequential scan of a table means lots of random disk I/O and OS prefetching is useless. So to address this you end up indexing everything you want to search on later. Note that non-pk indexes are a little slower in InnoDB because you have to traverse the index to find the primary key value, then you have to traverse the primary key index to retrieve the table info. In PostgreSQL things work differently. The table is a series of pages on disk and rows are allocated from these as a heap. You can scan a table, but not an index, in physical order in PostgreSQL. Therefore typically PostgreSQL sequential scans on tables are lot faster than on MySQL because it is sequential, rather than random, page reads. Indexes point at the tuple ID which stores the page number and row number within a page. An index scan is a tree traversal followed by processing pages indicated in the tuple ID. This leads to a bunch of interesting things: Adding indexes is usually a performance win with InnoDB. However for PostgreSQL, it will typically look up what indexes it has and balance index scans against sequential scans of tables. Unlike InnoDB, sequential scans sometimes win out planner-wise, esp. on small tables. So what indexes you need depends quite highly on how things are organized.