8 ms·
Parallel Sequential Scan is Committed to PostgreSQL 9.6
- losvedir 11y agoNaive question here, but why wouldn't parallel scans actually be worse when reading from a spinning disk? And even for cached data I'd expect the bottleneck to be I/O still. Or is this just important because it's laying the groundwork for parallel queries, and we shouldn't expect any improvements from this feature alone?
- gleb 11y agoPresumably the data is in cache already - i.e. it has already been read from disk and is sitting in RAM.
- fiatmoney 11y agoIt's a pretty rare database server that has its data on a single disk.
- jeltz 11y agoAnd the databases which fit on a single disk usually also fit in RAM, which removes the IO bottleneck.
- andy_ppp 11y agoWhat do SSDs look like when reading in parallel?
- LoSboccacc 11y agoGrey
- meshko 11y agoSSD drives contain multiple NANDs. So a well designed SSD controller will take advantage of that (including data striping, essentially turning your drive into a tiny RAID). Of course it is not clear how good your consumer SSD drives are at that, but any server-grade drive you are using in your db server should support parallel reads. It gets tricky though because it reads data so quickly it might saturate the io bandwidth even with a single request, I've never done that math. All I know is that SSDs make my queries magical :)
- Andys 11y agoIf you have SSDs, its faster. If you have more than 1 spinning disk in a RAID, its faster too.
- meshko 11y agobecause while one worker is blocked by IO the other one is processing the results of the previous IO.
- quizotic 11y agoThe post shows a 3.5x performance improvement for requested 4-way parallelism, so that's a significant improvement right now! You might be surprised how many queries are bound by memory bus bandwidth and CPU, rather than I/O ... but even I/O bound queries may benefit: Remember that postgres is a "demand-pull" system. A sequential scan node returns a single tuple at a time, whenever its parent node asks for a tuple. So there can be considerable time between requests for an actual disk block. Parallelizing the scan should result in more frequent requests to the disk controller, which could then have better opportunity to optimize reads - even for a single disk. Since most of the latency for spinning disks is seek time, having more outstanding block-read requests should help performance.
- gaius 11y agoThere are no stupid questions, only stupid answers... like this one :-) No but seriously, if your "spinning disk" is even a semi-modern storage controller, it will queue and reorder your parallel requests for the most efficient access to the several, or dozens, or hundreds of physical disks it manages. So why not slam it with all the requests in a single sequence? Because a) if you are set up to handle parallel returns in your code, you don't care about the ordering in which blocks are returned so much and b) the way this is physically implemented is n queues of d depth and you want to fill them all with work.
- mrmondo 11y agoThis is going to be fantastic for us. I'm assuming 9.6 is still some way away though.
- narsil 11y agoDates selected during PgCon this year for 9.6 show a release in October 2016: https://wiki.postgresql.org/wiki/PgCon_2015_Developer_Meeting#9.6_Schedule https://wiki.postgresql.org/wiki/PgCon_2015_Developer_Meetin...
- elchief 11y agoThat's what they said for 9.4 and 9.5 too. More likely December 2016
- gtaylor 11y agoIt seems like this could make Postgres even better for large-scale data warehousing. Very exciting!
- lobster_johnson 11y agoTake a look at Greenplum, which was open-sourced recently. It's an MPP fork of Postgres: http://greenplum.org http://greenplum.org.
- meshko 11y agoThat example benchmark he has is weird. You'd probably want to test this one something with at least a million rows, taking more than a 100ms and probably actually returning some data. This sub-second difference can be attributed to solar flairs. Also it is not clear that it is not just result of the table getting cached. Did they implement a feature like this and not run a real performance test?
- quizotic 11y agoRobert Haas is the real deal, and will certainly have tested this feature. Perhaps I missed it, but I don't think the post specified how many rows were in the source. Could have been more than a million. Having a relatively expensive filter (regex) eliminating all results keeps the performance picture clearer. Anything done with a tuple that passes the filter would apply to both the regular and the parallel scan cases, so eliminating the results highlights just the difference between regular and parallel scans
- meshko 11y agoI really really doubt that it can process a million uncached rows under a second. But yeah, I just tested an million was not a good example because it does take only a bit over a second on decent hardware. So perhaps should have said 10 million. I agree about not returning any results being the right test.
- rachbelaid 11y ago
- deleted 11y ago[deleted]
- sgt 11y agoPostgres is going in one direction - for the better. And as a long time Postgres user I've been really happy with it for years. Now with JSONB support I am even happier, as I can build powerful RDBMS + "NoSQL" apps that outperform traditional NoSQL databases. To my disappointment several members of my team have been championing Oracle recently though - presumably because it's a safer option and can be managed by others under an SLA. How does one argue against this? With Postgres we're not going to get SLA's because we know it very well, but with Oracle we would. It's mostly perception management IMHO, and I'm mostly against it. What killer core features does Oracle have that Postgres doesn't?
- jimmytucson 11y agoWell, parallel execution, for one. Am I reading this right? Postgres has not had parallel query, DML, or DDL until now? This has been a feature of Oracle for as long as I've been using it. Say what you want about enterprise software but Oracle is a beast... if you work for a company willing to shell out for it. That cost will seem sillier though Postgres closes the feature gap.
- dragonwriter 11y agoWell, professional support with SLAs of available for postgres through, most notably, EnterpriseDB, who also is a significant source of resources for core postgres development. So whether or not that's really something you need, that it is available for Oracle isn't a distinguishing point when compared with Postgres.
- LoSboccacc 11y agoAmazon RDB - not exactly the same as a managed oracle, but you get much in the package: backups, transparent multizone failover are the one that I prize the most.
- sciurus 11y agoI think you mean RDS, which is available for postgresql or Oracle.
- mixmastamyk 11y agoPG is awesome, but I remember people often complaining about its replication features a few years ago. Have they been improved?
- dagw 11y agoImproved, yes. Fixed, no. Basically if you want proper full replication, you won't get it out of the box, but will have to use various third party tools. That being said 9.4 added a bunch of the fundamental changes that that laid the foundation for full replication, now we're just waiting PG to finish exposing those features with userland tools.
- amatix 11y agoBuilt-in multi-master replication is the only real missing piece there, AFAIK every other replication base is covered well natively. Besides that, sync & async replication has been there nicely since 9.2. 9.3 added timeline switches, needed for cascaded slave setups in practice. 9.4 added replication slots so the master can keep track of slave sync status, and the foundations for multi-master & logical replication. 9.5 added pg_rewind which speeds up resyncs.
- DrJokepu 11y agoWhat do you mean by "proper full replication"?
- barrkel 11y agoMaster-master
- rgacote 11y agoThe EnterpriseDB folk http://www.enterprisedb.com/products-services-training/products-overview/xdb-replication-server-multi-master http://www.enterprisedb.com/products-services-training/produ... and 2ndQuadrant http://2ndquadrant.com/en-us/resources/bdr/ http://2ndquadrant.com/en-us/resources/bdr/ both have multi-master replication.
- TheCondor 11y agoHas anyone tried Postgres xl? It has this kind of logic at the server level, no?
- lobster_johnson 11y agoI don't think any of the current open-source forks are ready for production. The oldest one is Postgres-X2 [1] (formerly Postgres-XC), a project sponsored by NTT. It implements fully consistent multimaster replication and partitioning. After many, many years apparently still isn't production-ready, and it seems to have a significant scalability bottleneck that's tied to its design. Another issue is that being a fork, it has to be continually updated from the mainline. It's currently based on 9.3. Postgres-XL [2] is apparently a merger of Postgres-XC and a different implementation called StormDB that was bought by a small company called TransLattice. (TransLattice also sponsored or acquired an earlier project that went nowhere, Postgres-R.) Unlike XC/X2, they say they aim to contribute changes back to the mainline, and they also claim their distributed query model is superior. With the commerical support behind it (it's used as the basis of a commercial product), it's possible that this is something that will be usable. Unfortunately, it seems very quiet and not very open-sourcy; most of the development seems to be by just one guy [3], and nobody seems to be using it in production at this point. [1] https://github.com/postgres-x2/postgres-x2 https://github.com/postgres-x2/postgres-x2 [2] http://www.postgres-xl.org/ http://www.postgres-xl.org/ [3] http://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summary http://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summar...