Postgres 9.1 vs Mysql 5.6 InnoDB?
Is MySQL's query optimizer still stupid? Is it still super slow on very complicated queries?
All query optimizers are stupid at times. PostgreSQL's is less stupid in most cases. Some of PostgreSQL's more recent SQL features (windowing functions, recursive WITH queries etc) are very powerful but if you have a dumb ORM they might not be usable.
Size of the project: Say an ordering system with roughly 10-100 orders/day per account, couple of thousand accounts, eventually, each can have several hundred to several thousand users.
Doesn't sound that large - well within reach of a big box.
Better at: being future proof and flexible when it comes to growing and changing requirements.
PostgreSQL has a strong developer team, with an extended community of contributors. Release policy is strict, with bugfixes-only in the point releases. Always track the latest release of 9.1.x for the bugfixes.
MySQL has had a somewhat more relaxed attitude to version numbers in the past. That may change with Oracle being in charge. I'm not familiar with the policies of the various forks.
Performance is also important as to keep costs low in hardware department.
I'd be surprised if hardware turned out to be a major component in a project this size.
Also availability of skilled workforce would be a factor.
That's your key decider. If you've got a team of experienced Perl + PostgreSQL hackers sat around idle, use that. If your people know Lisp and MySQL then use that.
OLTP or OLAP: OLTP
PostgreSQL has always been strong on OLTP.
My personal viewpoint is that the PostgreSQL mailing list are full of polite, helpful, knowledgeable people. You have direct contact with users with Terabyte databases and hackers who have built major parts of the code. The quality of the support is truly excellent.
PostgreSQL is a lot more advanced when it comes to SQL features.
Things that MySQL still doesn't have (and PostgreSQL has):
deferrable constraints
check constraints(MySQL 8.0.16 added them, MariaDB 10.2 has them)full outer join
MySQL silently uses an inner join with some syntax variations:
https://rextester.com/ADME43793lateral joins
regular expressions don't work with UTF-8(Fixed with MySQL 8.0)regular expressions don't support replace or substring(Introduced with MySQL 8.0)table functions (
select * from my_function()
)common table expressions(Introduced with MySQL 8.0)recursive queries(Introduced with MySQL 8.0)writeable CTEs
window functions(Introduced with MySQL 8.0)function based index(supported since MySQL 8.0.15)partial index
INCLUDE additional column in an indexes (e.g. for unique indexes)
multi column statistics
full text search on transactional tables(MySQL 5.6 supports this)GIS features on transactional tables
EXCEPT or INTERSECT operator (MariaDB has them)
you cannot use a temporary table twice in the same select statement
you cannot use the table being changed (update/delete/insert) in a sub-select
you cannot create a view that uses a derived table(Possible since MySQL 8.0)create view x as select * from (select * from y);
statement level read consistency. Needed for e.g.:
update foo set x = y, y = x
orupdate foo set a = b, a = a + 100
transactional DDL
DDL triggers
exclusion constraints
key/value store
Indexing complete JSON documents
SQL/JSON Path expressions (since Postgres 12)
range types
domains
arrays (including indexes on arrays)
roles (groups) to manage user privileges(MariaDB has them, Introduced with MySQL 8.0)parallel queries (since Postgres 9.6)
parallel index creation (since Postgres 11)
user defined data types (including check constraints)
materialized views
custom aggregates
custom window functions
proper
boolean
data type
(treating any expression that can be converted to a non-zero number as "true" is not a proper boolean type)
When it comes to Spatial/GIS features Postgres with PostGIS is also much more capable. Here is a nice comparison.
Not sure what you call "ease of use" but there are several modern SQL features that I would not want to miss (CTEs, windowing functions) that would define "ease of use" for me.
Now, PostgreSQL is not perfect and probably the most obnoxious thing can be, to tune the dreaded VACUUM process for a heavy write database.