Hacker News Reader

Comment by mrfusion | Hacker News Reader

What's the significance of this?

This completes the feature set for JSON document storage so you can now use Postgres instead of NoSQL solutions. However Postgres's replication story is still a little weak compared to other solutions.

What are current best practices for configuring replicated PG with hot failover?

To be clear, what I want is: - The ability to deploy a cluster of say, 5 replicated PostgreSQL instances - All write transactions go to a single leader, which replicates them to the other instances - Reads can go to any instance - If a leader crashes the cluster will elect a new one without human intervention, and no committed transactions will be lost.

I've always been unclear on how to do this with Postgres.

I'd be interested to know this as well.

I've worked mostly with MariaDB Galera Cluster which is pretty effortless to setup, but for PostgreSQL there doesn't seem to be any clear direction on how to do the same things.

I am cheating and getting more or less the same availability properties by using multi-AZ RDS i.e. postgresql with AWS managing the synchronous block replication and standby promotion.

If someone is planning to use PostgreSQL as a NoSQL solution, one can use BDR (Bi-Directional Multi-Master Replication -> https://github.com/2ndQuadrant/bdr) and its global sequences (if needed).

There are plenty of other replication solutions available for Postgres, indeed.

You don't have to write the entire jsonb column in order to update one field. Or you can perform various update operations on it. This is pretty cool

This is incorrect. None of these functions modify the value of a column. All of them are purely functional and return a jsonb value with the specified field(s) replaced.

The significance is one of performance and convenience: before, you would have had to construct a new jsonb object with only the field(s) you wanted modified changed. (In fact it is possible to define these new operators in terms of such; I have done so.) Now you can just use these handy built-in functions, which presumably perform better than the manual method as well.

I think you are using different interpretations of "you".

If you do:

  update aTable
  set loginInfo = loginInfo - 'lastLogin'

where 'loginInfo' is a jsonb column, you, the programmer, need not write out the parts of the data to keep. PostgreSQL still sees this as 'read column, evaluate expression, write result', so it will read and write the entire jsonb value.

This is only partially correct. These new features reduce the amount of data that must be transmitted to/from the postgres server and how much json must be parsed, but the full row is still written to the WAL (write ahead log). This is true even for partial updates to JSONB columns and even when only updating other non-JSONB columns in the row.

When you do need to update JSONB columns this is a big improvement. You still should consider the size of your JSONB columns and the number & frequency of updates to those rows.

Reply on news.ycombinator.com