

MyISAM, InnoDB, and PostgreSQL - mattyb
http://sequel.heroku.com/2010/03/29/myisam-innodb-and-postgresql/

======
justinsb
The biggest differences normally arise with multiple queries running
concurrently. For example, InnoDB & PostgreSQL use MVCC whereas MyISAM uses
locking, and PostgreSQL normally wins most benchmarks with high concurrency on
multi-core machines (8+).

I'm not certain I understand what you ran in the 'heavy' and 'light' threading
tests. Can you provide some more details / explanation?

------
prodigal_erik
Everyone measures how expensive transactions are compared to no isolation, but
I've never seen anyone switch off isolation and measure how often their code
stumbles across a half-baked update from another thread, nor how badly their
code screws up their data when it happens.

------
jokull
My problem with PostgreSQL is that you're half expected to tweak it to your
needs. Ideally I would have to record and replay real life activity against
the database for each adjustment. It's too much work to do properly. An expert
could probably make a good first guess though. Which is why I've wondered if
there is a tool where you drag a couple of sliders that describe your server
specs and application nature and output config.

~~~
moe
_My problem with PostgreSQL is that you're half expected to tweak it to your
needs._

That's a myth. Postgres runs out of the box just as well as MySQL, with no
more configuration effort.

A good starting point could be: <http://www.revsys.com/writings/postgresql-
performance.html>

As you can see there is only a very small number of settings that you really
should set consciously, just as you have to choose consciously between
InnoDB/MyISAM and various baseline settings on MySQL.

Beyond these eight parameters (most of them relate to the amount of RAM you
want it to use) postgres ships with reasonable defaults. All the other options
may become interesting to tune it later for special workloads (something that
you can hardly do with MySQL) but are usually safe to ignore.

~~~
sabat
The security settings that allow you to even connect are 1) hard to find, 2)
somewhat difficult to track down documentation for, and 3) not what everyone
would call "reasonable". I like Postgres, but it needs to evolve a more admin-
centric attitude in my estimation.

~~~
moe
Excuse me?

<http://www.postgresql.org/docs/8.4/static/index.html>

If it takes you more than 10 seconds to find "Client authentication" under
"Server administration" then do you really think you are qualified to setup a
database server?

Sorry if this comes across rude, but criticizing security in postgres in a
comparison against MySQL is beyond absurd.

The grant-system in MySQL is a _bloody_ _mess_. It doesn't even have Roles.
And it is prone to "funny" side-effects, such as the dreaded anonymous user
issue which afaik is still unfixed in latest stable.

In a nutshell: If you don't remove the two anonymous user grants that mysql
puts in there _by_ _default_ then a grant to 'foo'@'%' will allow 'foo' to
connect from any host _except_ localhost. Makes sense? Not? Thought so.

Here's why: The connection attempt from localhost is rejected because the
default entry ''@'localhost' matches _before_ your 'foo'@'%' entry. So that's
what we call admin-centric these days, shipping with mousetraps like that in
the default config?

[http://dev.mysql.com/doc/refman/5.0/en/connection-
access.htm...](http://dev.mysql.com/doc/refman/5.0/en/connection-access.html)

Then there's postgres, look how hard it is:

First you specify what users are allowed to connect from where and to which
databases (pg_hba.conf). Then you create the actual users (CREATE USER).
Finally you grant privileges to them (GRANT). Optionally you can group them
under roles.

The common setup that allows local users to connect without a password as any
user but demands a password from remote connections takes all of two lines in
pg_hba.conf:

    
    
      # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
      host    all         all         0.0.0.0/0             md5
      local   all         all                               trust
    

Then you can make your users:

    
    
      CREATE USER foo WITH PASSWORD 'secret';
    

And give them access to stuff:

    
    
      GRANT ... TO foo;
    

Man that was hard. I'm all exhausted now. But hey, it's right there in the
docs. With examples and all. [http://www.postgresql.org/docs/8.4/static/auth-
pg-hba-conf.h...](http://www.postgresql.org/docs/8.4/static/auth-pg-hba-
conf.html)

~~~
sabat
Right, so if you want to connect over the network? What happens? Oh, yeah --
it doesn't work. And then you have google and google with no real idea of what
keywords to use other than 'postgres connection problem' until you find the
arcane configuration that lets you connect.

Real simple. Yeah.

~~~
moe
Have you _ever_ looked at the postgres documentation at all?

