E.25. Postgres-XL Release 9.5r1

E.25.1. Overview
E.25.2. Migration to Postgres-XL 9.5r1
E.25.3. Major Enhancements in Postgres-XL 9.5r1
E.25.4. Changes
E.25.5. Important Bug Fixes
E.25.6. Important Bug Fixes and Improvements since 9.5r1beta1
E.25.7. Important Bug Fixes and Improvements since 9.5r1beta2
E.25.8. Known Limitation
E.25.9. Contributing to Postgres-XL
E.25.10. Credits

Release Date


Current as of 2016-04-18

E.25.1. Overview

This major release of Postgres-XL comes almost 2 years after Postgres-XL 9.2 was released, which was based on PostgreSQL 9.2. This release includes most of the new features added in PostgreSQL 9.3, 9.4 and 9.5 releases. This also includes almost all significant performance enhancements that were made to PostgreSQL in the last few years.

Apart from this, a lot of work has gone into fixing outstanding bugs, improving stability of the product and significantly improving performance for OLTP workloads. The following sections describe the major enhancements in PostgreSQL that are now available in Postgres-XL. A short list of features that are currently not supported or have limited support is also mentioned in Section E.25.3.5.

E.25.2. Migration to Postgres-XL 9.5r1

E.25.2.1. Migration to Postgres-XL 9.5r1 from PostgreSQL 9.5

A dump/restore using pg_dumpall is required for those wishing to migrate data from PostgreSQL 9.5 release. We don't currently support pg_upgrade, as a mechanism to upgrade from PostgreSQL 9.5

E.25.2.2. Migration to Postgres-XL 9.5r1 from Postgres-XL 9.2

Version 9.5r1 contains a number of changes that may affect compatibility with previous releases. Since Postgres-XL 9.5r1 release includes all changes from PostgreSQL 9.3, PostgreSQL 9.4 and PostgreSQL 9.5 releases, it is recommended that you review respective release notes of those releases to find the exact incompatibilities.

E.25.3. Major Enhancements in Postgres-XL 9.5r1

Postgres-XL 9.5r1 is the first major release after Postgres-XL 9.2 release. So this release contains most of the major enhancements that went into PostgreSQL releases 9.3, 9.4 and 9.5. This is a very short list of such enhancements, but all other enhancements also apply, unless otherwise stated.

E.25.3.1. Major Enhancements from PostgreSQL 9.6

  • Substantial improvement in 2PC performance by avoiding creation of state files when its not necessary.

E.25.3.2. Major Enhancements from PostgreSQL 9.5

  • Allow INSERTs that would generate constraint conflicts to be turned into UPDATEs or ignored

  • Create mechanisms for tracking the progress of replication, including methods for identifying the origin of individual changes during logical replication

  • Add Block Range Indexes (BRIN)

  • Substantial performance improvements for sorting

  • Substantial performance improvements for multi-CPU machines

E.25.3.3. Major Enhancements from PostgreSQL 9.4

  • Add jsonb, a more capable and efficient data type for storing JSON data

  • Add new SQL command ALTER SYSTEM for changing postgresql.conf configuration file entries

  • Reduce lock strength for some ALTER TABLE commands

  • Add support for logical decoding of WAL data, to allow database changes to be streamed out in a customizable format

  • Allow background worker processes to be dynamically registered, started and terminated

E.25.3.4. Major Enhancements from PostgreSQL 9.3

  • Make simple views auto-updatable

  • Add many features for the JSON data type, including operators and functions to extract elements from JSON values

  • Implement SQL-standard LATERAL option for FROM-clause subqueries and function calls

  • Add optional ability to checksum data pages and report corruption

  • Prevent non-key-field row updates from blocking foreign key checks

  • Greatly reduce System V shared memory requirements

E.25.3.5. Major Enhancements PostgreSQL that are currently not supported

E.25.4. Changes

Below you will find a detailed account of the changes between Postgres-XL 9.5r1 and the previous major release.

