Postgres-XL 9.5R1Beta2 Released!

The Postgres-XL 9.5R1Beta2 release went out yesterday. It’s another step forward to have a stable 9.5 release sometime very soon. A few key enhancements from the last beta release are captured in this blog. For the full list, I would recommend to read the release notes.

Support for binary data transfer for JDBC and libpq

If you’d trouble receiving data in binary format via JDBC or libpq, this release should fix those issues for you. The coordinator now have intelligence to figure out if the client is requesting data in binary format and handle those cases correctly.

Pushdown of Append and MergeAppend plans

One of the problems reported with the beta1 release was that for inherited tables, coordinator fails to pushdown plans to the remote nodes even when its possible. A simple example would be:

postgres=# CREATE TABLE parent (a int, b int);
CREATE TABLE
postgres=# CREATE TABLE child1() INHERITS (parent);
CREATE TABLE
postgres=# CREATE TABLE child2() INHERITS (parent);
CREATE TABLE
postgres=# EXPLAIN SELECT sum(a) FROM parent ;
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=417.19..417.20 rows=1 width=4)
   ->  Append  (cost=100.00..405.89 rows=4521 width=4)
         ->  Remote Subquery Scan on all (datanode_1,datanode_2)  (cost=100.00..100.01 rows=1 width=4)
               ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=4)
         ->  Remote Subquery Scan on all (datanode_1,datanode_2)  (cost=100.00..152.94 rows=2260 width=4)
               ->  Seq Scan on child1  (cost=0.00..32.60 rows=2260 width=4)
         ->  Remote Subquery Scan on all (datanode_1,datanode_2)  (cost=100.00..152.94 rows=2260 width=4)
               ->  Seq Scan on child2  (cost=0.00..32.60 rows=2260 width=4)
(8 rows)

You must have noticed that the planner hasn’t chosen the most optimal plan for the query. It would bring all rows from all tables at the coordinator and then perform an aggregation. For very large tables, this will clearly result in bad performance. The new release fixes this problem as seen from the EXPLAIN output below:

postgres=# EXPLAIN SELECT sum(a) FROM parent ;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Aggregate  (cost=76.50..76.51 rows=1 width=4)
   ->  Remote Subquery Scan on all (datanode_1,datanode_2)  (cost=0.00..65.20 rows=4521 width=4)
         ->  Aggregate  (cost=0.00..65.20 rows=1 width=4)
               ->  Append  (cost=0.00..65.20 rows=4521 width=4)
                     ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=4)
                     ->  Seq Scan on child1  (cost=0.00..32.60 rows=2260 width=4)
                     ->  Seq Scan on child2  (cost=0.00..32.60 rows=2260 width=4)
(7 rows)

Partial aggregates will be computed at each node and only the final result is computed on the coordinator. This should improve performance for such queries by many folds.

Process-level control for runtime change in logging level for selective elog() messages

You may remember that in R1Beta1 release we added support for overriding compile time log levels for elog() messages. In this release, that support is further extended to include process-level control for the messages. This will further help the developers and users to investigate issues on a live system. (Note: The server must be compiled with –enable-genmsgids to take benefit of this facility).

The pg_msgmodule_set() and pg_msgmodule_change() functions are used to change the log level of selective elog messages. The following new functions provide a finer control over the logging.

    - pg_msgmodule_enable(pid) - the given pid will start logging as per the
      currently set levels for elog messages.
    
    - pg_msgmodule_disable(pid) - the given pid will stop logging and use the
      compile time levels.
    
    - pg_msgmodule_enable_all(persistent) - all current processes will start
      logging as per currently set levels. If "persistent" is set to true then all
      new processes will also honour the currently set levels.
    
    - pg_msgmodule_disable_all() - all current and future processes will stop
      logging and only use compile time levels.

The latest source code is available at the project website for download. Test the new release and let us know if you find bugs or have suggestions for further improvements.