4 ms·

>Schemaless does not exist. There is always a schema, except in a schemaless data store, the schema has been moved to app code I get what you're saying but kee

by jasode 10y ago

>Schemaless does not exist. There is always a schema, except in a schemaless data store, the schema has been moved to app code I get what you're saying but keep in mind that "schemaless" isn't a philosophical statement about denying ontology[1]. Instead, it's an industry label for avoiding database schema operations. E.g. a rapidly-evolving app doesn't know ahead of time all the rigid columns they need so they use "schemaless" db strategy to avoid "ALTER TABLE ADD COLUMN X" or avoid an export/import to new v2,v3,etc tables. From the relative point-of-view of the db engine, it doesn't see a "schema" when the so-called "columns" are embedded as strings inside of generic fields. [1] https://en.wikipedia.org/wiki/Ontology https://en.wikipedia.org/wiki/Ontology

VLM 10y ago
And without the adult supervision that "ALTER TABLE" theoretically implies, you end up with 15 columns named "t1" and the like, no idea what any of it means, and source code to match (assuming its not gone and you're trying to reverse engineer the schema-less data) There is a place for unorganized, disorganized, unengineered, unplanned data structures, but its not in the DB.
agentultra 10y ago
Change management is not something that should be feared if given careful consideration! I don't always know the "perfect" schema design up front but there are great tools in the modern SQL tool belt for managing abstractions and evolving schemas. In PostgreSQL: CREATE SCHEMA crm; The "crm" schema can store all of my customer relationship management related tables. I may not know all of the data I need for them right now and will need to evolve them over time. However if I start with a solid design for my public schema I can use views in the public schema: CREATE VIEW public.customers AS SELECT c.id, c.contact_name, c.email FROM crm.customers AS c; This is a simple view. You applications can CREATE/UPDATE/INSERT against the view in the public schema with no changes to your code. However if you want to evolve the schema of customers you can do so in the `crm` schema adding tables, columns, etc. CREATE VIEW public.customers AS SELECT c.id, c.contact_name, c.email FROM crm.customers AS c WHERE deleted = false; You can then evolve your public schema to include more information as needed by updating the view. The caveat is that if your view starts materializing data from more than one table you'll have to intercept INSERT/UPDATE/DELETE and replace them with your own functions to put the data in the right place. This works quite well for implementing nice features like administrative flags without having to update your application code or spread that logic out: it's all kept in the database. You still get the power of indexes, a strong query language, and structured data. PostgreSQL is great as an application server allowing you to comment all of your objects and having deep introspection facilities allows you to not only be flexible with your data models but also consistent. Update: Added an example of changing the public schema to add a filter to the query for an administrative flag
- noobsquared 10y ago
  Where can i learn more about this type modern SQL tools you mention? (Besides what you've just mentioned of course :D)
  - deleted 10y ago
    [deleted]
- wwweston 10y ago
  "PostgreSQL as an Application Server" is a book I'd be likely to buy.
  - agentultra 10y ago
    Like maybe https://agentultra.com/postgres-book.html https://agentultra.com/postgres-book.html ?
    - wwweston 10y ago
      Not sure if I'm more impressed if you'd already started work on it, or sketched up the page/TOC + mailchimp in response. :) But I signed up!
- flukus 10y ago
  So now every time I add a column I have to update the schema as well. That's making change management harder, not easier.
cwyers 10y ago
I don't really get this. ALTER TABLE ADD COLUMN X is a cheap operation when your database is small, at least on decent hardware. It's when you get up to scale that it starts being a burden. During the rapidly-evolving stage is when it should be the easiest to alter database schemas, because you shouldn't have accumulated all that much data yet.
- batmansmk 10y ago
  And... if you accumulate a lot of data,you may mostly access it in an aggregated way, such as GROUP BY. where ALTER TABLE ADD COLUMN X is free.
- PhineasRex 10y ago
  I work at a company with multi-gigabyte tables and ALTER TABLE is still easy to pull off, we just use tools the community has built for larger tables. It's one of the most unfounded fears I've heard in the industry.
dasil003 10y ago
Sure, but the cost of schema maintenance is grossly overstated by those who simply aren't familiar with the tooling to do so. Furthermore, the cost of dealing with the corrupt data down the line will absorb all the gains you had in a matter of months, unless of course you've reinvented strong schema control at the application layer. A lot of inexperienced devs look at NoSQL as being simpler than SQL because the dev documentation is shorter and has a more stylish theme. They then assume that all the touted benefits of SQL are YAGNI and they'll cross that bridge when they come to it. The mistake they make is assuming that the benefits of SQL databases are something that only is needed for a mature application. To the contrary, SQL gives an incredible hedge against the uncertainty of change in the early days of an app when churn is high. A normalized data structure allows much more query flexibility; you should only denormalize for performance once you realize what the workload and logic will look like for the long haul. Of course if you have a known workload that you want to scale up huge then that's a legitimate use case for NoSQL, but the purpose is not flexibility.
- hackits 10y ago
  Our current manager wants to transition over to NoSQL, as he doesn't see any value having to constantly update JPA Repositories and dependencies when a column is added or removed from the database table. I recommended that JPA Repositories shouldn't be stored at the lowest level of Maven jar package's eg... we have a very deep nested Maven pom dependencies (about 6 layers deep) of which the JPA repositories are defined in the lowest of lowest levels. Granted I gave references to research papers from Google that JPA Repositories and Hibernate ORM should be at the project layer (due to their nature of changing rapidly). Turns out, still going with the NoSQL solution. Although it seems like everyone have convinced themselves that NoSQL is faster development processes than a simple table with constraints.
  - hinkley 10y ago
    Leaf node dependencies should be very stable. Otherwise you spend all your time rebuilding or at least rerunning integration tests. I work at a company that has achieved the opposite of that with not one but two libraries, and deployment is a miserable experience.

by jasode 10y ago

VLM 10y ago

And without the adult supervision that "ALTER TABLE" theoretically implies, you end up with 15 columns named "t1" and the like, no idea what any of it means, and source code to match (assuming its not gone and you're trying to reverse engineer the schema-less data) There is a place for unorganized, disorganized, unengineered, unplanned data structures, but its not in the DB.

agentultra 10y ago

Change management is not something that should be feared if given careful consideration! I don't always know the "perfect" schema design up front but there are great tools in the modern SQL tool belt for managing abstractions and evolving schemas. In PostgreSQL: CREATE SCHEMA crm; The "crm" schema can store all of my customer relationship management related tables. I may not know all of the data I need for them right now and will need to evolve them over time. However if I start with a solid design for my public schema I can use views in the public schema: CREATE VIEW public.customers AS SELECT c.id, c.contact_name, c.email FROM crm.customers AS c; This is a simple view. You applications can CREATE/UPDATE/INSERT against the view in the public schema with no changes to your code. However if you want to evolve the schema of customers you can do so in the `crm` schema adding tables, columns, etc. CREATE VIEW public.customers AS SELECT c.id, c.contact_name, c.email FROM crm.customers AS c WHERE deleted = false; You can then evolve your public schema to include more information as needed by updating the view. The caveat is that if your view starts materializing data from more than one table you'll have to intercept INSERT/UPDATE/DELETE and replace them with your own functions to put the data in the right place. This works quite well for implementing nice features like administrative flags without having to update your application code or spread that logic out: it's all kept in the database. You still get the power of indexes, a strong query language, and structured data. PostgreSQL is great as an application server allowing you to comment all of your objects and having deep introspection facilities allows you to not only be flexible with your data models but also consistent. Update: Added an example of changing the public schema to add a filter to the query for an administrative flag

noobsquared 10y ago
Where can i learn more about this type modern SQL tools you mention? (Besides what you've just mentioned of course :D)
- deleted 10y ago
  [deleted]
wwweston 10y ago
"PostgreSQL as an Application Server" is a book I'd be likely to buy.
- agentultra 10y ago
  Like maybe https://agentultra.com/postgres-book.html https://agentultra.com/postgres-book.html ?
  - wwweston 10y ago
    Not sure if I'm more impressed if you'd already started work on it, or sketched up the page/TOC + mailchimp in response. :) But I signed up!
flukus 10y ago
So now every time I add a column I have to update the schema as well. That's making change management harder, not easier.

cwyers 10y ago

I don't really get this. ALTER TABLE ADD COLUMN X is a cheap operation when your database is small, at least on decent hardware. It's when you get up to scale that it starts being a burden. During the rapidly-evolving stage is when it should be the easiest to alter database schemas, because you shouldn't have accumulated all that much data yet.

batmansmk 10y ago
And... if you accumulate a lot of data,you may mostly access it in an aggregated way, such as GROUP BY. where ALTER TABLE ADD COLUMN X is free.
PhineasRex 10y ago
I work at a company with multi-gigabyte tables and ALTER TABLE is still easy to pull off, we just use tools the community has built for larger tables. It's one of the most unfounded fears I've heard in the industry.

dasil003 10y ago

Sure, but the cost of schema maintenance is grossly overstated by those who simply aren't familiar with the tooling to do so. Furthermore, the cost of dealing with the corrupt data down the line will absorb all the gains you had in a matter of months, unless of course you've reinvented strong schema control at the application layer. A lot of inexperienced devs look at NoSQL as being simpler than SQL because the dev documentation is shorter and has a more stylish theme. They then assume that all the touted benefits of SQL are YAGNI and they'll cross that bridge when they come to it. The mistake they make is assuming that the benefits of SQL databases are something that only is needed for a mature application. To the contrary, SQL gives an incredible hedge against the uncertainty of change in the early days of an app when churn is high. A normalized data structure allows much more query flexibility; you should only denormalize for performance once you realize what the workload and logic will look like for the long haul. Of course if you have a known workload that you want to scale up huge then that's a legitimate use case for NoSQL, but the purpose is not flexibility.

hackits 10y ago
Our current manager wants to transition over to NoSQL, as he doesn't see any value having to constantly update JPA Repositories and dependencies when a column is added or removed from the database table. I recommended that JPA Repositories shouldn't be stored at the lowest level of Maven jar package's eg... we have a very deep nested Maven pom dependencies (about 6 layers deep) of which the JPA repositories are defined in the lowest of lowest levels. Granted I gave references to research papers from Google that JPA Repositories and Hibernate ORM should be at the project layer (due to their nature of changing rapidly). Turns out, still going with the NoSQL solution. Although it seems like everyone have convinced themselves that NoSQL is faster development processes than a simple table with constraints.
- hinkley 10y ago
  Leaf node dependencies should be very stable. Otherwise you spend all your time rebuilding or at least rerunning integration tests. I work at a company that has achieved the opposite of that with not one but two libraries, and deployment is a miserable experience.