Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL when it is not your job

reinout.vanrees.org

Post reply on HN
Print view View original

31–40 of 44 posts

Re: PostgreSQL when it is not your job

#31

Post by jeffdavis »

seunosewa wrote: post #16

Some of the suggestions make PostgreSQL seem less mature than InnoDB, still: "[don't put] sessions in the DB", "[don't put] constantly-updated counters in the database", and "[don't put] task queues in the database." My forum gets almost a million page views daily; we store all our data in a(n) InnoDB database, including sessions, task queues, and constantly updated counters. They work just fine and are not even bott…

> Some of the suggestions make PostgreSQL seem less mature than InnoDB, still: "[don't put] sessions in the DB", "[don't put] constantly-updated counters in the database", and "[don't put] task queues in the database."

It's not that you can't do them in postgresql, or even that it will perform slowly. But when you have the chance to offload work from the database server that doesn't need to be there, it's often a good idea to do so.

I wouldn't have worded it the way the author did, either --it's certainly situation-dependent whether those are a good idea. But I wouldn't take it as a "postgres isn't up to the task".

Re: PostgreSQL when it is not your job

#32

Post by spudlyo »

hennk wrote: post #10
rosser wrote: post #4

As someone whose job it is to keep peoples' PostgreSQL instances happy, this list is fairly comprehensive, and much of it is good. His advice about configuration directives towards the top of the article, however, is terrible. In particular, work_mem: the article suggests setting it to 2-3x the size of the largest temp file you see. The thing you need to be mindful of with work_mem is that the limit is per sort . I h…

Just out of interest, but how large was the "moderately sized" IN() clause? I am asking, as we are looking at postgresql as an alternative to MySQL, and we have some queries currently with up to 5000 values inside IN()

This is not super fast in MySQL either, compared to the alternative of creating a temporary table with your 5000 values in it and doing a join on that.

Re: PostgreSQL when it is not your job

#33

Post by gbog »

seunosewa wrote: post #16

Some of the suggestions make PostgreSQL seem less mature than InnoDB, still: "[don't put] sessions in the DB", "[don't put] constantly-updated counters in the database", and "[don't put] task queues in the database." My forum gets almost a million page views daily; we store all our data in a(n) InnoDB database, including sessions, task queues, and constantly updated counters. They work just fine and are not even bott…

I read a similar argument against git recently: too many things to do manually, but the counter argument is that at this level you need full control on what happens and there should be no magic.

So If you have counters table you need to analyse it more often, by hand, because you don't want your rdbms to guess on your behalf if a table is a write only or update only. MySQL do a lot of guessing, like, say, PHP.

Re: PostgreSQL when it is not your job

#34

Post by jeltz »

jeltz wrote: post #25
einhverfr wrote: post #21

Part of the reason why PostgreSQL has so any knobs is that these things are not always thing that cookie cutter approaches work with. Although if you do need to worry about these, it probably is your job and you probably are at least going to learn it. I am not convinced about his list of "stupid db tricks you should not do." For example: 1) Sessions in the db are sometimes a good thing and sometimes not. They do hav…

Actually since PostgreSQL 9.1 LIKE '%this%' is indexable with the pg_trgm contrib module. Since it is based on the trigrams in your search query it obviously has its caveats, generally the longer the query the more effective the index lookup is. I would imagine for example '%th%' requires a full table/index scan since it contains zero trigrams. http://www.postgresql.org/docs/9.1/static/pgtrgm.html#AEN137...

Found there is also a third party module, made by the same guys who made LIKE able to use pg_trgm indexes, called wildspeed. http://www.sai.msu.su/~megera/wiki/wildspeed

It may be faster than using pg_trgm but I have no idea if it is still maintained.

Re: PostgreSQL when it is not your job

#35

Post by einhverfr »

jeltz wrote: post #30
einhverfr wrote: post #26

Earlier quoted context omitted.

But pg_trgm isn't really the same either, is it? I have looked at pg_trgm primarily for handling misspellings and suggested alternatives. Also it wasn't clear to me how "%this%" would be differentiated from "his thin snake."

Yes, pg_trgm was built for that but someone figured out how LIKE could be hacked to use the trigram indexes (gist_trgm_ops, gin_trgm_ops). So if you have a * _trgm_ops index on the column normal LIKE and ILIKE queries may use that index. I assume your example would be a false index hit which then is necessary to verify against the real value. The same would apply to make sure 'This' is not a hit when doing a case sen…

The extensibility is really cool. And yeah, that looks like a very useful thing. Thanks for the pointer.

Re: PostgreSQL when it is not your job

#36

Post by ashray »

antihero wrote: post #27

