Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL 10 Beta 1 Released

postgresql.org

Post reply on HN
Print view View original

111–120 of 172 posts

Re: PostgreSQL 10 Beta 1 Released

#111

Post by cryptonector »

cygned wrote: post #84
saurik wrote: post #82

Earlier quoted context omitted.

I was expecting that presentation to be stuff you were missing, but the presentation is saying that essentially every feature they describe is implemented in PostgreSQL. Of the SQL:2011 ones, only one wasn't (temporal tables), and even the SQL:2016 features had partial support (with the summary slide at the end of other features they didn't do in detail having stuff that looks familiar in a PostgreSQL context). Do yo…

I really would like to use Temporal Tables (slides 137 and following) to say like 'give me the record of last week'. I can of course do that already manually, but it is tedious and I hope it will be faster if implemented directly.

Besides temporal tables, I want temporal materialized views. I needed such a thing so much so that I implemented such a thing in PlPgSQL: https://github.com/twosigma/postgresql-contrib/blob/master/p...

This lets me materialize a view and then inspect deltas between refreshes, with history, and even update the materialized view from triggers and have those changes recorded in a history table automatically.

Re: PostgreSQL 10 Beta 1 Released

#112

Post by cryptonector »

phonon wrote: post #109
cygned wrote: post #84

Earlier quoted context omitted.

I really would like to use Temporal Tables (slides 137 and following) to say like 'give me the record of last week'. I can of course do that already manually, but it is tedious and I hope it will be faster if implemented directly.

https://github.com/arkhipov/temporal_tables seems like a good option?

Thanks for the link!

Re: PostgreSQL 10 Beta 1 Released

#113

Post by adamzochowski »

X86BSD wrote: post #43
byefruit wrote: post #20

Earlier quoted context omitted.

At my previous company we made heavy use of its lossy compression feature.

You would get compression with Postgres running on ZFS.

Postgresql has compression by default on, for all large text and other large fields that get great benefit of compression. From documentation -" The technique is affectionately known as TOAST (or "the best thing since sliced bread"). "- https://www.postgresql.org/docs/8.0/static/storage-toast.htm...

Re: PostgreSQL 10 Beta 1 Released

#114

Post by infogulch »

byefruit wrote: post #20
htsh wrote: post #16

Earlier quoted context omitted.

I'm interested. When you say almost, can you elaborate on any remaining use cases when you'd use Mongo?

At my previous company we made heavy use of its lossy compression feature.

Is that related to the cool "hash compression" technology I hear about? Apparently it can compress an arbitrarily large file into just a few bytes, amazing!

Re: PostgreSQL 10 Beta 1 Released

#115

Post by cryptonector »

crgwbr wrote: post #35

Will having logical replication make doing a DB version upgrade in production easier? We're using Postgres 9.4 on RDS right now, and there doesn't seem to be an upgrade path that doesn't involve some downtime.

The most important (to me) thing about logical replication is that with it I can have additional schema elements in replicas. This is important for some use cases where one uses another group's DB and needs to add schema elements that the other DB's admins do not want to host (e.g., because they might think them risky) or where you don't want to have to be constrained by the other DB's maintenance schedules (e.g., for updating your schema elements).

Re: PostgreSQL 10 Beta 1 Released

#116

Post by manigandham »

ssijak wrote: post #42
fnayr wrote: post #38

Earlier quoted context omitted.

I know it's pretty popular to hate on Mongodb now (even more so than it was to love on Mongodb 4 years ago), but there are still areas where it's better than a relational db. In game development, it's extremely helpful (especially as an "indie") to change the structure on a whim so easily. Also based on the design of the game I'm working on, I believe the document structure captures the structure of the data so much…

But the posters above you said that JSON and JSONB types in Postgres, and functionality around them, eliminated the need to use other databases for document type data. What you are describing can be done with PostgreSQL. One thing that is missing is better client libraries that make use of those data types. Morphia wins for now in that regard.

.NET has great support with Marten:

http://jasperfx.github.io/marten/

Re: PostgreSQL 10 Beta 1 Released

#117

Post by rodgerd »

cygned wrote: post #84
saurik wrote: post #82

Earlier quoted context omitted.

I was expecting that presentation to be stuff you were missing, but the presentation is saying that essentially every feature they describe is implemented in PostgreSQL. Of the SQL:2011 ones, only one wasn't (temporal tables), and even the SQL:2016 features had partial support (with the summary slide at the end of other features they didn't do in detail having stuff that looks familiar in a PostgreSQL context). Do yo…

I really would like to use Temporal Tables (slides 137 and following) to say like 'give me the record of last week'. I can of course do that already manually, but it is tedious and I hope it will be faster if implemented directly.

Time travel existed for a long time, but eventually got culled in v8 (IIRC) because not enough people were using it to justify the code complexity.

Re: PostgreSQL 10 Beta 1 Released

#118

Post by ris »

pilif wrote: post #13

While everybody is going to be rightfully excited about the logical replication, for me personally, CREATE STATISTICS and the new ROW syntax for UPDATE amount to the additions that have the probably biggest effect on me ever since I moved to postgres exclusively when 7.1 was released. Especially CREATE STATISTICS (wonderful explanation here https://www.postgresql.org/docs/10.0/static/multivariate-sta... ) is the one…

As exciting as this is (and it really is) it's yet another thing that explodes the number of possibilities for optimization. It's getting towards the point of unmanageability and I hope there will be a movement towards, if not auto-tuning databases, assistive tools for exploring both this and indexing possibilities.

Re: PostgreSQL 10 Beta 1 Released

#119

Post by derefr »

snuxoll wrote: post #103
anarazel wrote: post #101

Earlier quoted context omitted.

Hm, in that case, what stops you from just using a bytea column?

My major issue with bytea is two fold, the same oid issue that you have with LOB's (we have 10's of billions of pages) so you're forced to less than optimal solutions like table inheritance for large numbers of records, plus it severely bloats the size of the heap file making a VACCUM FULL take a century if needed.

> so you're forced to less than optimal solutions like table inheritance for large numbers of records

Would the "native table partitioning" feature introduced in this release be a solution (or, at least, more optimal) for this?

Alternately, if you want to go off-heap, what about using Foreign Data Wrappers (https://wiki.postgresql.org/wiki/Foreign_data_wrappers#File_...)? These two both sound like they might solve your problem:

• https://github.com/ZhengYang/dc_fdw/wiki

• http://multicorn.org/

Re: PostgreSQL 10 Beta 1 Released

#120

Post by zie »

gigatexal wrote: post #91

unrelated to this particular release, but since we're talking about postgres... how do people find the ability to write stored procedures and functions in languages other than just SQL? I'm coming from a MSSQL shop and curious how writing a query in python for example has benefited anyone if at all -- did you / can you use modules like numpy or pandas in a postgres python procedure?

Python under PG can do anything python can do.. but that said, just because you CAN import numpy/pandas and go to town, doesn't mean you SHOULD. In fact you probably really shouldn't :)

That said, we use python inside of PG, and it's awesome. It's not really any more powerful than PG/SQL(their built-in language) i.e. in terms of what you can do in the DB itself.

Our main app is also python, so we have a continuity of language from front-end to back-end. We do have some DB functions that take advantage of python's flexibilities, but like I said, we try to keep external modules and craziness out of the PG backend if possible. Sometimes there is no help for it, and you do what you must, and it's very handy that you can just go do it.

Post reply on HN