5 ms·

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 s

by esaym 11y ago

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.

assface 11y ago
> The same query when ran on DB2 took 12 hours, on postgres (5x more data) it took 24 hours. You're doing it wrong. These type of queries should be run on a column store (e.g., Vertica).
- esaym 11y ago
  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.
  - CuriousSkeptic 11y ago
    Did you look inte monetdb? I'm curios about real world experiences with that one.
- greggyb 11y ago
  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.
pgaddict 11y ago
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.
- radiowave 11y ago
  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.
  - calpaterson 11y ago
    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
    - lukaseder 11y ago
      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. But don't forget: CTEs can be recursive.
  - jsprogrammer 11y ago
    Which makes you wonder why Postgres can't just do that optimization for you.
greggyb 11y ago
If a BI guy is writing big multi-page queries using CTEs that run for hours, that's a sign of numerous other problems.
- CuriousSkeptic 11y ago
  That's just unfair, I bet you get to write thousands of lines of code with execution times measured in weeks.
  - greggyb 11y ago
    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. Edit: Extra word word.
    - 11y ago
MrBuddyCasino 11y ago
No surprise here, DB2 is the slowest of the big databases. It is also a steaming piece of shit.
shrikant 11y ago
> 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.

by esaym 11y ago

assface 11y ago

> The same query when ran on DB2 took 12 hours, on postgres (5x more data) it took 24 hours. You're doing it wrong. These type of queries should be run on a column store (e.g., Vertica).

esaym 11y ago
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.
- CuriousSkeptic 11y ago
  Did you look inte monetdb? I'm curios about real world experiences with that one.
greggyb 11y ago
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.

pgaddict 11y ago

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.

radiowave 11y ago
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.
- calpaterson 11y ago
  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
  - lukaseder 11y ago
    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. But don't forget: CTEs can be recursive.
- jsprogrammer 11y ago
  Which makes you wonder why Postgres can't just do that optimization for you.

greggyb 11y ago

If a BI guy is writing big multi-page queries using CTEs that run for hours, that's a sign of numerous other problems.

CuriousSkeptic 11y ago
That's just unfair, I bet you get to write thousands of lines of code with execution times measured in weeks.
- greggyb 11y ago
  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. Edit: Extra word word.
  - 11y ago

MrBuddyCasino 11y ago

No surprise here, DB2 is the slowest of the big databases. It is also a steaming piece of shit.

shrikant 11y ago

> 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.