Обсуждение: POSTGRES/MYSQL

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

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'.
Theanswer 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 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
youmust 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
RegisteredOffice at above address. Please Click Here >><https://www.manifest.co.uk/legal/> 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

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)

Benedict:

On Tue, Mar 12, 2019 at 3:11 PM Benedict Holland
<benedict.m.holland@gmail.com> wrote:
> MySQL isn't ACID.

Are you sure of that? I mean, I stopped using it in the late nineties,
and it only had MyISAM then, certainly not ACID, but my understanding
is it's got some ACID storage backends and options ( I seem to recall
InnoDB is one of them, and last thing I know is it did ACID DML but
had problems with DDL ).

Don't get me wrong, I prefer postgres too, I think it is easier and
nicer, but MySql has gone a long way and is not that bad ( as "not
acid" ).

> Postgresql is a full-featured database that doesn't allow injection.

Also, regarding this, it seems to hint at "injections are mysql fault"
and "you are safe from injections using Pg". Databases do not allow or
disallow (SQL) injection. It's the app code which does it. I can write
code with and without injections which works equally well, or bad, in
both engines. Nearly anyone can.

.....
> 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
anythingelse, PostgreSQL is infinitely better. 

No, it's not. I use postgres for a lot of things, and some of them are
only done in this over mysql because I have postgresql servers
installed and I am familiar with it and the volume is not that high
that switching to mysql justifies the learning cost. And it would
probably be the other way round if I were a mysql guy.

And, not knowing too much about the OP detailed requirements, I would
recommend Pg for them because I know it can do the things he seem to
want, and I am not sure about mysql. I am nearly sure it can do them,
but I cannot assert it.

Pg is really good, but not a panacea.

Francisco Olarte.

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:

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)

Benedict:

On Tue, Mar 12, 2019 at 4:56 PM Benedict Holland
<benedict.m.holland@gmail.com> wrote:
> When you create a table in a transaction, it will commit the transaction and prevent a rollback. MySQL is not ACID.

And when you call COMMIT in postgres it will commit and prevent a rollback.

This does not mean MySQL is not ACID, it means DDL cannot be transactioned.

It is, in fact, one of the reasons why I do not use MySql, but I think
InnoDb and friends are ACID, and the problem is the system catalogs
were Isam an not transactioned.

And, If I remember correctly, one of the features Oracle announced for
8.0 was transactional DDL ( althought not sure if it can be mixed with
other things in a transaction, these things are hairy due to the
multiple storage engines normally involved in a single mysql instance
). But I'm not going to test it.

regards.
   Francisco Olarte.

Benedict:

On Tue, Mar 12, 2019 at 5:53 PM Benedict Holland
<benedict.m.holland@gmail.com> wrote:
> I am not saying it is not well documented. I am saying that it isn't ACID compliant, which it isn't, as they
document.

You are using the term "ACID compliant". Could you please point me to
some definition of what this is ( not ACID, I know what ACID is, not
compiant or compliance, I now what compliance is like "SQL-99
compliant", "ACID compliant" is what I do not have a definition for ).

Francisco Olarte.

On Tue, 12 Mar 2019 at 12:53, Benedict Holland
<benedict.m.holland@gmail.com> wrote:
> I am not saying it is not well documented. I am saying that it isn't ACID compliant, which it isn't, as they
document.

I *love* the notion of being able to roll back DDL, but it has long
been common for DDL to *not* be transactional even with some of the
Big Expensive Databases (such as the one whose name begins with an
"O").

Up until version 11.something, "Big O" apparently did NOT have this,
and MS SQL Server didn't in version 2008.

https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189122(v=sql.105)

Of course, those are somewhat old versions.  But nobody would have
claimed those systems not to be "ACID Compliant" at the time; you're
setting the bar a bit too high.

Someone's asking the merits of PostgreSQL versus MySQL; it certainly
*is* possible to overplay the case.

I'm perfectly happy with a claim like...

 "PostgreSQL does transactional DDL, which we find quite valuable, and
while MySQL supports ACID for data manipulation, with suitable choice
of storage engines, there is not the same capability to be able to
roll back DDL within a transaction."
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On 3/12/19 3:19 PM, Christopher Browne wrote:
> On Tue, 12 Mar 2019 at 12:53, Benedict Holland
> <benedict.m.holland@gmail.com> wrote:
>> I am not saying it is not well documented. I am saying that it isn't ACID compliant, which it isn't, as they
document.
> I *love* the notion of being able to roll back DDL, but it has long
> been common for DDL to *not* be transactional even with some of the
> Big Expensive Databases (such as the one whose name begins with an
> "O").
>
> Up until version 11.something, "Big O" apparently did NOT have this,
> and MS SQL Server didn't in version 2008.

This has always shocked me.  DEC's relational and CODASYL dbms products (now 
owned by Big O, and still being updated) has had transactional DDL for 35 years.

I wouldn't be surprised if their PDP11 predecessors had it 40 years ago.

-- 
Angular momentum makes the world go 'round.