In defense of the "Performance Considerations" against postgres, I worked on a team that had a BI guy that regularly wrote giant multi page queries using WITH statements.
The main database was IBM DB2 9.7 on RHEL and it had about 3 months (800GB) of data in it (we pruned it nightly). We had a "reporting" database that was postgres 9.3 or 9.4 (can't remember). It was the same schema but was never pruned. It had about 15 billion rows total and about 5TB of data. About 700GB was indexes. The same query when ran on DB2 took 12 hours, on postgres (5x more data) it took 24 hours.
The query plans were mostly identical (neither one did much to optimize the WITH statements). The speed difference seemed to come from Postgres just accessing the disk better.
When doing an index scan, the disk read speed was about 80MBs, while DB2 was 10MBs. Full table scans in Postgres were between 180-220MBs while DB2 was around 60MBs. Same storage too (netapp over nfs). DB2 was using direct IO so it like to read in 4k chunks, but even disabling that didn't help much.
All the money was flowing to IBM licenses. Hence when we needed another database for reporting, we went with open source. Vertica is big money++ And really, after my stint with that company, I really can't stand commercialized licensed software anymore.
The problem isn't the technology here. The problem is that they were using a replica of the OLTP database for reporting purposes.
OLTP and OLAP are hugely different workloads. A good 3NF (or close to it) schema that you'll find in an OLTP database is designed for fast writes with minimal locking; it fits well with the workload of OLTP.
If you're reporting on that data and performing analytical queries, then your schema needs to reflect that and optimize for large reads without much concern around lock contention for the primary workload.
I am not opposing the suggestion of a columnstore database, as we utilize these extensively with our clients. There is simply a much larger problem in the specified example. 15B rows is not too big a deal in an RDBMS for an analytical workload.
The impact of CTEs being "optimization fences" in PostgreSQL really depends on workload, and how cleverly you write your queries. If you are not aware of this - as most users using CTEs merely as an alias - you'll be badly surprised.
I've been recently running TPC-DS on PostgreSQL 9.5 (alpha1 IIRC), to see how we're doing (I'm one of the contributors). Thanks to the grouping sets, we're now able to execute all 99 queries in the benchmark, which is awesome. Regarding performance, I've expected grouping sets to be a performance issue as the current implementation is rather simple. But to my mild surprise that's not the case - CTEs are the main problem.
On 100GB data set some queries took hours to complete (well, I interrupted them so I'm not sure how long it'd actually take). After a rewrite (replace CTE with a subquery), the query took a a minute or so.
A point that's well worth stating for those that may not realize it: rewriting a query that uses CTE into one that uses subqueries is really just a case of using copy and paste to perform substitution: replace the CTE references with their definitions. So, to a large extent you can have the best of both worlds.
SQLAlchemy (which is sort of an embedded dsl for SQL) lets you give variable names to subqueries and as a result I don't really use CTEs. I bet the people using iBatis and JOOQ can do the same thing
As far as using CTEs for structuring your SQL is concerned, you're right, unless people really want the side-effects CTEs sometimes have, such as materialisation in PostgreSQL.
It's planned, apparently. [0] When it was discussed on the mailing list, it sounds like people were in favor of adding a keyword to disable it, making the syntax something like WITH UNBOXED (subquery) or WITH VIEW (subquery).
Because CTEs are not named subqueries, really. For example, CTE is only evaluated once, even if it's referenced in multiple places, and those places may have conflicting ideas of what's the best way to evaluate it.
Consider for example this:
WITH x AS (SELECT a, b FROM t)
SELECT * FROM x x1 JOIN x x2 ON (x1.a = x2.b);
Now, had this been evaluated using a merge join, the "x" CTE would have to be sorted first by "a" or "b" (for either side of the join). Well, that can't really happen.
Another issue is locking - consider this version, for example:
WITH x AS (SELECT a, b FROM t FOR UPDATE)
SELECT * FROM x x1 JOIN x x2 ON (x1.a = x2.b);
In other words, it's way more complicated than it might seem, especially if you can't break existing uses (e.g. the locking).
It seems to me that these would be rare rather than the typical case. I think we could make the case that the more straightforward uses (like the ones in the articles) could be optimized away as described above. Leave the more complex/less optimized code path for these edge cases.
SQL is a declarative language with semantics derived from relational algebra, which to me implies that there should be no execution semantics at all.
So the statement that "a CTE is only evaluated once" should not be part of any specification for the language. And I would not expect two different executions of a query to maintain that invariant if the optimizer finds it better not to.
The problem is we currently have an implementation that behaves as explained above (planned in isolation, evaluated once), and there are applications relying on that behaviour. We can't just change that without breaking them.
True. At the same time you can't condemn future users to this suboptimal behavior just because a bad desicion in the past.
I'm not arguing for just abruptly reverse the design, but that the aim should be to reverse in an orderly manner with suitable deprecations and migration paths in place.
In order to be free of execution semantics, you'd have to disallow calling side-effectful functions within a query, which would be a major departure from the current behaviour.
> For example, CTE is only evaluated once, even if it's referenced in multiple places, and those places may have conflicting ideas of what's the best way to evaluate it.
Not disputing what you're saying, but I swear I read something the other day recommending being careful using CTE's as they are re-executed once for every reference in the query (or something to that effect).
Not at all. As my profile states I work as a BI consultant. If you want to consider the entirety of an organization's ETL processing as a monolith, then overnight is really the longest execution time frame I'm allowed to talk about. I can also guarantee that if I were to propose a full 8 hours of ETL, I would be required to put up some serious justification.
That being said, my focus in my company is more on the front end. In this case there's a range of acceptable timeframes, but we've had clients with a strict requirement that any report with exposure to VPs or higher has to have <=2 second response time. Even outside of that organization, more than a minute for a report is usually pretty much a non-starter.
The problems I'm referring to have nothing to do with a BI guy implementing complicated logic; this is just a part of the job description. Let me quote the section where it becomes clear that there's a huge problem.
> The main database was IBM DB2 9.7 on RHEL and it had about 3 months (800GB) of data in it (we pruned it nightly). We had a "reporting" database that was postgres 9.3 or 9.4 (can't remember). It was the same schema but was never pruned. It had about 15 billion rows total and about 5TB of data. About 700GB was indexes. The same query when ran on DB2 took 12 hours, on postgres (5x more data) it took 24 hours.
Emphasis is mine. They're reporting on an OLTP schema. The reason that the query is many pages long, and that it takes 24 hours to run, is because the CTEs are being used as in-line ETL. Their "reporting" database is nothing of the sort, it's just a replica of their operational system.
That poor BI guy is either out of his depth and doesn't know how or is simply not allowed to build the appropriate solution. Both are problems, and both extend far beyond the BI guy.
I could write pages on the "company's problems" but I don't feel like it. The "reporting" database was my idea and my attempt to actually be able to do sometime "cool" there.
The java software devs, even though they all had 15+ years experience, really had no clue what a database was other than a thing to put data in. App environment configs, customer configs, binary images,... you name it and it was stuck in the main database. The schema was designed by a sysadmin that left shortly after the company (a start up) actually started getting real customers. Messing with the schema was basically off limits. No one saw an issue with it.
Everyone thought the schema was brilliant because that sysadmin thought it would be a cool idea to replace indexes with partitions (DB2 9.7 at the time had a brand new "partition by range" feature). He wrote giant bash scripts to partition many tables by timestamps on either a 15 minute, one hour, or one day interval AND by groups of account IDs (or any other ID that might be in the row). His logic was that even though all queries will do a full table scan.. but hey these are all really tiny tables now! Management and the senior devs thought it was a brilliant idea. It didn't work so well in the end, and ultimately indexes were just added randomly all over the place. But the partitions remained and the scripts would run every hour... but sometimes they'd fail to add partitions because of locks. Which ultimately left no where for certain data to go (based on timestamps) and the app would go down. This went on for years...
But the other issue, as time rolled on, when the schema did need to be changed, it was done the quickest and dirtiest way possible. One patch I saw go through added a "username" column to several tables to track what user made a change to a row, but it was varchar instead of a FK... completely de-normalizing the schema. No one knew or understood the data on the dev team.
I tried best as I could to do analytics, but the DB2 DBA's were always on my case for "accessing" "their" database. I managed to get buy in from my manager to start standing up postgres databases for reporting. We tried Mysql at first but its supported SQL syntax was too butchered for anything other than simple queries.
The idea was to have a carbon copy of the OLTP schema in postgres and populate it with Pentaho or some other BI tools/scripts. Once that was mostly working we were going to build a data warehouse schema with stuff in the proper formats so we could just query without having to transform. Sadly after a few months my manager moved me off of the project and put a rather new contractor over the project who knew nothing about data architecture, or linux (he constantly complained about the command line and would say things like "I stopped using DOS years ago"), or even postgres. I was moved to simply help with some vmware upgrades which was boring. They then opened a "jr DBA" position and filled that with someone straight out of college to handle standing up the postgres instances.
And with that, I left.... and eventually most of the rest of the team too.
You were definitely on the right track to set up an ODS and then stand up a DW in front of that. Looks like you were firmly stuck in the "not allowed" camp and trying to work around absurd limitations.
> PS, they are looking for a BI guy...
No thanks.
I'm spoiled by being a consultant. Anyone who wants to pay our rates is already committed to making changes.
> We had a "reporting" database that was postgres 9.3 or 9.4 (can't remember). It was the same schema but was never pruned.
Big no-no right there -- a reporting database shouldn't have the same schema as your main transactional DB, because the latter is designed and built for write-heavy workloads, not reporting or analytical ones.
I'm pretty certain that a lot of the stuff happening in the CTEs could simply be designed as part of a "true" reporting schema and ETL'ed in as a batch job.
Completely agree with this - last week I was writing a query with one of our junior developers. Rewriting to use a couple of with clauses with very descriptive names immediately made the intention clear. The with clause is really the clean code enabler for my SQL.
WITH statements seem convinient. But nested recursive SQL statements / subqueries can be an performance issue. Sometimes speed is more important than a SQL99 or SQL2003 feature, some implementations are fast with SQL89/92 syntax.
Using SQL Server my normal pattern of development is to do what I need using CTE's (WITH) and then switch to temp tables if/when the queries start to show poor performance.
That's valid, but you want to keep in mind that SQL Server has ONE tempdb that is used across all databases. TempDB is used for temp tables, but TempDB is used for a raft of other features (like snapshot isolation). You need to ensure you don't start getting tempdb contention.
This is a gem of a website, thanks for posting. For all the noise about NOSQL and ORM tools, there will always be a humongous number of situations where SQL is unavoidable (or even the best tool for the job).
A somewhat related tip that works in SqlServer (not sure about other dialects) is to put the column alias first rather than at the end of a calculated column. E.g. rather than:
SELECT
Foo,
Sum(Baz) AS Bar
FROM Quux
Use instead:
SELECT
Foo,
Bar = Sum(Baz)
FROM Quux
The advantage here is that the names also line up neatly and the shape (column names etc.) of the result is obvious.
Likely not. My brief digging into trying to find a definitive document on what is 'standard SQL' led me down a rabbit hole and I gave up on working out whether it was or wasn't. The likelihood of having to change to a different provider on any project that has settled on SqlServer in my experience is fairly close to 0. YMMV
I'm so used to using and reading the "AS" syntax for aliasing that if I came across this it'd throw me off.
It seems to me that sticking everything in CTEs would be equally as jarring. Writing SQL or reading and understanding someone else's SQL is almost always a non-linear process, not least of all because the processing order by the database engine isn't top-to-bottom.
IMO, Microsoft got it right way back when with LINQ syntax. They did it for a different reason (to make things easier to convert to a sequence of plain, old fashioned method invocations), but the idea was the right one. For those who don't know, in a LINQ query you first specify the data source(s) and joins, then your filtering logic, then grouping and ordering, and then your projections, including projections into objects and anonymous structs. That's just the way it oughtta have been from the start. As a bonus, it also simplifies things like autocomplete, because you know the data sources up front.
36 comments
[ 2.9 ms ] story [ 75.0 ms ] threadThe main database was IBM DB2 9.7 on RHEL and it had about 3 months (800GB) of data in it (we pruned it nightly). We had a "reporting" database that was postgres 9.3 or 9.4 (can't remember). It was the same schema but was never pruned. It had about 15 billion rows total and about 5TB of data. About 700GB was indexes. The same query when ran on DB2 took 12 hours, on postgres (5x more data) it took 24 hours.
The query plans were mostly identical (neither one did much to optimize the WITH statements). The speed difference seemed to come from Postgres just accessing the disk better.
When doing an index scan, the disk read speed was about 80MBs, while DB2 was 10MBs. Full table scans in Postgres were between 180-220MBs while DB2 was around 60MBs. Same storage too (netapp over nfs). DB2 was using direct IO so it like to read in 4k chunks, but even disabling that didn't help much.
You're doing it wrong. These type of queries should be run on a column store (e.g., Vertica).
OLTP and OLAP are hugely different workloads. A good 3NF (or close to it) schema that you'll find in an OLTP database is designed for fast writes with minimal locking; it fits well with the workload of OLTP.
If you're reporting on that data and performing analytical queries, then your schema needs to reflect that and optimize for large reads without much concern around lock contention for the primary workload.
I am not opposing the suggestion of a columnstore database, as we utilize these extensively with our clients. There is simply a much larger problem in the specified example. 15B rows is not too big a deal in an RDBMS for an analytical workload.
I've been recently running TPC-DS on PostgreSQL 9.5 (alpha1 IIRC), to see how we're doing (I'm one of the contributors). Thanks to the grouping sets, we're now able to execute all 99 queries in the benchmark, which is awesome. Regarding performance, I've expected grouping sets to be a performance issue as the current implementation is rather simple. But to my mild surprise that's not the case - CTEs are the main problem.
On 100GB data set some queries took hours to complete (well, I interrupted them so I'm not sure how long it'd actually take). After a rewrite (replace CTE with a subquery), the query took a a minute or so.
But don't forget: CTEs can be recursive.
[0]: https://wiki.postgresql.org/wiki/Todo#Optimizer_.2F_Executor. Search for CTE.
Consider for example this:
WITH x AS (SELECT a, b FROM t) SELECT * FROM x x1 JOIN x x2 ON (x1.a = x2.b);
Now, had this been evaluated using a merge join, the "x" CTE would have to be sorted first by "a" or "b" (for either side of the join). Well, that can't really happen.
Another issue is locking - consider this version, for example:
WITH x AS (SELECT a, b FROM t FOR UPDATE) SELECT * FROM x x1 JOIN x x2 ON (x1.a = x2.b);
In other words, it's way more complicated than it might seem, especially if you can't break existing uses (e.g. the locking).
So the statement that "a CTE is only evaluated once" should not be part of any specification for the language. And I would not expect two different executions of a query to maintain that invariant if the optimizer finds it better not to.
The problem is we currently have an implementation that behaves as explained above (planned in isolation, evaluated once), and there are applications relying on that behaviour. We can't just change that without breaking them.
I'm not arguing for just abruptly reverse the design, but that the aim should be to reverse in an orderly manner with suitable deprecations and migration paths in place.
Not disputing what you're saying, but I swear I read something the other day recommending being careful using CTE's as they are re-executed once for every reference in the query (or something to that effect).
That being said, my focus in my company is more on the front end. In this case there's a range of acceptable timeframes, but we've had clients with a strict requirement that any report with exposure to VPs or higher has to have <=2 second response time. Even outside of that organization, more than a minute for a report is usually pretty much a non-starter.
The problems I'm referring to have nothing to do with a BI guy implementing complicated logic; this is just a part of the job description. Let me quote the section where it becomes clear that there's a huge problem.
> The main database was IBM DB2 9.7 on RHEL and it had about 3 months (800GB) of data in it (we pruned it nightly). We had a "reporting" database that was postgres 9.3 or 9.4 (can't remember). It was the same schema but was never pruned. It had about 15 billion rows total and about 5TB of data. About 700GB was indexes. The same query when ran on DB2 took 12 hours, on postgres (5x more data) it took 24 hours.
Emphasis is mine. They're reporting on an OLTP schema. The reason that the query is many pages long, and that it takes 24 hours to run, is because the CTEs are being used as in-line ETL. Their "reporting" database is nothing of the sort, it's just a replica of their operational system.
That poor BI guy is either out of his depth and doesn't know how or is simply not allowed to build the appropriate solution. Both are problems, and both extend far beyond the BI guy.
Edit: Extra word word.
The java software devs, even though they all had 15+ years experience, really had no clue what a database was other than a thing to put data in. App environment configs, customer configs, binary images,... you name it and it was stuck in the main database. The schema was designed by a sysadmin that left shortly after the company (a start up) actually started getting real customers. Messing with the schema was basically off limits. No one saw an issue with it.
Everyone thought the schema was brilliant because that sysadmin thought it would be a cool idea to replace indexes with partitions (DB2 9.7 at the time had a brand new "partition by range" feature). He wrote giant bash scripts to partition many tables by timestamps on either a 15 minute, one hour, or one day interval AND by groups of account IDs (or any other ID that might be in the row). His logic was that even though all queries will do a full table scan.. but hey these are all really tiny tables now! Management and the senior devs thought it was a brilliant idea. It didn't work so well in the end, and ultimately indexes were just added randomly all over the place. But the partitions remained and the scripts would run every hour... but sometimes they'd fail to add partitions because of locks. Which ultimately left no where for certain data to go (based on timestamps) and the app would go down. This went on for years...
But the other issue, as time rolled on, when the schema did need to be changed, it was done the quickest and dirtiest way possible. One patch I saw go through added a "username" column to several tables to track what user made a change to a row, but it was varchar instead of a FK... completely de-normalizing the schema. No one knew or understood the data on the dev team.
I tried best as I could to do analytics, but the DB2 DBA's were always on my case for "accessing" "their" database. I managed to get buy in from my manager to start standing up postgres databases for reporting. We tried Mysql at first but its supported SQL syntax was too butchered for anything other than simple queries.
The idea was to have a carbon copy of the OLTP schema in postgres and populate it with Pentaho or some other BI tools/scripts. Once that was mostly working we were going to build a data warehouse schema with stuff in the proper formats so we could just query without having to transform. Sadly after a few months my manager moved me off of the project and put a rather new contractor over the project who knew nothing about data architecture, or linux (he constantly complained about the command line and would say things like "I stopped using DOS years ago"), or even postgres. I was moved to simply help with some vmware upgrades which was boring. They then opened a "jr DBA" position and filled that with someone straight out of college to handle standing up the postgres instances.
And with that, I left.... and eventually most of the rest of the team too.
PS, they are looking for a BI guy...
You were definitely on the right track to set up an ODS and then stand up a DW in front of that. Looks like you were firmly stuck in the "not allowed" camp and trying to work around absurd limitations.
> PS, they are looking for a BI guy...
No thanks.
I'm spoiled by being a consultant. Anyone who wants to pay our rates is already committed to making changes.
Big no-no right there -- a reporting database shouldn't have the same schema as your main transactional DB, because the latter is designed and built for write-heavy workloads, not reporting or analytical ones.
I'm pretty certain that a lot of the stuff happening in the CTEs could simply be designed as part of a "true" reporting schema and ETL'ed in as a batch job.
It seems to me that sticking everything in CTEs would be equally as jarring. Writing SQL or reading and understanding someone else's SQL is almost always a non-linear process, not least of all because the processing order by the database engine isn't top-to-bottom.