Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL vs MySQL: an apples to oranges comparison

ledgersmbdev.blogspot.co.uk

Post reply on HN
Print view View original

61–70 of 71 posts

Re: PostgreSQL vs MySQL: an apples to oranges comparison

#61

Post by lobster_johnson »

It's worth mentioning that while Postgres does promote the idea that the database is the gatekeeper to the data -- where you're supposed to manage data with stored procedures, triggers and so on -- it's not in any way mandatory, and not necessarily the way people prefer to use Postgres.

Personally, I lean towards treating the database as a passive data store controlled by the application, as opposed to using the database as an application platform. For example, validating and normalizing is done in the client application.

While I would also be happy about putting logic in the database, this currently requires a split of the data logic between the application and the database in a way that I don't like: Stored procedures, functions, triggers and so on are all persisted alongside data. (Internally they are treated very much like data by the database, in fact.)

This is unlike the form of the application, which manifest itself in its source code, which resides in a Git repo, and so on. The database code is always "live", whereas the app code runs at my behest. There is a reason why the article uses "create or update procedure", not "create procedure".

So if I move some of my code into stored procedures, triggers, rules, checks, etc., I have to push this code to the database using SQL scripts. This drives the code into a kind of dark, murky hole with respect to visibility and versioning. What code am I running? I'd have to look into the catalogs to see.

Also, can I run different versions of the code concurrently? Not in the same database, apparently?

And how do I seamlessly, elegantly upgrade the code? Sure, I can manufacture SQL scripts that do the "create or update procedure" dance, but what if I removed a procedure? Or what my database schema changes -- then my code has to move along in tandem with the schema.

As far as I know, current database migration tools just aren't very good. In other words, for me, personally, I don't think the toolchain is quite there. It sounds like a nightmare to manage. Having dealt with libraries such as PostGIS, which are built on functions and stored procedures, I know how gnarly this system can be.

Personally, I would like to see a database that supported non-persistent data logic. It could be plugging in actual source code (put foo.rb in a folder, database will run it and export its methods as database functions) or interfacing through an API (instead of "create trigger" to create a trigger, have the database call my REST API /user/validate for each row) or similar.

Re: PostgreSQL vs MySQL: an apples to oranges comparison

#62

Post by eurleif »

lobster_johnson wrote: post #61

It's worth mentioning that while Postgres does promote the idea that the database is the gatekeeper to the data -- where you're supposed to manage data with stored procedures, triggers and so on -- it's not in any way mandatory, and not necessarily the way people prefer to use Postgres. Personally, I lean towards treating the database as a passive data store controlled by the application, as opposed to using the data…

>Or what my database schema changes -- then my code has to move along in tandem with the schema.

Well, yes, but that's true if you don't use stored procedures, too. And don't stored procedures make it a bit easier, since you can update the schema and code all at once (atomically, even, in PostgreSQL)?

Re: PostgreSQL vs MySQL: an apples to oranges comparison

#63

Post by einhverfr »

fdr wrote: post #44
VLM wrote: post #21

Earlier quoted context omitted.

"If you write small - medium rails apps" If you are writing using a MVC like rails, mysql is easier because the M is solely in the rails app rather than some of the M being in the rails and some of the M being in the DB configuration. Theoretically there's no postgresql issue if you're comfortable splitting design and config stuff into two areas is acceptable IF all the devs are also DBAs, or if you are careful to ne…

For many small Rails web applications, details having sane (and rich) datetime support in the database is both nearly invisible (turning bugs into errors, and working about the same otherwise) and very useful. The same could be said about silent truncations of strings (one can get the same behavior in Postgres by using a explicit cast, or just a built-in function). LedgerSMB -- an author of which wrote this article -…

Certainly it isn't for everyone.

The thing is, LedgerSMB aims to be a database for many apps. You can't do that and still have an app that runs on many databases.

