POSTGRES/MYSQL

More blog online, choose one for your attention. https://www.mssqltips.com/sqlservertip/5745/compare-sql-server-mysql-and-postgresql-features/

Best Regards,
| |
Chris
|
|
yuanzefuwater@126.com
|
签名由网易邮箱大师定制

On 03/11/2019 19:31，Sonam Sharma<sonams1209@gmail.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.

Regards,
Sonam

Sonam Sharma schrieb am 11.03.2019 um 12:31:

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.

you might want to have a look at this:

https://stackoverflow.com/a/8182996

My personal(!) opinion: I wouldn't trust a database that thinks:

delete from customer
where '1fool';

is a valid SQL statement and will happily delete all customers.

Sonam Sharma wrote:

In what terms postgres is better than MYSQL.

It is easier to name the few things where MySQL might be better:
- If you need a key-value store that receives lots of updates.
- More widely used.

If I moved to an open source database, I wouldn't choose one that
is owned and controlled by Oracle, but free open source.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On 3/11/19 4:31 AM, Sonam Sharma 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.

This cannot really be answered until more information is provided about
how your current database is used and how you want to continue in the
future.

Regards,
Sonam

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,
Ours is retail company and the DB size is Max 30gb, currently we are using
db2.

On Mon, Mar 11, 2019, 8:21 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On Mon, Mar 11, 2019 at 6:32 AM Sonam Sharma <sonams1209@gmail.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.

Regards,
Sonam

There isn't a simple answer to this, it's like asking 'which is better for
cooking: aluminum or stainless steel'. The answer is 'it depends on what
you're trying to do'.i

There are things that PostgreSQL does very well. There are things that
MySQL does very well. There are things that they don't do as well.

PostgreSQL is, IMHO, more robust and will handle larger databases. MySQL
is better at isolating users from each other and requires less expertise to
administer.

Depending on your needs, the size of your organization and your budget, you
may want to bring in outside consultants to help you plan.
--
Mike Nolan
nolan@tssi.com

On Mon, 11 Mar 2019 at 12:36, Michael Nolan <htfoot@gmail.com> wrote:

There isn't a simple answer to this, it's like asking 'which is better for cooking: aluminum or stainless steel'. The answer is 'it depends on what you're trying to do'.i

Metaphors can be dangerous (especially when automotive ones get
chosen!), but this is a pretty good one.

Often, it doesn't terribly much matter which kind of cookware you use;
both aluminum and steel will allow you to cook your dinner, and if
your needs are not challenging, the differences may make little
difference. That seems likely to be true here; both Postgres and
MySQL have a sizable set of relatively common facilities where they
would function in broadly similar ways. This wasn't so true in the
MySQL 3 versus PostgreSQL 6 days, when they had enormously different
expressions of basic usage patterns. (e.g. - in MySQL, you'd get data
eaten by weak data types, or performance killed when writes demand
full table locks, but PostgreSQL would lose performance when VACUUM
would lock the whole table). Modern MySQL is a lot less sloppy than
it used to be, and Modern PostgreSQL performs a way lot better than it
used to.

And you can certainly fry bacon in either a steel or aluminum pan;
performance of that is liable to depend more on the qualities of stove
and of the bacon than on the qualities of the fry pan.

Paralleling the notion that performance and reliability might depend
more on the qualities of the server, between CPUs, amounts and speed
of RAM, and whether you're persisting data on SSDs versus "spinning
rust disks." If your disk dies, the database goes away, "poof,"
irrespective of what brand of database you're using...

It is also somewhat foolish to get deep into minutiae when we have no
idea which details do or do not matter for the particular situation.

It's pretty likely that there *is* some relevant minutiae, but, when
the only details given are:

- Retail company
- DB size ~30GB
- Present database is DB2

That doesn't point all that readily at "relevant minutiae".

Sorts of things that might help:
- What kinds of data types are in use?
- What is the architecture of the system in practice?
- What kinds of transaction load are being thrown at the system?
- Presumably some OLTP activity to record retail activities
- Reporting on activities (which might involve replication?)
- Data lifecycles (how does data arrive, how does it exit once irrelevant?)

There are consulting organizations out there that would be quite
prepared to help with that sort of analysis.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On 3/11/19 9:31 AM, Sonam Sharma wrote:

Hi Adrian,
Ours is retail company and the DB size is Max 30gb, currently we are
using db2.

Things to consider:

1) Migration tools for DB2 --> MySQL/Postgresql. I have not done this,
so someone else will have to comment.

2) The clients/frameworks/ORMs you use now to connect to the database.
Do they also work with Postgresql/MySQL?

On Mon, Mar 11, 2019, 8:21 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 3/11/19 4:31 AM, Sonam Sharma 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.

This cannot really be answered until more information is provided about
how your current database is used and how you want to continue in the
future.

Regards,
Sonam

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue 12 Mar, 2019, 1:58 AM Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:

On 3/11/19 9:31 AM, Sonam Sharma wrote:

Hi Adrian,
Ours is retail company and the DB size is Max 30gb, currently we are
using db2.

Things to consider:

