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

181–190 of 258 posts

Re: New in PostgreSQL 10

#181

Post by ahoka »

takeda wrote: post #148
dantiberian wrote: post #59

What is the current best option for Postgres failover? I looked at this at the start of the year, and found lots of options, but all of them seemed to have various drawbacks, and none were natively supported or built-in to Postgres.

I would recommend repmgr[1] over solutions like DRBD suggested. The simple reason is that DRBD replicates on block level and doesn't understand postgres data structures, while repmgr relies on mechanisms provided by Postgres. So if there is a failure, you're far less likely to learn that all your data is gone, because some important block on disk was not replicated and the database is not readable. With repmgr the wo…

Repmgr is relatively easy to setup and supports automatic failover with repmgrd. Resyncing a failed node is also just a few commands. The only limitation I have encountered was that I had to run it behind pgbouncer so the clients can always connect to the primary instance. I think that will change with 10 as it will support client side discovery of the primary node.

Re: New in PostgreSQL 10

#182

Post by anarazel »

blaisio wrote: post #178
hardwaresofton wrote: post #96

Does anyone have any use cases where PostgreSQL falls down/loses to other DB systems? I know sharding/replication has long been a sticking point, but what else is there? Why do people still choose MySQL/MariaDB/Oracle over PostgreSQL at all?

The biggest case almost completely unhandled by PostgreSQL is if you need a clustered index like in MySQL. There are basically two common ways of organizing rows in a database: store everything in a tree according to the primary key, or store everything in a heap, and have a separate tree relating values to pointers into the heap. MySQL stores rows in a tree, and PostgreSQL stores rows in a heap. The strategy used by…

> The biggest case almost completely unhandled by PostgreSQL is if you need a clustered index like in MySQL.

Heh. It's funny how "the biggest thing" is different for a lot of users. I can't count the number of times I heard "if you only had XXX, I'd migrate / be happy / ..." for vastly different features. If you then tell them what other people think the most critical bit is they look at you with some surprise.

That's not to say these people, including you, are wrong, just that it's not always that clear what the highest priority items are.

It doesn't get easier, although it has a lot of advantages too, if there's no coherent direction for the entire project, due to all the different directions various companies and people want to go.

WRT clustered tables: I personally think it's a good feature, but there's other more pressing concerns. Since my employer thinks that as well, I'm for now working on other things...

Re: New in PostgreSQL 10

#183

Post by patates »

irrational wrote: post #154
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 m…

Do you plan to write more about the migration, like a blog post? That would be very interesting to read.

Re: New in PostgreSQL 10

#184

Post by ComodoHacker »

bonesmoses wrote: post #134
pbnjay wrote: post #39

> Cross-column Statistics Holy crap I didn't even know this was feasible much less in development! Can't wait to test this out, as significant amount of my data sets have these kinds of relationships. Parallel query will also be great for certain queries I do regularly. Been looking forward to this! Thank you Thank you to all the devs that had a part of this!

Cross-column statistics are one of my favorites. The way Postgres 10 handles it is really a _huge_ advancement that addresses a painful edge case that has been around for ages. https://blog.2ndquadrant.com/pg-phriday-crazy-correlated-col...

To be honest, skewed data problem can be addressed with partitioning in many cases.

Cross-column statistics is a great feature nevertheless.

Re: New in PostgreSQL 10

#185

Post by noncoml »

davedx wrote: post #174
noncoml wrote: post #40

Earlier quoted context omitted.

Designing for the future is a guaranteed project failure. If one is starting a new project, hence contemplating what DB to use, starting with an ACID db is a safe bet in most cases(unless of course they are already starting with a huge amount of data). By the time the outgrow the ACID database they will have a better idea of what exactly they need and more importantly they will have the resources to make the switch.…

> Designing for the future is a guaranteed project failure. Yes, I understand this. I would not want to get bogged down on infrastructure when trying to deliver early versions of a product. At the same time, I think database choice is one of the more important tech stack decisions you should make early on. Cassandra also sounds like a good bet for the type of data I'm interested in storing (ML model inputs, so lots o…

> ML model inputs, so lots of key -> numerical value data

How big are the numerical value data? AFAIK Cassandra does like large amount of data per value. https://docs.datastax.com/en/cql/3.3/cql/cql_reference/refLi...

Also what do you expect the total amount of data to be?

BTW, I just recommended to start with PostgreSQL because it just works and will have your back no matter what ways you like to use retrieve you data.

However if you are absolutely sure you don’t require secondary indexes, joins, etc, and just need a KV database, Cassandra will work fine too.

Re: New in PostgreSQL 10

#186

Post by unixhero »

patates wrote: post #183
irrational wrote: post #154

Earlier quoted context omitted.

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 m…

Do you plan to write more about the migration, like a blog post? That would be very interesting to read.

This needs to become a thing. Where people produce writeups on Oracle To Postgres, and how much better it is; under something like #RunsMuchBetterWithPostgres ...

It is strange that there aren't more writeups on Postgresql migrations.

Yandex had a good one. Posted here a long while ago: https://news.ycombinator.com/item?id=12489055

Re: New in PostgreSQL 10

#187

Post by jeltz »

dhd415 wrote: post #153
Boxxed wrote: post #150

Earlier quoted context omitted.

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.

PostgreSQL can do all of the same query optimizations. The main difference is that for primary key scans PostgreSQL will have an additional layer of indirection meaning more work needs to be done when scanning on primary key and potentially more disk seeks.

On the flip side PostgreSQL's approach is good when you query secondary indexes since these can point directly to the heap rather than to a primary key, removing the cost of long primary keys and allowing for scanning the heap in physical order after some kinds of secondary key lookups. It is also cheaper to sequentially scan heap compared to sequentially scanning a clustered index.

There are also some difference s on the write side too, but the gist of it is that both models have their own strengths and weaknesses.

Re: New in PostgreSQL 10

#188

Post by user5994461 »

dantiberian wrote: post #59

What is the current best option for Postgres failover? I looked at this at the start of the year, and found lots of options, but all of them seemed to have various drawbacks, and none were natively supported or built-in to Postgres.

I think a reasonable option is to use pgbouncer. I wouldn't comment about it because I have long abandonned databases with no failover in favor of solution that works, but people who tried hard enough seem to get pgbouncer to a working point.

postgre fans will hate, but if you really need something with automated failover, you might have to take a look at better databases. Either Postgre/Oracle in the paid SQL world, or elasticsearch/cassandra/riak in the free NoSQL world.

Re: New in PostgreSQL 10

#189

Post by sharninder »

unixhero wrote: post #186
patates wrote: post #183

Earlier quoted context omitted.

Do you plan to write more about the migration, like a blog post? That would be very interesting to read.

This needs to become a thing. Where people produce writeups on Oracle To Postgres, and how much better it is; under something like #RunsMuchBetterWithPostgres ... It is strange that there aren't more writeups on Postgresql migrations. Yandex had a good one. Posted here a long while ago: https://news.ycombinator.com/item?id=12489055

Another one here:

https://www.youtube.com/watch?v=qyc5ohv0RLY

Re: New in PostgreSQL 10

#190

Post by thorin »

patates wrote: post #183
irrational wrote: post #154

Earlier quoted context omitted.

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 m…

Do you plan to write more about the migration, like a blog post? That would be very interesting to read.

I'd be very interested in this after years of working with Oracle (and SQLServer), I know a little Postgres and would like to learn more.
Post reply on HN