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

91–100 of 138 posts

Re: New Features Coming in PostgreSQL 10

#91

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.

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

Re: New Features Coming in PostgreSQL 10

#92

Post by pgaddict »

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…

Linux kernel is another such project, I think.

One of the reasons why it's done this way (through mailing lists and not e.g. through pull requests on github) is that all the history is tracked in a way that's fully under control of the community. So it's fairly easy to find who/when submitted the patch, how it looked like, etc.

Of course, another reason is history - most of the process was established long before git, when CVS was the VCS.

Re: New Features Coming in PostgreSQL 10

#93

Post by pgaddict »

scrollaway wrote: post #78
anarazel wrote: post #63

Earlier quoted context omitted.

In my opinion github isn't quite ready for more complex things: - Lengthy reviews yield PRs that measure many megabytes, become unusably slow - There's no reasonable way to do PRs based on other PRs, without closing and reopening then when the underlying branch is merged - large changes can't even be displayed - doesn't work offline. I do a fair amount of review while traveling, and not being able to do reviews while…

Yeah absolutely, hence why I said github-like. What I mean by that is something more modern, that supports such review style. If only Github supported mailing lists...

There's still the aspect of handing over the control to a third party that may disappear next month, discontinue the features you rely on, etc.

FWIW, I don't think the workflow is a major barrier. Do 'git format-patch' and submit it to mailing list is about as simple as it can be. It's all the other stuff (complex code-base, going through review, ...) and fairly high bar for quality that makes it challenging.

Re: New Features Coming in PostgreSQL 10

#94

Post by pgaddict »

rhaas wrote: post #51
smac8 wrote: post #49

Wow, so awesome. I do hope at some point we can see some language improvements to PLPGSQL. More basic data structures could go a long way in making that language really useful, and I still consider views/stored procedures a superior paradigm to client side sql logic

I agree with you that stored procedures are superior to client-side logic, because it means that you can have multiple routes of access to the database and all of them enforce the same business logic. But what exactly do you mean by "more basic data structures"?

PL/SQL has various types of collections, for example, that are super-useful when you need to do more complicated processing without having to create temporary tables and such.

Re: New Features Coming in PostgreSQL 10

#95

Post by pgaddict »

snuxoll wrote: post #58
frik wrote: post #42

Earlier quoted context omitted.

> massive redesign of the storage engine Have the Postgres thought about adding support for more than one storage engine? Then they could implement new ideas in a fork, an one could run them side-by-side and migrate over to it. https://www.postgresql.org/message-id/4CB597FF.1010403@cheap... For example MySQL had been mocked for its old ISAM storage engine. Then MySQL added InnoDB as another storage engine, the SQL in…

Pluggable storage engines for databases don't work that well in practice. Either you end up with the MySQL situation where the storage engine is so dumb that you can't push any smart optimizations into it (making having pluggable engines moot in the first place), or you have to write such a large interface that it's not worth providing.

That depends on what you mean by pluggable storage and how it's implemented ...

For example PostgreSQL supported custom index access methods, which you might see as a custom storage format (although only for secondary storage). You had to modify the source code and rebuild PostgreSQL, but there was a fairly clear separation / internal API that allowed that. Since PostgreSQL 9.6 you can do that without the custom build (i.e. you can create a new index in an extension and use CREATE ACCESS METHOD to plug it into the server).

We don't have such clear internal separation for the primary storage, but it's hard to ignore the possible benefits of alternative storage types. Another reason is that we're constantly scavenging for free bits in various places (e.g. flags in tuple headers needed by new features etc), and allowing multiple formats would help with this by supporting "old" and "new" table format. So it'll likely follow what happened to indexes - build a clear internal API, allow multiple storage formats internally, eventually make it usable from extensions.

(These are just my personal opinions, of course.)

Re: New Features Coming in PostgreSQL 10

#96

Post by okket »

petepete wrote: post #45
fiatjaf wrote: post #40

Ok, I'm not a database manager for enormous projects, so these changes may be great, but I don't understand them and don't care about them. Postgres is already the most awesome thing in Earth to me. Still, if my opinion counts I think SELF-UPDATING MATERIALIZED VIEWS should be the next priority.

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.

Re: New Features Coming in PostgreSQL 10

#97

Post by scurvy »

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

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 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. It's a major pain point.

Re: New Features Coming in PostgreSQL 10

#98

Post by xyzzy_plugh »

jhgg wrote: post #80
ams6110 wrote: post #76

A question on this statement, in the SCRAM authentication description: stealing the hashed password from the database or sniffing it on the wire is equivalent to stealing the password itself How is that the case? That's exactly the thing that hashed passwords prevent. Of course, if it's just an MD5 hash that's feasibly vulnerable to brute-forcing today, but it's still not "equivalent" to having the clear-text passwor…

The point is that you only send the hash to the database to connect. If you steal the hash, you can connect to the database using said hash, not needing the plaintext. The password might as well be the hash in this case. Hence the equivalency. Using that scheme, all you prove is that you know the hash of the password. SCRAM allows you to prove you know the plaintext password without actually transmitting it.

If you steal the hash from the database, yes. I don't know how stealing the hash over-the-wire is equivalent to having the password, since it is salted (with a salt generated by the server) and is not reusable.

Re: New Features Coming in PostgreSQL 10

#99

Post by xyzzy_plugh »

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.
Post reply on HN