2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Pavan's PlanetPostgreSQL3 / Postgres-XL 9.5R1Beta2 Released!
Pavan Deolasee

Postgres-XL 9.5R1Beta2 Released!

March 30, 2016/0 Comments/in Pavan's PlanetPostgreSQL /by Pavan Deolasee

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.

Tags: beta, postgres-xl, release
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
PostgreSQL User Group NL Planning to succeed
Scroll to top
×