KohaCon10: MySQL and Postgres differences

by ByWater Staff on Oct 28, 2010

Presentation by Mark Kirkwood.

Postgres does not have all those handy “SHOW” commands from MySQL, rather ‘backslash’ commands. You can use \d for “SHOW TABLES”, for example.

Postgres only has one engine, so any engine specification (like InnoDB) will fail. MySQL InnoDB requires ‘drop table’ to fully recover space, where ‘truncate’ will not. Helpful hint from BibLibre: make the koha.sessions table MyISAM rather than InnoDB to save space.

The last inserted values of a column are retrieved differently in Postgres. It’s more flexible, where you can specify the column to return (rather than having another SELECT query).

Postgres will not allow you to insert badly formatted dates, whereas MySQL will do the insert and give a quiet warning.

Postgres requires you to group by non-aggregated columns in a GROUP BY clause, whereas MySQL will let you.

Postgres does not have a query cache. I’ve run mysqltuner.pl and found that Koha does make some good use of this, so we’d need to be aware of the trade-off.

Row versioning is handled differently. Postgres needs ‘vacuum’ run aggressively enough to prevent choking, since old versions of table rows are kept. Small, often updated tables are better handled by MySQL. More complex the query, better than Postgres performs.

Many databases have special data types for large amounts of text (text, blob, largeblob, etc.). In Postgres, a VARCHAR can be unlimited (practically speaking), thanks to vertical partitioning. Allows for funny things like adding text-encoded images into text fields, without having to access the all the data at once to get the information about it later.

Postgres’s EXPLAIN is much more detailed and helpful than MySQL’s. Good for optimizing and tuning. Shows an artificial number for ‘cost’, but as part of a detailed query tree.

MySQL is much easier to replicate than Postgres. Postgres needs external tools, whereas MySQL has it natively.

To deal with a lot of these issues, we will use DBIx::Class to abstract.

[tag]kohacon10[/tag]

[Originally posted by Ian Walls]