4 ms·
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 prima
by wisty 15y ago
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.
- avar 15y agoIndeed, 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.
- einhverfr 15y agoSee "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.
- ryandvm 15y agoWhile 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 http://www.postgresql.org/docs/9.1/static/sql-cluster.html
- deleted 15y ago[deleted]
- jeltz 15y agoPostgreSQL'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.
- rhaas 15y agoYep, see http://rhaas.blogspot.com/2010/11/mysql-vs-postgresql-part-1-table.html http://rhaas.blogspot.com/2010/11/mysql-vs-postgresql-part-1...