10 ms·

This is all pretty sane, except the schema-less part. I just don't understand why people get all hung up over schemas. Sure, migrations are a minor inconvenie

This is all pretty sane, except the schema-less part. I just don't understand why people get all hung up over schemas. Sure, migrations are a minor inconvenience, but if you just add fields in an ad-hoc fashion over time the data becomes messy and it's hard to determine any invariants about a large dataset. Sure this is avoided through careful code organization, but then aren't you just re-inventing schemas out-of-band?

gizzlon 14y ago
I think this is due to how you work and what you build. If you plan it out, focus on the data structures you need and then build it, schemas are fine because you know up-front what you want. In more ad-hoc, constantly changing, "ops this didn't work because of unknown factor X" type of projects, schemas are a pain. It's sounds really nice to have a data store that adapts when it's impossible to know up-front what you need from your data structures.
- yummyfajitas 14y ago
  In more ad-hoc, constantly changing, "ops this didn't work because of unknown factor X" type of projects, schemas are a pain. Such a pain: ALTER TABLE foo DROP COLUMN bar; ALTER TABLE foo ADD COLUMN baz varchar(64); From what I've seen, a significant chunk of the desire to use schemaless NoSQL hipster DBs is simply a desire to avoid learning SQL as if it were a real programming language. The only real use case I've ever seen for schemaless DBs is fields with lots of ad-hoc columns added by multiple people (typically logging/metrics data).
  - VLM 14y ago
    "lots of ad-hoc columns added by multiple people" Inevitably completely un-normalized and junk data. Even worse with no documentation or procedure anything ever added becomes permanent legacy that can never be removed. Been there, lived it, hated it, won't allow it to happen again.
    - yummyfajitas 14y ago
      So you won't allow logfiles to happen again?
      - VLM 14y ago
        LOL, no, not as a primary store of data, no never again. Plain text logs are a great place to funnel all "unable to connect to database" type of errors for dbas / sysadmins to ponder, however. I've implemented quite a few systems where data changes are logged into a log table, all fully rationalized, so various reports can be generated about discrepancies and access rates and stuff like that. This is also cool for endusers to see who made the last change etc. Trying to reverse engineer how some data got into a messed up condition using logs that can be JOINed to actual data tables as necessary is pretty easy, compared to trying to write a webserver log file parser to read multiple files to figure out who did what, when, to the data resulting in it being screwed up. You parse log files for data just once before you decide to do that stuff relationally. Debug time drops by a factor of 100x.
        DenisM 14y ago
        I'd like to pick your brain as that's the problem I'm facing right now - I have a web site that is accessed by users, and I would like to get a comprehensive picture of what they do. I already have a log table for logging all changes (as you said - I can show it to the users themselves so they know who hanged what in a collaborative environment), but I struggle defining meaningful way to log read access - should I record every hit? Would that be too much data to store and process later? Should I record some aggregates instead?
        seestheday 14y ago
        It sounds to me like you're trying to reinvent web analytics. Is there a reason you need user level granularity or is aggregate data enough?
        14y ago
  - jes5199 14y ago
    I've worked on systems where changing a single column meant that we'd have to take four hours of downtime while MySQL slowly...did whatever it does.
    - VLM 14y ago
      Is this the old bug (or whatever) from many years ago where it was faster to drop the indexes, make your schema change, then add the indexes back? Not sure if that still applies anymore. The scenario was something like if you change the length of a column such that the index needs to recalc it, like maybe truncate a CHAR(15) to a CHAR(10), or do something weird with full text indexes, it would loop thru each row, recalc the row for the index, completely sort and insert the row into the index, and then repeat for the next line. So it scaled as if you were inserting each row one line at a time (which can be pretty slow with lots of rows and extensive indexes) but there's a sneakier way to do it. Or, if by change a column, you mean something like an "update table blah set x=x+1;", and the x column was part of an index, that used to really work the indexing system hard, one individual row at a time. I think that issue was optimized out a long time ago. I believe there was a sneaky way to optimize around it other than the index drop and create trick, by doing all 10 million increments as part of a transaction such that it would do all 10 million increments, close out the transaction, then recalculate the index. Now there was something sneaky to the sneaky that you couldn't do a transaction on one update so you updated all the "prikey is even" and then updated all the "prikey is odd" or something like that as a two part transaction. I didn't exactly do this last week so if I misremember a detail...
