There is this idea that from 9.5 we will be able to upgrade pg clusters to newer, major, version, without downtime (well, almost), thanks to magic of logical replication.

Sounds very promising, so I figured – I'll test it. To some extent at least.

Read more »

On 21st of October, Robert Haas committed patch:

postgres_fdw: Push down aggregates to remote servers.
Now that the upper planner uses paths, and now that we have proper hooks
to inject paths into the upper planning process, it's possible for
foreign data wrappers to arrange to push aggregates to the remote side
instead of fetching all of the rows and aggregating them locally.  This
figures to be a massive win for performance, so teach postgres_fdw to
do it.
Jeevan Chalke and Ashutosh Bapat.  Reviewed by Ashutosh Bapat with
additional testing by Prabhat Sahu.  Various mostly cosmetic changes
by me.

Read more »

On 20th of October, Robert Haas committed patch:

Rename "pg_xlog" directory to "pg_wal".
"xlog" is not a particularly clear abbreviation for "write-ahead log",
and it sometimes confuses users into believe that the contents of the
"pg_xlog" directory are not critical data, leading to unpleasant
consequences.  So, rename the directory to "pg_wal".
This patch modifies pg_upgrade and pg_basebackup to understand both
the old and new directory layouts; the former is necessary given the
purpose of the tool, while the latter merely avoids an unnecessary
backward-compatibility break.
We may wish to consider renaming other programs, switches, and
functions which still use the old "xlog" naming to also refer to
"wal".  However, that's still under discussion, so let's do just this
much for now.
Michael Paquier

discussion link

Read more »

September 26th, 2016 by depesz | Tags: | No comments »

It looks that for some reason my subscription to pgsql-committers disappeared, and I stopped receiving mails from this list.

I also didn't notice it, for some time – not sure when it got lost.

I did resubscribed now, but if you can think of any specific commits that I should have written about, but haven't, please let me know, and I'll write about it. Sorry for the problem.

Today, on irc, someone asked interesting question.

Basically she ran a query like:

select a, b, c, d, e, f from table order by a

then, she processed the query to get, for each a array of unique values of b, c, d, e, and f, and then he inserted it back to database, to some other table.

It was a problem, because the table had many rows (millions I would assume), and the whole process was slow.

So, how to make it faster?

Read more »

Lately at least two people on irc asked questions similar to “how do I know how many queries there are in database, per second?“.

So, let's see what we can find out.

Read more »

Some time ago someone on irc asked about creating fast counters for something (banners I think).

I talked with her (him?) about it, but figured, I can as well write a blogpost, so others can use it too.

Read more »

I happen to play Zombicide. It's cooperative board game set in some kind of post-apocalyptic, zombie-infested, world.

Despite the general theme, lots of people like it, so we play it quite a lot.

One thing that bugged us, was : in given scenario, which weapon it's best to use?

Read more »

Some time ago, I wrote blogpost about how to pick a task from queue, without locking.

It was written in 2013, and as such it couldn't reflect everything we have now in PostgreSQL – namely SKIP LOCKED – which was added to PostgreSQL over year later.

Two people mentioned SKIP LOCKED in comments, but it was before it was committed even to git repo. But now, we have, officially released, PostgreSQL version with this mechanism, so let's see what it can do.

Read more »

On 8th of April, Alvaro Herrera committed patch:

Support \crosstabview in psql
\crosstabview is a completely different way to display results from a
query: instead of a vertical display of rows, the data values are placed
in a grid where the column and row headers come from the data itself,
similar to a spreadsheet.
The sort order of the horizontal header can be specified by using
another column in the query, and the vertical header determines its
ordering from the order in which they appear in the query.
This only allows displaying a single value in each cell.  If more than
one value correspond to the same cell, an error is thrown.  Merging of
values can be done in the query itself, if necessary.  This may be
revisited in the future.
Author: Daniel Verité
<span class="signoff">Reviewed-by: Pavel Stehule, Dean Rasheed</span>

Read more »