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

MySQL vs PostgreSQL

wikivs.com

Post reply on HN
Print view View original

21–27 of 27 posts

Re: MySQL vs PostgreSQL

#21

Post by thwarted »

samuel wrote: post #19
cbrinker wrote: post #9

Earlier quoted context omitted.

Well, I pose to you this question: Would you rather have 1 type of car that the government makes that works pretty reliably and is a middle-ground car, or would you rather have a selection of cars to chose from; sports, truck, sedan, minivan, etc. ? Everyone has different needs. I use MySQL extensively for it's memory-based storage engine, MyISAM for quick & dirty non-escential data i/o, and InnoDB for when data need…

The flexibility is great, but only if grouped with consistency. No foreign keys on MyISAM but yes on InnoDB? Thanks but no, thanks.

No disk backed storage with heap tables but yes on InnoDB? Thanks but no, thanks.

Uh, the definition of flexibility is lack of consistency between the options. Otherwise, it's a false choice.

Re: MySQL vs PostgreSQL

#22

Post by earthboundkid »

cbrinker wrote: post #9
joestrickler wrote: post #5

I don't understand why MySQL has 9 different storage engines. Would someone mind quickly posting the benefits of that architecture as opposed to focusing on one 'storage engine' and making it really good?

Well, I pose to you this question: Would you rather have 1 type of car that the government makes that works pretty reliably and is a middle-ground car, or would you rather have a selection of cars to chose from; sports, truck, sedan, minivan, etc. ? Everyone has different needs. I use MySQL extensively for it's memory-based storage engine, MyISAM for quick & dirty non-escential data i/o, and InnoDB for when data need…

Car analogies?

Well, I pose you this question:

Would you rather have a car that runs into a wall and explodes and kills everyone inside or no car at all? The car is like using car analogies on Slashdot and not having a car is like not using car analogies on YC.

In conclusion and in summary, go back to Slashdot. KTHXBAI.

Re: MySQL vs PostgreSQL

#23

Post by davidw »

cbrinker wrote: post #9
joestrickler wrote: post #5

I don't understand why MySQL has 9 different storage engines. Would someone mind quickly posting the benefits of that architecture as opposed to focusing on one 'storage engine' and making it really good?

Well, I pose to you this question: Would you rather have 1 type of car that the government makes that works pretty reliably and is a middle-ground car, or would you rather have a selection of cars to chose from; sports, truck, sedan, minivan, etc. ? Everyone has different needs. I use MySQL extensively for it's memory-based storage engine, MyISAM for quick & dirty non-escential data i/o, and InnoDB for when data need…

I've always preferred the bicycle analogy: MyISAM is like a bicycle with no brakes. It goes real fast down hills, unless something goes wrong.

Re: MySQL vs PostgreSQL

#24

Post by kylec »

zby wrote: post #20
ashleyw wrote: post #13

Earlier quoted context omitted.

That is a bad idea I think — SQLite locks the database while inserting data, obviously this is bad for a big application. I don't think it was designed for web apps with possibly hundreds of queries a second.

SQLite is a lot faster than than the 'real' RDBMSes on single queries - so this lock on writing is not that painful as it might seem. I think SQLite is really eating into that quick-and-dirty db segment occupied by MySQL - while PostgreSQL is still ahead in the standard-compliant-robust-and-lot's-of-features department.

There was one time that I forgot to specify an index on a table in SQLite and I ran into locking issues with relatively light load. So indexes, where applicable, are a good thing.

Also, you can consider sandwiching several updates between BEGIN and END. I've discovered this dramatically improves performance. You just query "BEGIN" to start, execute your updates, then query "END" to process them.

Re: MySQL vs PostgreSQL

#25

Post by jm4 »

jamongkad wrote: post #16
ashleyw wrote: post #13

Earlier quoted context omitted.

That is a bad idea I think — SQLite locks the database while inserting data, obviously this is bad for a big application. I don't think it was designed for web apps with possibly hundreds of queries a second.

Thank you, but what is the definition of a big application? FaceBook? what if you were building something along FogBugz and the like? or maybe even something like BaseCamp? would SQLite fit those types of applications?

You couldn't get anywhere near the size of Facebook on SQLite. Even a FogBugz or BaseCamp type of application on SQLite could be problematic if you are doing lots of writes.

The driver you use to connect to the database is also very important. If I'm not mistaken the PHP driver was pretty terrible a while back. It may be better now. It didn't handle the locks well which sometimes resulted in deadlocked queries with only a handful of users.

Personally, I wouldn't run any multiuser application doing a significant number of writes on SQLite except during early development. I think where it really shines is when you need a lightweight embedded SQL database without the hassle of having to install something extra. Think Amarok or something similar.

I ran Trac on SQLite for a while. Setup was a breeze and performance was reasonable with 3 or 4 users, but a switch to PostgreSQL was a night and day improvement.

If you're set on using an embedded database you might want to check out Firebird or H2. I haven't used Firebird myself, but I have had excellent results with H2. You have the flexibility to use it a few different ways - embedded vs. server mode and in-memory vs. disk storage - and it is very compatible with MySQL syntax so it's useful as a drop-in replacement.

Re: MySQL vs PostgreSQL

#26

Post by ashleyw »

ash wrote: post #14
ashleyw wrote: post #13

Earlier quoted context omitted.

That is a bad idea I think — SQLite locks the database while inserting data, obviously this is bad for a big application. I don't think it was designed for web apps with possibly hundreds of queries a second.

You are right. But "hundreds of queries a second" are fine if there're mostly _read_ queries. http://www.sqlite.org/faq.html#q5

Yeh I should have mentioned the times I've used SQLite was early in development, or one time when I created a script which would pump tons of data into the database in threads, which resulted in errors and missing data. I'm sure I could have added better error exceptions and keep retrying, but if you translate that to a multi-user app, thats just not going to cut it. A quick change to MySQL fixed it. :)

I do like SQLite, just not for anything where you cant control the writes per second, or need it to be high.

Re: MySQL vs PostgreSQL

#27

Post by mdasen »

joestrickler wrote: post #5

I don't understand why MySQL has 9 different storage engines. Would someone mind quickly posting the benefits of that architecture as opposed to focusing on one 'storage engine' and making it really good?

It isn't about better vs worse. It's about the right tool for a job.

For example, if you needed to store key => value pairs, how would you do that? If you use an array (array[0] = ('key', value')), you would have to iterate over the entire array to find the key you're looking for (Order n), but if you use a hash table, you can do key look-ups in constant time (Order 1).

MySQL's different storage engines provide similar opportunities to use different ways of storing data for different ends. MyISAM has amazingly fast read speeds, but is really poor under write conditions. Sounds like a crappy storage engine, right? Well, what if you have a table of zip codes and their geo-coordinates? How often are you going to be writing new data to that table? Every couple months in a batch update? So, if you put your postal codes in a MyISAM table you can take advantage of high read rates and ignore the fact that MyISAM is terrible for writes since you don't really write to the table.

Post reply on HN