Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

New Features Coming in PostgreSQL 10

rhaas.blogspot.com

Post reply on HN
Print view View original

111–120 of 138 posts

Re: New Features Coming in PostgreSQL 10

#111

Post by hodgesrm »

rhaas wrote: post #19
brianwawok wrote: post #13

Earlier quoted context omitted.

What is you use case for it? My only thought was sending just one table to replica to be used to do analytics on ..

Replication across major versions, for example to upgrade without downtime. Partial replication, to distribute shared data across a series of clusters, or for analytics and reporting as you mention. Replicating the data without replicating any table bloat. Being able to do limited writes (e.g. to temporary tables) on the standby. http://rhaas.blogspot.com/2011/02/case-for-logical-replicati...

Indeed--anything where you want the secondary to be other than a bit-for-bit copy of the primary. It's also convenient for HA in some cases due to the fact that the DBMS copies are fully independent, hence free from propagated bit-level errors and also available for unimpeded reads.

MySQL started with logical replication very early on and it has proven extraordinarily useful. One of the more interesting use cases is feeding log transactions into data warehouses, which should be possible in PostgreSQL 10.

Re: New Features Coming in PostgreSQL 10

#112

Post by gnaritas »

okket wrote: post #96
petepete wrote: post #45

Earlier quoted context omitted.

How do you mean? Couldn't you use a trigger to update the view?

A trigger on what? Every update, insert, delete, etc.? On every table in the view? Even if that is possible, it may be a major performance killer. This has to be done internally, I think.

It is possible, that's how I do it, and it doesn't really affect performance at all. You just don't do it real time, the triggers just kick an id into a queue and you have a background task work the queue updating the materialized table from the view it's a copy of just for the id's in the queue.

Re: New Features Coming in PostgreSQL 10

#113

Post by js2 »

atombender wrote: post #61
jacques_chester wrote: post #28

I deeply appreciate the great care that Postgres committers take in writing their merge messages. I think of it as a sign of respect for future developers to take the time to write a clear account of what has happened.

Postgres is one of the few projects that still use a strict patch-oriented development process that's based almost entirely around mailing-list communication. While core team members can commit directly to the repo, everyone else must submit the code changes for review to the pgsql-hackers mailing list as a clean, self-contained patch, where it's discussed and considered for inclusion. An accepted patch might be comm…

Git itself is another such project.

Re: New Features Coming in PostgreSQL 10

#114

Post by api »

xyzzy_plugh wrote: post #99
api wrote: post #60

The feature I'd really love is master selection with Raft or similar and automatic query redirection to the master for all write queries (and maybe for reads with a query keyword). That would make it very easy and robust to cluster pg without requiring a big complicated (a.k.a. high admin overhead and failure prone) stack with lots of secondary tools. This kind of fire and forget cluster is really the killer feature…

Can't pgbouncer/pgpool2 solve query redirection? I don't understand the desire for all-in-one solutions.

That desire comes from three places:

1. Minimize cognitive load by minimizing the number of things you have to learn.

2. Minimize deployment complexity and dependencies.

3. Complexity is just evil I'm general. Linear increases in complexity result in exponential increases in bugs, vulnerabilities, and failure modes. It's just combinatorics.

Re: New Features Coming in PostgreSQL 10

#115

Post by sorkin2 »

scurvy wrote: post #97
Heliosmaster wrote: post #35

Earlier quoted context omitted.

Streaming replication isn't hard at all: http://davide.im/setting-up-a-failover-database-for-postgres...

It's not hard to setup initially, but I'll admit that it's not very good. It's not very good in a long-lived scenario where you're changing your replication topology for routine maintenance tasks. Changing from master to replica is easy, but now you have to rebuild that original master off of the former replica now. Completely start over. You can't just start up again from a given transaction ID. MySQL's GTID impleme…

> Changing from master to replica is easy, but now you have to rebuild that original master off of the former replica now. Completely start over. You can't just start up again from a given transaction ID. MySQL's GTID implementation is much better in this regard. You can change masters and replicas all repoint them without rebuilding. You can't do that (currently) with Postgresql.

Have you heard of pg_rewind? https://www.postgresql.org/docs/current/static/app-pgrewind....

Re: New Features Coming in PostgreSQL 10

#116

Post by barrkel »

frik wrote: post #44
Normal_gaussian wrote: post #4

Extended Statistics! I was following the replication changes, but have just discovered the extended statistics and am more excited about them. The directory renaming at the bottom of the post is interesting - I wonder if many other projects have to do things like this?

It would be great if some Linux distros clear up the directory mess. There are directories in there with names that no ones remembers what they originally meant in the UNIX of 1970s, or what ever. For compatibility they could be just hard/soft-links to a more sane directory structure. Well the same goes for Windows. With Win95, WinNT 3.5, WinXP, WinVista they restructured the internal directory tree and renamed thing…

winsxs uses hard links - space wastage is more likely from more versions than just dupes. Also, many windows tools won't account t correctly for hard links in disk usage stats.

