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

New in PostgreSQL 10

wiki.postgresql.org

Post reply on HN
Print view View original

151–160 of 258 posts

Re: New in PostgreSQL 10

#151

Post by grzm »

Boxxed wrote: post #150
dhd415 wrote: post #144

Earlier quoted context omitted.

It's disappointing that pgsql has no index-organized tables aka clustered indexes. They are quite advantageous for a variety of workloads.

You can cluster a table by an index: https://www.postgresql.org/docs/9.6/static/sql-cluster.html Or am I not understanding what you're asking for?

Indexes in PostgreSQL require lookups into the table to access the data values in the rows. Index organized tables have indexes which include the data values in the index itself, removing the need for the lookup in the table itself.

Here's some more detail:

https://news.ycombinator.com/item?id=10451095

Re: New in PostgreSQL 10

#152

Post by adventured »

linux2647 wrote: post #141
adventured wrote: post #113

Earlier quoted context omitted.

> Why do people still choose MySQL/MariaDB/Oracle over PostgreSQL at all? Oracle: because the enterprise is its own unique beast. Companies frequently stick to what they know and have invested into, so long as it still works. Enterprise customers buy/adopt software in a very different way than everyone else. MySQL: if you've got a history with the product and it works well for what you're doing, there is absolutely n…

Aside from those reasons, are there technical reasons for Oracle over Postgres? My coworkers and I want to move our data warehouse over to Postgres and my boss has been warming up to the idea. (I know switching databases with that much data can be complex and troublesome, but those concerns are outside the scope of this question)

Oracle is still a very good option for data warehousing. When well tuned it's extremely performant and reliable for that purpose. If I were operating an EDW today, I'd choose Oracle over Postgres 10 times out of 10 when it comes to very large scale operations. You'll still see plenty of credible arguments around that Oracle can be optimized to be faster than every major alternative. And you'll still see arguments around replication that are legitimate.

Obviously I don't know what your cost or performance considerations are. If your organization can save a meaningful amount of money and operational headache by switching to Postgres, that can be a big deal for any business. If cost isn't much of a consideration in your operation (that is, what sticking with Oracle is costing you), stick to Oracle for data warehousing over Postgres - at least for now imo.

With Oracle it's the same story it has been for a long time (and probably will remain), it's expensive and a pain in the ass to get up and running the way you want it. Postgres has everything to gain vs Oracle from here forward, it seems very likely to bite into some of Oracle's well guarded high value territory in the coming years.

Re: New in PostgreSQL 10

#153

Post by dhd415 »

Boxxed wrote: post #150
dhd415 wrote: post #144

Earlier quoted context omitted.

It's disappointing that pgsql has no index-organized tables aka clustered indexes. They are quite advantageous for a variety of workloads.

You can cluster a table by an index: https://www.postgresql.org/docs/9.6/static/sql-cluster.html Or am I not understanding what you're asking for?

That kind of clustering is a point-in-time operation. Subsequent inserts and updates aren't stored in clustered order. When the ordering is guaranteed, the query planner can be more aggressive in optimizing certain kinds of queries. A real clustered index could also be used as an implicit covering index as grzm mentions.

Re: New in PostgreSQL 10

#154

Post by irrational »

alceta wrote: post #19

If anyone even remotely involved with the maintenance and development of pg reads this thread - Thank you! - for all your efforts in building and improving a first class product that keeps me amazed at the strides it takes with each major. release.

Truly. We've recently moved from Oracle (after using it for 15 years) to Postgresql. It's like a breath of fresh air. The documentation for Postgres is unbelievably superior to Oracle. So far its performance is equal to or better than Oracle. We had to go through and rewrite thousands of queries, but the sql syntax of Postgres was always simpler and more logical than the equivalent in Oracle (I think Oracle has too much baggage from being around too long). All in all I'm so impressed by Postgres. I'm sure there are features in Oracle that Postgres doesn't have that keep people on Oracle, but I would imagine that the vast majority of Oracle installs could be moved to Postgres.

Re: New in PostgreSQL 10

#155

Post by 3131s »

mst wrote: post #123
GoodbyeEarl wrote: post #119

Hey, HN! Would you recommend me a practical introduction to data modelling with Postres?

Learn normalisation from wikipedia, then read "The Art of SQL" to get a feeling for the shape of the weirdnesses, then read the postgresql documentation from end to end. (disclaimer: worked for me, may not for anybody else)

I intend to read the PostgreSQL docs end to end at some point, but last time I checked the full PDF comes out to something like 3600 pages! I am astounded by the quality of everything about PostgreSQL, the community included.

Re: New in PostgreSQL 10

#157

Post by odammit »

Does anyone know if the partitioning can enforce a uniqueness constraint across child tables?

I couldn’t tell from the write up if this is one of the things addressed with Native Partitioning

Re: New in PostgreSQL 10

#158

Post by nine_k »

rb808 wrote: post #51

Shouldn't that be PostgreSQL X? Seriously good work. I'm not sure why my firm still buys Oracle licenses.

Well, this Postgres release introduces automatic partitioning; in Oracle, it has been available for like 20 years.

Also, some shops are heavily invested in PL/SQL code.

The price of migration may far outweigh the license savings.

Re: New in PostgreSQL 10

#159

Post by tomc1985 »

odammit wrote: post #157

Does anyone know if the partitioning can enforce a uniqueness constraint across child tables? I couldn’t tell from the write up if this is one of the things addressed with Native Partitioning

Seconded, this is a pretty important feature for some use cases

Re: New in PostgreSQL 10

#160

Post by striking »

manigandham wrote: post #145
lambda wrote: post #136

Earlier quoted context omitted.

Those aren't all of the instructions for apt-get. The full instructions are on https://docs.microsoft.com/en-us/sql/linux/quickstart-instal... and appear to consist of: curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)" sudo apt-get update sudo apt-get install -y mssql-server sudo /opt/mssql/bin/mss…

Yes, you have to add the repo, the same as if you wanted the latest postgres bits on any distro. You also have to edit configs for both and you can also choose to install extra tools for both. I'm surprised at how many people seem to have taken issue with a simple comment. Postgres is easy to install... and now so is MSSQL compared to the complex slow GUI installer they had before. Maybe it'll help the original comme…

Debian and derivatives have it in repos by default. Please don't make claims like those.
Post reply on HN