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

41–50 of 138 posts

Re: New Features Coming in PostgreSQL 10

#41

Post by JohnDotAwesome »

hartator wrote: post #24

I am considering more and more a move back from MongoDB to PostgreSQL. I will be missing being schema less so much though. Migrations - particularly Rails migrations - left a bad taste in my mouth. Anyone did the move recently and what are their feelings?

Yeah, all the things that need flexible schema can go in a jsonb column. While querying on JSON has gotten less painful, it's still a bit of a chore. But I've found that I rarely need to do that. Or if I do, I just denormalize a bit and put those fields in a regular ol' column.

We did the move maybe 4-5 years ago? At least in the JavaScript world, this makes your life so so much better.

Of course, you still have to handle migrations, but at least you have transactions :)

Re: New Features Coming in PostgreSQL 10

#42

Post by frik »

snuxoll wrote: post #22
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…

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 interface is the same.

Re: New Features Coming in PostgreSQL 10

#43

Post by pgaddict »

ajross wrote: post #23
JoachimSchipper wrote: post #9

Earlier quoted context omitted.

Florian Weimer's reply is also interesting: "Why do you think that? I don't see this documented anywhere, and I doubt it is something many readers of the C standard, the man page, or the glibc manual would expect. The manual suggests to store the strxfrm output and use it for sorting. I expect that some applications put it into on-disk database indexes as a result. This will lead to subtle breakage on glibc updates.…

Which manual suggests storing the output of strxfrm? The glibc man page doesn't seem to. I don't know that this is resolvable. The documented behavior of strxfrm() is just about its output properties. Improvements to the transformation algorithm would be expected to be made, if it's improvable. If a database needs this to be static over time it needs to pick a particular transformation algorithm and specify it exactl…

This somewhat mixes two issues, really.

Firstly, there are issues with glibc versioning, when the collation rules change even without proper version bump. I don't have a link to details at hand, but I've heard a number of complaints about it over the years from various PostgreSQL hackers, including Peter Geoghegan who did a lot of sort optimizations in 9.5 & 9.6. I'm sure there's something deep in the PostgreSQL mailing list archives. You'll have to dig deep, because we learned the lesson years ago.

Regarding strxfrm(), PostgreSQL certainly does not store the results anywhere - it's only used during the actual sort, e.g. during CREATE INDEX or to help with ORDER BY in a query, and then thrown away. So either I'm forgetting something, or Robert mixed this up in the blog post.

There's however an issue with strxfrm() disagreeing with strcoll(), which is a major problem, of course, because we build and index using strxfrm() and then fail to find the values in it using strcoll(). For more details, including links to pgsql-bugs and RH bugtracker, see https://wiki.postgresql.org/wiki/Abbreviated_keys_glibc_issu...

ICU seems not to have these issues, because it has better and more carefully maintained versioning scheme. Perhaps we'll still get broken indexes after an ICU upgrade, but at least we'll know about it.

Re: New Features Coming in PostgreSQL 10

#44

Post by frik »

Normal_gaussian wrote: post #4

Extended Statistics! I was following the replication changes, but have just discovered the extended statistics and am more excited about them. The directory renaming at the bottom of the post is interesting - I wonder if many other projects have to do things like this?

It would be great if some Linux distros clear up the directory mess. There are directories in there with names that no ones remembers what they originally meant in the UNIX of 1970s, or what ever. For compatibility they could be just hard/soft-links to a more sane directory structure.

Well the same goes for Windows. With Win95, WinNT 3.5, WinXP, WinVista they restructured the internal directory tree and renamed things. It was okay with WinXP, just the long user folder was trouble some because of 260 chars MAX_PATH limit. But with Vista and 64-bit support the fucked up and it's now a big mess in Win7+ (syswow64, system32, registry, winsxs, dotNet folders, ... such a big mess and sometimes also waste of HDD space by duplicates of files).

Re: New Features Coming in PostgreSQL 10

#45

Post by petepete »

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?

Re: New Features Coming in PostgreSQL 10

#46

Post by pgaddict »

Normal_gaussian wrote: post #4