- dasil003 14y ago
  > It's sounds really nice to have a data store that adapts when it's impossible to know up-front what you need from your data structures. This seems backwards to me. Relational databases are much better at ad-hoc querying of data, whereas NoSQL scales for narrower access patterns. The fact that you can dump arbitrary columns without a migration is a nice convenience, but in general it will be less queryable than it would be if you added the column in a SQL database.
raverbashing 14y ago
So that you can store/retrieve data without knowing the schema. Oh but you have to know the schema right? Yes, some other part of the application knows the schema, but this part doesn't have authority over the DB. Also, the schema may be data as well. NoSQL reduces the work needed for that.
- vasyainv 14y ago
  Perhaps, but can't you just organize your objects by having a table for each type and adding a column as needed? it doesn't sound like such a big deal
  - raverbashing 14y ago
    Looks like fun But why would I waste developer time doing that if I can only do db.table.insert(obj) - in MongoDB for example (obj is a JS object) Also, finding all objects with a field named 'field1' and value '3' is slower if you do that in a relational DB (and that's the simplest case)
    - mayank 14y ago
      Why on earth would that be slower in a relational db? That makes no sense, since indices in both nosql and relational dbs are variants of b-trees.
      - raverbashing 14y ago
        Well, it's slower because of a join that exists in SQL (the relationship between your 'field/value' table and the entry. Apart from that, as you said indexes are similar. The fun thing about NoSQL skeptics is how they think of only the current scenarios they work with, and they won't believe you until they get burned by it. So be it.
      - deleted 14y ago
        [deleted]
    - vasyainv 14y ago
derefr 14y ago
> but if you just add fields in an ad-hoc fashion over time the data becomes messy and it's hard to determine any invariants about a large dataset In systems large enough to be running multiple versions of an app at the same time, talking to the same database, you have to do exactly this--but you have to shoehorn it into relations (doing things like having a field where 99.999% of entries in a column are null, and gradually get computed as the row is accessed by the new version of the app.) NoSQL lets you just say what you mean--that in some versions of the app, the schema is X, in some versions it's Y, and the database isn't the arbiter of the schema.
- sergiosgc 14y ago
  No. NoSQL lets you say nothing about the schema, and so it becomes a problem for the application layer, above the DB, to handle. In fact, this is much what happens with most "solutions" NoSQL presents to SQL database problems: Let's not implement it, then it's not a problem. What happens when you push problems up the stack? Do they get solved automatically? No? Will they get solved? Perhaps, if really needed. And, for the cherry on top: Will the solutions be similar to the ones SQL databases use? They will. You see, when you are implementing atomic transactions, for example, you may get ahead if you have some information about the problem domain. However, for most cases, you are solving the same problem SQL databases solved decades ago. And you'll find the same solution. Just not as well implemented nor as well tested.
  - VLM 14y ago
    "Let's not implement it, then it's not a problem." Its interesting that culturally more than a decade ago, when mysql tried this strategy with transactions, namely, not having them until roughly the turn of the century, it was reviled mostly by people who don't know what transactions are nor did they need them, but were nonetheless very unhappy about mysql not having that checkbox get checked. Now its culturally seen as a huge win to simply not implement something difficult. I don't know if its a decline in feature list length as a fetish or just simple copy catting of others behavior (perhaps in both situations) or some kind of pull yourself up by your bootstraps romantic outlook on reimplementation or the inevitable result of homer simpson meets the database, but whatever it is, its an interesting major cultural change.
StavrosK 14y ago
Yeah, I have experimented with schemaless stores a bit (mostly with JSON fields in postgres, for now), to avoid adding columns for various things to the database, and I am not impressed. Not only is initialization and migration hard, but I've come to realize that there's no downside in adding these to the schema itself. Sure, the schema gets a bit bloated and dirty, but having undocumented fields in a dict whose existence is signified only by a line of code assigning something is not better. Where schemaless stores are great is prototyping. Especially for more algorithmic code, where I don't really know what storage I'll be needing and the algorithm will only live there for a few days, schemas are just a burden. That's why I wrote Goatfish: https://github.com/stochastic-technologies/goatfish https://github.com/stochastic-technologies/goatfish

gizzlon 14y ago

I think this is due to how you work and what you build. If you plan it out, focus on the data structures you need and then build it, schemas are fine because you know up-front what you want. In more ad-hoc, constantly changing, "ops this didn't work because of unknown factor X" type of projects, schemas are a pain. It's sounds really nice to have a data store that adapts when it's impossible to know up-front what you need from your data structures.

yummyfajitas 14y ago
In more ad-hoc, constantly changing, "ops this didn't work because of unknown factor X" type of projects, schemas are a pain. Such a pain: ALTER TABLE foo DROP COLUMN bar; ALTER TABLE foo ADD COLUMN baz varchar(64); From what I've seen, a significant chunk of the desire to use schemaless NoSQL hipster DBs is simply a desire to avoid learning SQL as if it were a real programming language. The only real use case I've ever seen for schemaless DBs is fields with lots of ad-hoc columns added by multiple people (typically logging/metrics data).
- VLM 14y ago
  "lots of ad-hoc columns added by multiple people" Inevitably completely un-normalized and junk data. Even worse with no documentation or procedure anything ever added becomes permanent legacy that can never be removed. Been there, lived it, hated it, won't allow it to happen again.
  - yummyfajitas 14y ago
    So you won't allow logfiles to happen again?
    - VLM 14y ago
      LOL, no, not as a primary store of data, no never again. Plain text logs are a great place to funnel all "unable to connect to database" type of errors for dbas / sysadmins to ponder, however. I've implemented quite a few systems where data changes are logged into a log table, all fully rationalized, so various reports can be generated about discrepancies and access rates and stuff like that. This is also cool for endusers to see who made the last change etc. Trying to reverse engineer how some data got into a messed up condition using logs that can be JOINed to actual data tables as necessary is pretty easy, compared to trying to write a webserver log file parser to read multiple files to figure out who did what, when, to the data resulting in it being screwed up. You parse log files for data just once before you decide to do that stuff relationally. Debug time drops by a factor of 100x.
      - DenisM 14y ago
        I'd like to pick your brain as that's the problem I'm facing right now - I have a web site that is accessed by users, and I would like to get a comprehensive picture of what they do. I already have a log table for logging all changes (as you said - I can show it to the users themselves so they know who hanged what in a collaborative environment), but I struggle defining meaningful way to log read access - should I record every hit? Would that be too much data to store and process later? Should I record some aggregates instead?
        seestheday 14y ago
        It sounds to me like you're trying to reinvent web analytics. Is there a reason you need user level granularity or is aggregate data enough?
        14y ago
- jes5199 14y ago
  I've worked on systems where changing a single column meant that we'd have to take four hours of downtime while MySQL slowly...did whatever it does.
  - VLM 14y ago
    Is this the old bug (or whatever) from many years ago where it was faster to drop the indexes, make your schema change, then add the indexes back? Not sure if that still applies anymore. The scenario was something like if you change the length of a column such that the index needs to recalc it, like maybe truncate a CHAR(15) to a CHAR(10), or do something weird with full text indexes, it would loop thru each row, recalc the row for the index, completely sort and insert the row into the index, and then repeat for the next line. So it scaled as if you were inserting each row one line at a time (which can be pretty slow with lots of rows and extensive indexes) but there's a sneakier way to do it. Or, if by change a column, you mean something like an "update table blah set x=x+1;", and the x column was part of an index, that used to really work the indexing system hard, one individual row at a time. I think that issue was optimized out a long time ago. I believe there was a sneaky way to optimize around it other than the index drop and create trick, by doing all 10 million increments as part of a transaction such that it would do all 10 million increments, close out the transaction, then recalculate the index. Now there was something sneaky to the sneaky that you couldn't do a transaction on one update so you updated all the "prikey is even" and then updated all the "prikey is odd" or something like that as a two part transaction. I didn't exactly do this last week so if I misremember a detail...
dasil003 14y ago
> It's sounds really nice to have a data store that adapts when it's impossible to know up-front what you need from your data structures. This seems backwards to me. Relational databases are much better at ad-hoc querying of data, whereas NoSQL scales for narrower access patterns. The fact that you can dump arbitrary columns without a migration is a nice convenience, but in general it will be less queryable than it would be if you added the column in a SQL database.

raverbashing 14y ago

So that you can store/retrieve data without knowing the schema. Oh but you have to know the schema right? Yes, some other part of the application knows the schema, but this part doesn't have authority over the DB. Also, the schema may be data as well. NoSQL reduces the work needed for that.

vasyainv 14y ago
Perhaps, but can't you just organize your objects by having a table for each type and adding a column as needed? it doesn't sound like such a big deal
- raverbashing 14y ago
  Looks like fun But why would I waste developer time doing that if I can only do db.table.insert(obj) - in MongoDB for example (obj is a JS object) Also, finding all objects with a field named 'field1' and value '3' is slower if you do that in a relational DB (and that's the simplest case)
  - mayank 14y ago
    Why on earth would that be slower in a relational db? That makes no sense, since indices in both nosql and relational dbs are variants of b-trees.
    - raverbashing 14y ago
      Well, it's slower because of a join that exists in SQL (the relationship between your 'field/value' table and the entry. Apart from that, as you said indexes are similar. The fun thing about NoSQL skeptics is how they think of only the current scenarios they work with, and they won't believe you until they get burned by it. So be it.
    - deleted 14y ago
      [deleted]
  - vasyainv 14y ago

derefr 14y ago

> but if you just add fields in an ad-hoc fashion over time the data becomes messy and it's hard to determine any invariants about a large dataset In systems large enough to be running multiple versions of an app at the same time, talking to the same database, you have to do exactly this--but you have to shoehorn it into relations (doing things like having a field where 99.999% of entries in a column are null, and gradually get computed as the row is accessed by the new version of the app.) NoSQL lets you just say what you mean--that in some versions of the app, the schema is X, in some versions it's Y, and the database isn't the arbiter of the schema.

sergiosgc 14y ago
No. NoSQL lets you say nothing about the schema, and so it becomes a problem for the application layer, above the DB, to handle. In fact, this is much what happens with most "solutions" NoSQL presents to SQL database problems: Let's not implement it, then it's not a problem. What happens when you push problems up the stack? Do they get solved automatically? No? Will they get solved? Perhaps, if really needed. And, for the cherry on top: Will the solutions be similar to the ones SQL databases use? They will. You see, when you are implementing atomic transactions, for example, you may get ahead if you have some information about the problem domain. However, for most cases, you are solving the same problem SQL databases solved decades ago. And you'll find the same solution. Just not as well implemented nor as well tested.
- VLM 14y ago
  "Let's not implement it, then it's not a problem." Its interesting that culturally more than a decade ago, when mysql tried this strategy with transactions, namely, not having them until roughly the turn of the century, it was reviled mostly by people who don't know what transactions are nor did they need them, but were nonetheless very unhappy about mysql not having that checkbox get checked. Now its culturally seen as a huge win to simply not implement something difficult. I don't know if its a decline in feature list length as a fetish or just simple copy catting of others behavior (perhaps in both situations) or some kind of pull yourself up by your bootstraps romantic outlook on reimplementation or the inevitable result of homer simpson meets the database, but whatever it is, its an interesting major cultural change.

StavrosK 14y ago

Yeah, I have experimented with schemaless stores a bit (mostly with JSON fields in postgres, for now), to avoid adding columns for various things to the database, and I am not impressed. Not only is initialization and migration hard, but I've come to realize that there's no downside in adding these to the schema itself. Sure, the schema gets a bit bloated and dirty, but having undocumented fields in a dict whose existence is signified only by a line of code assigning something is not better. Where schemaless stores are great is prototyping. Especially for more algorithmic code, where I don't really know what storage I'll be needing and the algorithm will only live there for a few days, schemas are just a burden. That's why I wrote Goatfish: https://github.com/stochastic-technologies/goatfish https://github.com/stochastic-technologies/goatfish