Is there any reason to avoid using postgresql for small web apps? That
is, is it’s
overhead so large that the lighter MySQL will work substantially better
for small apps on
small machines? Or are they very similar in performance and
configuration?
I’ve used MySQL but postgresql has its appeals and I’m wondering if I
need to keep MySQL
around at all.
I’ve been using PostgreSQL for a while now and I’ve never seen any
performance issues. My blog runs on Typo backed by PostgreSQL and my
latest rails app (http://wishlists.sitharus.com/ - very much in beta
at the moment) runs on Postgres.
As for the Windows Unicode issue, Postgres on Windows is a very new
system, it’s quite possible there are issues they still need to work
around. I don’t use Windows, so I wouldn’t know.
Is there any reason to avoid using postgresql for
small web apps?
None at all, IMO.
That is, is it’s
overhead so large that the lighter MySQL will work
substantially better for small apps on
small machines?
I find PostgreSQL to be quite nimble. It’s my MySQL
process that seems to inexplicably use CPU cycles,
even when it’s not busy.
Or are they very similar in
performance and configuration?
I find PostgreSQL performs as well as, and probably
better. I also find it easier to configure and even
compile from source. PostgreSQL is even easier to
admin, since auto-vacuuming has been integrated into
it.
I’ve used MySQL but postgresql has its appeals and
I’m wondering if I need to keep MySQL
around at all.
Well, unfortunately there are many apps out there that
are coded only for MySQL (like Wordpress)…
On the other hand, Postgresql claims that “Windows
does not support Unicode” and you can’t
have Unicode fields on postgresql on Windows. This
is a big mistake. See:
Robby,
I’m currently using MySQL, and one of the things that holds me back
about
PostgreSQL is CocoaMySQL, which is teh bomb, and rocks :). Do you know
of a
similar solution for PostgreSQL and an OS X client?
On Wed, 2005-11-16 at 21:16 +0200, Warren S. wrote:
Is there any reason to avoid using postgresql for small web apps?
That is, is it’s overhead so large that the lighter MySQL will work
substantially better for small apps on small machines? Or are they
very similar in performance and configuration?
I’ve used MySQL but postgresql has its appeals and I’m wondering if I
need to keep MySQL around at all.
PostgreSQL works great on small to large. I use it on simple stuff like
my blog, rubyurl, my bands websites for just a guest book… and I’ve
worked on systems that had a few TeraBytes of data in the past. Several
of our client projects at PLANET ARGON involve migrating from existing
MySQL schemas to PostgreSQL. Rails has actually helped speed up our
migration process to about 60 seconds[1] of waiting time… and maybe
5-10 minutes of getting AR::Migration setup.
PostgreSQL is teh bomb…and it works GREAT with PostgreSQL.
The license is also a bit more attractive for commercial usage.
I have been using MySQL 5 with great success! Thought about switching,
but the MySQL license with 5 is much nicer for commercial use.
Warren S. [email protected] wrote: Message Is there any
reason to avoid using postgresql for small web apps? That is, is it’s
overhead so large that the lighter MySQL will work substantially better
for small apps on small machines? Or are they very similar in
performance and configuration?
I’ve used MySQL but postgresql has its appeals and I’m wondering if I
need to keep MySQL around at all.
Other nice MySQL tools are avaialble, for free, from MySQL itself,
are MySQL Administrator and MySQL Query Builder. Both are excellent
and run on OS X.
The “Big mistake” is in the Postgresql FAQ in saying that Windows
doesn’t support Unicode.
XP supports Unicode very well indeed. The FAQ I referenced prattles on
about Slovenian
code pages, of all things. Windows “Code pages” have been superseded by
Unicode. If you
go to http://msdn.microsoft.com and search for “Unicode” you get an
eyeful.
Having defended the undefendable, however, I’d like to know if
postgresql really still
doesn’t support Unicode on windows, as I plan to develop on WindowsXP
and deploy on Linux.
Robby,
I’m currently using MySQL, and one of the things that holds me back
about
PostgreSQL is CocoaMySQL, which is teh bomb, and rocks :). Do you know
of a
similar solution for PostgreSQL and an OS X client?
Dave
I’m a command-line junkie for SQL.
pgadmin exists… but I don’t use it myself.
With ActiveRecord::Migration + script/console, who really needs to do
much in the way of crazy sql stuff anymore?
The license is also a bit more attractive for commercial usage.
This can be pretty important for certain commercial uses. I’m fighting
with a commercial product now that chose MySQL as a base and I’m rather
disappointed that I’m forced to pay a yearly license fee as an end user.
I like the fact that PostgreSQL is built from the ground up as a real
SQL engine, with lots of features. It’s SQL syntax is standards based
and not as arbitrary as some other implementations. Assuming you are
properly using indexes, it is mighty fast for tables with many rows.
Something I always do it tweak the service config to give it tons of
memory, too.
I think most distinctions which gave MySQL an ‘edge’ in the past are
moot pionts now. It’s not significantly faster (if at all), it’s not
massively more popular (that is to an extent where it impacts support
and debugging, anyway), and companies seem to be less sensitive to
having to pay something to make them feel warm and fuzzy.
Is there any reason to avoid using postgresql for
small web apps?
None at all, IMO.
I much prefer postgresql for all sorts of reasons. The only downside I
can think of is backup/restore is a little more straightforward on
mysql. Especially backing up and restoring a whole series of database in
one go. We had to write a little script to do that, but maybe its easier
these days…
Oh yeah and sometimes, even with 8.0 it’s a pain to change column’s
datatype, but maybe that holds true also for mysql if it’s using views
and stored procedures etc.
But I much prefer it over mysql as it has nice features and is more
standards compliant. In mysql for instance you can (could?) have
datetime values like 0000-00-00 00:00:00 and the whole set and enum
things are just ugly.
I don’t think any of your worries have a real foundation, but IMO for a
small non critical webapp it really doesn’t matter which one you use.
Pick
the one you like and use it.
Browsing around the web, I see many outdated comparisons of MySQL
and PostgreSQL, but no comprehensive pros/cons between the two
recent platforms, MySQL 4.1.x (and 5.0) versus PostgreSQL8.1. In
particular, I wonder about contentions that:
PostgreSQLhas better data integrity than MySQL. This would make
me opt for Postgres if I felt it were true. MySQL, though, has a
pretty impressive array of large businesses using their software.
Is there an architectural reason why Postgres 8.1 would be better
than MySQL 4.1.x InnoDB for data integrity?
ACID (ACID - Wikipedia is a fairly good outline).
The only real ACID backend MySQL has (InnoDB, nothing else is really
ACID) has recently been purchased by Oracle, who aren’t exactly best
mates with MySQL AB.
Oh, and MVCC means Postgres is very fast with transactions.
MySQL is faster than PostgreSQL. I believe this is mostly a
MySQL MyISAM comparison, so it would only be useful for aspects of
your web app that didn’t need transactional support.
Not really true any longer. With some performance tuning - eg
appropriate indexes and cache sizes, PostgreSQL is supposed to be
pretty close to MyISAM’s speed, and that’s with ACID compliance.
Browsing around the web, I see many outdated comparisons of MySQL and
PostgreSQL, but no comprehensive pros/cons between the two recent
platforms,
MySQL 4.1.x (and 5.0) versus PostgreSQL8.1. In particular, I wonder
about
contentions that:
PostgreSQLhas better data integrity than MySQL. This would make me
opt
for Postgres if I felt it were true. MySQL, though, has a pretty
impressive
array of large businesses using their software. Is there an
architectural
reason why Postgres 8.1 would be better than MySQL 4.1.x InnoDB for data
integrity?
MySQL is faster than PostgreSQL. I believe this is mostly a MySQL
MyISAM
comparison, so it would only be useful for aspects of your web app that
didn’t need transactional support.
I have gone with MySQL because I took the path of least resistance from
my
first PHP/MySQL web apps. If 37signals say constraints are good, then I
guess it’s good to have a constraint of being one man with one (very)
small
brain and not that much time already trying to learn parts of an
OS-to-database stack. I’m not going to delegate business logic to the
database in the form of stored procedures. I’m still undecided whether
triggers and stored procedures make sense for my app.
Other reasons I like MySQL:
It’s driven by a company and I believe this is a good way to drive
open
source projects. That’s not to say PostgreSQL lacks orbiting support
companies, but MySQL is a fairly large company.
There’s a good selection of clients. Cocoa was mentioned. I’m using
Webyog
for Windows. DBDesigner and the upcoming MySQL Workbench are nice DB
design
tools.
No shortage of documentation and books, including proven strategies of
replication at places like Yahoo.
(blue sky) Potential for radical upgrade using in-memory clusters.
Yeah,
I’m only dreaming about days when I’d actually need something like MySQL
Cluster, and it’s a very different engine than standard MyISAM and
InnoDB,
but man, the specs look great. In a couple of years when memory is even
cheaper and 64-bit CPUs are the norm for new server boxes, I wonder how
much
traffic a “simple” 3 box cluster could handle.
ACID (ACID - Wikipedia is a fairly good outline).
The only real ACID backend MySQL has (InnoDB, nothing else is really
ACID) has recently been purchased by Oracle, who aren’t exactly best
mates with MySQL AB
That doesn’t sound good. What does this mean for the future of the
InnoDB
engine? It’s open source, so they can’t remove it, but I guess the
support
can substantially decrease.
Not really true any longer. With some performance tuning - eg
appropriate indexes and cache sizes, PostgreSQL is supposed to be
pretty close to MyISAM’s speed, and that’s with ACID compliance.
Have there been any benchmarks published? Given my resources and time,
though, the most applicable benchmark would be performance using
standard
configurations with simple modifications only – no heroic
reconfigurations.
That’s also why I like MySQL. It has a reputation for being fast and
reliable out of the box.
On Thu, 2005-11-17 at 12:06 +1300, Phillip H. wrote:
Maybe I should do some, I have both MySQL and PostgreSQL on all my
machines. They’re not amazing machines, but I’ll see if I can get
some figures if I have time.
Out of the box, PostgreSQL will be slightly slower. It assumes that you
are working with a bigger system and it can be fine-tuned to be fairly
fast.
The real test is when your database grows… MySQL gets slower…and
starves for more CPU…and PostgreSQL is the guy in the back room
playing Doom while it just works.