1) Migration tools for DB2 --> MySQL/Postgresql. I have not done this,
so someone else will have to comment.

This will be fairly simple for a 30gb database if you can afford some
downtime. I have some bit experience with this (DB2 9.7 to PostgreSQL). You
can use db2look to export the DDL out and then do some minor modifications
and run the SQL (more or less) as is on Postgres.

Use "db2 export" command to export data in CSV format (separate file for
each table).

Use Postgres COPY command to copy data from CSV format.

Make sure that you set PostgreSQL client encoding to something that aligns
with database codepage on DB2 and LC_LANG setting. If DB2 is hosted on AIX
or Linux, avoid using a Windows client to fire db2 export. Migrating off
from Windows could be tricky. The codepage and encoding issues might need a
few retries to align. So pls test in UAT with prod like data sample.

2) The clients/frameworks/ORMs you use now to connect to the database.
Do they also work with Postgresql/MySQL?

On Mon, Mar 11, 2019, 8:21 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 3/11/19 4:31 AM, Sonam Sharma 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.

This cannot really be answered until more information is provided

about

how your current database is used and how you want to continue in the
future.

Regards,
Sonam

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--

--

Best Regards,

*Sameer Kumar | Senior Solution Architect*

*ASHNIK PTE. LTD.*

36 Robinson Road, #14-04 City House, Singapore 068877

T: +65 6438 3504 | www.ashnik.com

Skype: sameer.ashnik | M: +65 8110 0350

On 12/03/2019 05:35, Michael Nolan wrote:
[...]

 MySQL is better at isolating users from each other and requires less
expertise to administer.

[...]

I keep reading that MySQL is easier to administer, but never seen any
evidence of that.  And in my very limited experience of both, I've found
PostgreSQL easier to set up & administer.

From what I've read about problems with MySQL, I think that if you
value your data, just don't use MySQL.   At least 4 times, since 2001,
I've searched for PostgreSQL vs MySQL comparisons, and each time found
PostgreSQL to be superior in terms of performance, reliability,
robustness, and ease of use.

There is a definite trend of people moving from MySQL to PostgreSQL, and
its not just because of Oracle (MySQL diehards are moving to MariaDB).

I have a lot more confidence in PostgreSQL, than MySQL/MariaDB.

Cheers,
Gavin

P.S. Don't top post!  As commenting at the bottom, is the norm for
PostgreSQL mailing lists.

+1 for Postgres here. We moved to it from Oracle 15 years ago and have
never looked back for a second.

mySQL databases over the same period have needed occasional repairs that
Postgres never has. We couldn't possibly choose mySQL over Postgres now;
the feature list of mySQL is so inferior we'd need to come up with some
major engineering patches.

Tim Clarke
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420

On 11/03/2019 19:20, Gavin Flower wrote:

On 12/03/2019 05:35, Michael Nolan wrote:
[...]

MySQL is better at isolating users from each other and requires less
expertise to administer.

[...]

I keep reading that MySQL is easier to administer, but never seen any
evidence of that. And in my very limited experience of both, I've
found PostgreSQL easier to set up & administer.

From what I've read about problems with MySQL, I think that if you
value your data, just don't use MySQL. At least 4 times, since 2001,
I've searched for PostgreSQL vs MySQL comparisons, and each time found
PostgreSQL to be superior in terms of performance, reliability,
robustness, and ease of use.

There is a definite trend of people moving from MySQL to PostgreSQL,
and its not just because of Oracle (MySQL diehards are moving to
MariaDB).

I have a lot more confidence in PostgreSQL, than MySQL/MariaDB.

Cheers,
Gavin

P.S. Don't top post! As commenting at the bottom, is the norm for
PostgreSQL mailing lists.

Main: +44 (0)1376 503500 | Fax: +44 (0)1376 503550
Web: https://www.manifest.co.uk/

Minerva Analytics Ltd
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England

----------------------------------------------------------------------------------------------------------------------------

Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info>
Legal: Minerva Analytics is the trading name of: Minerva Analytics
Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >><https://www.manifest.co.uk/legal/&gt; for further information.

Em 11/03/2019 16:20, Gavin Flower escreveu:

On 12/03/2019 05:35, Michael Nolan wrote:
[...]

 MySQL is better at isolating users from each other and requires less
expertise to administer.

[...]

I keep reading that MySQL is easier to administer, but never seen any
evidence of that.  And in my very limited experience of both, I've
found PostgreSQL easier to set up & administer.

If you allow me an apart, "easy to administer" must means also that we
have no referencial integrity hell - which MySQL doesn't guarantee. I
had problems in past even using InnoDB, which is supposed to work...
Easy to adminiter also means not having to deal with internal tables
whenever MySQL decides not accepting our administrator passwords anymore...

So, IMHO, easy to administer is a system that don't drive me crazy, and
_for me_ this system is PostgreSQL - at least in my very special case
(which is reach old age without becoming nuts).

;-)

Regards,

Edson

Quoting Adrian Klaver <adrian.klaver@aklaver.com>:

On 3/11/19 9:31 AM, Sonam Sharma wrote:

