PostgreSQL when it is not your job • Hacker News Board

Re: PostgreSQL when it is not your job

#41

Post by papsosouid » Thu, Jun 07, 2012, 1:49 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…

>PSQL is sincerely a waste of time and energy for small things

As someone who has been using SQL server, postgresql, mysql, and oracle virtually every day for 13 years, I have to disagree. Postgresql is by far the easiest, most user-friendly one of the bunch. Mysql is full of tons of weird unexpected behaviours, and the command line mysql shell is so bad it is essentially useless.

Re: PostgreSQL when it is not your job

#42

Post by papsosouid » Thu, Jun 07, 2012, 1:56 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…

>Some of the suggestions make PostgreSQL seem less mature than InnoDB, still

I like the way you throw "still" on the end, as if innodb has ever been more mature than postgresql. That's very amusing. The suggestions you reference are not postgresql specific, they are general suggestions to not do really dumb stuff with your database, and apply to every database.

>My forum gets almost a million page views daily

Of course you can do dumb stuff, especially if you have a tiny site like that where even the crappiest server you can buy is going to be way overkill. In a case like that, go right ahead and store sessions in the DB if you want, it won't matter at all. The suggestion is for people who expect to end up with a busy site, where updating your database constantly with every page request is going to hurt your scalability and gains you nothing.

>We don't have to worry about running VACUUM periodically either.

I don't think anyone is suggesting that running a 10 year old version of postgresql is a good idea.

>I feel PostgreSQL may be a wee bit overhyped

I wonder if that has anything to do with your obvious ignorance of the subject.

Re: PostgreSQL when it is not your job

#43

Post by lobster_johnson » Thu, Jun 07, 2012, 6:30 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…

Vacuum has not been an issue since the days of Postgres 7.x (10 years ago).

Re: PostgreSQL when it is not your job

#44

Post by justin_vanw » Sun, Jun 17, 2012, 2:09 AM UTC

rosser wrote: post #17

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?

Async commit has nothing to do with battery backed disks or not. That is Fsync. Async commit will bundle multiple commits and sync them at the same time. Depending on how you configure it, you stand to lose as much as the last X seconds of commits before the server went down, and never more. (you can configure it to lose much less, it's configurable!).

It's all a tradeoff of reliability and recoverability vs performance. Postgres gives you lots of choices so you can make the tradeoff that is appropriate for your data.