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

81–90 of 136 posts

Re: PostgreSQL 9.6 Released

#81

Post by fatbird »

pumainmotion wrote: post #77
fatbird wrote: post #21

Earlier quoted context omitted.

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?

I'm really reaching here to recall, but the short version is that actual searches never took more than a second. All I really cared about was how noticeable a delay to expect, and it was never more than that.

On a bulk import of 1,000+, it took a couple minutes to ingest them. This was all on a $20/month VPS.

Re: PostgreSQL 9.6 Released

#82

Post by kuschku »

ngrilly wrote: post #62
pilif wrote: post #58

Earlier quoted context omitted.

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

I do some searching with pgsql with tiny datasets and ts_rank, on a 10GB dataset of 11 million rows (mostly chat data), and get response times for ranking over all of it around 10-100ms on a cheap OVH 5€ VPS.

Query: https://paste.kde.org/pcxyg0fay | Explain: https://explain.depesz.com/s/jN3V (101ms)

Sometimes queries end up even a lot faster, for example the same as above, but searching for "c plus plus", runs in this plan + runtime: https://explain.depesz.com/s/NPOc (11ms)

Re: PostgreSQL 9.6 Released

#83

Post by jelder »

pedrocr wrote: post #9
noxin wrote: post #8

Earlier quoted context omitted.

They likely benchmarked it on a 32 core system. Like a dual Opteron board. If the task was single-threaded before a 32-fold improvement is reasonable.

It's very difficult to get a 32x speedup from 32 cores as there are always parts that are inherently serial, so it's more likely they tested it on a 64 core machine or something like that.

Yes, this is thanks to Amdahl's Law.

https://en.wikipedia.org/wiki/Amdahl%27s_law

Re: PostgreSQL 9.6 Released

#84

Post by ngrilly »

kuschku wrote: post #82
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…

I do some searching with pgsql with tiny datasets and ts_rank, on a 10GB dataset of 11 million rows (mostly chat data), and get response times for ranking over all of it around 10-100ms on a cheap OVH 5€ VPS. Query: https://paste.kde.org/pcxyg0fay | Explain: https://explain.depesz.com/s/jN3V (101ms) Sometimes queries end up even a lot faster, for example the same as above, but searching for "c plus plus", runs in thi…

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.

Re: PostgreSQL 9.6 Released

#85

Post by greggyb »

olavgg wrote: post #70
greggyb wrote: post #60

Earlier quoted context omitted.

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.

Thanks, this and your other response are very useful!

Re: PostgreSQL 9.6 Released

#86

Post by evanelias »

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.

FWIW, these solutions rarely bypass MySQL entirely or at all. Although there are ways to access InnoDB without making SQL queries (Memcached API; Handler Socket), the MySQL server is still involved. It just skips the normal protocol, auth, SQL parsing, etc.

Even then, there aren't a lot of published cases of people using these alternative access methods at scale yet. AFAIK, all of the large kv use-cases you've mentioned still go through traditional SQL queries. Despite the overhead of SQL parsing, it provides more control and visibility. The ecosystem around alternative access methods isn't nearly as mature.

Re: PostgreSQL 9.6 Released

#87

Post by evanelias »

MustardTiger wrote: post #61
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.

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.

Re: PostgreSQL 9.6 Released

#88

Post by Jweb_Guru »

greggyb wrote: post #5
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.

No one has tested a query that got more than 32x faster, so they don't want to promise something they can't prove.

There's also a limited amount of memory-level parallelism available... with 4-DIMM sockets you might need an 8-socket machine to get a 32x improvement on large (memory-bound) sequential scans, which I'd guess you can get on top-end Power machines.

(You can probably get more memory level parallelism with random access, but your overall bandwidth will likely be lower... fully exploiting memory bandwidth is complicated and difficult to do for real applications).

Re: PostgreSQL 9.6 Released

#89

Post by brightball »

ngrilly wrote: post #62
pilif wrote: post #58

Earlier quoted context omitted.

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

Re: PostgreSQL 9.6 Released

#90

Post by kuschku »

ngrilly wrote: post #84
kuschku wrote: post #82

Earlier quoted context omitted.

I do some searching with pgsql with tiny datasets and ts_rank, on a 10GB dataset of 11 million rows (mostly chat data), and get response times for ranking over all of it around 10-100ms on a cheap OVH 5€ VPS. Query: https://paste.kde.org/pcxyg0fay | Explain: https://explain.depesz.com/s/jN3V (101ms) Sometimes queries end up even a lot faster, for example the same as above, but searching for "c plus plus", runs in thi…

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.

Post reply on HN