[odb-users] after sqlite, choosing between postgresql and mysql

[Boris Kolpackov]

MM <finjulhich at gmail.com> writes:

> This is a hard question but let me describe our use case
> 
> 1. small number of concurrent users (under dozen)
> 2. 3 databases in use, some read only, some read and write
> 3. 2 dozen tables in each database
> 4. Nbr of rows in each table ranges from 10 to 100 000 max
> 5. Nbr of columns from 3/4 to 2 dozen columns, but only few tables
> 6. concurrency: most of the transactions are read. 10% perhaps or less are
> writes.
> 7. There may be a desire to do a cross database "transaction", i.e. either
> both databases are written successfully to or the transaction reverts on
> both.
> 
> That is what I can think of at the moment. Any advice?

I think both databases won't have any issues with points 1-6 on any
reasonably decent hardware. Point 7 might get trickier. I have no
experience with that on MySQL but for PG we have used foreign
tables to achieve something like this.

More generally, you commonly see the following points factored in
when comparing the two databases:

1. PG is commonly viewed as a "saner" implementation of SQL. For
   example, it supports DDL statements within transactions. Another
   point for PG is support for bulk operations (which are now also
   supported in ODB).

2. MySQL, at least until recently, had better replication story, but
   I haven't tried replication in either so I cannot give any first-
   hand reports.

3. MySQL seems to have better concurrency story in that it is a
   multi-threaded database compared to multi-process for PG (but
   there are also reported advantages to multi-process, like better
   reliability).

Personally, my choice would be (and has been) PostgreSQL because it
aligns better with my view of what a database should feel like. Also,
in my experience with supporting ODB, more serious use-cases tend to
gravitate towards PG.

If you want to see where we ourselves use the PG+ODB combo, that would
be brep: https://git.build2.org/cgit/brep/