Extended Statistics! I was following the replication changes, but have just discovered the extended statistics and am more excited about them. The directory renaming at the bottom of the post is interesting - I wonder if many other projects have to do things like this?

To be fair, the extended statistics available in PG10 are about the most primitive ones possible. If the PG10 stats help with your queries, great, but otherwise it's mainly laying infrastructure for the more advanced stuff - histograms, MCV lists, expression statistics, and ultimately join statistics (which is about the main source of estimation issues). Oleg also mentioned it might be useful for JSON statistics, which would be cool.

Hopefully those bits will get in faster - I first submitted the patch in 2014, just before pgconf.eu, I think. OTOH I can't really complain, because I'm shitty developer so the initial versions were far from committable. The quality requirements for PostgreSQL patches are damn high these days.

BTW if you have examples of real-world queries hurt by poor estimates, report them to pgsql-performance mailing list. It's an important piece of information about what cases to look at first. Obviously, we already have already collected various queries, but having more is good.

Re: New Features Coming in PostgreSQL 10

#47

Post by petergeoghegan »

ajross wrote: post #23
JoachimSchipper wrote: post #9

Earlier quoted context omitted.

Florian Weimer's reply is also interesting: "Why do you think that? I don't see this documented anywhere, and I doubt it is something many readers of the C standard, the man page, or the glibc manual would expect. The manual suggests to store the strxfrm output and use it for sorting. I expect that some applications put it into on-disk database indexes as a result. This will lead to subtle breakage on glibc updates.…

Which manual suggests storing the output of strxfrm? The glibc man page doesn't seem to. I don't know that this is resolvable. The documented behavior of strxfrm() is just about its output properties. Improvements to the transformation algorithm would be expected to be made, if it's improvable. If a database needs this to be static over time it needs to pick a particular transformation algorithm and specify it exactl…

PostgreSQL never stored strxfrm() output in indexes, but did at one time use it to build indexes (that is, strxfrm() was used to build values when sorting). However, it would be nice to be able to store strxfrm() output on disk, in indexes, which as you say leaves the implementation sensitive to changes in the transformation algorithm, and not just the underlying behavior defined by a collation.

Fortunately, it is possible to do this safely with ICU, which explicitly decouples implementation issues from behavioral issues. Maybe this will happen at some point in the future. For now, abbreviated keys are only used for sorting to build an index, and not in any subsequent index scan.

If you want much faster text sorting back, using "abbreviated keys", you can use ICU collations in Postgres 10. With the C standard library, strxfrm() was disabled when it was found to be buggy within glibc [1].

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

Re: New Features Coming in PostgreSQL 10

#48

Post by anarazel »

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…

Yes. But it's not a settled matter. The storage engines in mysql didn't work out that well, each of them duplicating a lot of work, having significantly different behavior, ...

Re: New Features Coming in PostgreSQL 10

#49

Post by smac8 »

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

Re: New Features Coming in PostgreSQL 10

#50

Post by petergeoghegan »

ajross wrote: post #23
JoachimSchipper wrote: post #9

Earlier quoted context omitted.

Florian Weimer's reply is also interesting: "Why do you think that? I don't see this documented anywhere, and I doubt it is something many readers of the C standard, the man page, or the glibc manual would expect. The manual suggests to store the strxfrm output and use it for sorting. I expect that some applications put it into on-disk database indexes as a result. This will lead to subtle breakage on glibc updates.…

Which manual suggests storing the output of strxfrm? The glibc man page doesn't seem to. I don't know that this is resolvable. The documented behavior of strxfrm() is just about its output properties. Improvements to the transformation algorithm would be expected to be made, if it's improvable. If a database needs this to be static over time it needs to pick a particular transformation algorithm and specify it exactl…

BTW, the new amcheck tool, available in Postgres 10, lets you validate that an index is consistent with its designated sort order (B-Tree operator class). At least you now have some way of detecting the kind of inconsistency you describe.

I wrote amcheck, and maintain a version targeting earlier releases of PostgreSQL on Github: https://github.com/petergeoghegan/amcheck

Post reply on HN