Haas: New Features Coming in PostgreSQL 10

[Posted April 8, 2017 by corbet]

Here's an extensive summary of new features in the upcoming PostgreSQL 10 release from Robert Haas. "PostgreSQL has had physical replication -- often called streaming replication -- since version 9.0, but this requires replicating the entire database, cannot tolerate writes in any form on the standby server, and is useless for replicating across versions or database systems. PostgreSQL has had logical decoding -- basically change capture -- since version 9.4, which has been embraced with enthusiasm, but it could not be used for replication without an add-on of some sort. PostgreSQL 10 adds logical replication which is very easy to configure and which works at table granularity, clearly a huge step forward. It will copy the initial data for you and then keep it up to date after that."

Haas: New Features Coming in PostgreSQL 10

Posted Apr 10, 2017 18:26 UTC (Mon) by brunowolff (guest, #71160) [Link] (3 responses)

Another nice improvement I ran into was sp-gist support for inet. For an exclude index over 3.5 million records the index creation time dropped from about 24 hours down to about 10 minutes going from a gist inet index to an sp-gist inet index.

Haas: New Features Coming in PostgreSQL 10

Posted Apr 11, 2017 8:28 UTC (Tue) by ovitters (guest, #27950) [Link] (2 responses)

Can Postgres automatically figure out the best index type? Having to know the details (drawbacks, benefits) of all the various index types seems a bit too much. Sometimes it is nicer if by default it does the likely right thing, then still allowing you to tweak it later. Ideally any re-tweak should be possible automatically as well.

Haas: New Features Coming in PostgreSQL 10

Posted Apr 11, 2017 12:32 UTC (Tue) by brunowolff (guest, #71160) [Link]

Normally a particular operator class is going to have a default index type you're going to want to use. It looks like the Gist index for inet was known to be poor and someone worked on an SP-Gist index to try to improve the situation. (Which it did for me.) I suspect at this time they will need to keep the Gist index around for backward compatibility for a while, as the default index for inet goes back from before there was a Gist index for it. So there seems to be a precedent for not dropping index support.
This might be more complicated going forward with the improvements in hash indexes also coming in version 10. Previously b-tree indexes were typically faster and provided better crash safeness than hash indexes, so that for operators with b-tree support you would choose b-tree. But it looks like their might be some cases where you'd prefer hash indexes going forward. B-tree will probably still be the safe default, but if you really needed to try to get things faster, you might want to run benchmarks to see if a hash index would be better in a particular case.

Haas: New Features Coming in PostgreSQL 10

Posted Apr 21, 2017 15:21 UTC (Fri) by intgr (subscriber, #39733) [Link]

Short answer: No. Not in the general case because it doesn't know what kinds of queries you intend to run. Different indexes support different operators. Even in the simple case of text and numeric values, you can have hash indexes -- which only support equality (=) queries -- or btree indexes, which support range queries (>, <, <=, etc), ORDER BY and can speed up merge joins.

Then there are BRIN indexes, which trade off faster update speed and smaller disk size for slower lookup times. Similarly GIN and GiST support some overlapping operator classes, but have different performance tradeoffs.

For the 'inet' type as well, if all you want is range queries, then btree is better than SP-GiST. If you need the more complicated operators then both GiST and SP-GiST provide almost the same set of operators. I don't understand them enough to say whether there's a useful tradeoff involved or one is clearly better than the other. If the latter, then keeping the old GiST implementation around sounds like a misfeature.