Slashdot Mirror
PostgreSQL Getting Parallel Query
New submitter iamvego writes: A major feature PostgreSQL users have requested for some time now is to have the query planner "parallelize" a query. Now, thanks to Robert Haas and Amit Kapila, this has now materialized in the 9.6 branch. Robert Haas writes in his blog entry that so far it only supports splitting up a sequential scan between multiple workers, but should hopefully be extended to work with multiple partitions before the final release, and much more beside in future releases.
"All queries," LOL cuz they're all the same.
Idiot.
Not a dual core Pentium D or Athlon X2 from 2005?
From multiple points of view, they are. Idiot.
Maybe what they meant is that the bottleneck should be disk, RAM, bus, and/or network I/O for the vast majority of sub-queries if "done right". In other words, a single core should be able to computationally sift rows fast enough to keep up with row scans from multiple "sources" for up to say 8 sources, which may be the max practical number of sources.
However, I imagine that a complex or "mathy" equation in a query could make it be CPU intensive. But maybe that's relatively rare.
Table-ized A.I.
*Your
You mean 500 johns or 500 joins? Sounds painful either way.
Table-ized A.I.
Is this similar to MySQL / MariaDB's shard query? Do the commercial databases have this?
Either way, it's good to see. It's been some time since cpu speeds flatlined and multiprocessors became common.
This will greatly improve the already impressive PostgreSQL database engine and help it compete against the more well-known Enterprise Relational Database Engines at a much better price point (free).
I believe Oracle owns using more than 1 cpu in a query if I am correct so their own rdbms looks faster. Maybe it is now being enabled if it expires or maybe not.
I don't want to be sued using it
http://saveie6.com/
Your abuse of apostrophes is disgusting, and the charges probably won't hold up. What is the point of protecting 500 Lithuanians?
Just a few months back, lwn.net had a longish story on PostgreSQL. They were scoring a victory with the "UPSERT" command addition in 9.5, which with speed updates old records, OR inserts a new one, if none. A big feature on your commercial databases. Apparently, PostgreSQL's biggest worry lately is that it has so many developers adding cool new features that there's some resource lacks maintaining and cleaning the base code. (Possibly unfair oversimplification of lwn.net story.)
I discovered PostgreSQL to get a free geodatabase for mapping, with the PostGIS plug-in...the open plug-in architecture being one of the greatest things about a FLOSS database. After nearly 25 years with Oracle and thinking everything else was a toy by comparison, PG blew me away. Amazing features, high performance, reliable. It's an amazing project, and this news is both impressive and unsurprising.
I only ever got to develop a single project on pgsql and I regret that. This was back in 2001. MySQL was pretty immature at the time but had the enormous install base. I went with PostgreSQL because it was more mature. It never let me down. The deployment went fine, it ran great, customer used it on and off for about 6 years and then it was just no longer needed.
Fast forward to 2011, ten years later, and now I'm running the show and developing a point of sale for the family business I'm in and I run with MySQL just because I'm so much more familiar with it. That and the master-master replication suite from Percona. MySQL is way better than it was in 2001 but still lacking here and there. I still question that choice. I will eventually migrate off MySQL but I don't know if it'll be MariaDB or PostgreSQL.
Didn't Greenplum fork PostGres in order to create a 'shared nothing' version with parallel queries? And isn't that now open source? So maybe they could integrate that code?
However, I imagine that a complex or "mathy" equation in a query could make it be CPU intensive. But maybe that's relatively rare.
Good point.
"First they came for the slanderers and i said nothing."
This is clearly a grossly contrived, impossible scenario. Anyone who has spent more anything resembling a reasonable amount of time with Lithuanians will testify to their admiration for real talent and equal disdain for ineptitude. The mother in question would never have made it past her tenth customer without demonstrating strong aptitude for the work in question; had her goods proven lacking, she would have been disposed of in short order, most likely in a fairly public manner to serve as a warning to others who might similarly waste the time and energy of valued customers. Clearly, the parent is no Lithuanian, nor knows any, and is therefore nothing more than a nasty little bitch who will eventually tell such tall tales to the wrong audience; bye-bye, bitch-boi.
I've been using Postgres for well over a decade now, and I still love it. Yes, you have to tune it, like any powerful tool.
Granted this first pass is only for sequential scans, but those are the simplest to parallelize and generally the slowest. Some queries rely on table scans as not every column can be indexed.
Postgres' growing feature set is amazing. Thanks team!
particularly in parallel.
Sleep your way to a whiter smile...date a dentist!
Goatse has plenty of bandwidth
It will be interesting to see how it compares with MongoDB. MongoDB does not use joins. MongoDB is webscale.
Confucius say, "Find worm in apple - bad. Find half a worm - worse."
This optimisation caters for a niche (admittedly a relatively large one) where there are relatively few queries but large ones. A more typical usage is where there are many smaller queries. I hope that this does not compromise the total throughput, so that the total parallelisation of multiple concurrent queries is not slowed to allow parallelisation withing individual queries. Either that or it should be a switchable option.
A leader in parallel query processing is Terradata, it uses custom hardware for massive parallelism. Will be interesting to see if Postgresql can scale on commodity/cloud hardware.
As the fine blog post explains, it is a switchable option. You can set max_parallel_degree=0 to turn it off. Actually, right now, it's off by default, and you have to set max_parallel_degree>0 to turn it on.
I wouldn't call it a "niche" exactly. It's one of the major main use cases for employing an RDBMS.
We just have a lot of "database people" with very limited experience and a limited mindset.
A Pirate and a Puritan look the same on a balance sheet.
While I think PostgreSQL is a fantastic database, I find it perplexing that the CitusDB guys can come out of nowhere and make Postgres perform parallel operations, either on a single node or in a cluster, and get MUCH better results than the core developers can achieve. All without making *any* changes to the core. It's mind-boggling.
A more typical usage is where there are many smaller queries.
Typical usage of what? If I have an OLTP system, for a transactional web based system, sure i'd agree. But if I am operating a data warehouse with fact tables housing hundreds of millions of rows, or trying to run largish reports on top of my OLTP system (say for state/fed reporting, or financial reporting), my "typical usage" is not many smaller queries.
The last two projects I have been on, turning on auto-parrallism in Oracle has made huge performance gains. Not just for reads, but also when enabled for DML, we saw a lot of our bulk inserts and merges ran significantly faster. And this was on SQL that didn't change at all, just our DBAs making a few parameter changes on the DB.
I am building an object store where some of my data objects can each be a key-value store that is used as a column in a relational table. Some queries against a table require a full scan (e.g. SELECT * from my_table where address like '%Main Street%';). If I had a table with a billion rows, it can take awhile to scan the whole address column looking for matches (I dedup the values in each column, but there can still be 100 million unique address values in such a table.) The solution is to break each column into multiple segments and let separate threads scan each segment looking for matches. The scan can occur in parallel on multi-core machines and complete in a much quicker manner than forcing a single thread to scan the whole thing. It sounds very similar to what they are trying to do with PostgreSQL (except that database is row based where the whole row is stored together, instead of a columnar database like mine). Here are two short demo videos of the system in action. https://www.youtube.com/watch?... https://www.youtube.com/watch?...
Abstracting Binlog Servers and MySQL Master Promotion without Reconfiguring all Slaves
http://blog.booking.com/abstracting_binlog_servers_and_mysql_master_promotion_wo_reconfiguring_slaves.html