Hacker News Reader

Comment by bob1029 | Hacker News Reader

> Is your data layout defined by a single interpretation from a single point of view?

I think this might be the most important question at technology selection and architecture time. Answering it usually requires talking to the business and customers.

If you are certain there is exactly 1 valid "view" of the data that will be used throughout, then perhaps enshrining it in code makes sense. If you are even a tiny bit uncertain of this, a relational-style model probably works better. SQL is the end game for most businesses once they realize the game theory around this one...

I am curious what HN thinks as major reasons for why everyone seems to have moved away from 1 big SQL database. From my perspective, yeah we have "web scale" edge cases that threaten vertical scalability on writes, but most businesses will never touch this, including members of the F100.

View on HN

At a previous F100 company -- a tech company whose products are widely used, we'll say -- we received guidance that RDBMS was verboten except with explicit approval. This had nothing to do with the best ways to model a given dataset, or achieving the best performance, and everything to do with schema flexibility and a history of outages caused by fucking up schema migrations. These problems weren't occurring in our NoSQL designs, and whatever benefits SQL databases offered didn't counter the huge benefits we gained from NoSQL's lack of rigid schema.

Of course, bad uses of key-value stores can have massive performance impacts, and huge monetary costs when leveraging cloud platforms like DynamoDB -- I've seen a lot of cases where people didn't properly structure their data for DDB, and ended up performing loads of scans and sending costs through the roof.

> schema flexibility

If the business is of the notion that the schema is "flexible", then it is probably time to bring all of the MBAs into a conference room and have a come-to-jesus conversation about the limitations of information theory and human suffering.

At a certain point, when someone says "Widget", everyone in the organization needs to be on the same page. This goes well beyond any specific technology.

Hybrid solutions are possible; e.g. JSONB in Postgres, where you can still index and join with decent performance.

> and everything to do with schema flexibility and a history of outages caused by fucking up schema migrations. These problems weren't occurring in our NoSQL designs, and whatever benefits SQL databases offered didn't counter the huge benefits we gained from NoSQL's lack of rigid schema

Yikes

"Data consistency issues? We'll catch those during integration"

I read that as "We don't want things to crash immediately when the data model changes. We want things to keep chugging along until the last possible moment, when we will realize we've been silently corrupting everything"

And then there are silent query failures. Want to change “name”: “John Smith” to “name”: {“first”: “John”, “last”: “Smith”}? Easy! No schema migration!

But you have to modify all your queries to support both old and new formats, or stop the world and change all the data (after modifying all your code, including dynamically generated queries).

And if you don’t, your queries fail silently.

Sounds like a company whose end users are mostly not their customers.

>I am curious what HN thinks as major reasons for why everyone seems to have moved away from 1 big SQL database.

We haven't moved away from it, but we have run into a certain class of problems that seem related to the 1 big SQL database architecture. We're a really old enterprise, with a lot of non-technical people creating a lot of technical solutions back in the day that have become calcified and therefore have to keep existing. One of the things we have is 5 levels of SQL data transformations from the operational database (the one that actually has applications) into different generations of datamodel as the "type" of business we did changed.

The problem is that as we accumulate ever more layers, we keep building on the layers before. The application that was built 10 years ago on abstraction layer 2 now needs some data from layer 4, let's create a new script that loops that data back into the previous layer and keep going. Eventually we've ended up with a huge amount of interdependent tables that all load data from other tables/views in weird and unintuitive ways, and the project to sort out the mess was deemed too expensive and postponed until the 2030's.

I think it's understandable that people see those problems and consider how we could have avoided them. Unfortunately, for reasons I don't fully grasp, it seems impossible to apply anything to software engineers that require discipline, and we have to somehow make it impossible to create the spaghetti. That's where separation comes in. If you can't read the data from some other service, then it's impossible to create a spaghetti mess that kills velocity for both parties.

The vertical separation of application becomes a software solution to the people problem of poor engineering discipline in enterprises.