One question on avoiding giant IN clauses with Django? Say I have a class called Fridge, and a classes called Vegetables and Condiments. Both of these have ManyToMany relationships between themselves and Fridge. So something like: class Fridge(models.Model): condiments = models.ManyToManyField(Condiments) vegetables = models.ManyToManyField(Vegetables) And here we have a QuerySet that represents our white fridges: qs…

Might be a good idea to take that to stackoverflow. My suggestion though would be to use that little sauce that we can't achieve programmatically yet. Human intelligence. How often do you think you're going to need this data ? How often does it change ?

Cache it based on that. Whatever way you do it, queries like this are expensive. If you need it often enough, throw it into memory and invalidate as necessary. More often than not, your cache invalidation will use less lines than what you wrote up there.

Also, don't be afraid to drop into raw sql if you really want to do something a certain way. The example you posted above clearly looks like the ORM working against you.

Re: PostgreSQL when it is not your job

#37

Post by sjwright »

spudlyo wrote: post #32
hennk wrote: post #10

Earlier quoted context omitted.

Just out of interest, but how large was the "moderately sized" IN() clause? I am asking, as we are looking at postgresql as an alternative to MySQL, and we have some queries currently with up to 5000 values inside IN()

This is not super fast in MySQL either, compared to the alternative of creating a temporary table with your 5000 values in it and doing a join on that.

That's a problem I routinely come across, and it's frustrating, because there's no reason a self-contained IN(SELECT) should ever be slower than two-stepping it with a temporary table, or worse, two queries on the client side. But it often is.

Re: PostgreSQL when it is not your job

#38

Post by gaius »

pjungwir wrote: post #20
rosser wrote: post #4

As someone whose job it is to keep peoples' PostgreSQL instances happy, this list is fairly comprehensive, and much of it is good. His advice about configuration directives towards the top of the article, however, is terrible. In particular, work_mem: the article suggests setting it to 2-3x the size of the largest temp file you see. The thing you need to be mindful of with work_mem is that the limit is per sort . I h…

I've always understood that IN clauses are hard on query optimizers and should be re-written as correlated subqueries with EXISTS/NOT EXISTS, often for giant speedups. But I've met lots of developers who have never seen this pattern before and get scared by it. Of course if you can also re-write it as a join, that's even easier.

You don't have to assume it, just look at the query plans! It's unlikely to be as simple as "IN bad, JOIN good".

Edit: Ah, now I see that this article should really be titled "Working around Django's ORM with Postgres".

Re: PostgreSQL when it is not your job

#39

Post by reinout »

masklinn wrote: post #7
justin_vanw wrote: post #5

This advice is just copypasta. It's also pretty dangerous and wrong. Example: "shared-buffers. below 2GB: set it to 20% of full memory, below 32GB: 25% of your full memory." -- Don't do this. Set it to around 20% of your memory if you have a small machine, such as a vps or desktop. If you have lots of memory, set it between 2GB and 4GB. Anything above 8GB exceeds what it is designed to handle and can cause major perf…

> This advice is just copypasta. > It's also pretty dangerous and wrong. It's livenotes from a presentation, I expect Reinout van Rees noted the parts that interested him as the actual presentation has what you think "right": > If you have lots of memory, set it between 2GB and 4GB. Anything above 8GB exceeds what it is designed to handle From the slides: > Above 32GB (lucky you!), set to 8GB. > This is a great way t…

Hi, I'm the Reinout that made the notes. Yes, it was a live summary and his slides went by very fast. I'm surprised how much I got written down. So I had to leave stuff out, for instance the >32GB comment (which I did as it seemed applicable only to few people).

Masklinn, thanks for doing this bit of checking! For many people (including myself :-) the summary will be enough, but checking the actual presentation is a good idea if you run into problems.

Re: PostgreSQL when it is not your job

#40

Post by antihero »

ashray wrote: post #36
antihero wrote: post #27

One question on avoiding giant IN clauses with Django? Say I have a class called Fridge, and a classes called Vegetables and Condiments. Both of these have ManyToMany relationships between themselves and Fridge. So something like: class Fridge(models.Model): condiments = models.ManyToManyField(Condiments) vegetables = models.ManyToManyField(Vegetables) And here we have a QuerySet that represents our white fridges: qs…

Might be a good idea to take that to stackoverflow. My suggestion though would be to use that little sauce that we can't achieve programmatically yet. Human intelligence. How often do you think you're going to need this data ? How often does it change ? Cache it based on that. Whatever way you do it, queries like this are expensive. If you need it often enough, throw it into memory and invalidate as necessary. More o…

Ok, here's my question on StackOverflow: http://stackoverflow.com/questions/10930169/how-do-i-get-dja...
Post reply on HN