E.25.4.1. Performance

  • WAL log only the actual GID used to prepare a 2PC transaction, not the maximum bytes reserved for GID.

    This change considerably reduces the WAL space required while preparing a transaction and shows siginificant performance improvement.

  • Significantly improve performance for queries that can be fully executed on a single node by shipping them to the node.

    Postgres-XC has Fast Query Shipping (FQS) feature to fully ship queries that can be safely executed on the remote datanodes, without any finalisation at the coordinator. The same feature has now been ported to Postgres-XL. This improves performances for extremely short queries which are now directly planned and executed on the datanodes.

  • Bump up default value for sequence_range to 1000.

    The earlier default for this GUC was 1. But performance of INSERT is observed to be extremely poor when sequences are incremented by 1. So the default value of this GUC is now bumped up to 1000. This can create holes in the sequence assignment. Applications that do not want holes in sequence values should set this GUC to 1.

  • Add a developer-GUC 'gloabl_snapshot_source' to allow users to override the way snapshots are computed.

    This is a developer GUC and it must be used with caution since its usage can lead to inconsistent or wrong query results. In Postgres-XL, snapshots are normally computed at the GTM so that a globally consistent view of the cluster can be obtained. But sometimes applications may want to read using a slightly stale snapshot that is computed on the coordinator, so that an extra round trip to the GTM is avoided. While such snapshots can improve performance, they may give a wrong result, especially when more than one coorinators are running.

  • Compute RecentGlobalXmin on each node separately and send it to the GTM periodically. GTM then computes a cluster-wide RecentGlobalXmin and passes it back to the nodes.

  • Wait for the socket to become ready to receive more data before attempting to write again.

    When client is pumping data at a much higher speed than what the network or the remote nodes can handle, coordinator used to keep buffering all the incoming data, eventually running out of memory. We now deal with this problem in a much better way.

  • Use poll() instead of select() to check if one or more file descriptors are ready for read/write.

    select() system call is not well equipped to handle large number of file descriptors. In fact, it has an upper limit of 1024, which is not enough in a large distributed system such as Postgres-XL. So we now use poll() for checking which sockets are ready for send/recv.

  • Fix aggregate handling for BIGINT/INT8 datatype for platforms with support for 128-bit integers.

  • Significant reduction in XID consumption.

    In the older release of Postgres-XL, every transaction would consume an XID, irrespective of it did any write activity to the database. PostgreSQL fixed this problems a few years back by using Virtual XIDs. This release of Postgres-XL solves this problem to a great extent by completely avoiding XID assignment for SELECT queries and only assigning them when are really required.

E.25.4.2. Additional Features

  • Support Greenplum syntax for specifying distribution strategy for a table.

    Postgres-XL now supports additional syntax for specifying distribution strategy. This syntax is compatible with Greenplum. See CREATE TABLE for more details.

  • Support Redshift syntax for specifying distribution strategy for a table.

    Postgres-XL now supports additional syntax for specifying distribution strategy. This syntax is compatible with Redshift. See CREATE TABLE for more details.

  • Add xc_maintenance_mode GUC which is useful for resolving in-doubt prepared transactions.

  • Add support for pg_stat_statements.

  • Allow DMLs inside a plpgsql procedure on the coordinator.

  • Add necessary machinery to support TABLESAMPLE clause.

  • Add support for materialized views on the coordinator.

  • Add 'C' and 'R' to log_line_prefix.

    This helps us print remote coordinator ID and PID of the remote coordinator process and useful for debugging.

  • Add support to receive error HINTs from remote nodes and send them back to the client along with the error message.

  • Add two new GUCs, log_gtm_stats and log_remotesubplan_stats to collect runtime information about GTM communication stats and remote subplan stats.

  • Support recursive queries on replicated tables.

  • Add a developer GUC "enable_datanode_row_triggers" to allow ROW TRIGGERS to be executed on the datanodes.

    This is a developer GUC and it must be used with caution since the feature is not fully supported yet. When this GUC is turned on, ROW TRIGGERS can be defined on tables. Such triggers will only be executed on the datanodes and they must be written in a way such that they don't need access to cluster-wide data. This feature is not well tested and users are advised to do thorough testing before using this feature.

