Tuesday, October 24

News and Roadmap for BDR (Multi-master PostgreSQL)

2ndQuadrant, Simon's PlanetPostgreSQL
Postgres-BDR is an open source project from 2ndQuadrant that provides multi-master features for PostgreSQL. We have pursued a joint strategy of providing both working code available now and also submitting the features into core PostgreSQL. Postgres-BDR 1.0 runs on a variant distro of PG9.4. This is in Production now and receives regular maintenance and security updates. 2ndQuadrant will support this until 9.4 End of Life in December 2019. One of the greatest achievements to come out of our work on BDR is the logical replication technology. Our engineers spent a considerable amount of energy to contribute the tech to PostgreSQL core and I feel especially proud that this is a headline feature of the upcoming PG10 release. And Now BDR 2.0 …  BDR 2.0 runs on community PG9.6 as (more…)

News and Roadmap from Postgres-XL 10

2ndQuadrant, Pavan's PlanetPostgreSQL
We expect Postgres-XL 10 to be released in the next few months, with the new features of partitioning and logical replication. You’ll be able to load Postgres-XL directly from PostgreSQL. For the first time, you’ll be able to run massively parallel queries both across the datanodes and within the datanodes to give huge performance gains. Earlier today, we updated the master branch of the Postgres-XL repository to include all commits from PostgreSQL's master branch up to June 26. That means the XL project is now fully to date with the PostgreSQL source code, meaning there is now only minimal lag between PostgreSQL and Postgres-XL. At this point the code is only in Development/Alpha, though we expect to produce Beta1 soon after PostgreSQL Beta 3 in August. Support from the (more…)

PG Phriday: Uptown Func

Shaun's PlanetPostgreSQL
One of the coolest things about Postgres functions is that they can return rows as if they were a table. Not only is it possible, but creating a set-returning function in Postgres is almost frighteningly trivial when compared to other database engines. In fact, some Oracle users are probably rolling their eyes and muttering "Just use a cursor!" already. Just hear us out! Postgres functions can return cursors too, but using them afterwards isn't exactly a friendly experience. Cursors are passed by reference, so the function must either accept a parameter to name the cursor, or it generates something wacky like "<unnamed portal 1>". After that, cursors can only be used with FETCH instead of SELECT, greatly limiting their utility. What about views, then? Obviously they can return (more…)

PG Phriday: Postgres on Tap

Shaun's PlanetPostgreSQL
Testing within the context of a database can sometimes seem a bit overwhelming. Not only is it necessary to create a reasonable set of test data to bootstrap multiple tables, but we need some reliable invocation system to compare query, view, and function output to expected results. And that is no small feat. For a very long time, the "easiest" way to test end-user database objects involved building a Makefile that included PGXS. Then we had to create the tests, run the tests, and copy over the output into static expected files. Modifying any test always meant updating the static output file as well. It's great that the tools are there, yet why do we need to be halfway to a Postgres developer in order follow good QA practices with Postgres? Well, maybe we don't. It turns out, (more…)

Announcing Release 5 of the PostgreSQL Buildfarm Client

Andrew's PlanetPostgreSQL
Release 5 of the PostgreSQL Buildfarm Client has been released and can be downloaded from https://buildfarm.postgresql.org/downloads/releases/build-farm-5.tgz In a similar move to PostgreSQL version numbering, with this release we move to a one part numbering system. In addition to a number of bug fixes and very minor changes, this release has the following features / changes: Cross-version pg_upgrade module is no longer experimental - see below TAP tests now run the "check" target, but in most cases redundant installs are avoided Improved and expanded TAP test running on MSVC animals - these now run the same tests as other animals Automatic garbage collection of git repositories, once a week by default. This should improve the speed of git operations, especially on (more…)

PostgreSQL Popularity Continues to Grow!

Simon's PlanetPostgreSQL
Some years ago, I prophesized that PostgreSQL would win in the end, and that we would be able to tell because there would be "Microsoft PostgreSQL". I am happy to report that has now happened. Microsoft PostgreSQL database service on Azure was announced recently. Of course, that was just a metaphor for "a.n.other big company", I'm not signalling MS as requiring special attention here, in fact they have been the last to do this. That means PostgreSQL database services are now available from all of the main cloud service providers: Amazon, Google, IBM and Microsoft. Yay! We have achieved 100% breakthrough in terms of cloud adoption - everyone recognizes now that PostgreSQL is big and adoption is accelerating. PostgreSQL is now used in UK and US governments, see UK (http://www. (more…)

PG Phriday: Dearly De-parted

Shaun's PlanetPostgreSQL
With the christening of PG Phriday at its new location, it's only fitting that we revisit a frequent boon and bane of organized storage: partitioning. When volume and scale reach a certain point that the very laws of physics become a barrier to efficiency, partitions are a crucial tool to escape the otherwise inevitable pile of unmaintainable bits. It's not feasible to reindex a multi-terabyte table with tens or hundreds of billions of rows. It's excruciating to excise portions for long-term archival, and all but impossible to perform anything but the most cursory maintenance in general. Huge tables are a partition advocate's dream. But until fairly recently, the implementation in Postgres has been... less than savory. While nothing is perfect, Postgres has never actually offered (more…)

Visualizing sar data

Mark's PlanetPostgreSQL
Hopefully you are now regularly collecting system statistics after reading Tomas Vondra’s defense of using sar.  If you don’t have anything in place that readily visualizes the data, I have some examples that you can hopefully build off on to see everything you need to see. In addition to sar, the sysstat package also has a utility called sadf to display collected data.  Simply running sadf without any arguments will show you the recently aggregated processor utilization data resulting in: gentoo 599 2017-05-25 17:10:01 UTC all %user 13.03 gentoo 599 2017-05-25 17:10:01 UTC all %nice 0.01 gentoo 599 2017-05-25 17:10:01 UTC all %system 5.16 gentoo 599 2017-05-25 17:10:01 UTC all %iowait 0.47 gentoo 599 2017-05-25 17:10:01 UTC all %steal 0.00 gentoo 599 2017-05-25 17:10:01 (more…)

Future of Postgres-XL

PostgreSQL, Tomas' PlanetPostgreSQL
You probably know that Postgres-XL is a distributed database based on PostgreSQL. A few days ago we pushed the XL 9.6 code into the public git repository. Additional details about the new stuff available in Postgres-XL 9.6 are available here. The topic of this blog post is quite different, though. I'd like to discuss some changes to the project management and development practices, and why (and how) we plan to tweak it. (more…)