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

101–110 of 138 posts

Re: New Features Coming in PostgreSQL 10

#101

Post by cwp »

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?

I did just that move when I realized that I was doing a lot of work so impose schemas on my "schemaless" data and another bunch of work to implement joins in my application.

I found the best way to do migrations is with vanilla SQL. I wrote a little tool to read migrations from SQL files in a directory, send them to the server and keep track of which ones have already been applied. Simple and easy.

The big benefit of migration is that your app code doesn't have to deal with every possible schema that you've ever used; it can rely on the data being uniform.

I'm very happy with the switch; wouldn't go back to Mongo for anything.

Re: New Features Coming in PostgreSQL 10

#103

Post by foota »

pgaddict wrote: post #90
rhaas wrote: post #53

Earlier quoted context omitted.

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.

How do you know when the change will affect the matview? I don't know the syntax for creating it off the top of my head, but imagine a materialized view limited to the top ten records of a table.

Re: New Features Coming in PostgreSQL 10

#105

Post by int_19h »

lazzlazzlazz wrote: post #86

How is Postgres so consistently the best open-source DB project from features to documentation? It's unreal.

Not just a DB project, either. I'd say it's one of the best executed (in a very broad sense of the word) open source projects around, in general.

From end user perspective, they have stable, quality releases with a predictable cycle and subsequent maintenance releases. They have great documentation - one of the best in the industry, much less open source. Things generally work as you'd expect them to, and when not (e.g. for historical or implementation reasons), you have clear and convincing explanations. And so on.

I haven't seen their developer side, but based on other people's feedback, it's also good - high quality bar for code, stringent review process etc. More importantly, they seem to be making the right (= leading to more stable quality releases with great features) technical decisions consistently, which to me is a hallmark of a very well run team.

I also can't remember any publicized "drama" around Postgres, either on the inside (dev disagreements etc), or between the team and the users. It looks like everyone's happy, or at least happy enough.

I don't know what the magic sauce is here, but it feels like many other open source projects could learn a lot from the Postgres team and community.

Re: New Features Coming in PostgreSQL 10

#106

Post by jordanthoms »

Will DDL replication for the logical replication be landing in 10 or later?

We have some use cases where logical replication would be very helpful, but keeping the schema in sync manually seems like a pain - will there be a documented workaround if DDL replication doesn't make it in?

Re: New Features Coming in PostgreSQL 10

#107

Post by akurilin »

lazzlazzlazz wrote: post #86

How is Postgres so consistently the best open-source DB project from features to documentation? It's unreal.

I second this, the Postgres contributors are consistently setting the bar for the rest of OSS projects out there, it's consistently been my favorite part of the stack for a long time now.

Don't forget about their phenomenal #postgresql channel on Freenode. The folks working on Postgres have been gracious enough to patiently answer my not always fully baked questions for the past 5 years on there, they're a bottomless treasure trove of best practices and pragmatic advice.

Re: New Features Coming in PostgreSQL 10

#108

Post by dhd415 »

evanelias wrote: post #82
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…

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

Clustered indexes aren't an all-or-nothing choice. SQL Server allows heap tables without any clustered indexes, tables with a clustered index on the primary key, and tables clustered on a secondary index with the primary key as a non-clustered index. It is really nice to have all three of those options available.

Re: New Features Coming in PostgreSQL 10

#109

Post by MBCook »

xyzzy_plugh wrote: post #98
jhgg wrote: post #80

Earlier quoted context omitted.

The point is that you only send the hash to the database to connect. If you steal the hash, you can connect to the database using said hash, not needing the plaintext. The password might as well be the hash in this case. Hence the equivalency. Using that scheme, all you prove is that you know the hash of the password. SCRAM allows you to prove you know the plaintext password without actually transmitting it.

If you steal the hash from the database, yes. I don't know how stealing the hash over-the-wire is equivalent to having the password, since it is salted (with a salt generated by the server) and is not reusable.

Because the next time you connect to the server you provide the same hash. The person doesn't know your plane text, but they can get into the server just fine.

Re: New Features Coming in PostgreSQL 10

#110

Post by jeltz »

okket wrote: post #96
petepete wrote: post #45

Earlier quoted context omitted.

How do you mean? Couldn't you use a trigger to update the view?

A trigger on what? Every update, insert, delete, etc.? On every table in the view? Even if that is possible, it may be a major performance killer. This has to be done internally, I think.

I am not sure it necessarily would be that bad. After all foreign keys are implemented with triggers and they are usually fast enough. You just need to write trigger functions which are fast enough.
Post reply on HN