POSTGRES/MYSQL

classic Classic list List threaded Threaded
30 messages Options
12
Reply | Threaded
Open this post in threaded view
|

POSTGRES/MYSQL

Sonam Sharma
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
Reply | Threaded
Open this post in threaded view
|

Re:POSTGRES/MYSQL

Chris-9
On 03/11/2019 19:31[hidden email] 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
Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Thomas Kellerer
In reply to this post by Sonam Sharma
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.




Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Laurenz Albe
In reply to this post by Sonam Sharma
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


Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Adrian Klaver-4
In reply to this post by Sonam Sharma
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Sonam Sharma
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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Michael Nolan
In reply to this post by Sonam Sharma


On Mon, Mar 11, 2019 at 6:32 AM Sonam Sharma <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Christopher Browne-3
On Mon, 11 Mar 2019 at 12:36, Michael Nolan <[hidden email]> 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?"

Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Adrian Klaver-4
In reply to this post by Sonam Sharma
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 <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Sameer Kumar


On Tue 12 Mar, 2019, 1:58 AM Adrian Klaver, <[hidden email]> 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 <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]

--

-- 

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


Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Gavin Flower-2
In reply to this post by Michael Nolan
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.


Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Tim Clarke-3
+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 [hidden email]<mailto:[hidden email]>
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/> for further information.
Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Edson Carlos Ericksson Richter
In reply to this post by Gavin Flower-2

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



Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Thiemo Kellner, NHC Barhufpflege
In reply to this post by Adrian Klaver-4

Quoting Adrian Klaver <[hidden email]>:

> 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


Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Gavin Flower-2
On 12/03/2019 09:40, Thiemo Kellner wrote:

>
> Quoting Adrian Klaver <[hidden email]>:
>
>> 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



Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Michael Nolan
In reply to this post by Gavin Flower-2


On Mon, Mar 11, 2019 at 2:20 PM Gavin Flower <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Chris Travers-5
In reply to this post by Sonam Sharma


On Mon, Mar 11, 2019 at 7:32 PM Sonam Sharma <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Laurenz Albe
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


Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Thomas Kellerer
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)




Reply | Threaded
Open this post in threaded view
|

Re: POSTGRES/MYSQL

Benedict Holland
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 <[hidden email]> wrote:
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)




12