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

51–60 of 138 posts

Re: New Features Coming in PostgreSQL 10

#51

Post by rhaas »

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"?

Re: New Features Coming in PostgreSQL 10

#52

Post by phamilton »

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?

Effectively yes, but if it's that simple why not make this a built in functionality? Other DBs have it.

Re: New Features Coming in PostgreSQL 10

#53

Post by rhaas »

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, and having transition tables available in PL/pgsql will make it easier, but it's not necessarily easy to figure it all out by hand for a complex view involving joins and aggregates.

Re: New Features Coming in PostgreSQL 10

#55

Post by mozumder »

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?

Triggers on materialized views are really error-prone and tedious. It's cache invalidation, which is hard.

Re: New Features Coming in PostgreSQL 10

#56

Post by anarazel »

mozumder wrote: post #54

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.

Re: New Features Coming in PostgreSQL 10

#57

Post by pavanvd »

anarazel wrote: post #18
djcj88 wrote: 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…

There's a patch reducing write amplifications (when caused by indexes), by a significant degree. Unfortunately it didn't quite get ready in time for the feature freeze of 10 - as it affects the on-disk format, we considered the risk to be too high.

As the author of the patch I don't quite agree to it. But it's true that the patch did not receive adequate review even though most of the on-disk changes were known and coded at least 7 months before the feature freeze. So it's hard to tell which part of the patch wasn't ready. But there is always next cycle. So lets work towards getting it ready for v11.

Re: New Features Coming in PostgreSQL 10

#58

Post by snuxoll »

frik wrote: post #42
snuxoll wrote: post #22

Earlier quoted context omitted.

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…

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

Re: New Features Coming in PostgreSQL 10

#59

Post by nickpeterson »

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.

Re: New Features Coming in PostgreSQL 10

#60

Post by api »

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 of things like MongoDB and RethinkDB. Yes people with really huge deployments might want something more tunable, but that's only like 1% of the market.

Of course those NoSQL databases also offer eventual and other weaker but more scalable consistency modes, but like highly tuned manual deployment these too are features for the 1% of the market that actually needs that kind of scale.

A fire and forget cluster-able fully consistent SQL database would be nirvana for most of the market.

Post reply on HN