Thread: Choice of DB

Nico Callewaert schrieb am 27.11.2018 um 22:08:
> First of all the Uber story scared me. 
> Seems they went from Postgres to MySql because of several issues. 

Some of the issues were valid, some were not.
Some of them have been addressed since then, and some (many?) could have been solved if they had asked questions on the
Postgresmailing list.
 

You might be interested in this response: https://thebuild.com/presentations/uber-perconalive-2017.pdf

And the discussion in the Postgres community regarding that:

https://www.postgresql.org/message-id/flat/CAJBB%3DEU%2BWHcz4amzL6DDxdVsZvhWJAZQ9f_jVeV9FNW1%3Da6eBQ%40mail.gmail.com#f83e6201d8a9d5ec4be02bc70c2ffb8d

You might also want to read the discussions on Reddit and HackerNews:

https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/
https://news.ycombinator.com/item?id=14222721

In a Nutshell: It seems, Uber is using a relational database as a schemaless key/value store - of course that isn't
goingto get the most out of the database.
 

> It's not like we have a thousand simultanous users who are updating
> records non stop.
For a system doing non-stop simultaneous updates, Postgres most probably performs a lot better than MySQL (MariaDB).
MySQL has traditionally been faster with (mostly) read-only workloads using simple queries, but never really excelled
withconcurrent read/write scenarios
 


> I'm wondering if PostgreSQL outperforms Firebird in client/server ERP
> applications? 
This is really hard to tell, and the only one who can answer that is you.
Because only you have the statements and the data to test it.

Given the recent performance improvements that happened in 9.6, 10 and 11 (including parallel query processing), I'd
saychances are quite good that Postgres is at least as fast as Firebird, if not substantially faster.
 

Odoo, probably the most wide-spread OpenSource ERP System - runs exclusively on Postgres, and Apache OFBiz also
recommendsPostgres over MySQL
 

Thomas

To prevent any database war, I would suggest you to try a little POC ...

Regarding your testcase, I think that Postgresql can work fine, with the
expected performances.

What is much complicated is evaluating the migration effort ...

List all the Datatypes, object types you are using with Firebird, 
and if you are using PL (triggers, functions, ...)
and check what are the corresponding datatypes / objects or corresponding
PL/pgsql syntax.

Then choose the more representative part of your application (a simple table
set, PK, FK) 
and try to migrate it.
Reload sample data, and test !

You will have a much better view of the chances of success ;o)

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html

On Tue, 27 Nov 2018 14:40:16 -0700 (MST)
legrand legrand <legrand_legrand@hotmail.com> wrote:

…
> List all the Datatypes, object types you are using with Firebird,
…

Shorter: Pg is SQL standards compliant, mysql/mariadb are not.

Check: create a table with a 10 characters varchar and insert a
"whatever length text but > 10 chars" into it, it'll gladly accept it
without complaining - IIRC, this is a direct violation of SQL99…

Jean-Yves

Jean-Yves,

I'm not defending MySQL/MariaDB, I much feel the same as you about
their insane default settings,
but these are tunable. Just saying.

--
Andrej
On Wed, 28 Nov 2018 at 11:05, Bzzzz <lazyvirus@gmx.com> wrote:
>
> On Tue, 27 Nov 2018 14:40:16 -0700 (MST)
> legrand legrand <legrand_legrand@hotmail.com> wrote:
>
> …
> > List all the Datatypes, object types you are using with Firebird,
> …
>
> Shorter: Pg is SQL standards compliant, mysql/mariadb are not.
>
> Check: create a table with a 10 characters varchar and insert a
> "whatever length text but > 10 chars" into it, it'll gladly accept it
> without complaining - IIRC, this is a direct violation of SQL99…
>
> Jean-Yves
>


--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.georgedillon.com/web/html_email_is_evil.shtml
http://www.catb.org/jargon/html/email-style.html

I'm late but the other folks hit the high points: PostgreSQL is kinda
the default if you're serious. Don't need a reason to use it; need a
reason to use anything else.

- Category-defining GIS.

- Integrity and intelligent data-wrangling features in the DBMS.
Conveniently use as much of that as you want. (I'm a purist on this stuff.)

- Regular, robust development n users communities. If there's a new
feature or capability that's useful, it'll get built into postgreSQL.

FWIW the supporting tools are quite good. One startup in my home city
runs entirely on postgreSQL, wrangled entirely w/ pgadmin4. Thousands of
multi-tenanted customers over a few years.

If you want to go hard core, pgmodeler is as extensive and robust a
design n diagramming tool as I've seen: on a par with $1,000's / seat
commercial tools.

/Use Profile
I consistently advise start-ups: default to postgreSQL unless you have a
real, demonstrable reason why you need something it doesn't do. And for
planning purposes, if you really need something else:

- It's probably about 20 / 80, the weird stuff you need the special
thing for vs. the rest of your activity.

- It's better than even money you'll push the "mundane" stuff into
postgreSQL.

Architecture is a solution: components are choices. Understand what you
need to do, and the rest will follow.


/Meta
In general, for decisions like this, when there's a pain point with the
tech -- and every fanboi for whatever you don't pick will whine about
everthing that's "easier" in their pet thing -- ask: "Compared to what?"
"What am I giving up to get that?

I usually ain't worth it. At worst, grab a point solution for that one
piece of work.

Also, look at your whole tech life-cycle and business road map. Yeah,
you can get hit / page / render gong in no time with a key / value
store, or document database. And it'll scale like crazy. And then, what
doesn't it do so well, that you have to deal with later?

Everything your dbms doesn't do for you wrangling schema, coordination,
data integrity, etc, you'll have to deal with in the app. Fro example,
"eventually consistent" is a lovely thing. And there's a reason NetFlix
published a dozen or so architecture papers on how to make their systems
hosted on then-AWS (may have been OBE in the meanwhile) reliable and
robust enough to run their consumer streaming media delivery.


/Examples
Take a look at RedFin "back in the day" migrating from mySQL to
postgreSQL, maybe 10 years ago. Entire migration / conversion tracked in
their company blogs.

Similarly, "back in the day" the default, and why would you need
anything else under Rails was mySQL. That migrated, so postgreSQ for the
usual reasons.


- Jim


On 11/27/18 5:03 PM, Nico Callewaert wrote:
> Hi to everybody who answered !
> 
> I've read each answer very well. Thanks a lot to everybody for taking
> the time to reply. It's very encouraging to read that PostgreSQL is
> doing so well. The choice is made :-)
> Nice to read all that information.
> 
> Thanks and best regards!
> 
> Op di 27 nov. 2018 om 22:32 schreef James Keener <jim@jimkeener.com
> <mailto:jim@jimkeener.com>>:
> 
>     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.development1972@gmail.com
>     <mailto:app.development1972@gmail.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...
> 


-- 
Jim Bullock, Rare Bird Enterprises, "Conscious Development"
LinkedIn: http://www.linkedin.com/in/rarebirdenterprises

Listen to a round table of experts in these books from Dorset House:
Roundtable on Project Management, http://www.dorsethouse.com/books/rpm.html
Roundtable on Technical Leadership,
http://www.dorsethouse.com/books/rtl.html