Re: Choice of DB

In my experience with high volume ecommerce and location tracking systems
is that PostgreSQL performed more than adequately. Especially given that I
trust PostgreSQL and I _do not_ trust MySQL/MariaDB at all to have any data
integrity, even the most recent versions.

I've had databases sized into the 100s of GB run perfectly well, but those
are relatively small. I've heard reports from larger installations that
PostgreSQL keeps chugging.

Additionally, the additional features of PostgreSQL over MySQL/MariaDB are
really night and day and much more complete and useful, such as window
functions, check constraints, json datatype, triggers, transition tables,
plpgsql, proper transactions, expression indecies, better explain output,
ranged types and indecies, richer datatypes, GIN/GIST searches, sane
decisions in terms of type handing and automatic casting, as well as all of
the extensions available including my favourites: PostGIS and pg-routing.

Every time I have to use MySQL, I end up wishing I had the power of
PostgreSQL, because I either have to be painful and inefficient, or I have
to accept a lesser end-product. (I literally don't have any nice things to
say about MySQL or MariaDB other than "it doesn't always break". I've been
burned many times by it.)

Moreover, between recent features such as partitioning updates and logical
replication, scaling horizontally is becoming easier and easier.

Also, Amazon is in the process of moving all of their Oracle-based ERP
systems to PostgreSQL (via their Aurora product). Despite one news story
based on a journalist's misunderstanding, the transition appears to be
going well, but they're not rushing it, so they won't be off until 2020.
(That was the last date I've heard.)

The "scary" uber story boils down to two things, from my outsider
understanding:

1) They had a lot of long-running, dependent transactions due to poor
coding, which obviously led to poor performance, and would do so with any
database system.

2) They don't really "use" MySQL in any RDBMS sense; they basically use it
as a Key-Value store for some new database they've written that's similar
to Google's BigTable in terms of access styles.

I wouldn't worry about Ubers missteps and problems.

Jim

On Tue, Nov 27, 2018 at 4:08 PM Nico Callewaert <
app(dot)development1972(at)gmail(dot)com> wrote:

> Hi,
>
> It's my first post to this list. I'm a little familiar with PostgreSQL
> from the documentation but never used it yet in a real environment. I've
> used the last 15 years Firebird as the RDBMS for our ERP application. Not
> bad, it served us somehow ok, but not that great either... I've grown
> unhappy with the product because of the many corrupted database we
> encounter at customer sites. I have plans to rewrite the ERP application
> and use a new RDBMS and create a new database from scratch. And of course
> the problem now is, what RDBMS to use. I know this is not an easy question
> and has no straight answer. And I certainly don't want to start a war of
> words of pro's and con's of database servers. However I have to make a
> choice.
>
> First of all the Uber story scared me. Seems they went from Postgres to
> MySql because of several issues. But maybe issues I would never run into.
> First of all, performance is certainly very important. It's said that
> MariaDB outperforms "everything", but I'm not sure if that's a good choice.
> It's not like we have a thousand simultanous users who are updating records
> non stop. Most of our sites have between 10 and 50 users. Of course they
> insert and update data, but not at a speed of xxx transactions per second.
> They are simply users who enter quotes, orders, delivery notes, invoices,
> projects, time registration of employees on the road, etc....
>
> I'm wondering if PostgreSQL outperforms Firebird in client/server ERP
> applications? The big thing is, sometimes large datasets are fetched, I'm
> wondering if PostgreSQL would do better than firebird and if the
> performance of Postgres is fine let's say compared to mysql or mariadb,
> because if the performance won't be good, my boss would consider the
> migration from firebird a total failure.
>
> Thanks in advance and sorry if my question doesn't fit in with the list.
>
> Best regards...
>