Working towards Postgres-XL 9.5
It’s been busy few months as we work towards merging Postgres-XL with the latest and greatest release of PostgreSQL. Postgres-XL is an open source fork of PostgreSQL that provides a scalable platform for OLTP and Business Intelligence. The current release of Postgres-XL is based on PostgreSQL 9.2, so it lacks all the improvements made to PostgreSQL over the last three years.
2ndQuadrant and other companies are working on bringing distributed scalability into PostgreSQL core as well as building tools and extensions outside the core. As part of that, Postgres-XL has a number of features that we’d like to bring back into core PostgreSQL, so 2ndQuadrant has picked up the task of updating the Postgres-XL code base to the latest PostgreSQL release as the first step. After more than 3 months work, PostgreSQL 9.5 is still in alpha stage, so we wanted to give a progress report on how the work is proceeding. I also need to say the magic words: This ongoing work on Postgres-XL is part of the AXLE project, funded by the European Union under grant agreement 318633.
Preparation for the Merge
Since PostgreSQL and Postgres-XL both use GIT as source control system, it makes the merge process much simpler since GIT provides many tools to assist the process. But as soon as we tried the merge, we faced the first hurdle.
We realised that the current Postgres-XL repository is based on an older minor 9.2 release of PostgreSQL. That means there were commits and changes in the Postgres-XL master branch which either never made to PostgreSQL’s master branch or had different commit ids. So merge with PostgreSQL master branch threw a lot more conflicts than what we would have other expected. So the first task we must accomplish was to rebase the Postgres-XL 9.2 repository on a later commit point. This obviously required careful treading, making sure that nothing breaks during the process. Once we had the basic rebase done, we also merged all Postgres-XL bug fixes and enhancements, created a Postgres-XL 9.2 stable branch and merged the 9.2 branch with the latest available PostgreSQL 9.2 minor release.
Challenges Faced During the Merge
The actual merge with PostgreSQL master branch was not an easy task either. Note that we were jumping 3 major releases of PostgreSQL, which almost accounted for 3 years of development work. Thankfully, git-mergetool comes very handy for such large scale merges. You can use your favourite editor (vimdiff in our case) to nicely see the merge conflicts and resolve them. While some of the conflicts are straightforward and require minor adjustments, many require careful reading and understanding of the code. While it’s not trivial to support all new features, we tried to preserve as much as possible and we have been quite successful.
The other major challenge was merging documentation changes. Since Postgres-XL project had created a copy of the existing SGML documentation, the GIT merge with the master branch did not yield any updates to the copy. This required manual merge. To ensure that this is not required again in future merges, we now make the documentation changes in-place.
There are many things that needs to be completed before we can release Postgres-XL 9.5 to general public:
- Enhance regression test coverage for Postgres-XL
- Fix bugs and add support for new features
- Do systematic performance tests and tuning
- Create Postgres-XL 9.5 branch and merge with latest PostgreSQL 9.5 stable branch
We aren’t yet ready for review of Postgres-XL, but we expect Postgres-XL 9.5 Beta to be ready round about the same time as PostgreSQL 9.5 Beta.
Look for my next blog post in about a month’s time for the next update.
Very excellent work! I’m using the XL 9.2 and is longing for 9.5.
One question: in XL 9.2, if XL needs to redistribute a table among N data nodes, then it will create N*N connections among date nodes — this may exhaust the system resources; will this problem get improved in XL 9.5?
It’s not really a problem. Read the blog article on “Joins Don’t Scale” https://blog.2ndquadrant.com/joins-dont-scale/
We haven’t done anything specific in that area. Focus of this merge was to get XL in sync with the latest PostgreSQL release, thus benefiting from several improvements and enhancements that went to the upstream product. Is the redistribution improvement something very important for you? Can you please also share your experiences with XL 9.2 so far?
Hi,Wolfgang, I think the redistribution improvement is important if we have many data nodes.
For my experience in XL, as a whole, it is very awesome project! We’re using it in data mining in insurance industry and are extending it to other areas. It beats Oracle completely! Looking forward to the new releases. Hope that the XL is as active as PostgreSQL and is updated as frequently as PostgreSQL.
During my experience with XL, I dig into the source codes (downloaded from http://www.postgres-xl.org/download/, http://sourceforge.net/projects/postgres-xl/files/Releases/Version_9.2rc/postgres-xl-v9.2-src.tar.gz/download) .
I found that there are some problems with table created on a single node like this:
create table t2 (f1 int, f2 int) to node(dn2);
I found a bug with MVCC. It’s very difficult to repro but I swear that I have seen it several times. I reported it to the XL email list but looks like it isn’t got fixed (maybe because difficult to repro?)
My repro of the bug is (not always happen):
1) Open 2 connections to the XL
2) in both connection 1 & 2:
set default_transaction_isolation=’repeatable read’;
3) in connection 1:
postgres=# create table t2 (f1 int, f2 int) to node(dn2);
Time: 30.144 ms
postgres=# insert into t2 values (11,12);
INSERT 0 1
Time: 5.047 ms
Time: 0.215 ms
postgres=# select * from t2;
f1 | f2
11 | 12
Time: 2.494 ms
4) switch to connection 2, and then:
Time: 0.302 ms
postgres=# select * from t2;
f1 | f2
11 | 12
Time: 3.257 ms
postgres=# update t2 set f2=f2+1000 where f1=11;
Time: 3686.544 ms
Time: 1.468 ms
5) back to connection 1: /* Note that the transaction in connection 1 BEGIN before session 2 COMMIT! */
postgres=# select * from t2;
f1 | f2
11 | 12
11 | 1012 /* BUG: this row shouldn’t appear */
Time: 1.235 ms
I tried to debug but didn’t find the reason.
It only happens sometimes (not always) with table created in a single node but never happens on distributed or replicated tables. Maybe you might need to carefully review the related code paths?
Sorry, the above post should be a reply for Pavan Deolasee’s post. I wrote the wrong name in the post…
This is fantastic news! We will certainly test the beta version with our shop system and web framework. What are the plans for future versions of postgres? Will there be a tighter connection of the release schedules between PostgreSQL and PostgreSQL XL? It’s also nice to know that our taxes are spent on something worthwhile.
Its slightly hard at this point to comment on the future releases. A lot would depend on the adoption and acceptance on XL. With limited resources, its hard to support multiple releases, catch up with the latest PG release, add new XL specific features and also add PG-compatible missing features. We hope that this release will get us far along the way since we were lagging behind PostgreSQL by at least 3 years.
Hope triggers for partitioned tables will be enabled/fixed soon too!
Are FDWs supported now?
We are planning to make tests with XL95 and pgstrom soon too, for some analytical and rather lengthy queries. Will share the findings with you once done.
Unfortunately neither of those two things are supported yet. The primary purpose of this work was to bring XL close to latest PG release and fix outstanding bugs/issues. Once we get there, we can start adding more features. But please go ahead with your tests and let us know the results. Please keep in mind though that we are still in an early alpha state!
Great! Eagerly waiting for 9.5 release. Have to benchmark with few other NoSQL solutions. Confident that PostgreSQL/XL will do it’s job at best.
Thanks Chaitanya. Please share your findings with us!
Are the developments on PostgresXL in any way related to the BDR project, or will PostgresXL 9.5 be making use of any of the new cluster API’s and extension points introduced in Postgresql in the past years? I more or less assumed that PostgresXL was a dead end…
These are two different projects, but we would surely look to benefit from each other’s experiences. We are not using much of the newer APIs from PG given that most of XL’s development in that area pre-dates availability of much of that infrastructure. The project is definitely not dead 🙂
We are very interested in PostgreXL 9.5. And we are willing to join or help the development.
Maybe we can test PostgreXL 9.5 in our environment.
Is there any way to access the code and test it?
Any help with testing or development is always welcome. The latest code is hosted at git://git.postgresql.org/git/postgres-xl.git. Download from there and give it a try. Please understand that the code is still in alpha state, but any early feedback is definitely welcome.
You’d mentioned that the XL beta would go out about the same time as 9.5’s beta. Is that still happening? As of this writing, 9.5RC1 is imminent, so this seemed like a good time to check back.
Thanks for making this happen!
Thanks for your interest!
We are a bit delayed because we decided to put a lot more efforts in benchmarking and also added a lot more improvements than what we’d planned for, especially for OLTP workloads. Most of that work is now committed. In fact, I created and pushed the XL9_5_STABLE branch yesterday to the repository which is now fully merged with the latest REL9_5_STABLE branch. So while the beta is delayed, I’m personally quiet happy with where we are right now. Most of the major code changes and bulk of the merge is behind us.
Hi, I see that PostgreSQL has FDW for Cassandra and Hadoop. What scenario’s would you use Postgresql XL for big data instead of Cassandra and Hadoop?
How does Postgres-XL compare to Greenplum?
Has Postgres-XL been tested with many datanodes? For example, 64 datanodes?
Well, Postgres-XL is the World’s First Open Source Data Warehouse. Greenplum is the second? 😉
XL is designed to work with many nodes, and Yes, its been tested.
Great to see you guys merging the xl and 9.5. and pushing some changes to 9.6.
I also hope you guys help with this performance bottleneck in S_Lock (PinBuffer and UnpinBuffer) and getSnapshotData as seems a good fit/opportunity with the work your doing already..
and put it in the new XL and 9.6 codebase ofcourse 🙂
MySQL 5.7 has made incredible performance gains in reads (1.6 Million) as well as more importantly for my use cases… Connect/sec rate.
MariaDB will have this soon, and I fear that Postgres will be left even more in the dust. hardware $ savings, AWS etc will outweigh the cost of an odd outage/restore recurring
Thanks! We’ll surely look at the work you mentioned above and see what’s the best way to get that in XL.
of course any way to get it into PG 9.7 also greatly appreciated 😉
Congrats on the pglogical announcement!! does this complement or replace BDR?
last silly question, any reason why XL+9.5 (and eventually 9.6 I assume) merged together by 2nd quadrant 😉 couldn’t = PG 9.7 alpha code base? i.e. any reason to keep PG and XL as two separate branches/solutions? when you guys are doing amazing work of merging 9.5 into XL.. I dont fully understand why someone would deploy 9.5 as an example when new merged XL has all 9.5 benefits/functions as well as MPP etc etc etc..
Great work Pavan. Where can we log the bugs for the 9.5 RC upon testing it
You may use [email protected] ML to report issues until we have some sort of issue-tracker for the project.