Choice of DB

[Nico Callewaert]

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...

[James Keener]

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 <

Post by Nico Callewaert
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...

[Nico Callewaert]

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!

Post by James Keener
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
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 <

Post by Nico Callewaert
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...

[jbullock]

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

Post by Nico Callewaert
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!
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
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
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...

[Thomas Kellerer]

Post by Nico Callewaert
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 Postgres mailing 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 going to get the most out of the database.

Post by Nico Callewaert
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 with concurrent read/write scenarios

Post by Nico Callewaert
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 say chances 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 recommends Postgres over MySQL

Thomas

[legrand legrand]

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

[Bzzzz]

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

…

Post by legrand legrand
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

[Andrej]

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

Post by Bzzzz
On Tue, 27 Nov 2018 14:40:16 -0700 (MST)
…

Post by legrand legrand
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

[Steve Crawford]

On Tue, Nov 27, 2018 at 1:08 PM Nico Callewaert <

Post by Nico Callewaert
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...

It might be better posted to the "general" list to get a wider audience.
From what you have described, PostgreSQL seems like a great choice. Your
stated reason for looking for alternatives is corrupted databases. I can
say that in the last 15 years I have never encountered that with PostgreSQL
and when someone trips on the power cord or some other issue brings down a
machine PostgreSQL always recovers extremely quickly to a consistent state.

As to performance, PostgreSQL should be plenty fast for the application you
described. I do run consistently at XXX transactions per second where XXX
is in the low 100 TPS. And that is on 10-year-old hardware that is not
breathing hard and not yet upgraded to the latest PostgreSQL. Performance
is more than speed and just like reading one story about Uber (that has had
a lot of pushback), you can find plenty about MySQL recovery times in the
many minutes to several hours.

Look at the overall picture. For feature set, SQL compliance, code quality,
performance, reliability, user-groups, mailing list support, documentation,
cloud availability, commercial support availability, etc. PostgreSQL does
very well.

Cheers,
Steve