I am not convinced that grades in between are all that great. I think you have to choose the degree of portability you want first and the level of db-as-api you want first and then program to that.

Re: PostgreSQL vs MySQL: an apples to oranges comparison

#64

Post by einhverfr »

lobster_johnson wrote: post #61

It's worth mentioning that while Postgres does promote the idea that the database is the gatekeeper to the data -- where you're supposed to manage data with stored procedures, triggers and so on -- it's not in any way mandatory, and not necessarily the way people prefer to use Postgres. Personally, I lean towards treating the database as a passive data store controlled by the application, as opposed to using the data…

Personally, I would like to see a database that supported non-persistent data logic. It could be plugging in actual source code (put foo.rb in a folder, database will run it and export its methods as database functions) or interfacing through an API (instead of "create trigger" to create a trigger, have the database call my REST API /user/validate for each row) or similar.

The question is where you put your API. The biggest tradeoff I see is whether you can run on many different RDBMS's or whether you are tied to one RDBBMS. But similarly the question becomes to what extent your db is tied to the application and to what extent it can be used safely by many apps.

So that's a big tradeoff. The rest can be solved the same way you solve the problems elsewhere, and there are some advantages to being able to put your unit tests in db transactions and roll them back.

There's a second big issue too which rarely gets noticed. Doing application-style development in the db rarely works well. To do this well, you really need to make your queries front and center, write good, clear queries, and so forth. I have seen what happens when app developers try to write stored procedures and I don't really recommend that.

Re: PostgreSQL vs MySQL: an apples to oranges comparison

#65

Post by jaakl »

I have a story about performance of two of these. I maintain a Drupal website which under MySQL. Single server system, not very high load. But recently, last Monday it was in news and load increased some 10 fold. People started complaining that it does not respond, or gives "offline" message. So I managed to login to server and it had load average 130 or so, mostly under MySQL. Drupal gives no mercy to DB for sure. It just so happened that I had a recent copy of the site with PostgreSQL, as I had planned to switchover to use PostGIS, but not everything was working there yet. But site was down anyway, so I saw good option to test it against high load, I reconfigured site and restarted Apache to direct traffic to the new database. After minute or two the load average was in the range 5-6. Not normal for 2-core server really, but significantly lower than before, and the site was saved, and I was not able to perform the load test with real users. PostgreSQL was not tuned at all, just plain yum installation of latest 9.1.4, how much MySQL tuning was done I do not now, it was set up before my time. But it smashed my basic belief that MySQL should work better for large number of simple transactions. Stupid thing is that now I know less than before: I don't know any case where MySQL could preferred.

Re: PostgreSQL vs MySQL: an apples to oranges comparison

#66

Post by lmm »

einhverfr wrote: post #58
lmm wrote: post #51

Earlier quoted context omitted.

My first job was at a company that made the UK's fastest growing list something like five times in a row. Eight years after being founded by two guys in their college room it was sold for GBP100m. Prior to that sale we never had a dedicated DBA. Our two and a half sysadmins kept MySQL up to date and handled the replication setup for teams that didn't want to do it themselves (which was perhaps 2/3). Table layout and…

What do you think the job of a DBA is? I have one customer who has a couple of sysadmins do all that for both MySQL and PostgreSQL servers. If that's all you need, you probably don't need a dedicated DBA unless you are running Oracle and that's just because with Oracle your DBA can always find something to do. If MySQL can be said to make DBA's obsolete because it doesn't take that much maintenance, Informix beat the…

>If MySQL can be said to make DBA's obsolete because it doesn't take that much maintenance, Informix beat them to that by a few decades.

Right, but is that true for Postgres? If companies that use MySQL generally don't employ dedicated DBAs, and companies that use Postgres generally do, then it's fair to say this might result in some bias when dedicated DBAs compare the two.

Re: PostgreSQL vs MySQL: an apples to oranges comparison

#67

Post by taligent »

jaakl wrote: post #65

