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

PostgreSQL 9.6 Released

postgresql.org

Post reply on HN
Print view View original

61–70 of 136 posts

Re: PostgreSQL 9.6 Released

#61

Post by MustardTiger »

ignoramous wrote: post #7

A tangential question: Everyone speaks about InnoDB and how performant and reliable it is... and multiple firms even use it as a KV-store (Uber/Pinterest/AWS) bypassing MySQL entirely. I have never heard much about storage engines in Postgres, why could this be so? Wikipedia has a (stub) article on InnoDB, but nothing on Postgres' storage engines... just wondering why that is.

>Everyone speaks about InnoDB and how performant and reliable it is

What? Everyone speaks about how unreliable it is and how many major data corruption problems it has.

>I have never heard much about storage engines in Postgres, why could this be so?

Because they didn't take the approach of having multiple storage engines, they just made one that works and is not easily removed from the database.

Re: PostgreSQL 9.6 Released

#62

Post by ngrilly »

pilif wrote: post #58
ngrilly wrote: post #50

Earlier quoted context omitted.

Do you rank full-text search results using something like ts_rank? If yes, do you suffer from slow queries?

We use it, but we don't suffer slow queries in our case.

I'd like to know more about your case, because my own experience is that ordering by ts_rank causes a big slowdown.

PostgreSQL documentation says: "Ranking can be expensive since it requires consulting the tsvector of each matching document, which can be I/O bound and therefore slow. Unfortunately, it is almost impossible to avoid since practical queries often result in large numbers of matches."

Some PostgreSQL developers are working on improving this by using indexes only to compute the ranking, but the related patches are not done yet.

What is the size of your data set (number of rows and size on disk) and the average response time?

Re: PostgreSQL 9.6 Released

#63

Post by rwultsch »

ngrilly wrote: post #41
rwultsch wrote: post #34

Earlier quoted context omitted.

If you don't want a clustered index in InnoDB you can define the primary key as an auto incrementing uint.

Yes, you can, but it doesn't change the fact that you still have a clustered index (an index organized table), which is great for PK lookups, but bad if you do a secondary indexes lookup (because you need to lookup through 2 B-trees instead of 1). There is real, and well-known, tradeoff here.

You missed the point of my post. You are going to have one of the two issues, either looking through two index or indexes including the a large PK. At least with InnoDB you can make the choice. The strategy I suggested gets you the desired outcome of not including a large PK in all secondary indexes.

Re: PostgreSQL 9.6 Released

#64

Post by chishaku »

fabian2k wrote: post #17

Just from reading the documentation, the full text search features on Postgres already look pretty powerful. And it is encouraging that they are actively being worked on. I'm wondering how this compares to a dedicated search engine like Solr or Elasticsearch. Are there huge differences in performance, features or search quality? At which scale does using Postgres for full text search still make sense?

Does anyone have experience with ZomboDB?

"ZomboDB is a Postgres extension that enables efficient full-text searching via the use of indexes backed by Elasticsearch. In order to achieve this, ZomboDB implements Postgres' Access Method API.

In practical terms, a ZomboDB index appears to Postgres as no different than a standard btree index. As such, standard SQL commands are fully supported, including SELECT, BEGIN, COMMIT, ABORT, INSERT, UPDATE, DELETE, COPY, and VACUUM."

https://github.com/zombodb/zombodb

Re: PostgreSQL 9.6 Released

#65

Post by aidos »

mgberlin wrote: post #42

Does anyone know when this will be available on AWS RDS?

On that topic - what's the general feeling about RDS?

I'm running pg on ec2 with a hot standby slave. I need the postgis extension but am not doing anything particularly esoteric. Ideally I'd like to have the certainty of aws handling backups for me.

I was researching moving to RDS today and would love to hear thoughts on whether it's a good general solution or not. What happens about downtime during upgrades or swapping instance sizes?

Re: PostgreSQL 9.6 Released

#67

Post by ngrilly »

rwultsch wrote: post #63
ngrilly wrote: post #41

Earlier quoted context omitted.

Yes, you can, but it doesn't change the fact that you still have a clustered index (an index organized table), which is great for PK lookups, but bad if you do a secondary indexes lookup (because you need to lookup through 2 B-trees instead of 1). There is real, and well-known, tradeoff here.

You missed the point of my post. You are going to have one of the two issues, either looking through two index or indexes including the a large PK. At least with InnoDB you can make the choice. The strategy I suggested gets you the desired outcome of not including a large PK in all secondary indexes.

> The strategy I suggested gets you the desired outcome of not including a large PK in all secondary indexes.

For an application in which most queries need a secondary index lookup, using heap organized tables is more efficient because the database needs to traverse only one B-tree (for the secondary index) that gives the physical position of the row in the heap. When using index organized tables, the database needs to traverse 2 B-trees (the secondary index first, then the primary index). Making the primary key short by using an auto incrementing integer helps, but doesn't remove this overhead.

Re: PostgreSQL 9.6 Released

#69

Post by luhn »

aidos wrote: post #65
mgberlin wrote: post #42

Does anyone know when this will be available on AWS RDS?

On that topic - what's the general feeling about RDS? I'm running pg on ec2 with a hot standby slave. I need the postgis extension but am not doing anything particularly esoteric. Ideally I'd like to have the certainty of aws handling backups for me. I was researching moving to RDS today and would love to hear thoughts on whether it's a good general solution or not. What happens about downtime during upgrades or swap…

> What happens about downtime during upgrades or swapping instance sizes?

This is one of my favorite features of RDS: You can set a maintenance window and have the option to not have changes take effect until that window. So if I want to upgrade Postgres or change the instance size, I set it up and the downtime happens when I'm fast asleep and nobody is using the site.

I also think (but not 100% sure) that if you have Multi-AZ enabled, changes are done by upgrading the slave, failing over, and then upgrading the ex-master, so downtime is limited to the failover period.

Re: PostgreSQL 9.6 Released

#70

Post by olavgg »

greggyb wrote: post #60
olavgg wrote: post #27

Earlier quoted context omitted.

I tested parallel queries on PostgreSQL 9.6 on a few TBs of data, 5 billion rows on an older dual Xeon E5620 server. I also striped 4 Intel S3500 800GB drivers with ZFS and enabled LZ4 compression which has a compressratio of 4x. For a sequential full table scan I could process about 2000MB/s of data(only 125MB/s was read from each SSD), I was limited by CPU power. Anyway, same query took about 25 minutes on PostgreS…

Would you be willing to re-run that with SQL Server 2016? A Dev license is free, and there's been a lot of relational engine optimization since 2012. I'd be curious to see what the latest release can do compared to Postgres' latest. I realize I'm asking a stranger on the internet to do something for free for me. If you don't have time or inclination to do this, no worries, but it seems like you've got a nice setup to…

I've tried SQL Server 2016, no difference.
Post reply on HN