PostgreSQL when it is not your job • Hacker News Board

Re: PostgreSQL when it is not your job

#11

Post by wahnfrieden » Wed, Jun 06, 2012, 7:45 PM UTC

raverbashing wrote: post #8

THIS Thanks Even better if it was a quick guide to all quirks PSQL Dear DBAs, PostgreSql may be great and etc, but if I need to spin a DB for testing/proof of concept, you bet I'm going to use MySQL 20 out of 10 times. "Go RTFM" sorry, I lost count of how many times I had to set up MySQL or PSQL and MySQL is much more intuitive and easy to work with. PSQL is sincerely a waste of time and energy for small things. If I…

Is MySQL faster at handling IN than PSQL? Just because it says it's not well-supported in PSQL doesn't mean it is in MySQL. I don't know either way, but I'd like to.

Re: PostgreSQL when it is not your job

#12

Post by raverbashing » Wed, Jun 06, 2012, 8:07 PM UTC

wahnfrieden wrote: post #11

raverbashing wrote: post #8

THIS Thanks Even better if it was a quick guide to all quirks PSQL Dear DBAs, PostgreSql may be great and etc, but if I need to spin a DB for testing/proof of concept, you bet I'm going to use MySQL 20 out of 10 times. "Go RTFM" sorry, I lost count of how many times I had to set up MySQL or PSQL and MySQL is much more intuitive and easy to work with. PSQL is sincerely a waste of time and energy for small things. If I…

Is MySQL faster at handling IN than PSQL? Just because it says it's not well-supported in PSQL doesn't mean it is in MySQL. I don't know either way, but I'd like to.

I'm not sure how Django constructs the query, but it certainly does differently between MySQL and PostgreSql (and Oracle) since this is in the Django driver

Re: PostgreSQL when it is not your job

#13

Post by rosser » Wed, Jun 06, 2012, 8:07 PM UTC

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()

I don't remember specifically, but I believe it was in the thousands to low tens of thousands of rows. It's also not consistent. I've seen larger IN() clauses that never have a problem, and smaller ones that consistently do. It's been on my very low priority to-do list to put together some demo cases for the mailing lists, because overnight to < 4s just from that little refactor isn't the greatest...

Re: PostgreSQL when it is not your job

#14

Post by xxiao » Wed, Jun 06, 2012, 8:09 PM UTC

for the memory portion, if the stated is true, why not postgresql just use them directly? that's fixed values based on the hardware it runs on.

Re: PostgreSQL when it is not your job

#15

Post by tmoertel » Wed, Jun 06, 2012, 8:25 PM UTC

rosser wrote: post #13

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()

I don't remember specifically, but I believe it was in the thousands to low tens of thousands of rows. It's also not consistent. I've seen larger IN() clauses that never have a problem, and smaller ones that consistently do. It's been on my very low priority to-do list to put together some demo cases for the mailing lists, because overnight to < 4s just from that little refactor isn't the greatest...

Are we talking about IN clauses that contain a correlated subquery or something the optimizer would have a hard time determining was independent of outer context?

Re: PostgreSQL when it is not your job

#16

Post by seunosewa » Wed, Jun 06, 2012, 8:31 PM UTC

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 bottlenecks when the isolation level is REPEATABLE READ.

We use SERIALIZABLE though, as a matter of principle, so we had to implement a more fancy schema, but everything happens in the InnoDB database. We don't have to worry about running VACUUM periodically either. I feel PostgreSQL may be a wee bit overhyped.

Re: PostgreSQL when it is not your job

#17

Post by rosser » Wed, Jun 06, 2012, 8:41 PM UTC

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…

Are you actually suggesting that using asynchronous commit on non-battery-backed disks is safe, while calling the article's advice "dangerous and wrong"? You do understand that asynchronous commit increases your likelihood of data loss, right?

Re: PostgreSQL when it is not your job

#18

Post by bsg75 » Wed, Jun 06, 2012, 8:48 PM UTC

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…

My take is those suggestions are RDBMS generic, not PostgreSQL specific - although many do store those types of data successfully in PostgreSQL, MySQL, etc., because of the mention of Redis as a counter store.

Re: PostgreSQL when it is not your job

#19

Post by __mharrison__ » Wed, Jun 06, 2012, 9:46 PM UTC

Here's an attempt[0](by the creator of the Postgres High Perf Book, (and some patches by yours truly)) to automate PG configuration. It's not perfect, but certainly better than OOB settings

0 - https://github.com/gregs1104/pgtune

Re: PostgreSQL when it is not your job

#20

Post by pjungwir » Wed, Jun 06, 2012, 11:19 PM UTC

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.