New Features Coming in PostgreSQL 10 • Hacker News Board

Re: New Features Coming in PostgreSQL 10

#71

Post by chillydawg » Sat, Apr 08, 2017, 6:30 PM UTC

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?

get two or three dedicated servers, stick a 2 disk mirrored raid in each one, run one primary and have barman manage your snapshots+streaming replication. hook in some cronjobs to monitor age of stuff and failure of stuff. that is pretty much it for anything less than, say, 5TB. Beyond that your copy times are too high and making snapshots becomes extremely difficult.

Re: New Features Coming in PostgreSQL 10

#72

Post by fiatjaf » Sat, Apr 08, 2017, 6:43 PM UTC

rhaas wrote: post #53

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.

The work that has been done on transition tables is intended to enable future work on automatically updated materialized views; the idea is that the system will automatically derive a query to update the view based on the deltas between the set of old rows and the set of new rows. That will take more work, though. I do agree it would be valuable. It's possible to set up similar things by writing your own triggers, an…

Thank you.

Re: New Features Coming in PostgreSQL 10

#73

Post by fiatjaf » Sat, Apr 08, 2017, 6:44 PM UTC

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 are no better docs than Postgres docs.

Re: New Features Coming in PostgreSQL 10

#74

Post by knv » Sat, Apr 08, 2017, 6:47 PM UTC

Any recommendations for scaling Postgresql's best practices? Really appreciate it.

Re: New Features Coming in PostgreSQL 10

#75

Post by StreamBright » Sat, Apr 08, 2017, 6:49 PM UTC

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.

You are implying you need an ORM for the simple key-value (jsonb) data access. I don't think you would benefit much.

Re: New Features Coming in PostgreSQL 10

#76

Post by ams6110 » Sat, Apr 08, 2017, 6:52 PM UTC

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

Re: New Features Coming in PostgreSQL 10

#77

Post by arc_of_descent » Sat, Apr 08, 2017, 6:58 PM UTC

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 understand your need for a book. I prefer to read a book when diving into a new tech. That being said, when I started out using PostgreSQL years back, there were only online docs, and I must say they are although lengthy at time, very good.

Also, pgAdmin?

Re: New Features Coming in PostgreSQL 10

#78

Post by scrollaway » Sat, Apr 08, 2017, 7:01 PM UTC

anarazel wrote: post #63

scrollaway wrote: post #62

Earlier quoted context omitted.

Wine is another such project :) But I gotta say it's only working for those projects because they have an extremely high barrier of entry to the code itself in the first place (working on projects like Wine and Postgres is scary, even though you can get started with easy stuff). It also works for them because they have maintainers and core committers used to the workflow, already tooled on the workflow etc. But I won…

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

Re: New Features Coming in PostgreSQL 10

#79

Post by iEchoic » Sat, Apr 08, 2017, 7:03 PM UTC

I'm so excited for table partitioning. I use table inheritance in several places in my current project, but have felt the pain of foreign key constraints not applying to inherited children. Reading about table partitioning, I'm realizing that this is a much better fit for my use case.

Postgres continues to amaze me with the speed at which they introduce the right features into such a heavily-used and production-critical product. Thanks Postgres team!

Re: New Features Coming in PostgreSQL 10

#80

Post by jhgg » Sat, Apr 08, 2017, 7:04 PM UTC

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.