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

91–100 of 136 posts

Re: PostgreSQL 9.6 Released

#91

Post by ngrilly »

brightball wrote: post #89
ngrilly wrote: post #62

Earlier quoted context omitted.

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

Ordering can get expensive no matter what just base on how many things you're actually sorting. Ideally, if you can find a way to limit the size of the data set before the ranking sort you'll see a big improvement.

Currently, the only way to make ranking tolerable is to limit the size of the data set before the ranking sort. This is what I did in my tests.

But it looks like it could be possible to massively improve ranking performance by storing all necessary information directly in the GIN index, as proposed here:

https://wiki.postgresql.org/images/2/25/Full-text_search_in_...

Re: PostgreSQL 9.6 Released

#92

Post by takeda »

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…

Compared to others my experience with RDS is bad, although I used it last year ago perhaps things improved.

One major issues is that you are restricted to what you can do with it, not all options are available. You can only use extensions that they provide. (this I'm a bit fuzzy about) but changing disk size made service unavailable for ~30 minutes (proportional to new disk size). You weren't able to configure replication, the replication only happens to the backup node. You weren't even able to set up replication across regions.

The replication is kind of a bummer, because if you ever would like to move your data (perhaps to a vm or outside of aws) you would need to have an outage. Also if I remember there was no way to do major version update in place.

There was also another incident (it was caused by bug so hopefully it was fixed and won't happen to anyone else). We had cluster set up with a backup. One day out of nowhere the service stopped working and was unavailable for 1.5 hours. That was quite big issue because we used it for monitoring (zabbix), so any outage makes us blind to issues. Turned out that due to bug their backup routine made a mistake and started doing backup on the master server (normally it supposed to do on slave).

Re: PostgreSQL 9.6 Released

#93

Post by ngrilly »

kuschku wrote: post #90
ngrilly wrote: post #84

Earlier quoted context omitted.

Thanks a lot for sharing this! Last time I tried, it was on a machine with a spinning disk... It looks like I should try again with a SSD, which are a lot better with regard to random access. Your search term is "Quassel". What happens if you search for a term that matches a lot of rows? This is the case where ts_rank is very expensive. I'd be curious to look at the explain of such a low-selectivity query.

> What happens if you search for a term that matches a lot of rows? This is the case where ts_rank is very expensive. I'd be curious to look at the explain of such a low-selectivity query. That’s actually quite unproblematic, if you have the tsvector as its own column (not just as index). It’s far more problematic to actually load that data from disk.

> That’s actually quite unproblematic, if you have the tsvector as its own column (not just as index).

Yes, it works, but it is slow because the tsvector is usually big enough to be stored in a TOAST table, and this produces a lot of random access reads.

This is why there is a project of storing additional information in the GIN (term positions) in order for the index to contain all necessary information for the ranking:

https://wiki.postgresql.org/images/2/25/Full-text_search_in_...

Re: PostgreSQL 9.6 Released

#94

Post by ngrilly »

scjody wrote: post #73
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.

If you create indexes for all the columns used as filters, which is somehow what Elasticsearch does, then PostgreSQL is able to combine them (generating a bitmap for each used index and ANDing them) and you should get decent performance, don't you?

Re: PostgreSQL 9.6 Released

#95

Post by kuschku »

ngrilly wrote: post #93
kuschku wrote: post #90

Earlier quoted context omitted.

> What happens if you search for a term that matches a lot of rows? This is the case where ts_rank is very expensive. I'd be curious to look at the explain of such a low-selectivity query. That’s actually quite unproblematic, if you have the tsvector as its own column (not just as index). It’s far more problematic to actually load that data from disk.

> That’s actually quite unproblematic, if you have the tsvector as its own column (not just as index). Yes, it works, but it is slow because the tsvector is usually big enough to be stored in a TOAST table, and this produces a lot of random access reads. This is why there is a project of storing additional information in the GIN (term positions) in order for the index to contain all necessary information for the rank…

> usually big enough to be stored in a TOAST table

Ah, luckily, in my case, that can’t happen – each row’s message contains one IRC message, so at most 512 bytes. That also automatically ensures we’ll never run into TOAST issues.

Re: PostgreSQL 9.6 Released

#96

Post by snowwolf »

mslate wrote: post #56
snowwolf wrote: post #32

Please can the Postgres team put some major focus on completing logical replication [1]. It's the missing piece to making upgrading across major versions painless and quick on large databases so that we can take advantage of all these nice new features. We're on a Heroku's hosted Postgres service so can't install the pglogical extension. 1. http://blog.2ndquadrant.com/why-logical-replication/

I don't think you would be able to take advantage of logical replication on Heroku Postgres regardless--they don't allow you to replicate to your own instances, only other Heroku-hosted instances. This makes migrating off Heroku for Postgres a PITA and requiring down-time.

True, that would be an extra bonus if Heroku started allowing replication to non Heroku instances, but as long as they support logical replication to a Heroku Follower instance then you can upgrade to new major versions with near zero downtime - set up logical follower running latest postgres version and then promote to master once it has caught up. Currently you can't have a follower that is a different version to master - meaning an upgrade requires either a full backup and restore to new version resulting in significant downtime if you have a large database or using the pg_upgrade utility which is generally not recommended as it is not guaranteed to work.

Re: PostgreSQL 9.6 Released

#97

Post by malisper »

> Index-only scans for partial indexes

This one is huge for my company. Almost every single query of ours could use an index-only scan, but the planner would never choose to perform one because of the weirdness around partial indexes. We expecting a several x speedup once we upgrade to 9.6. All the need to improve now is a way to keep the visibility map up to date without relying on vacuums.

Re: PostgreSQL 9.6 Released

#98

Post by nugator »

brightball wrote: post #23
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?

Having used all 3, Postgres search is my go to for most use case simply because I don't have to deal with managing deltas to an outside system and keeping things in sync. The search features are powerful and fast and PG's ability to combine multiple indexes in search results make it trivially easy to include a bit of full text search in a query right next to geographic distance filters or other conditions. You can al…

What about when having different PG database instances that has data you want to join on? Would you still use PG as an aggregated read-only copy of the databases or would you use for example ES?

Re: PostgreSQL 9.6 Released

#99

Post by snuxoll »

malisper wrote: post #97

> Index-only scans for partial indexes This one is huge for my company. Almost every single query of ours could use an index-only scan, but the planner would never choose to perform one because of the weirdness around partial indexes. We expecting a several x speedup once we upgrade to 9.6. All the need to improve now is a way to keep the visibility map up to date without relying on vacuums.

I don't see ever going away from using vacuum to maintain the visibility map, but hopefully the changes in 9.6 will make it a non-issue on large tables.

Re: PostgreSQL 9.6 Released

#100

Post by aidos »

rpedela wrote: post #74
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…

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.

I've just read about this and there are a lot of people saying they had significant downtime during upgrades ([1] one such story, but there were quite a few on stackoverflow etc).

I'm going to run some tests myself to see how well it works on my existing data (only 30GB at the moment, but it was only 20GB a month ago and is growing fairly rapidly).

[1] https://inopinatus.org/2016/02/16/notes-from-a-postgresql-rd...

Post reply on HN