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

101–110 of 136 posts

Re: PostgreSQL 9.6 Released

#101

Post by malisper »

snuxoll wrote: post #99
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.

I think there is already a patch for Postgres 10 that runs the vacuum on insert only tables. While not completely solving the problem, that will be helpful.

Re: PostgreSQL 9.6 Released

#102

Post by [deleted] »

snuxoll wrote: post #99
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.

[deleted]

Re: PostgreSQL 9.6 Released

#103

Post by anarazel »

snuxoll wrote: post #99
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.

> I don't see ever going away from using vacuum to maintain the visibility map

I don't think that's that unlikely to change. There's two major avenues: Write it during hot-pruning (which is done on page accesses), and perform a "lower impact" vacuum on insert-only tables more regularly

> but hopefully the changes in 9.6 will make it a non-issue on large tables.

You mean the freeze map? That doesn't really change the picture for regular vacuums, it changes how bad anti-wraparound vacuums are. The impact of the table vacuum itself is most of the time not that bad these days (due to the visibility map), what's annoying is usually the corresponding index scans. They have to scan the whole index, which is quite expensive.

Re: PostgreSQL 9.6 Released

#104

Post by pgaddict »

asah wrote: post #15
dhd415 wrote: post #10

Earlier quoted context omitted.

PostgreSQL, for better or worse, doesn't have pluggable storage engines. There's some discussion on their dev mailing list about the possibility of adding that capability in PG10, though: http://postgresql.nabble.com/Pluggable-storage-td5916322.htm... Some earlier (2013) discussion on the same topic: https://wiki.postgresql.org/wiki/2013UnconfPluggableStorage

tl;dr: Foreign Data Wrappers (FDW) provide 99% of the same functionality, but with even more flexibility incl smart query optimizer support.

That is far too optimistic, IMHO.

FDW are a great way to access external data sources, but it lacks proper support for visibility and transactions, and so on. Also, the FDW API follows the "tuple at a time" execution model, which prevents a lot of optimizations in the upper part of the stact (vectorized execution etc.).

There are several products using FDWs to change storage, but I'd call it a misuse of a feature designed for very different purpose.

IMHO it's hardly a way forward without significant changes/improvements (which may happen, I don't know).

Re: PostgreSQL 9.6 Released

#105

Post by combatentropy »

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 had used PostgreSQL for a decade, including full-text search, but just within apps that were already storing their data in Postgres.

The time came to replace our website search (tens of thousands of pages), and we decided to try rolling our own. Someone suggested ElasticSearch, and as I read through it, it seemed to do less than PostgreSQL. I still had the hard problems of (1) spidering the site and (2) converting all the file formats (.doc, .xls, .pdf, etc.).

I ended up just putting wget on a daily cron job to spider the site. Then I ran the saved files through a hodgepodge of scripts to extract the plain text and put it into PostgreSQL.

Once it's there, it's far easier to do the rest. Postgres has its own functions to search for matches, rank the matches, give you snippets, and even highlight the search words in the snippets. It's amazing.

Searches run in a split second. Well, at first, when I was testing, they often took a few seconds. But the weird thing is that after go-live it ran faster. My best guess is that so many users caused Postgres to cache more and more of itself into RAM. The whole server is still using less than 1 GB though, and it's running Apache and Postgres for the website and all its apps.

Re: PostgreSQL 9.6 Released

#106

Post by pgaddict »

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.

Thanks, it's nice to see the patch is likely beneficial for other people!

Re: PostgreSQL 9.6 Released

#107

Post by ngrilly »

kuschku wrote: post #95
ngrilly wrote: post #93

Earlier quoted context omitted.

> 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.

Now I understand why you didn't suffer from the slow ranking issue. In my test case, text is longer and triggers the TOAST management code.

Re: PostgreSQL 9.6 Released

#108

Post by kuschku »

ngrilly wrote: post #107
kuschku wrote: post #95

Earlier quoted context omitted.

> 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.

Now I understand why you didn't suffer from the slow ranking issue. In my test case, text is longer and triggers the TOAST management code.

Yeah, if your vectors are in TOAST, you really have a huge issue with ranking. There’s no simple way to get around that, except with customized solutions like Lucene/Solr/ES

Re: PostgreSQL 9.6 Released

#109

Post by pgaddict »

Roboprog wrote: post #45

Is it just selection bias from posted links on HN, or has the PostgreSQL team been doing many (feature) releases lately? Sounds good!

There's still only one major PostgreSQL release per year. There were a few posts about cool stuff built on top of PostgreSQL, a few posts about progress of the 9.6 development (e.g. when the parallel query got committed) etc.

Re: PostgreSQL 9.6 Released

#110

Post by ngrilly »

combatentropy wrote: post #105
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 had used PostgreSQL for a decade, including full-text search, but just within apps that were already storing their data in Postgres. The time came to replace our website search (tens of thousands of pages), and we decided to try rolling our own. Someone suggested ElasticSearch, and as I read through it, it seemed to do less than PostgreSQL. I still had the hard problems of (1) spidering the site and (2) converting…

What is the on-disk size of the table storing the plain text?
Post reply on HN