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

71–80 of 136 posts

Re: PostgreSQL 9.6 Released

#71

Post by dhd415 »

olavgg wrote: post #27
jbkkd wrote: post #4

Congratulations to the PostgreSQL Global Development Group on a much-anticipated release. Curious about this: > parallelism can speed up big data queries by as much as 32 times faster Why would it be only 32 times faster? The sky's the limit if there aren't major bottlenecks on the way.

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…

I'm a fan of both PostgreSQL and SQL Server, but I think these numbers are very workload-specific. I've gotten 1GB/s throughput on SQL Server 2012 on spinning disks and CPUs older than the E5620, so I've no doubt that same workload would exceed 2GB/s on your hardware. The apples-to-apples comparison here is between the two versions of PG where the performance improvement is clear. It's harder to do an apples-to-apples comparison between PG and SQL Server because the optimal schema and queries for a particular workload are likely to differ for each of them.

Re: PostgreSQL 9.6 Released

#72

Post by olavgg »

dhd415 wrote: post #71
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…

I'm a fan of both PostgreSQL and SQL Server, but I think these numbers are very workload-specific. I've gotten 1GB/s throughput on SQL Server 2012 on spinning disks and CPUs older than the E5620, so I've no doubt that same workload would exceed 2GB/s on your hardware. The apples-to-apples comparison here is between the two versions of PG where the performance improvement is clear. It's harder to do an apples-to-apple…

With SQL Server I don't get 2000MB/s on the same hardware, more like 600-800MB/s. This is most likely because of LZ4 compression and large block sizes(64k-128k) on ZFS, that results in a lot less IO. Because with SQL Server, IO was the bottleneck.

So yes, it is very workload specific. For random read/write they are probably more similar. But for reading a lot of data that can be read sequentially, PostgreSQL seems to win hands down, because it can get a lot of help from ZFS compression.

I would love to run the same test when SQL Server is available on Linux. But ZFS do also deliver slightly better throughput and slightly more iops on the FreeBSD platform, which I ran this benchmark on. And SQL Server probably demands a 4k block size, which is so small that LZ4 compression has no effect as I've already tried to run SQL Server on ZFS via iSCSI.

Re: PostgreSQL 9.6 Released

#73

Post by scjody »

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?

This has been around for a while but it's a great summary of what Postgres can do: http://rachbelaid.com/postgres-full-text-search-is-good-enou...

In my experience performance is great if you're just doing text search, but if you combine that with other operators in the same SELECT it can be much slower than Elasticsearch since in many of those cases Postgres needs to fall back to a full table scan.

Re: PostgreSQL 9.6 Released

#74

Post by rpedela »

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…

Postgres RDS is solid and it supports PostGIS. If you have multi-AZ enabled, then downtime is typically measured in seconds even when upgrading versions or changing instance sizes. It will update one instance, automatically switch to it, and update the other one. It automatically handles backups, syncing read slaves, etc too. It is awesome in my experience.

Re: PostgreSQL 9.6 Released

#75

Post by aidos »

luhn wrote: post #69
aidos wrote: post #65

Earlier quoted context omitted.

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

Ah ok. That's useful info about the multiAZ setup - I'll have a look into that.

In my case, we now have customers around the world so we don't get the "night time" luxury. Part of the work I'm now completing is to split the system into an accounts db and customer data db. I was thinking to dip my toe in the water by just moving the account db to RDS to see how it goes.

Re: PostgreSQL 9.6 Released

#76

Post by takeda »

pilif wrote: post #39
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?

While it's ok for our purposes, I would wish for a bit better customisability of the text parser and it definitely needs better support for compound words to be perfect. The first issue is with relation to https://www.postgresql.org/docs/9.6/static/textsearch-parser... : The documentation says > At present PostgreSQL provides just one built-in parser, which has been found to be useful for a wide range of applications…

Someone here mentioned ZomboDB[1]. Would that help you?

[1] https://github.com/zombodb/zombodb

Re: PostgreSQL 9.6 Released

#77

Post by pumainmotion »

fatbird wrote: post #21
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?

I used it at 9.4 for a document management system with thousands, not millions, of PDFs that got indexed on upload, and it worked extremely well at that scale--fast, and with all the basic text search features well-covered (tokenization, stemming, etc.). A big win for me was that doing it well in Postgres meant the site could stay a simple Django site rather than adding another service.

Curious to know since you mentioned that it was fast for thousands of PDFs... any rough timing information on some of your queries for that kind of dataset?

Re: PostgreSQL 9.6 Released

#78

Post by merb »

luhn wrote: post #69
aidos wrote: post #65

Earlier quoted context omitted.

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

downtime is more limited by your application. i.e. if there is a failure your connection / database pool just needs to reconnect. btw. we only have had psycopg2 pointing at it yet and that worked without a downtime. however i guess java hikaricp is as fast as that well. only "failures" will have a "small" downtime, however planned downtime is pretty/zero fast.

Re: PostgreSQL 9.6 Released

#79

Post by dhd415 »

olavgg wrote: post #72
dhd415 wrote: post #71

Earlier quoted context omitted.

I'm a fan of both PostgreSQL and SQL Server, but I think these numbers are very workload-specific. I've gotten 1GB/s throughput on SQL Server 2012 on spinning disks and CPUs older than the E5620, so I've no doubt that same workload would exceed 2GB/s on your hardware. The apples-to-apples comparison here is between the two versions of PG where the performance improvement is clear. It's harder to do an apples-to-apple…

With SQL Server I don't get 2000MB/s on the same hardware, more like 600-800MB/s. This is most likely because of LZ4 compression and large block sizes(64k-128k) on ZFS, that results in a lot less IO. Because with SQL Server, IO was the bottleneck. So yes, it is very workload specific. For random read/write they are probably more similar. But for reading a lot of data that can be read sequentially, PostgreSQL seems to…

Hmm, I've never run SQL Server on anything other than NTFS where 64KB was definitely the recommended block size. In either case, it's great to have choices. When the license fee is coming out of my pocket, I'm definitely not choosing SQL Server.

Re: PostgreSQL 9.6 Released

#80

Post by jameslk »

chishaku wrote: post #64
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, CO…

It looked promising when I was evaluating it a few months ago, but it's limited to use with Elasticsearch 1.x, which was not going to work for us.
Post reply on HN