Live data from Hacker News

New Features Coming in PostgreSQL 10

rhaas.blogspot.com

81–90 of 138 posts

Re: New Features Coming in PostgreSQL 10

#81
post #79

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-criti…

Unfortunately, foreign keys won't be supported right away.

Read about the new feature and its limitations here: https://www.postgresql.org/docs/devel/static/ddl-partitionin...

Re: New Features Coming in PostgreSQL 10

#82
post #22
post #15

I did read the article, but I can't find any mention of addressing the "Write amplification" issue as described by Uber when they moved away from postgres. https://eng.uber.com/mysql-migration/ I had heard talk on Software Engineering Daily that this new major revision was supposed to address that. Is this issue resolved by the new "Logical replication" feature? It doesn't seem directly related, but it seems like may…

Write amplification is a result of PostgreSQL's decision to not used clustered indexes, there's not much that can be done to avoid it without a massive redesign of the storage engine - though there are patches out there to reduce the penalty in some cases. In all reality though, Uber wanted a key-value store and not an RDBMS, MySQL was a better choice for this since InnoDB isn't much more than a fast K/V store (hence…

Not arguing with your assessment of Uber's requirements; but in general, why do you view InnoDB as not much more than a K/V store? And why do you equate clustered indexes with K/V stores?

InnoDB is a complex piece of software, supporting transactions, row-level locking, MVCC, schemas, secondary indexes, crash recovery, hot copy/backup, complex caching and buffering, many tunables, and extensive metrics visibility. Just because it's more appropriate for Uber's rather unusual EAV-like use-case, this doesn't mean InnoDB is a glorified K/V store.

Re: clustered indexes, it's a storage engine architecture choice with well-known trade-offs, both positive and negative. SQL Server also uses clustered indexes and is widely respected among database experts.

Regarding the topic overall, there are use-cases where Postgres is the best choice, and there are use-cases where it isn't. That doesn't inherently mean that other databases are uniformly worse. People like to trash MySQL, sometimes for completely valid reasons, but other times for FUD. But fwiw, several of the major features in Postgres 10 have already been supported in MySQL/InnoDB for a long time, in some cases for over a decade. Of course, that goes both ways; there are awesome major features that Postgres has had for a decade that MySQL still lacks.

Re: New Features Coming in PostgreSQL 10

#83
post #11

Dumb question: does declarative partitioning pave the way for native sharding in Postgres? I'm not super super familiar, but it seems like along with some other features coming in Postgres 10, like parallel queries and logical replication, that this is eventually the goal.

I hope that it will have that effect. We need a few other features first: partitionwise join, partitionwise aggregate, asynchronous query, and ideally hash partitioning.

Doesn't this basically replicate all of the work done by Citus?

Re: New Features Coming in PostgreSQL 10

#84
post #81
post #79

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-criti…

Unfortunately, foreign keys won't be supported right away. Read about the new feature and its limitations here: https://www.postgresql.org/docs/devel/static/ddl-partitionin...

Thanks, I hadn't read this. That's too bad, hopefully we'll see that in the future (if it's technically possible at all?). That'd be a huge feature for me.

Re: New Features Coming in PostgreSQL 10

#85
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?

IMHO triggers are almost always best avoided. There are some exceptions but most of the time you want changes to be explicit not happening "by magic" behind the scenes as a side-effect of something else.

Re: New Features Coming in PostgreSQL 10

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

Some work started in this direction. I didn't follow closely the whole thread but I don't think that got commited in PG10.

https://www.postgresql.org/message-id/flat/20170119213859.GA...

More info in the EDB roadmap: https://wiki.postgresql.org/wiki/EnterpriseDB_database_serve...

Postgres has been amazing in shipping the foundation required to deliver complex feature.. Logical Replication is an example of it, all the piece commited in the last 6y allowed to make this patch achievable.

Re: New Features Coming in PostgreSQL 10

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

With 2ndquadrant working on Postgres-XL (http://www.postgres-xl.org/), I think that you can be confident that you will see a lot of the features being proposed to core postgres. It will just take some times to build the building block necessary like: global index, distributed sequence, repartition ...

I quite confident that the postgresql from 5y in future will be quite different in term of storage / server topology support. I won't be surprise pg_bouncer capacity to finally make its way to core when we have a coordinator.

Postgres has steady progression (even if not fast enough for some people) but they are moving without compromising robustness of their product for the users.

Re: New Features Coming in PostgreSQL 10

#90
post #53
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…

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