News for hackers. Stuff that matters.
Re:Postgres 9.2 will feature linear read scalability up to 6... by wisty on Tuesday April 03, 2012 @09:46PM (#3795794)
One of the big fundamental differences between MySQL and Postgres is that MySQL has "real" primary keys (the table is sorted by primary key), and Postgres primary keys are just an index with constraints. Neither solution is really better, but for some workloads MySQL will have an advantage here.
Re:Postgres 9.2 will feature linear read scalability up to 6... by jeltz on Wednesday April 04, 2012 @07:34AM (#3797069)
PostgreSQL's solution makes sequential scans and secondary indexes faster, while MySQL's makes primary key lookups faster. I believe there also are trade offs when it comes to updates and the cost of updating indexes.
Re:Postgres 9.2 will feature linear read scalability up to 6... by rhaas on Wednesday April 04, 2012 @09:00PM (#3800742)
Re:Postgres 9.2 will feature linear read scalability up to 6... by avar on Wednesday April 04, 2012 @05:16AM (#3796786)
Indeed, one of the main advantages of having the primary key indicate the layout of your data on disk is that you can optimize access to your data for the least amount of filesystem page cache misses by making the primary key be the column (or combinations of columns) that you know you're going to have predictable batch access to.

Benchmarking that can be tricky for the same reason that benchmarking anything that reduces cache expiration can be tricky. If you reduce cache expiration your query might not benifit, but if you take a holistic approach to it and apply it to the whole database it'll benefit as a whole.

Any big MySQL powered application is likely to make wide use of this. Which goes to show that doing any benchmark comparisons between databases is often meaningless. You can't benchmark the same schema/queries between databases because they'll be optimized for the database you started out with.

Re:Postgres 9.2 will feature linear read scalability up to 6... by einhverfr on Wednesday April 04, 2012 @06:08AM (#3796886)
See "Cluster ON" in PostgreSQL.

Note that with this, you don't have to use unique indexes to indicate on-disk layout. Of course cluster needs to be run occasionally but it is far more capable.

Re:Postgres 9.2 will feature linear read scalability up to 6... by ryandvm on Wednesday April 04, 2012 @07:36AM (#3797076)
While CLUSTER is great for optimizing a given index, as of 9.1, it still requires an exclusive lock (no reads or writes).

http://www.postgresql.org/docs/9.1/static/sql-cluster.html