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

111–120 of 136 posts

Re: PostgreSQL 9.6 Released

#111

Post by combatentropy »

ngrilly wrote: post #110
combatentropy wrote: post #105

Earlier quoted context omitted.

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?

42 MB for the table, which has columns for the address, title, plain-text body, and computed text vectors for 43,000 pages (web pages and office documents of average length). Then another 100 MB for the GIN index on the text-vector column.

Re: PostgreSQL 9.6 Released

#112

Post by anarazel »

pgaddict wrote: post #109
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.

> There's still only one major PostgreSQL release per year.

Well, due to the delayed 9.5 release (January 7th), there have been two this year ;)

Re: PostgreSQL 9.6 Released

#113

Post by pgaddict »

anarazel wrote: post #112
pgaddict wrote: post #109

Earlier quoted context omitted.

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.

> There's still only one major PostgreSQL release per year. Well, due to the delayed 9.5 release (January 7th), there have been two this year ;)

Well, that really depends on where exactly you place start of a year ;-)

Chinese New Year was February 8, 2016. Orthodox New Year was January 14, 2016. So it's 2:1 for me.

Re: PostgreSQL 9.6 Released

#114

Post by ngrilly »

combatentropy wrote: post #111
ngrilly wrote: post #110

Earlier quoted context omitted.

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

42 MB for the table, which has columns for the address, title, plain-text body, and computed text vectors for 43,000 pages (web pages and office documents of average length). Then another 100 MB for the GIN index on the text-vector column.

That's only 977 bytes per page (42 MB / 43,000 pages). Are you sure about the numbers? Maybe the plain-text body is stored in a TOAST table?

Anyway, it looks like the whole dataset can fit in RAM, which explains the excellent performance, even with relevance ranking.

Re: PostgreSQL 9.6 Released

#115

Post by nnutter »

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…

Are there any good options to support logic (and/or) and facets/fields with Postgres? We started using ES basically just for the "free" query language. (Obviously we would want something that is safe from sql injection.)

Re: PostgreSQL 9.6 Released

#116

Post by combatentropy »

ngrilly wrote: post #114
combatentropy wrote: post #111

Earlier quoted context omitted.

42 MB for the table, which has columns for the address, title, plain-text body, and computed text vectors for 43,000 pages (web pages and office documents of average length). Then another 100 MB for the GIN index on the text-vector column.

That's only 977 bytes per page (42 MB / 43,000 pages). Are you sure about the numbers? Maybe the plain-text body is stored in a TOAST table? Anyway, it looks like the whole dataset can fit in RAM, which explains the excellent performance, even with relevance ranking.

Whoops! Yes, there is a corresponding TOAST table that I had to track down.

  Table: 341 MB (main table + TOAST table)
  Index: 100 MB

Re: PostgreSQL 9.6 Released

#117

Post by anewhnaccount »

nnutter wrote: post #115
brightball wrote: post #23

Earlier quoted context omitted.

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…

Are there any good options to support logic (and/or) and facets/fields with Postgres? We started using ES basically just for the "free" query language. (Obviously we would want something that is safe from sql injection.)

SQL supports logic. Either escape manually, use the templating in your driver or use an ORM.

Re: PostgreSQL 9.6 Released

#118

Post by batmansmk »

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?

We have been using Postgres Full Text Search for about 3 years now in production. The app is an analytics dashboard, over a set of structured and unstructured data. We have about 20M documents, with hierarchies, dimensions, but also free text elements. It does work extremely well, and having the possibility to group by as one would do in SQL is a god send for tabular or graph based data. Performance are really good, in particular due to the parallel aggregations.

We tested recently to load our index to an Elasticsearch index for one particular use case (a weighted sum of the 20M rows based on a FTS critera) where postgres was underperforming in our opinion. On the same hardware, using all available RAM and CPUs, ES took 6s and PG took 0.7s.

So far, on the 30+ queries of our dashboard tool, we have yet to find a use case that Postgres didn't handle better than Lucene based solutions.

Re: PostgreSQL 9.6 Released

#119

Post by cwp »

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…

Yes, I've noticed this too. The AWS documentation and the console both say that the changes may take a long time to apply, but in fact the database is up for most of that time. I've done several big upgrades that had only a few seconds of downtime.

Re: PostgreSQL 9.6 Released

#120

Post by MustardTiger »

evanelias wrote: post #87
MustardTiger wrote: post #61

Earlier quoted context omitted.

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

You may be confusing InnoDB with MyISAM (which is prone to corruption, especially upon crashes) or with running MySQL without a strict SQL mode (which causes bad things like silent truncation of overflowing values). InnoDB is, and always has been, a very reliable and durable storage engine with solid performance characteristics.

No, I am referring to innodb, which has a number of known reliability problems which are "wontfix".
Post reply on HN