E.25.4.3. Improvements to pgxc_ctl

  • Add support for gtmSlaveName in pgxc_ctl.conf.

  • Add "help" command to pgxc_ctl.

  • Improve "pgxc_ctl configure" command so that datanodes are also properly configured.

  • Add ability to specify extra server configuration and pg_hba configuration while adding a new datanode master or slave.

  • Add support to save history of pgxc_ctl commands.

  • Add ability to specify datanode slave ports and datanode slave pooler ports separately.

  • Add ability to specify separate XLOG directory while setting up a datanode or a datanode slave using pgxc_ctl.

  • Add a new "minimal" option to "prepare" command of pgxc_ctl.

    This new option can be used to create a sample pgxc_ctl.conf file to setup a Postgres-XL cluster on the local machine, using non-conflicting data directories and ports. This is very useful for quick testing.

  • Improve pgxc_ctl so that it checks if a directory is empty before it can be used as data directory for a new datanode/coordinator.

  • Add "force" option to pgxc_ctl init command to forcefully remove datanode, coordinator or gtm directory.

E.25.4.4. Misc Improvements

  • Analyze all regression failures and make necessary changes to the expected output or the test cases.

  • Persistent connections are not supported between datanodes.

    Configuration parameter persistent_datanode_connections is ignored on the datanodes. So connections between datanodes are returned back to the connection pool at the end of the transaction. A WARNING will be shown when this parameter is set on the datanode side and the value will be ignored.

  • Change GID format to include all participant nodes.

    Every implicit 2PC GID now includes node_id of every participating node in the 2PC transaction. This refers to the element of pgxc_node .node_id value.

  • Replicated tables are now highly-available for read-access.

    Every node now maintains a healthmap about all other nodes in the cluster. If a node goes down or is unreachable, the healthmap is updated. Queries that read from replicated tables are then sent to a healthy node. Unhealthy nodes are periodically pinged and their status is updated when they come back online.

  • "make check" now automatically sets up a 2-coordinator, 2-datanode cluster and runs parallel regression schedule.

  • Print EXPLAIN plans, as created by the datanodes, for queries that are fully shipped to the datanodes.

  • Force commit ordering at the GTM for transactions that have followed a specific logical ordering at the datanode/coordinators.

  • Add a Cluster Monitor process which periodically reports local state to the GTM for computation of a consistent global state.

  • Cancel queries on remote connections upon transaction abort.

    When a transaction abort or when user cancels a query, we now correctly send down the query cancellation to all the remote nodes and cancel the query on every node in the cluster.

  • Set the size of pending connections on a pooler socket to some respectable high limit.

  • Add support for GTM to backup data at BARRIER command.

  • Disable internal subtransactions.

E.25.5. Important Bug Fixes

  • Fix pgxc_clean so that it cleans up outstanding transactions correctly.

  • Fix multi-command SQL query execution i.e. queries that have multiple SQL commands separated by ;.

  • Fix memory leaks in prepared statement handling.

  • Send CREATE/ALETR POLICY utility commands to the remote nodes.

  • Send SET commands in the same order to remote nodes at session initialization time.

  • Propogate ALTER TABLE ALL IN correctly to all nodes.

  • Handle CREATE TABLE IF NOT EXISTS correctly.

  • Do not propogate REINDEX command to coordinators for indexes on temporary tables.

  • Rename sequences correctly on the GTM when schemas are renamed.

  • Push down LIMIT clause to the remote side if its a constant.

  • Refactor GTM connection management.

  • Fix a bug in deciding local GTM proxy while adding a new datanode master.

  • Fix a problem in COPY while redistributing table data when a node is added or removed from distribution or when distribution key is changed.

  • Set up pg_hba.conf on the master properly while adding a slave.

