Author: depesz

Waiting for 9.4 – Introduce jsonb, a structured format for storing json.

Portuguese Brazil Version

On 23rd of March, Andrew Dunstan committed patch:

Introduce jsonb, a structured format for storing json.
 
The new format accepts exactly the same data as the json type. However, it is
stored in a format that does not require reparsing the orgiginal text in order
to process it, making it much more suitable for indexing and other operations.
Insignificant whitespace is discarded, and the order of object keys is not
preserved. Neither are duplicate object keys kept - the later value for a given
key is the only one stored.
 
The new type has all the functions and operators that the json type has,
with the exception of the json generation functions (to_json, json_agg etc.)
and with identical semantics. In addition, there are operator classes for
hash and btree indexing, and two classes for GIN indexing, that have no
equivalent in the json type.
 
This feature grew out of previous work by Oleg Bartunov and Teodor Sigaev, which
was intended to provide similar facilities to a nested hstore type, but which
in the end proved to have some significant compatibility issues.
 
Authors: Oleg Bartunov,  Teodor Sigaev, Peter Geoghegan and Andrew Dunstan.
Review: Andres Freund

Continue reading Waiting for 9.4 – Introduce jsonb, a structured format for storing json.

Waiting for 9.4 – Constructors for interval, timestamp, timestamptz

On 4th of March, Alvaro Herrera committed patch:

Constructors for interval, timestamp, timestamptz
 
Author: Pavel Stěhule, editorialized somewhat by Álvaro Herrera
Reviewed-by: Tomáš Vondra, Marko Tiikkaja
With input from Fabrízio de Royes Mello, Jim Nasby

Continue reading Waiting for 9.4 – Constructors for interval, timestamp, timestamptz

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 – Add json_array_elements_text function.

On 29th of January, Andrew Dunstan committed patch:

Add json_array_elements_text function.
 
This was a notable omission from the json functions added in 9.3 and
there have been numerous complaints about its absence.
 
Laurence Rowe.

Continue reading Waiting for 9.4 – Add json_array_elements_text function.

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.