Re: POSTGRES/MYSQL

On Mon, Mar 11, 2019 at 7:32 PM Sonam Sharma <sonams1209(at)gmail(dot)com> wrote:

> Hi All,
>
> We are planning to migrate our database into any open source DB.
> Can someone please help me in knowing which one will be better among
> POSTGRESQL and MYSQL.
>
> In what terms postgres is better than MYSQL.
>

If course you will get a lot of pro-Postgres answers here. I am going to
try to give a balanced one.

I: Performance

MySQL with InnoDB performs differently than PostgreSQL. Primary key
lookups are marginally less expensive. Secondary index lookups are
significantly more expensive. Sequential scans are much more expensive.
If all you are ever doing is primary key lookups, MySQL might perform
better. For most real-world workloads, PostgreSQL does better.

Also MySQL has a query cache that allows the results of very common queries
to be much faster. PostgreSQL has a more complex cache system which
performs better on complex workloads. So in most cases, Postgres is better
(assuming appropriate tuning on both).

For updates, MySQL avoids a lot of index write overhead. PostgreSQL has
more overhead per update. In some cases this is a big deal. However I
have never seen such a situation that made PostgreSQL unsuitable.

I suspect parallel query is faster on MySQL but I have seen internal
parallelism lead to deadlocks with only a single session running. In other
words, bulk inserts deadlocking against themselves.

II: Porting

MySQL has per-client modes of query which affect what data can be properly
stored and how data can be retrieved. The positive side is that MySQL is
ok at pretending to be other database systems but not so good at ensuring
data integrity (strict mode can be turned off by any writer, so you can't
always trust what is written). The downside is that MySQL doesn't have as
much of a rich feature set of compliant features, so what you gain from
being able to use a supported dialect you may lose in having to rewrite
queries anyway.

PostgreSQL is fairly strict about data insertion and does not support
multiple dialects of SQL, so porting non-ANSI-SQL queries to PostgreSQL can
sometimes take more effort, but the feature set supported is much higher
so.... Six of one, half a dozen of the other.

III: Licensing

MySQL is owned by Oracle and GPL licensed. PostgreSQL is BSD-licensed and
owned by the individual contributors. If you are considering MySQL you
might want to use MariaDB instead. But PostgreSQL avoids most of these
issues and ensures that even if you are distributing the db with a
proprietary application, there are no licensing implications of doing that.

> Regards,
> Sonam
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more