E.25.6. Important Bug Fixes and Improvements since 9.5r1beta1

  • Add missing steps in the installation guide (which is used to create the INSTALL file).

  • Fix agregation handling when a collection function is not specified for the aggregate.

  • Fix bugs around handling of params passed to the datanodes.

  • Limit the frequency of reporting local state to GTM so that we don't report more than once every CLUSTER_MONITOR_NAPTIME seconds, even when GTM reports errors.

  • Correctly include the string terminator in calculating GID size, without which the GID will look corrupted and unusable after a crash recovery.

  • Include a version identifier in the GTM control file so that we can read different versions correctly for backward compatibility.

  • Correctly handle multi-command SQL statements i.e. statements with multiple ';' separated commands.

  • Fix handling of binary data transfer for JDBC as well as libpq protocols.

  • Fix several compilation warnings (Tomas Vondra)

  • Do not use 3-stage aggregation when ORDER BY is specified in the aggregate itself.

  • Fix problems in VPATH build.

  • Add support for process level control for overriding log levels for elog messages.

  • Improve handling of Append and MergeAppend plans. They are now pushed down to the datanodes whenever possible.

  • Add support for 3-stage aggregation for json_agg() aggregate, thus improving performance for the aggregate handling.

    Add support for checking status of a coordinator or datanode slave without requiring it to be started as a Hot Standby.

E.25.7. Important Bug Fixes and Improvements since 9.5r1beta2

  • Fully caught up to the latest PostgreSQL 9.5.2 release.

  • Compilation and regression support for SmartOS.

  • Add check against accidental start of GTM with an XID lower than what it's saved in its control file.

  • Correctly start GTM standby after it's added by 'pgxc_ctl add gtm slave' command.

  • Fix several memory leaks in the executor code path which should help ALTER TABLE .. ADD NODE and large INSERTs into a distributed table.

E.25.8. Known Limitation

While Postgres-XL strives hard to stay compatible with PostgreSQL so that applications running on PostgreSQL can be easily ported to Postgres-XL, there are certain known limitations of the product. Many of these can be addressed as and when sufficient development funding and resources become available.

  • Hot Standby is not supported

  • Distributed deadlock detection is not supported yet. Deadlock detection exists on each node, just not across nodes.

  • Materialised views are currently maintained only on the coordinator.

  • EXCLUSION CONSTRAINTS are enforced when both rows map to the same datanode.

  • User defined functions have several limitations.

  • There are restrictions on complex UPDATE/DELETE queries and updating distribution column values.

  • TRIGGERs are not supported.

  • EVENT TRIGGERs are not supported.

  • SERIALIZABLE TRANSACTIONs are not supported.

  • CREATE INDEX CONCURRENTLY is not supported.

  • SAVEPOINTs are not supported.

  • Large objects are not supported.

  • Recursive queries work only in certain conditions.

  • GROUPING SETS, ROLLUP or CUBE are not yet supported.

  • Foreign Data Wrappers are not supported.

  • INSENSITIVE/SCROLL/WITH HOLD cursors are not supported.

  • LISTEN/NOTIFY is not supported.

E.25.9. Contributing to Postgres-XL

Contributions to Postgres-XL are welcome. The code will be accepted under the same open source license that governs this released version of Postgres-XL. The authors of the patches will be credited appropriately in the release notes of the future releases.

E.25.10. Credits

Postgres-XL has been evolving over many years, originating with the GridSQL project and Postgres-XC, later combined as TransLattice Storm (StormDB). TransLattice open sourced the project, resulting in Postgres-XL 9.2. More recently, the EU-funded Big Data project AXLE funded the main work to bring the code up to date as Postgres-XL 9.5, allowing Postgres-XL to take advantage of the rapidly increasing BI features plugged into the core of PostgreSQL.