Re: New Features Coming in PostgreSQL 10

#117

Post by petepete »

okket wrote: post #96
petepete wrote: post #45

Earlier quoted context omitted.

How do you mean? Couldn't you use a trigger to update the view?

A trigger on what? Every update, insert, delete, etc.? On every table in the view? Even if that is possible, it may be a major performance killer. This has to be done internally, I think.

So what would make the MV self-update?

Re: New Features Coming in PostgreSQL 10

#118

Post by lathiat »

atombender wrote: post #61
jacques_chester wrote: post #28

I deeply appreciate the great care that Postgres committers take in writing their merge messages. I think of it as a sign of respect for future developers to take the time to write a clear account of what has happened.

Postgres is one of the few projects that still use a strict patch-oriented development process that's based almost entirely around mailing-list communication. While core team members can commit directly to the repo, everyone else must submit the code changes for review to the pgsql-hackers mailing list as a clean, self-contained patch, where it's discussed and considered for inclusion. An accepted patch might be comm…

> If you read the MySQL manual, it's absolutely littered with sloppy little breakages throughout its history: Like how, until 5.0.something, when comparing a "date" value with a "datetime" value, the time portion would be silently ignored and ('2017-04-08 14:04' = '2017-04-08') would return true; but they fixed that, and broke a lot of client code because they didn't stop to realize that a lot of developers depended on that behaviour.

This is an interesting comment for two reasons. Firstly because a lot of people also complain about MySQL's archaic defaults which often stay too long because of upgrade concerns (though they fortunately are fixing a lot of them already or for MySQL 8.0 - hooray).

But also because it speaks volumes, in my opinion, about the MySQL documentation that these are documented in the first place. I worked at MySQL for 9 years and though it was always clear our manual was always a good source of information, now that I am working on Ubuntu & OpenStack it is painfully obvious just how good the MySQL documentation team and processes were compared to many other projects. Even just the version ChangeLog.

I'm not saying other projects don't get it right (and have no opinion at all about postgresql's documentation state), but MySQL seems to get it pretty right in general.

Re: New Features Coming in PostgreSQL 10

#119

Post by lobster_johnson »

lathiat wrote: post #118
atombender wrote: post #61

Earlier quoted context omitted.

Postgres is one of the few projects that still use a strict patch-oriented development process that's based almost entirely around mailing-list communication. While core team members can commit directly to the repo, everyone else must submit the code changes for review to the pgsql-hackers mailing list as a clean, self-contained patch, where it's discussed and considered for inclusion. An accepted patch might be comm…

> If you read the MySQL manual, it's absolutely littered with sloppy little breakages throughout its history: Like how, until 5.0.something, when comparing a "date" value with a "datetime" value, the time portion would be silently ignored and ('2017-04-08 14:04' = '2017-04-08') would return true; but they fixed that, and broke a lot of client code because they didn't stop to realize that a lot of developers depended…

But MySQL's problem is that those "archaic defaults" shouldn't have happened in the first place.

No matter how good MySQL is good at documenting their bugginess, weirdness, flakiness and overall history of legacy warts, the point was PostgreSQL eliminates pretty much all of this through promoting, at every single stage of the development process, the same strict, uncompromising QA principles. By doing this, legacy behaviour generally disappears.

Postgres isn't bug-free, of course. But they take great care to not be continually chased by a tsunami of technical debt. If you build a house on a crappy foundation, you get a crappy house, so it's a good idea to spend time on the foundation before building the house. The Postgres team spent years on the foundation, before building the higher-level parts, and the rewards are obvious.

Meanwhile, MySQL has spent years and years slowly mopping up tech debt. Things have gotten a lot better with the tighter semantics, such as preventing "February 29th" from being inserted, or rejecting "0000-00-00" as a date, or silently ignoring data coercion errors, and so on. But those things shouldn't have happened in the first place if the developers had been better at QA. So while you're right that the documentation is decent at describing various legacy semantics, it's also encodes a history of carelessness that's rather embarrassing reading.

By the way, date/datetime regression I mention isn't in the documentation at all. It's in their bug tracker.

Re: New Features Coming in PostgreSQL 10

#120

Post by nickpeterson »

pgaddict wrote: post #91
nickpeterson wrote: post #59

Can anyone recommend a decently up to date book on postgres administration? Or are docs really the only way? I've used SQL Server for years but would likely choose postgres for an independent project if I intended to commercialize it. That said, I don't use it at work so it's hard to get in depth experience.

There's PostgreSQL 9 Admin Cookbook from Simon Riggs, for example (disclosure: I work for Simon). Packt has several other good books about PostgreSQL, but always check the author - they started publishing books authored by people entirely unknown in the community, that are "inspired" by book published before (you might also use "plagiarism" instead).

Yeah packtpub is a real crapshoot. They're great in that they'll seemingly publish whatever tech subject you want to write about. The downside is they publish anything...
Post reply on HN