1

I've used MySQL a lot over the past seven years and my experience has generally been, "ignore stored procedures and putting any sort of logic in the database. Keep business logic in code and keep the database as a dumb data store."

I'm building out a new project, currently with Python + Flask + MySQL. The MySQL database is mostly dealing with reads. There's one main table -- a list of items to display on the web app. I never mutate the rows in this table after initially creating them. This list of items to display is updated once daily via a cron script that grabs a client data feed.

Would PostgreSQL offer some benefit over MySQL in this scenario? (heavy volume of reads, writes restricted to one batch per day)

I'm currently hosting it on Heroku using ClearDB for MySQL if that makes any difference.

ashgromnies
  • 3,266
  • 4
  • 27
  • 43
  • When I have to use MySQL I always feel "restricted" because of all the SQL limits that MySQL imposes on me. But your project sounds as if it does not make use of any of the advantages a modern DBMS can give you, so it probably won't matter what you use. –  Mar 06 '12 at 17:22

4 Answers4

3

There are quite a few advantages to using PostgreSQL over MySQL, and there are quite a few advantages to using MySQL over PostgreSQL. Which one you choose depends entirely on what you're trying to achieve and what requirements you have for the project.

In your case, if the database is just a "dumb data store" you shouldn't see any difference at all in how MySQL behaves vs. PostgreSQL. There's a lot to be said for "sticking to what you know", so if you're more familiar with MySQL versus PostgreSQL... use MySQL.

Also, it's never a bad idea to use a proper database abstraction layer so that if you need to change the database platform in the future, you don't have to rewrite all your DB code.

Justin ᚅᚔᚈᚄᚒᚔ
  • 15,081
  • 7
  • 52
  • 64
  • I guess I'm just confused at when I'd want to use PostgreSQL over MySQL. I've always thought it was bad practice to put logic, code, and integrity restraints(e.g. foreign key checking) inside your DBMS. At least that's what I've learned -- it's often easier to check those constraints in code, and easier to maintain if you know all your business logic is in code as opposed to being split between code and database. Any project I've worked on where we've decided to use database integrity restraints, stored procedures, etc. has quickly turned sour. – ashgromnies Mar 07 '12 at 16:17
  • 1
    @ashgromnies, The location of business logic is a topic that cannot be adequately discussed in a comment. However, I'm obligated to say that your assumptions about it are wrong -- it is generally accepted that the database should be as smart as possible. The problem is that most developers don't know how to get this right, or where to draw the line between data logic and UI logic. I would strongly suggest you do some more research in this area rather than taking the blanket approach of avoiding integrity restraints and SPROCs entirely. For example, http://stackoverflow.com/questions/119540/. – Justin ᚅᚔᚈᚄᚒᚔ Mar 07 '12 at 20:32
1

I usually work on MySQL and one of the things I most miss from PostreSQL is the power and simplicity of window functions. Of course, I know this is not enough to choose one DBMS over another :) but you can read more on this in this link or maybe this other link and analyze in more detail which DBMS would be more suitable for your problem.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
0

Your app design is one that is perfectly suited for MySQL. If you're concerned about possible scaling issues, I'd suggest looking at a NoSQL solution instead of PostgreSQL. Not that there's anything wrong with PostgreSQL (far from it!), but for your use case, a high-speed distributed data store would be a good fit, and most NoSQL solutions are easier to set up and get working than trying to set up a DBMS cluster.

TMN
  • 3,060
  • 21
  • 23
0

"I've used MySQL a lot over the past seven years"

That's your answer there. Without a compelling reason to switch, don't.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51