Hi Adrian,
Ours is retail company and the DB size is Max 30gb, currently we
are using db2.

Things to consider:

1) Migration tools for DB2 --> MySQL/Postgresql. I have not done
this, so someone else will have to comment.

2) The clients/frameworks/ORMs you use now to connect to the
database. Do they also work with Postgresql/MySQL?

It is also worth to consider if the architecture/model of DB2 fits
PostgreSQL/MySQL. And while at it, how about getting rid of all the
itching quirks of the current solution anyway? I see the danger of
getting disappointed by any of PostgreSQL/MySQL if the current
solution uses DB2 features that cannot be easily mapped to any of the
contenders features.

Bottom line of my insinuation is that the migration tool could be less
an point if you get the opportunity to overhaul your application.

Kind two dimes

Thiemo

--
Signal: +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B

On 12/03/2019 09:40, Thiemo Kellner wrote:

Quoting Adrian Klaver <adrian.klaver@aklaver.com>:

On 3/11/19 9:31 AM, Sonam Sharma wrote:

Hi Adrian,
Ours is retail company and the DB size is Max 30gb, currently we are
using db2.

Things to consider:

1) Migration tools for DB2 --> MySQL/Postgresql. I have not done
this, so someone else will have to comment.

2) The clients/frameworks/ORMs you use now to connect to the
database. Do they also work with Postgresql/MySQL?

It is also worth to consider if the architecture/model of DB2 fits
PostgreSQL/MySQL. And while at it, how about getting rid of all the
itching quirks of the current solution anyway? I see the danger of
getting disappointed by any of PostgreSQL/MySQL if the current
solution uses DB2 features that cannot be easily mapped to any of the
contenders features.

Bottom line of my insinuation is that the migration tool could be less
an point if you get the opportunity to overhaul your application.

Kind two dimes

Thiemo

Yes, I'm aware that different RDDBMS's having their own quirks that
people either exploit as benefits, or have to work around in a new DB. 
So naively converting one DB to another may have huge performance hits,
and possible unexpected results (MySQL, I'm looking at you!) -- even if
both DB's were technically equally good!

I think a conversion tool is a good starting point.  However, I strongly
agree with Thiemo, that you should carefully review your existing
databases design/implementation -- so as to take maximum advantage of
the facilities of progress, and avoid any pitfalls created by naively
importing isms that are specific to your old db -- that might well be
counter productive in PostgreSQL.

Cheers,
Gavin

On Mon, Mar 11, 2019 at 2:20 PM Gavin Flower <GavinFlower@archidevsys.co.nz>
wrote:

On 12/03/2019 05:35, Michael Nolan wrote:
[...]

MySQL is better at isolating users from each other and requires less
expertise to administer.

[...]

I keep reading that MySQL is easier to administer, but never seen any
evidence of that. And in my very limited experience of both, I've found
PostgreSQL easier to set up & administer.

I did not say MySQL is easier to administer, I said it requires less
expertise to administer.

Perhaps I should have said it requires less expertise to administer it well
enough for it to function adequately. Consider the number of packages that
run on MySqL (like Joomla, Wordpress and phpbb3) and the level of expertise
of those running those systems.

I've never worked with DB2, but I did spend 10 years as a DBA for Oracle
systems, and I'd place PostgreSQL somewhere in between MySQL and Oracle for
the amount of expertise it takes to administer it properly. And the more
complex the application, the more that expertise is needed.

MySQL's data issues are well-documented and while they can generally be
dealt with at the application level, I prefer to handle both data issues
and implementation of business rules at the database level, because that
forestalls a lot of problems.
--
Mike Nolan

On Mon, Mar 11, 2019 at 7:32 PM Sonam Sharma <sonams1209@gmail.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

Chris Travers wrote:

Also MySQL has a query cache that allows the results of very common queries to be much faster.

I have used that feature, and it has bitten me:
https://stackoverflow.com/q/44244482/6464308

I guess only some rather pathological workloads really benefit from that.

For updates, MySQL avoids a lot of index write overhead. PostgreSQL has more overhead per update.

That is what I meant when I said that PostgreSQL is less suitable for a key-value store.

There is HOT update which can mitigate the problem if the updated columns are not indexed.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Laurenz Albe schrieb am 12.03.2019 um 10:05:

Also MySQL has a query cache that allows the results of very common queries to be much faster.

I have used that feature, and it has bitten me:
https://stackoverflow.com/q/44244482/6464308

Note that the query cache was removed in MySQL 8.0 (don't know about MariaDB)

MySQL isn't ACID. Postgresql is a full-featured database that doesn't allow
injection. It is very safe and secure. Also, the way that PostgreSQL has a
much better user management system and database/table level access ACLs.

Basically, you just asked a bunch of people who have used PostgreSQL over
MySQL why PostgerSQL is better. It is just better. The only time that MySQL
might be better is if you have a very simple website and you want a SQL
backend. For anything else, PostgreSQL is infinitely better.

Thanks,
~Ben

On Tue, Mar 12, 2019 at 5:34 AM Thomas Kellerer <spam_eater@gmx.net> wrote: