Waiting for 9.4 – Introduce logical decoding

On 3rd of March, Robert Haas committed patch:

Introduce logical decoding.
 
This feature, building on previous commits, allows the write-ahead log
stream to be decoded into a series of logical changes; that is,
inserts, updates, and deletes and the transactions which contain them.
It is capable of handling decoding even across changes to the schema
of the effected tables.  The output format is controlled by a
so-called "output plugin"; an example is included.  To make use of
this in a real replication system, the output plugin will need to be
modified to produce output in the format appropriate to that system,
and to perform filtering.
 
Currently, information can be extracted from the logical decoding
system only via SQL; future commits will add the ability to stream
changes via walsender.
 
Andres Freund, with review and other contributions from many other
people, including Álvaro Herrera, Abhijit Menon-Sen, Peter Gheogegan,
Kevin Grittner, Robert Haas, Heikki Linnakangas, Fujii Masao, Abhijit
Menon-Sen, Michael Paquier, Simon Riggs, Craig Ringer, and Steve
Singer.

Continue reading Waiting for 9.4 – Introduce logical decoding

Waiting for 9.4 – Allow BASE_BACKUP to be throttled

On 27th of February, Alvaro Herrera committed patch:

Allow BASE_BACKUP to be throttled
 
A new MAX_RATE option allows imposing a limit to the network transfer
rate from the server side.  This is useful to limit the stress that
taking a base backup has on the server.
 
pg_basebackup is now able to specify a value to the server, too.
 
Author: Antonin Houska
 
Patch reviewed by Stefan Radomski, Andres Freund, Zoltán Böszörményi,
Fujii Masao, and Álvaro Herrera.

Continue reading Waiting for 9.4 – Allow BASE_BACKUP to be throttled

Waiting for 9.4 – pg_basebackup: Add support for relocating tablespaces

On 22nd of February, Peter Eisentraut committed patch:

pg_basebackup: Add support for relocating tablespaces
 
Tablespaces can be relocated in plain backup mode by specifying one or
more -T olddir=newdir options.
 
Author: Steeve Lennmark
Reviewed-by: Peter Eisentraut

Continue reading Waiting for 9.4 – pg_basebackup: Add support for relocating tablespaces

Waiting for 9.4 – Include planning time in EXPLAIN ANALYZE output.

On 29th of January, Robert Haas committed patch:

Include planning time in EXPLAIN ANALYZE output.
 
This doesn't work for prepared queries, but it's not too easy to get
the information in that case and there's some debate as to exactly
what the right thing to measure is, so just do this for now.
 
Andreas Karlsson, with slight doc changes by me.

Continue reading Waiting for 9.4 – Include planning time in EXPLAIN ANALYZE output.

Waiting for 9.4 – New json functions.

On 28th of January, Andrew Dunstan committed patch:

New json functions.
 
json_build_array() and json_build_object allow for the construction of
arbitrarily complex json trees. json_object() turns a one or two
dimensional array, or two separate arrays, into a json_object of
name/value pairs, similarly to the hstore() function.
json_object_agg() aggregates its two arguments into a single json object
as name value pairs.
 
Catalog version bumped.
 
Andrew Dunstan, reviewed by Marko Tiikkaja.

Continue reading Waiting for 9.4 – New json functions.

Getting count of distinct elements, per group, in PostgreSQL.

So, couple of days ago, some guy, from Periscope company wrote a blogpost about getting number of distinct elements, per group, faster using subqueries.

This was then submitted to Hacker News and r/Programming on Reddit.

Then, the original authors submitted second blogpost comparing speed between four different DB engines. Which, in turn, was also commented on Reddit.

I found the numbers presented by Periscope (as their improvement) as not that great.

Unfortunately – their blog doesn't allow for comments, so I decided to test it, and write on my own blog, what I can find about it.

Continue reading Getting count of distinct elements, per group, in PostgreSQL.

Waiting for 9.4 – Add pg_stat_archiver statistics view.

On 28th of January, Fujii Masao committed patch:

Add pg_stat_archiver statistics view.
 
This view shows the statistics about the WAL archiver process's activity.
 
Gabriele Bartolini, reviewed by Michael Paquier, refactored a bit by me.

Continue reading Waiting for 9.4 – Add pg_stat_archiver statistics view.

Waiting for 9.4 – Add recovery_target=’immediate’ option.

On 25th of January, Heikki Linnakangas committed patch:

Add recovery_target='immediate' option.
 
This allows ending recovery as a consistent state has been reached. Without
this, there was no easy way to e.g restore an online backup, without
replaying any extra WAL after the backup ended.
 
MauMau and me.

Continue reading Waiting for 9.4 – Add recovery_target='immediate' option.

Waiting for 9.4 – Tablespace changes

On 18th of January, Stephen Frost committed patch:

Add ALTER TABLESPACE ... MOVE command
 
This adds a 'MOVE' sub-command to ALTER TABLESPACE which allows moving sets of
objects from one tablespace to another.  This can be extremely handy and avoids
a lot of error-prone scripting.  ALTER TABLESPACE ... MOVE will only move
objects the user owns, will notify the user if no objects were found, and can
be used to move ALL objects or specific types of objects (TABLES, INDEXES, or
MATERIALIZED VIEWS).

He followed it, with another commit:

Add CREATE TABLESPACE ... WITH ... Options
 
Tablespaces have a few options which can be set on them to give PG hints
as to how the tablespace behaves (perhaps it's faster for sequential
scans, or better able to handle random access, etc).  These options were
only available through the ALTER TABLESPACE command.
 
This adds the ability to set these options at CREATE TABLESPACE time,
removing the need to do both a CREATE TABLESPACE and ALTER TABLESPACE to
get the correct options set on the tablespace.
 
Vik Fearing, reviewed by Michael Paquier.

Continue reading Waiting for 9.4 – Tablespace changes