Weaknesses with MySQL and or PostgreSQL

[Roger Cigol 103]

The sort of question that would be kicked out of Stack Overflow: I am about to embark on a new database project for a customer: Maybe 200,000 records per year in the main table. Typically ten or fifteen users accessing database simultaneously. Running on a server on a local network (possibly move to cloud in the future). Interface to C++ Builder code using FireDAC. All running on Windows platforms. Only for internal use by the customer (not for re-sale) so can use MySQL under GPL. I'm finding it really hard to choose between MySQL and PostgreSQL. Any ideas on strengths /weaknesses I should look at whilst trying to make this decision?

[Markus Kinzler 174]

Oracle doesn't care if the software is only used internally. Better use MariaDB instead of MySQL. PostgreSQL  is much more potent than MySQL.

[Roger Cigol 103]

@Markus Kinzler Thanks for your advice. "Oracle doesn't care...." - does this mean my interpretation of the GPL v2 is wrong ie Oracle COULD make us pay the commercial license even though my customer would only be using it internally? or does this mean "Oracle doesn't care so all is well, I can use it " ? "Better use MariaDB" - why do you say that? "....is much more potent than...." - potent is a strange adjective to use here : it would be great if you could expand on what you mean by "potent"..... Your wisdom is appreciated....

[Scott 4]

MySQL can work wonders for simple and large databases but once you try and throw complex queries at it, this where the power of Postgres will make MySQL look like a toy. I would always recommend Postgres over MySQL if you have a choice, it just scales so much better.

[Guest]

MySQL out of the box is a glorified filesystem. It's spread and popularity is due to LAMP amongst other things*.

Oracle, PostgreSQL and Firebird all tote "industrial strength" ACID compliance.

PostgreSQL is syndicated open source whilst the Firebird is managed by a foundation out of Australia.

IMHO the choice between PostgreSQL and Firebird (at the point you are) is more about choosing the governance of the OS project.

 

Personally, i went from Access and Paradox to Firebird 1.0. Never looked back. FB rocks!

 

* MySQL, M$SQL and other "mass" market brands throw in a lot of "candy" and "chrome" into the server engine. That might be convenient for some developers but it degenerates the SQL whitepapers and may very well throw problems at you in the future (when you realize MySQL will not cut it). This is opinionated.

[Stéphane Wierzbicki 45]

I used to work with MySQL (from 2000 to 2012). I used it for our internal Datawarehouse. FWIW, some tables handled over 200 000 records per day (with almost 100 fields).

 

Since 2012 I switch to PostgreSQL for all my new projects. I really like it and to be honest, this database is extremely powerful and easy to maintain. 

[VLDG 1]

2 hours ago, Dany Marmur said:

2 hours ago, Dany Marmur said:

MySQL out of the box is a glorified filesystem. It's spread and popularity is due to LAMP amongst other things*.

Oracle, PostgreSQL and Firebird all tote "industrial strength" ACID compliance.

PostgreSQL is syndicated open source whilst the Firebird is managed by a foundation out of Australia.

IMHO the choice between PostgreSQL and Firebird (at the point you are) is more about choosing the governance of the OS project.

 

Personally, i went from Access and Paradox to Firebird 1.0. Never looked back. FB rocks!

 

* MySQL, M$SQL and other "mass" market brands throw in a lot of "candy" and "chrome" into the server engine. That might be convenient for some developers but it degenerates the SQL whitepapers and may very well throw problems at you in the future (when you realize MySQL will not cut it). This is opinionated.

I agree Firebird is a rock and "200,000 records per year in the main table" will be ok for any database !

 

[wuwuxin 28]

I used to use MySQL Enterprise (yes, I paid $5000/year, mainly for its ThreadPool plugin which is not available in community edition!).  Then I switched to PostgreSQL in 2020.   I never regret. 

 

My postgresql database has daily increment of 8 million records.

Edited May 17, 2021 by wuwuxin

[Markus Kinzler 174]

16 hours ago, Roger Cigol said:

@Markus Kinzler Thanks for your advice. "Oracle doesn't care...." - does this mean my interpretation of the GPL v2 is wrong ie Oracle COULD make us pay the commercial license even though my customer would only be using it internally? or does this mean "Oracle doesn't care so all is well, I can use it " ? "Better use MariaDB" - why do you say that? "....is much more potent than...." - potent is a strange adjective to use here : it would be great if you could expand on what you mean by "potent"..... Your wisdom is appreciated....

 

Oracle has an interest of making profit. So they may interprete the licences more strictly than others. Do you deliver/install the MySQL Server. Is this the case you need a commercial licence (as long as your application isn't GPL).

If the Server is beeing installed by your customers und you "only" use a existing installation then your customers may need a licence. (In this case olso the client dll must be excluded from your program)

MariaDB is a fork of MySQL with a better licence. which can be used as replacement for the MySQL Server (with lesser risks for you and your customers.)

A switch to and another DBMS like PosGres or FireBird ( which I also would prefer) may need adaptions in your program.

[Guest]

8 hours ago, Markus Kinzler said:

A switch to and another DBMS like PosGres or FireBird ( which I also would prefer) may need adaptions in your program.

Witch is one of the points of avoiding MySQL, MariaDB and other (M$SQL) engines that just throws in "neat" stuff to make non-devs feel like devs.

Keep to the standard (select an engine that does, firebird must be top-3 here) as much as possible and migrations may work.

 

Also, Interbase. Their new change-views looks like a boon. But they will have to do a LOT in order for me to switch from FireBird*.

 

* The cost is actually not an issues here. I "make" my clients donate to FBSQL as much as Interbase wold cost anyways.

* Cost calculations are very tricky for deployed extensive systems. Costs rise heavily in the details where the devil is. Things like null handling et.al.