Saturday, June 24

Tag: PostgreSQL

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.com...

Kanban & devops culture at 2ndQuadrant – Part 2

DevOps
In Part 2 of this series, we will continue our journey within the developmental dynamics of the Barman open source project for PostgreSQL database backup and disaster recovery. After providing a small introduction to devops and Kanban in Part 1, let's focus on the basic element of our daily management: The Boards.(more…)

More robust collations with ICU support in PostgreSQL 10

Eisentraut's PlanetPostgreSQL, PostgreSQL
In this article, I want to introduce the ICU support in PostgreSQL, which I have worked on for PostgreSQL version 10, to appear later this year.SortingSorting is an important functionality of a database system. First, users generally want to see data sorted. Any query result that contains more than one row and is destined for end-user consumption will probably want to be sorted, just for a better user experience. Second, a lot of the internal functionality of a database system depends on sorting data or having sorted data available. B-tree indexes are an obvious example. BRIN indexes have knowledge of order. Range partitioning has to compare values. Merge joins depend on sorted input. The idea that is common to these different techniques is that, roughly speaking, if you ha...

PostgreSQL 10 identity columns explained

Eisentraut's PlanetPostgreSQL, PostgreSQL
For PostgreSQL 10, I have worked on a feature called "identity columns". Depesz already wrote a blog post about it and showed that it works pretty much like serial columns:CREATE TABLE test_old ( id serial PRIMARY KEY, payload text );INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING *;andCREATE TABLE test_new ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, payload text );INSERT INTO test_new (payload) VALUES ('a'), ('b'), ('c') RETURNING *;do pretty much the same thing, except that the new way is more verbose. ;-)So why bother?CompatibilityThe new syntax conforms to the SQL standard. Creating auto-incrementing columns has been a notorious area of incompatibility between different SQL implementations. Some...

“Now is Better Than Never” – Why I decided to attend PyCon8…

2ndQuadrant, Britt's PlanetPostgreSQL
…and why I’m glad I did.It’s not all technical… Who knew?!Last year, Pycon7 was held right about the time I joined 2ndQuadrant. Seeing as I was new to the technology AND the Italian language (note: there was an English track), I opted out of attending. Well, after attending Pycon8, I can say that I won’t make that mistake again!Over the past year working in the Open Source community, I’ve learned more technical information than I could have ever imagined. Even then, attending a technical conference and understanding (completely) technical talks seemed a little far-fetched. Or so I thought!Since being introduced to the wonderful world of Open Source, PostgreSQL, and numerous other technologies - I’m continuously fascinated by the way that the communities intertwine. These te

Using the PostgreSQL TAP framework in extensions

Craig's PlanetPostgreSQL
Support for using the TAP protocol to run extended regression tests was added to PostgreSQL back in 9.4 with the adoption of Perl's prove tool and Test::More to test initdb, pg_basebackup, etc.Since then the TAP-based tests have been greatly expanded, particularly with the advent of the src/test/recovery tests and the PostgresNode module in PostgreSQL 9.6. PostgreSQL now comes with a built-in test harness for easily starting up postgres instances, creating and restoring backups for replication, setting up streaming, and lots more.You can now use this to test your extensions. pg_regress and its limitations Extensions have long supported pg_regress based tests. Just drop the test scripts in sql/. Put the expected results in expected/. List the test names (sans directory and file ex...

PGConf India 2017 – An Event to Remember

2ndQuadrant, Pavan's PlanetPostgreSQL
Yet another edition of PGConf India came to conclusion in early March. You may have noticed the change from PGDay to PGConf, which signals a much larger gathering of PostgreSQL enthusiasts, now and in future. What started as a small meet-up of like minded people 4 years back, has now grown into a 2-day conference with a dedicated training day and a multi-track event at the main conference.The keynote this year was delivered by Simon Riggs, a major developer and committer at PostgreSQL. He presented his thoughts on why Persistence is key to PostgreSQL's success. Persistence is why users trust PostgreSQL to manage their critical data and persistence is why PostgreSQL community is able to deliver a solid product, release after release.This year's conference was attended by more th

Benchmark on a Parallel Processing Monster!

David's PlanetPostgreSQL
Last year I wrote about a benchmark which I performed on the Parallel Aggregate feature that I worked on for PostgreSQL 9.6.  I was pretty excited to see this code finally ship in September last year, however something stood out on the release announcement that I didn’t quite understand: Scale Up with Parallel QueryVersion 9.6 adds support for parallelizing some query operations, enabling utilization of several or all of the cores on a server to return query results faster. This release includes parallel sequential (table) scan, aggregation, and joins. Depending on details and available cores, parallelism can speed up big data queries by as much as 32 times faster. It was the “as much as 32 times faster” that I was confused at. I saw no reason for this limit. Sure, if you have 32 CPU