Tuesday, October 24

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. Sorting Sorting 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 have sorted (more…)

What’s new in Postgres-XL 9.6

2ndQuadrant, PostgreSQL, Tomas' PlanetPostgreSQL
For the last few months, we at 2ndQuadrant have been working on merging PostgreSQL 9.6 into Postgres-XL, which turned out to be quite challenging for various reasons, and took more time than initially planned due to several invasive upstream changes. If you’re interested, look at the official repository here (look at the “master” branch for now). There’s still quite a bit of work to be done - merging a few remaining bits from upstream, fixing known bugs and regression failures, testing, etc. If you’re considering contributing to Postgres-XL, this is an ideal opportunity (send me an e-mail and I’ll help you with the first steps). But overall, Postgres-XL 9.6 is clearly a major step forward in a number of important areas. (more…)

Up to date access to postgres logs

Andrew's PlanetPostgreSQL
Some of my Italian colleagues have made a nifty little gadget called redislog for pushing postgres logs into Redis, the distributed in-memory cache. From there it can be fed into things like logstash. I thought it would be interesting instead to make the logs available via the Redis Foreign Data Wrapper as a Postgres table. That way we would have easy access to the running logs from Postgres with almost no effort. Here's what I did. First I built and installed redislog and redis_fdw. Then I added redislog to my server's shared_preload_libraries,  set log_min_duration_statement to 0 and restarted. Then I created a database called logger and did this in it: create extension redis_fdw; create server localredis foreign data wrapper redis_fdw; create foreign table (more…)

Logical Replication in PostgreSQL 10

Petr's PlanetPostgreSQL
PostgreSQL 10 is getting close to its first beta release and it will include the initial support for logical replication, which is was written primarily by me and committed by my colleague Peter Eisentraut, and is internally based on the work 2ndQuadrant did on pglogical (even though the user interface is somewhat different). I'd like to share some overview of basics in this blog post. What's logical replication? Let me start with briefly mentioning what logical replication is and what's it good for. I expect that most people know the PostgreSQL streaming master-standby replication that has been part of PostgreSQL for years and is commonly used both for high availability and read scaling. So why add another replication mechanism and why call it logical? Well, the traditional (more…)

Out of tree builds

Andrew's PlanetPostgreSQL
Recently I was asked "what's a vpath build?" If you regularly build postgres from source it's something you should know about. A vpath build is one where the build tree is kept completely separate from the source tree, so you can completely remove the build tree and your source tree is still there, clean and pristine. It's very easy to set up unless you're building with the Microsoft toolset. Essentially what you do is create the root of your build tree, change directory into that directory, and then call configure in your source tree from there.  configure knows all about setting up the vpath tree and does all the work for you. After that, you just run make etc just like you would normally. So it looks like this: mkdir mybuild cd mybuild /path/to/postgresql-source/configure make (more…)

In the defense of sar (and how to configure it)

2ndQuadrant, PostgreSQL, Tomas' PlanetPostgreSQL
Let me discuss a topic that is not inherently PostgreSQL specific, but that I regularly run into while investigating issues on customer systems, evaluating "supportability" of those systems, etc. It's the importance of having a monitoring solution for system metrics, configuring it reasonably, and why sar is still by far my favorite tool (at least on Linux). (more…)

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 *; and CREATE 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? Compatibility The new syntax conforms to the SQL standard. Creating auto-incrementing columns has been a notorious area of incompatibility between different SQL implementations. Some have lately (more…)

“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 (more…)

BuildFarm client release 4.19

Andrew's PlanetPostgreSQL
I have released version 4.19 of the PostgreSQL Buildfarm client. It can be downloaded from https://buildfarm.postgresql.org/downloads/releases/build-farm-4_19.tgz Apart from some minor bug fixes, the following changes are made: Include the script's path in @INC. That means you can usually run the script from anywhere rather than just its own directory. Set TZ after "make check" is run. This makes for slightly faster initdb runs in later stages. make TAP tests run with --timer change default log_line_prefix in config file to use %p instead of %c. That makes it easier to tie log messages to other messages that mention a pid Add a module to log running commands to a file as it runs and replace critical uses of `` with the new procedure. That means we have better traces if (more…)