Live data from Hacker News

New Features Coming in PostgreSQL 10

rhaas.blogspot.com

61–70 of 138 posts

Re: New Features Coming in PostgreSQL 10

#61

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 committed right away, or it will be queued up for the next scheduled "commitfest" [2], when patches are reviewed and finally committed to mainline. (I don't know how the commitfest interacts with git exactly; the commitfest database doesn't even link to git, only to email discussions.)

From the outside it seems a bit antiquated, but it's apparently been working well for them. The Postgres team is a pretty conservative bunch; they only switched from CVS to git in late 2010, for example.

They also really care about code quality, getting the design right early, and covering all possible edge cases. As a result, Postgres solid, clean, has unusually few legacy oddities, and almost never any subtle, suprising breaking changes. 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.

[1] https://wiki.postgresql.org/wiki/Submitting_a_Patch

[2] https://commitfest.postgresql.org

Re: New Features Coming in PostgreSQL 10

#62

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…

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 wonder how much productivity would be gained by using a github-like flow maybe enhanced a bit.

Re: New Features Coming in PostgreSQL 10

#63

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…

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 offline would prevent me from doing so

Then there's political issues: there's absolutely no guarantee github will be around in a few years. Postgres is old, and will hopefully live long into three future. When it started there was no sourceforge, and now there's again no sf. I very regularly look back into old development discussions...

Re: New Features Coming in PostgreSQL 10

#64

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'm not familiar with any books, but the docs really are excellent and have various sections for beginners and getting to know the system.

A good way in is to look at external tools like barman which manage dumps+streaming replication along with point-in-time restoration automatically for you rather than manually invoking all the stuff directly.

Mostly, postgres just works.

Re: New Features Coming in PostgreSQL 10

#65
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…

About redirection of write queries to the master, from 10 on, you will be able to specify all members of the cluster in the connection string and demand to connect to the master (like "postgresql://host1:5432,host2:5432/somedb?target_session_attrs=read-write"); libpq will do this automatically for you then, see the parameters "host" (now plural) and "target_session_attr" in section 33.1.2. here: https://www.postgresql.org/docs/devel/static/libpq-connect.h...

About raft-based leader-election, I believe the current recommendation is to look at patroni ( https://github.com/zalando/patroni), which has been built for docker and is now being integrated with Kubernetes; however, I don't think there is an inherent limitation that it couldn't be run on bare-metal.

Re: New Features Coming in PostgreSQL 10

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

Yes but here's the problem. Consider common scenarios like:

Master goes down. Slave takes over. Master comes back. Slave goes down 10 minutes later. Repeat.

This is common in e.g. multi data center replication and is often due to transient network failures. Netflix has a great open source tool called chaos monkey that can induce lots of random failure scenarios like this or much worse. Don't get me started on transient partial failures due to latency and packet loss spikes.

The manual nature of pg replication setup makes me really nervous here. What happens when it finds itself in a state where manual intervention is needed? You are now down.

This is tolerable for big companies with dedicated SREs and DBAs and enough of them that it's easy to always have someone on call, but it's a nightmare for smaller ventures. Even for larger ventures this adds a lot of cost overhead.

Like I said elsewhere this was really the true killer feature of the more successful NoSQL document store type databases. Everything else was largely hype.

We switched recently to RethinkDB for this reason. We miss the richness of SQL (to the point that we still use PG too for warehousing and analytics) but in return we got incredible robustness across three data centers. Of course our app does not need rich queries or strong consistency 99% of the time so YMMV. For some jobs ACID and complex queries on live data are not optional.

Re: New Features Coming in PostgreSQL 10

#67

I could use a count of the number of file I/Os that each query takes, in order to optimize my queries further...

That's been there for a while: EXPLAIN (ANALYZE, BUFFERS) yourquery; If you enable track_io_timing (has some overhead on platforms with slow timestamps, e.g. older VMware), you even get timing. If you want that aggregated, rather than for an individual query, you should look into pg_stat_statements.

The BUFFERS count is more for row count info as it operates on large chunks of data, instead of index optimization that needs to count how many times index structures are accessed. Counting IOs directly would be more useful for tuning indexes.

Re: New Features Coming in PostgreSQL 10

#68

Earlier quoted context omitted.

That's been there for a while: EXPLAIN (ANALYZE, BUFFERS) yourquery; If you enable track_io_timing (has some overhead on platforms with slow timestamps, e.g. older VMware), you even get timing. If you want that aggregated, rather than for an individual query, you should look into pg_stat_statements.

The BUFFERS count is more for row count info as it operates on large chunks of data, instead of index optimization that needs to count how many times index structures are accessed. Counting IOs directly would be more useful for tuning indexes.

Huh? It shows you the number of io operations.

Re: New Features Coming in PostgreSQL 10

#69
post #37

I know that several RDF data stores use PostgreSQL as a backend data store. With new features like better XML support, as well as older features for storing hierarchical data, I am wishing for a plugin or extension for handling RDF with limited (not RDFS or OWL) SPARQL query support. I almost always have PostgreSQL available, and for RDF applications it would be very nice to not have to run a separate service. I tend…

Which RDF data store uses Postgres as DB backend? And can one import WikiData? (does it scale) (I would rather avoid these old school RDF special case stores from SematicWeb days 10 years ago.)

https://github.com/cayleygraph/cayley is currently on the frontpage of HN, and it does use PGSQL as backend.

Re: New Features Coming in PostgreSQL 10

#70
post #65
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…

About redirection of write queries to the master, from 10 on, you will be able to specify all members of the cluster in the connection string and demand to connect to the master (like "postgresql://host1:5432,host2:5432/somedb?target_session_attrs=read-write"); libpq will do this automatically for you then, see the parameters "host" (now plural) and "target_session_attr" in section 33.1.2. here: https://www.postgresq…

Yes on the first one, and a big nope on the second for now. I passionately loathe Rube Goldberg machine deployments and am the kind of engineer who constantly asks "do we really need that?". I love exterminating complexity. But maybe that will change when we get to millions of concurrent users and tens of millions of devices and actually need Kubernetes to scale.

Raft is not complex. I doubt leader elect would be terribly hard to implement.

Post reply on HN