This topic has been very close to my heart and job function as an Enterprise Data Architect, for a while now. Even though some natively true Scale Out RDBMSs (also sometimes referred to as NewSQL Databases) like ClustrixDB (a drop-in replacement for MySQL) and Greenplum Database (uses PostgreSQL) have been there for a while, they are still not so mainstream yet as say an Oracle, Microsoft SQL Server, PostgreSQL or MySQL when it comes to mass adoption.
I came across two options available with PostgreSQL, which is a very widely used RDBMS these days.
One such being Postgres-XL which is natively full CAP compliant and a true Scale Out fork of PosgreSQL for both OLTP as well as OLAP application workloads, the other being Citus which is mainly aimed at the OLAP side of things. Both are open source and Citus Community Edition is free as well.
In this article, I am going to bring out a few important factors which might be worth considering if either are desired to be used for what they intend / profess to do.
Postgres-XL
XL is short for eXtensible Lattice. It also connotes an eXtra Large version of PostgreSQL, in this case across multiple systems / nodes. Postgres-XL is the core of TransLattice Storm (StormDB) rebranded and open sourced.
Postgres-XL uses the same license as PostgreSQL. Up until mid 2015, Postgres-XL used the Mozilla Public License.
Postgres-XL values stability, correctness and performance over new functionality. The Postgres-XL project ultimately strives to track and merge in code from PostgreSQL.
2ndQuadrant fully supports Postgres-XL. The company also offers consulting services to help organizations with application development, cluster design, deployment, feature development and performance analysis. Click here for more details.
Postgres-XL distributes the data amongst multiple nodes, and, crucially, it ensures a global consistent view of the data thanks to cluster-wide MVCC (Multi-version Concurrency Control) via the Global Transaction Manager component. It is full ACID compliant.
Geo-distribution is not natively supported i.e., Geo Scale Out is currently not possible out of the box, only localized Scale Out is.
For HA, you can add slaves for each node analogous to PostgreSQL’s streaming replication. In addition, the cluster can be configured such that the Global Transaction Manager (GTM) can have a GTM Standby. This will help mitigate the single point of failure issue with the GTM i.e., the shard controller.
In terms of automatic failover, it is currently not part of the core project, but Corosync/Pacemaker has been used for this purpose.
Postgres-XL includes Massively Parallel Processing (MPP) capability with sophisticated query planning and inter-node communication that allows it to fulfill query requests over large data sets quickly. Postgres-XL is well suited for Data Warehousing and Data Marts. In Postgres-XL queries are parallelized and the data node nodes communicate with one another directly.
PostGIS can be added to Postgres-XL. It has been tested with PostGIS 2.0.
Write-intensive applications can take advantage of Postgres-XL running on multiple servers and spreading out the write load. Postgres-XL is well-suited for OLTP. With read-intensive applications data is spread out amongst multiple nodes, effectively giving you more cache and more hardware resources to serve it. It eliminates the need for other solutions such as Memcached.
Postgres-XL supports traditional SQL, strongly conforms to ANSI SQL:2008, is fully ACID, all while being scalable. You can however certainly use it as a key-value store and take advantage of JSON and hstore functionality.
Postgres-XL is wire-level protocol compatible with PostgreSQL. That means Postgres-XL will work with any PostgreSQL-compatible driver, which includes JDBC, ODBC, OLE DB, Python, Ruby, perl DBI, Tcl, and Erlang. That also means that many apps written for PostgreSQL will work without modifications against Postgres-XL, even including the well-known pgAdmin utility.
Currently, no known commercial distribution is available i.e., if you want to use it, it can be a pain to set up and maintain.
Citus
This on the other hand is available both as an open source license as well as compiled distributions in 3 editions namely, Community, Cloud (DBaaS) and Enterprise from Citus Data, the parent company.
Citus is a PostgresSQL extension.
However, this is purely a Big Data Ingestion Engine with Scale Out Analytics support i.e., it is not suitable for any meaningful OLTP application as of its current (5.0) capabilities / limits that I have known to be, listed below:
- Master - Worker data distribution architecture introduces a single point of failure with the Master being the coordinator of all user requests, as opposed to a Peer to Peer coordinator architecture used by say a Cassandra for example. The implication being that if the Master is unavailable, then the whole grid is down.
- There's no SCHEMA support at all, you'll need to use the PUBLIC schema if you want to use sharded tables, anything else works only in bits and pieces OR does not work at all for the most part.
- DELETE and TRUNCATE operations are not supported on sharded tables, you'll need to DROP and re-create if you have such requirements.
- You can't UPDATE directly on columns of JSON data type for sharded tables, you'll need to figure out UPSERT alternatives, which may or may not work based on what you want to achieve.
- Minimal or no sub query support for sharded tables.
I would recommend running your own use case specific evaluations to find out more before you consider using either of the 2 options.
I plan on testing Postgres-XL and ClustrixDB shortly and publishing my findings on those 2 options as well. I would encourage you to share with me your thoughts and comments on this topic as well so I may benefit from your experiences and expertise in this area.
I can only conclude by saying that not all hope is lost on scale out with RDBMSs, progress may seem to be slower than with NoSQL DBs but there's definitely a strong momentum ahead on the front for certain:-)
No comments:
Post a Comment