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

121–130 of 138 posts

Re: New Features Coming in PostgreSQL 10

#121

Post by pgaddict »

foota wrote: post #103
pgaddict wrote: post #90

Earlier quoted context omitted.

I wonder why not to go for a changelog-based implementation. Instead of modifying the materialized view directly, write the changes into a changelog, and then update the matview in the background. More efficient, less locking issues, etc.

How do you know when the change will affect the matview? I don't know the syntax for creating it off the top of my head, but imagine a materialized view limited to the top ten records of a table.

I assume there will be some limitations what matviews can be updated automatically/ incrementally. E.g. updatable views (regular, not materialized) have a range of such restrictions, see: https://www.postgresql.org/docs/devel/static/sql-createview....

Hopefully, some of those restrictions will disappear, because matviews are often used to store pre-aggregted data, so the restriction on GROUP BY would be unfortunate.

Re: New Features Coming in PostgreSQL 10

#122

Post by pgaddict »

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.

I just remembered there's also "PostgreSQL: Up and Running" published by O'Reilly. It deals with more stuff than just administration, but Regina O. Obe and Leo S. Hsu are good authors.

Re: New Features Coming in PostgreSQL 10

#123

Post by pgaddict »

nickpeterson wrote: post #120
pgaddict wrote: post #91

Earlier quoted context omitted.

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...

Yeah, although there's a difference between "publishing whatever" and "publishing books that copy from other books".

Ultimately it's not the publisher but the author that matters, I guess.

Re: New Features Coming in PostgreSQL 10

#124

Post by paulddraper »

avar wrote: post #6

This bit about ICU support v.s. glibc: > [...] Furthermore, at least on Red Hat, glibc regularly whacks > around the behavior of OS-native collations in minor releases, > which effectively corrupts PostgreSQL's indexes, since the index > order might no longer match the (revised) collation order. To > me, changing the behavior of a widely-used system call in a > maintenance release seems about as friendly as locking a…

Well what do you expect?

Patch releases are for bug fixes. If you can't handle any change in behavior, including a bug fix, then you shouldn't be upgrading.

I understand the problem, and kudos to Postresql for figuring out a solution, but railing on glibc for fixing bugs in patch releases makes about as much sense as breeding raccoon families to chuck into people's cars.

Re: New Features Coming in PostgreSQL 10

#125

Post by scurvy »

sorkin2 wrote: post #115
scurvy wrote: post #97

Earlier quoted context omitted.

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:/…

I had not. Looks like it requires 9.5 or later? We're running 9.4 so we'll have to upgrade to use it. Thanks!

Re: New Features Coming in PostgreSQL 10

#126

Post by manigandham »

hartator wrote: post #33
stemcc wrote: post #29

Earlier quoted context omitted.

You can easily have schema-less with Postgres's jsonb data type.

Not really. Postgres ORMs are not meant to do schema-less and tables still need to be created.

In the .NET world, ServiceStack ORMLite has custom Postgres type attributes to use the jsonb and other fields automatically.

http://docs.servicestack.net/releases/v4.5.6#postgresql-data...

There is also Marten which is an ORM that offers a full document database interface all backed by Postgres JSON columns.

http://jasperfx.github.io/marten/documentation/documents/

Re: New Features Coming in PostgreSQL 10

#127

Post by [deleted] »

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.

[deleted]

Re: New Features Coming in PostgreSQL 10

#128

Post by manigandham »

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.

> I don't understand the desire for all-in-one solutions

You really don't understand it? It's less moving pieces to think about, worry about, read about, deploy, maintain, fix and almost always leads to better performance and security.

Something as simple as connection pooling should've already been part of the database and query redirection is even more important to have included.

Re: New Features Coming in PostgreSQL 10

#129

Post by mb4nck »

scurvy wrote: post #125
sorkin2 wrote: post #115

Earlier quoted context omitted.

> 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:/…

I had not. Looks like it requires 9.5 or later? We're running 9.4 so we'll have to upgrade to use it. Thanks!

You can get pg_rewind for 9.4 (and 9.3 in its branch) here:

https://github.com/vmware/pg_rewind/tree/REL9_4_STABLE

It's from the people who wrote it upstream, they provide the code there for earlier Postgres releases.

Re: New Features Coming in PostgreSQL 10

#130

Post by mb4nck »

Heliosmaster wrote: post #35
acdha wrote: post #34

What's the ops experience for a replicated setup like these days? i.e. assuming you want basic fault-tolerance at non-exotic size / activity levels, how much of a job is someone acquiring if, say, there are reasons they can't just use AWS RDS?

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

Note that starting from Postgres 10 (which this thread is about), you don't need to adjust wal_level and max_wal_senders (or max_replication_slots, for that matter) anymore. You still have to enable hot_standby=on on the standbys, though.

(and it is in general a good idea to keep the configuration the same as much as possible between primary and standbys).

Post reply on HN