I have a story about performance of two of these. I maintain a Drupal website which under MySQL. Single server system, not very high load. But recently, last Monday it was in news and load increased some 10 fold. People started complaining that it does not respond, or gives "offline" message. So I managed to login to server and it had load average 130 or so, mostly under MySQL. Drupal gives no mercy to DB for sure. I…

Did you try Percona or Twitter/Facebook's version of MySQL ?

Re: PostgreSQL vs MySQL: an apples to oranges comparison

#68

Post by [deleted] »

jaakl wrote: post #65

I have a story about performance of two of these. I maintain a Drupal website which under MySQL. Single server system, not very high load. But recently, last Monday it was in news and load increased some 10 fold. People started complaining that it does not respond, or gives "offline" message. So I managed to login to server and it had load average 130 or so, mostly under MySQL. Drupal gives no mercy to DB for sure. I…

[deleted]

Re: PostgreSQL vs MySQL: an apples to oranges comparison

#69

Post by einhverfr »

lmm wrote: post #66
einhverfr wrote: post #58

Earlier quoted context omitted.

What do you think the job of a DBA is? I have one customer who has a couple of sysadmins do all that for both MySQL and PostgreSQL servers. If that's all you need, you probably don't need a dedicated DBA unless you are running Oracle and that's just because with Oracle your DBA can always find something to do. If MySQL can be said to make DBA's obsolete because it doesn't take that much maintenance, Informix beat the…

>If MySQL can be said to make DBA's obsolete because it doesn't take that much maintenance, Informix beat them to that by a few decades. Right, but is that true for Postgres? If companies that use MySQL generally don't employ dedicated DBAs, and companies that use Postgres generally do, then it's fair to say this might result in some bias when dedicated DBAs compare the two.

My sense is that companies choosing to use Postgres are more likely to have dedicated DBA's before Postgres is deployed. In these companies the move to Postgres is often DBA-initiated.

In general, though, DBA can mean a bunch of things. It can range from a dev-ops kind of role to something like a sysadmin kind of role and a bunch of things in between. One thing I think we see industry-wide is that strict specialization in DBA tasks seems to be on the decline and for good reasons. It is a move I think from a strictly parts-oriented, details-centric operations approach to a big-picture, approach where the ability to communicate across teams is helpful.

Also regarding the BIG users of Postgres, the DBA-like people I have known who have worked there have been part-time DBA's and part-time C programmers doing things like porting Postgres to new platforms or building new replication systems. As far as I can tell, the dedicated "nothing-but-a-dba" is something that exists mostly waning.

Re: PostgreSQL vs MySQL: an apples to oranges comparison

#70

Post by einhverfr »

jeffdavis wrote: post #47
VLM wrote: post #18

Quick summary, assuming you know what MVC stands for: Mysql - Model is in your code. PostgreSQL - Model is at least partially in your database. There is a HUGE mistake in the article in the assumption that WRT the model design, that the database always knows best. Its possible to come up with weird situations where you just want the DB to store stuff and not nanny you. Consider a database of actual, real world, grave…

"Consider a database of actual, real world, gravestone inscriptions. If someone's gravestone stone has "1890-02-30" inscribed on it, I know thats wrong but I don't care, I need to store it exactly as is for historical purposes, I don't want a DB crash or need to recompile postgres to accept it, I don't want to force the users to falsify gravesite records, I don't want to have to store as a CHAR or VARCHAR and have to…

In one of my other posts in the object-relational series I noted that select * has very different implications in an object-relational vs a strictly relational model. In a strictly relational model you want your SQL query to define your data structures on output. In an object-relational model often times you want your data structures to be formed properly so the db can do other object-relational stuff with them later. So there select * becomes very useful as a way of ensuring that the data structures on output can be simply re-used later.

Of course if you are doing pure physical storage queries, select * is probably not what you want but if you have a logical model built up, you may want to do select * from it in order to ensure that your output matches some specific set of rules.

Post reply on HN