I can relate to this where I work we have similar issues the data has grown beyond what the original database schema was designed around. Ours is older than 10 years though - it is 27 years old (originally from 1996).

We originally had a (in my opinion) pretty nicely designed schema and set of tables to represent the data but over time as more data has been added additional tables have grown in a hodge-podge fashion. We now have a Frankenstein's monster of a database. Similar to you there are all sorts of weird interdependencies across various tables.

An additional complication is We have a lot of legacy programs/apps that Read/write to the database so changing types, field widths etc. Is basically impossible because you would have to recompile legacy code / Rewrite Messaging queues etc.

The modern solution to this problem seems to be something called "data lake" which ingests data from the source DB but also applies transformations onto it. I don't really understand how it works something like a SQL view but also keeps the underlying data accessible as well. I don't know all the details has a lot of traction for our IT people at the moment.

> why everyone seems to have moved away from 1 big SQL database

Hacker News is probably not representative of the whole tech ecosystem. I think a majority of applications still uses one big SQL database.

I recently released an open-source framework [1] that is entirely based on Data-Oriented Design. I have received a lot of comments from people for whom it was the right design. Having all your data in the same place makes so many things easier!

[1] https://sql.ophir.dev

Web technologies are also not representative of all the whole tech ecosystem. I can’t fit an SQL database on my microcontroller.

> I can’t fit an SQL database on my microcontroller.

Perhaps not SQL Server or Oracle, but unless we are talking about a very limited device there are likely options.

What about SQLite?

>I am curious what HN thinks as major reasons for why everyone seems to have moved away from 1 big SQL database

For the places I worked:

1. We transitioned to microservices

2. Performance, 1 BIG database slows that

3. Ops/maintenance is very hard in a huge DB

4. In a huge DB there can be a lot of junk no one uses, no one remembers why is there, but no one is certain whether that junk is still needed

5. We had different optimization strategies for reads and writes

6. Teams need to have ownership on databases/data stores so we can move fast instead waiting for DBAs to reply to tickets.

4. In a huge DB there can be a lot of junk no one uses, no one remembers why is there, but no one is certain whether that junk is still needed

Of course no one knows how to even begin to come up with a way of addressing that problem.

So the only viable option is to keep on masking it. And keep propagating the junk data and zombie schemas ever forward.

I'll second @viraptor and @hops answers : It is the same cause as the rise of microservices and DevOps adoption, easier politics. I worked for a big old company, and most of the problems were political and administrative. One big SQL database is quite efficient until the entity that owns it does not agree with the new CTO strategy and any another critical part of the business. Add an incident that shows the low resilience of the model and it quickly becomes a political headache, while the technical solution still seems evident to everyone.

> reasons for why everyone seems to have moved away from 1 big SQL database

I'm sure there are going to be other answers for the code side of things, but for ops:

Depends a lot on the size of the service, but in some cases: We got enough data that 1 big SQL store makes ops hard. (Took me 3 days to drop a table recently in a way that wouldn't affect the users) And splitting data became easier than before with specialised backends. (A sharded 2nd layer cache of live data seems way simpler to achieve than say 2 decades ago)

For me it's parallelisable delivery (or fragility, depending on how you look at it). If a team owns its own data store, it can make whatever changes it needs to and not have to worry about any other part of the software being broken by those changes.

The trade-off being having a separate team trying to integrate all the data back together with ETL.

Well, if you need to integrate it. If you have some hideous dashboards you might need to, it's true, but at that point it's worth investing in a data person whose job it is to keep up with all the breaking data warehouse integrations. They'd have to anyway with any data approach.

For the same reason they went with microservices - its easier to service the technical boundaries you control, and is a political solution rather than a technical one.

Getting something based a DBA in change control was hard, but shipping some IaC templates can be done in a sprint!

Because over time the one big relational database turns into a big ball of mud. Change becomes expensive and has a large blast radius.

Contextual business domains should be the foundation of any complex architecture. You reduce complexity and change blast radius and speed up agility and feature adoption.

Reply on news.ycombinator.com