I sometimes wish I could directly write a plan for my query instead of relying on the db to come up with something that matches it (OLAP). The other 90% percent I’m happy that it just works.
IMO your use case would suit only very static needs that maybe you should consider using a completely different thing than a SQL database. I see the abstraction you're talking about as unnecessary as the whole point - and while it's true that an optional feature that would let you do that could be nice, I fail to see any point of that.
If my desire is so wrong, why do things like index hints exist?
And the very static case is the easy one in OLAP. You build a star schema that is so simple that the planner could not get it wrong. To fill this schema you use some ETL tool that very much resembles a static plan.
In the dynamic, exploratory phase (does it make sense to look at the data this way) you have to deal with the nasty complicated queries.
I agree 1,000% -- I've thought about writing an article on it.
There's a huge difference between one-time queries (let the planner figure it out) versus production queries.
I've been bitten in the past by the SQL planner failing as it reaches some threshold and suddenly stops using an index it was using before, etc., and queries go from taking milliseconds to seconds. It's rare, but it happens, and the results can be catastrophic -- usually because it starts ignoring an index because it (wrongly) thinks the index would slow it down -- planners aren't perfect.
I'd much rather specify in advance the query plan to handle what I knew would be the eventual biggest-case scale of data: to always use X index (forced, not just hinted), and to deal, in advance, with whether or not sorting will always be possible in memory or if it will need to be done in a swap file on disk, and at what points filtering happens.
I actually don't care if this slows down my (already blazing fast) queries 10x or even 100x when the database is small (e.g. by sorting on disk instead of in memory, even when just 10 rows -- or using the index even when clearly unnecessary with just 10 rows). It buys me the peace of mind that there will be no discontinuities in performance in production, no surprises.
Of course this isn't for basic grab-a-row queries: I'm talking about more complex queries with all the bells and whistles (subqueries, multiple joins, filters, etc.) where the database's planner goes haywire.
This is FaunaDB’s strategy. Indexes and views are explicitly referenced and thus every query is implicitly also its own query plan, with local optimizations only. No discontinuous planning events.
What database are you using? Oracle [1] and MySQL [2] for example let you embed hints in the query in order to change the execution plan. I think that PostreSQL doesn't have such a feature though (at least it did not a few years ago).
You can also record and save plan you are happy with. So, no need to fix hints in code. It is more flexible and may be practical when you upgrade version or environment changes.
Can you do that in MySQL?
For Oracle you AFIAK need special permission to save a plan. In larger corporations this means you have call the db admin which makes total sense for OLTP (and is a good feature of SQL dbs). If you’re exploring something in OLAP this is not so great.
> Nevertheless Amazon uses Oracle Database to run their business. Even though they don’t want to. ... In the end, they will still use SQL—just not the Oracle Database.
I really dislike articles that try to make it sound like smart companies like Amazon and Facebook don't know what they are doing. Amazon has never said they won't use SQL. They have an innovative set of AWS SQL offerings including RDS, Aurora, and Redshift.
In fact, I would argue that Amazon Web Services is responsible as much or more than any other vendor for 'The Revival of Great SQL Ideas' that this article discusses. For evidence look at 'Amazon Redshift and the Case for Simpler Data Warehouses' by Gupta et al. [0] Amazon has profoundly altered data management not just in the SQL arena but across the board.
> The lesson we can learn from this story is the following: start with a generic database...SQL database are a good choice because they can do many tricks...The modern and successful architecture that is commonly used today is to have an SQL database that is sometimes surrounded by some one-trick ponies to take care of a few pain points.
Yup.
I like that we now have more of these one-trick ponies to choose from our toolbox when necessary, when a relational database just won't cut it.
But, my biggest complaint around the NoSQL movement is the marketing pseudo-hype it created. So many amateurs who don't understand database selection took it as gospel and evangelized it across the web (eg. Mongo w/ Node).
It's hard to correct people's understanding when they learn things wrong the first time, especially when there's a mountain of incorrect information they can point to on the web ("These people can't all be wrong, can they?" Well...).
The quoted statement is not correct at least as far as analytics are concerned. Two examples:
1.) Analytics in many enterprises increasingly feed off data lakes consisting of enormous quantities of data in object storage. SQL has a part to play but it's effectively computing aggregates and creating data marts off this deeper pool of data. Data lake architecture is likely to be increasingly dominant given the enormous growth in data volumes.
2.) Machine learning is transforming analytics. This looks like the next feature likely to be absorbed into DBMS systems. SQL integration with ML is likely to be a hot topic in future systems but a substantial fraction of ML processing will remain outside the DBMS.
So SQL is going to be present widely in most future solutions but that's not the same as saying that a single relational DBMS architecture will solve all problems. It's been clear for years that ACID-compliant RDBMS have a part in this picture but it's just part.
Overall the article still seems to be fighting the SQL/NoSQL wars of the last decade. A large part of the market is moving on to other use cases.
Data warehousing and ML have different requirements and needs than your typical N-tier web app. Even streaming event data warrants a different solution. It comes back to knowing how to choose the right database for the job.
The issue is around how these technologies are marketed – grandiose claims and few practical use cases. Once the marketing material permeates the industry and some part latches on, it becomes a self-reinforcing cycle. Blog posts, books, and courses bring the information to the masses. Then companies start to adopt the tools. Then they need to hire engineers who know those tools. So more information gets published about them because that's what people want to learn to get hired.
Many engineers today will turn to NoSQL for everything because of the past few years of marketing hype (and acronym-driven-development), and that's quite a shame.
For those looking to understand how to choose the right database for the job, I'd recommend first reading "Designing Data-Intensive Applications" (https://dataintensive.net)
The most infuriating part of "NoSQL" for me was always the conflation of SQL and RDBMS and ACID. Most NoSQL is simply non-relational or non-ACID. You still need an access layer.
After all these years people seem to have finally realized that the challenge was never SQL, it was data and you still have to think about that, even if you don't use SQL.
howfuckedismydatabase.com[0] is still as accurate as it ever was.
Diversity and competition makes for better outcomes. I was never on the NoSQL bandwagon but even then I could see the benefits for its users. Namely schemaless documents for rapid development and relatively easy horizontal scaling. From those we got things like Firebase and a slew of NewSQL databases. Some SQL databases now have horizontal scaling in their roadmaps.
> It's hard to correct people's understanding when they learn things wrong the first time
Don't have a good solution to this. Trouble is that the 'first time' is learning and any amount of hand waving and thought experiments isn't enough to refute what they've learned combined with what they've yet to learn. No pain, no gain I suppose.
18 comments
[ 4.7 ms ] story [ 65.2 ms ] threadAnd the very static case is the easy one in OLAP. You build a star schema that is so simple that the planner could not get it wrong. To fill this schema you use some ETL tool that very much resembles a static plan.
In the dynamic, exploratory phase (does it make sense to look at the data this way) you have to deal with the nasty complicated queries.
If you want to have that exact behavior I'd agree with the gp, we are talking about data access, but not all data access has to be a SQL database.
Index hints are just that, hints, they are not commands - because there are tipping points that they just dont make sense anymore.
There's a huge difference between one-time queries (let the planner figure it out) versus production queries.
I've been bitten in the past by the SQL planner failing as it reaches some threshold and suddenly stops using an index it was using before, etc., and queries go from taking milliseconds to seconds. It's rare, but it happens, and the results can be catastrophic -- usually because it starts ignoring an index because it (wrongly) thinks the index would slow it down -- planners aren't perfect.
I'd much rather specify in advance the query plan to handle what I knew would be the eventual biggest-case scale of data: to always use X index (forced, not just hinted), and to deal, in advance, with whether or not sorting will always be possible in memory or if it will need to be done in a swap file on disk, and at what points filtering happens.
I actually don't care if this slows down my (already blazing fast) queries 10x or even 100x when the database is small (e.g. by sorting on disk instead of in memory, even when just 10 rows -- or using the index even when clearly unnecessary with just 10 rows). It buys me the peace of mind that there will be no discontinuities in performance in production, no surprises.
Of course this isn't for basic grab-a-row queries: I'm talking about more complex queries with all the bells and whistles (subqueries, multiple joins, filters, etc.) where the database's planner goes haywire.
[1] https://docs.oracle.com/cd/B19306_01/server.102/b14211/hints...
[2] https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
I really dislike articles that try to make it sound like smart companies like Amazon and Facebook don't know what they are doing. Amazon has never said they won't use SQL. They have an innovative set of AWS SQL offerings including RDS, Aurora, and Redshift.
In fact, I would argue that Amazon Web Services is responsible as much or more than any other vendor for 'The Revival of Great SQL Ideas' that this article discusses. For evidence look at 'Amazon Redshift and the Case for Simpler Data Warehouses' by Gupta et al. [0] Amazon has profoundly altered data management not just in the SQL arena but across the board.
[0] https://event.cwi.nl/lsde/papers/p1917-gupta.pdf (Paragraph 3 of the abstract is my favorite part--it's a brilliant formulation of the problem they are solving.)
Yup.
I like that we now have more of these one-trick ponies to choose from our toolbox when necessary, when a relational database just won't cut it.
But, my biggest complaint around the NoSQL movement is the marketing pseudo-hype it created. So many amateurs who don't understand database selection took it as gospel and evangelized it across the web (eg. Mongo w/ Node).
It's hard to correct people's understanding when they learn things wrong the first time, especially when there's a mountain of incorrect information they can point to on the web ("These people can't all be wrong, can they?" Well...).
1.) Analytics in many enterprises increasingly feed off data lakes consisting of enormous quantities of data in object storage. SQL has a part to play but it's effectively computing aggregates and creating data marts off this deeper pool of data. Data lake architecture is likely to be increasingly dominant given the enormous growth in data volumes.
2.) Machine learning is transforming analytics. This looks like the next feature likely to be absorbed into DBMS systems. SQL integration with ML is likely to be a hot topic in future systems but a substantial fraction of ML processing will remain outside the DBMS.
So SQL is going to be present widely in most future solutions but that's not the same as saying that a single relational DBMS architecture will solve all problems. It's been clear for years that ACID-compliant RDBMS have a part in this picture but it's just part.
Overall the article still seems to be fighting the SQL/NoSQL wars of the last decade. A large part of the market is moving on to other use cases.
Data warehousing and ML have different requirements and needs than your typical N-tier web app. Even streaming event data warrants a different solution. It comes back to knowing how to choose the right database for the job.
The issue is around how these technologies are marketed – grandiose claims and few practical use cases. Once the marketing material permeates the industry and some part latches on, it becomes a self-reinforcing cycle. Blog posts, books, and courses bring the information to the masses. Then companies start to adopt the tools. Then they need to hire engineers who know those tools. So more information gets published about them because that's what people want to learn to get hired.
Many engineers today will turn to NoSQL for everything because of the past few years of marketing hype (and acronym-driven-development), and that's quite a shame.
After all these years people seem to have finally realized that the challenge was never SQL, it was data and you still have to think about that, even if you don't use SQL.
howfuckedismydatabase.com[0] is still as accurate as it ever was.
[0]: http://howfuckedismydatabase.com/nosql/
Diversity and competition makes for better outcomes. I was never on the NoSQL bandwagon but even then I could see the benefits for its users. Namely schemaless documents for rapid development and relatively easy horizontal scaling. From those we got things like Firebase and a slew of NewSQL databases. Some SQL databases now have horizontal scaling in their roadmaps.
> It's hard to correct people's understanding when they learn things wrong the first time
Don't have a good solution to this. Trouble is that the 'first time' is learning and any amount of hand waving and thought experiments isn't enough to refute what they've learned combined with what they've yet to learn. No pain, no gain I suppose.