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

21–30 of 138 posts

Re: New Features Coming in PostgreSQL 10

#21

Post by mark_l_watson »

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 to view PostgreSQL as a "Swiss Army knife" and having native RDF support would reinforce that.

Re: New Features Coming in PostgreSQL 10

#22

Post by snuxoll »

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 why MySQL uses clustered indexes).

Re: New Features Coming in PostgreSQL 10

#23

Post by ajross »

JoachimSchipper wrote: post #9
avar wrote: post #6

This bit about ICU support v.s. glibc: > [...] Furthermore, at least on Red Hat, glibc regularly whacks > around the behavior of OS-native collations in minor releases, > which effectively corrupts PostgreSQL's indexes, since the index > order might no longer match the (revised) collation order. To > me, changing the behavior of a widely-used system call in a > maintenance release seems about as friendly as locking a…

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 exactly, not just rely on whatever the C library happens to provide.

I mean, not only are PostgreSQL locale-sorted-indexes not portable across glibc releases. They aren't portable across any other system change either. No moving between distros or doing distro upgrades, etc... Those are all misfeatures probably worth fixing.

Re: New Features Coming in PostgreSQL 10

#24

Post by hartator »

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?

Re: New Features Coming in PostgreSQL 10

#25

Post by anarazel »

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…

> 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

I don't think that's entirely accurate - the issue is more that indexes contain pointers to the heap position (simplified) of a tuple, rather than being indirect and pointing to the primary key, which then is resolved by another index (be that clustered / primary or not).

Updates already don't have to update indexes iff none of the indexed columns change (HOT - Heap-Only-Tuples). The proposed change (WARM - write amplification reduction method), allows to avoid updating indexes on non-changing columns, even if other indexes change.

https://www.postgresql.org/message-id/CABOikdMNy6yowA+wTGK9R...

> In all reality though, Uber wanted a key-value store and not an RDBMS

Agreed on that.

Re: New Features Coming in PostgreSQL 10

#26

Post by anarazel »

mark_l_watson wrote: post #21

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…

As one of the people regularly working on postgres, I'm honestly a bit doubtful that it's realistic to add SPARQL frontend. Doing that well is a considerable amount of work, and there's relatively little overlap in experience between the communities. I suspect that focusing on other areas will have a bigger ROI.

But that's just my personal opinion, and other contributors and companies might very well disagree.

Re: New Features Coming in PostgreSQL 10

#27

Post by anarazel »

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…

> I mean, not only are PostgreSQL locale-sorted-indexes not portable across glibc releases. They aren't portable across any other system change either. No moving between distros or doing distro upgrades, etc... Those are all misfeatures probably worth fixing.

Hence ICU support, which allows to address these (because collations are properly versioned).

Re: New Features Coming in PostgreSQL 10

#29

Post by stemcc »

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?

You can easily have schema-less with Postgres's jsonb data type.

Re: New Features Coming in PostgreSQL 10

#30

Post by oever »

mark_l_watson wrote: post #21

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…

Can you say which RDF data stores these are? Are they generic or special purpose?
Post reply on HN