I know it's been mentioned all over the place already, but after reading about the PostgreSQL 7.4 release, including the more detailed changes that Bruce published, I'm very impressed.
On my TODO list for next year (that's 2004, in case you're not sure) it to play with PostgreSQL a lot more than I have in the past.
Posted by jzawodn at November 19, 2003 09:04 PM
It would be great if the "web community" started to use PgSQL a little more. It's a great DB engine but unfortunately it's very hard to find hosting services providing it.
Interesting, how many months will you hold still before you ditch MySQL for any new development after discovering this gem?
I think it will be around 3-4 months. Now when I hear the word 'MySQL' something just silently chokes inside me. Wish the same to you.
I could probably google this but since Julian seems to have already made the switch I'll ask him here. How much pain was involved in switching to postgres from mysql? I already use a db abastraction layer (adodb) in my php code so that should be pretty painless.
Also, for a website that whose queries are about 90% selects is it really worth switching?
My albieit somewhat limited views on going from MySQL to Postgres. Granted, I have not used MySQL much for several years now, so things have probably changed.
Briefly on changing from MySQL to postgres - it all depends. I was recently looking at a php-based application that used aodb as well. I ported the schema over, and everything went fine. Then I noticed that the code used a bunch of MySQL specific sql statements, which I didn't want to port. I don't know if you'll see any speedup or slowdown in the migration between the two rdbms', because that depends a lot on your schema as well as your queries.
As I don my flame retardant suit...
Overall, one of the nicest things about postgres is that it is truly ACID compliant. Every query, every insert, every everything is wrapped in a transaction. If you do an update on 500 rows, the update doesn't actually happen unless it can happen on every row. So, if you're in the process of updating 500 rows, and someone pulls the power cord, the database rolls back to the state of the database before the update. My understanding is that under MySQL, this is 'optional.'
Having written several php/mysql based web apps, one of the nice things that can be done with postgres is that you can do a lot of the logic of your database within the database, instead of at the PHP level. What does this mean, exactly? Well, you can do constraints on your data, triggers, and views.
In reverse order, a view is simply a 'static query'. So you have that ultra-complex join that you use for something. Instead of doing it at the application level, like you'd have to in MySQL, you can do it at the database level, at which point the RDBMS can do what they do, like caching queries and what not. But once your static query is defined, you can do queries against the query. They're amazingly simple, yet amazingly powerful tools.
Triggers are things that happen when a table is updated or modified or whatever. An more extravagant trigger that I've seen is one that when an order is marked 'shipped', an email is generated with all the shipping information and sent off to the client. (Oh. Did I mention stored procedures, and embedded lanugages?) A more simple example is a 'last updated' trigger. Postgres understands IP addresses. In one of my databases, I have a table containing IP ranges at my company. In another table, I have hosts. I have a trigger on the hosts table that will only allow hosts to be inserted that belong to a network in the network table.
MySQL does some constraints, but not nearly the same amount as Postgres. A more simple example is that you can make sure a row that contains the 'state' table (i.e. NY, CA, PA, NJ) is actually inserted with that particular data. For instance, Nj wouldn't be allowed, because it's not NJ.
Postgres also has embedded language capabilities. There's TCL, Perl, PHP, and PG/PLSQL. These allow for all kinds of things like stored procedures, and the ability to write your own functions. I've written functions to do stupid things like calculate netmasks and so on.
Permissions on Postgres are also very easy to do. The MySQL permission structure always kicks my butt. It always takes me forever to remember how to do it properly (Primarily, because when I started using MySQL, you did it a totally different way. MySQL didn't have 'grant' then).
I know there is more than this, but this is just my $.02 worth of postgres advocacy.
What about speed? From my understand, Postgre has always been more robust, but MySQL has always been amazingly faster. Has PostgreSQL made recent advancements in that department?
Over the years my site has gained more and more PHP with more and more database queries. At certain hours of the day, I can average as many as 90 queries a second. I can afford to go to a slower database right now, but I'm worried that if my customer base doubles in the next year, that I'll end up having to switch back to the faster database.
Although, if MySQL supported subqueries, views, or multi-table updates, I might be able to get rid of a lot of unnecessary queries in my code. Isn't some of this going to be in MySQL 4.1?
Nice things about PostgreSQL that PHP/MySQL users might care for:
- attach your business logic & data consistency code directly to the data with triggers, procedural code & constraints
- if you're a PHP hound, like a LOT of people out there, you can now take the existing logic you've written in PHP, and with minor tweaking, make it available directly in the database (I wonder if PL/PHP can make use of the various PHP accelerators out there...)
i think the only other thing i'd really love to see in PostgreSQL is concrete views. i think the way it currently works is that the query planner sort of "merges" your query with the view query and then executes it. it'd be nice in some cases if the database would actually maintain a table with the data from the view query. i think oracle does this.
Does PostgreSQL have a query cache like MySQL? It'd be nice if their query planner would cache the plans after the first execution...
i'd also like true bind variables (which ties in with a query cache), though they are implemented in a few abstraction layers i believe.
One of the things that people seem to forget is that a lot of newcomers came to web development and found that MySQL was a LOT easier to grasp. As a result, they became productive with it much faster. When I first started using Postgres I had a hard time getting my head wrapped around some of the more basic things. Things like seeing what databases I had or listing the tables. The online documentation didn't help much either.
Ulitimately, Postgres is a lot more powerful than MySQL, but MySQL has mindshare and velocity. Postgres could easily make certain things about how it interacts with the newbie user a lot easier without losing it's power.
This is a lot like the Windows vs Linux argument. One is much more powerful but also more difficult to use. I'm a Linux user (since '97) by the way.
One more thing that they (Postgres) needs to get nailed down is a simple and robust replication scheme. Postgres does have replication now, but it's unproven and to the best of my knowledge, asynchronous. It being asynchronous isn't bad by itself, but it seems to me that in certain environments (Like a lottery application) it doesn't make much sense. If three years ago Postgres had a viable replication solution (not third party hacks off sourceforge) I would've built my last cluster using it.
For the most part, time spent running down MySQL would be better put into making Postgres easier to use, helping newbies with it, and whatever else would be needed to put it's name and use at the fore.
I'm not sure about speed. I've seen stuff that says that they are the same, however, since MySQL doesn't do transactions, it's going to be somewhat faster.
A lot has to do with what your schema, indexing and queries are. Also, obviously, your hardware configuration and so on as well.I know that complex queries on Postgres are faster than on MySQL, but I don't know how much faster they are. There are tools within Postgres (explain analyze...) to analyze your query to see how long something takes, and how long portions of the code take to execute.
Postgres also has rather extensive online help capabilities, something that MySQL still lacks to any real degree (I use 4.x for Movable Type).
However, the other posters are correct in that a lot of the logic that you are currently doing in your php could be moved to the database. In fact, this could be done to the point that all you're doing with your php application is viewing the data. Obviously, my explaination here lacks somewhat, but, on some of my databases, I've made them so that I can operate them completely from psql instead of requiring the application. All my regular complex queries live in the database, as well as a lot of the other functionality. With MySQL, I couldn't do a lot of this stuff.
Another $.02 worth.
To Julian Tarkhanov,
the main problem in migrating to Postgres is the lack of support from hosting services out there... nevertheless you should always use standard SQL, thus reducing the problems in migrating to other database engines in the future...
Wow! Jeremy actually has an OPEN MIND about Postgres?? Postgres ROCKS, and after seeing a lot of the comments about its release on slashdot, that seems to be the general consensus.
BDKR,
I totally agree with you about the usability of PostgreSQL. When beginning to use a database, and just learning about databases, tables, fields, and queries, SHOW TABLES is inherently easier to remember than \dt, and makes more sense. The same logic applies to DESCRIBE table rather than \d table. The thing that makes SQL easy to learn is that it's plain english. The various \ commands in psql make it slightly harder to figure out at first.
I'm sure PostgreSQL is great, but Sabre isn't using it. So there.
:-)
I guess Sabre has their heads up their you-know-whats. As if Sabre is of any consequence to anything...
Tim:
What do you mean MySQL does't do transactions? Transacions have been there for a couple years now.
Still waiting for the native Win32 version...Even though the platform can eventually be Linux, I like to do my development on Windows.
Yes, I think Win32 version will be there eventually, and there is even UltraSQL now, but that didn't work too well yet.
So, is there a Win32 version already, did I just miss it?
Ilkka,
This is what I posted about Win32 PostgreSQL on /. :
CommandPrompt Mammoth PostgreSQL for Win32, Mac OS X, Linux
http://www.commandprompt.com/entry.lxp?lxpe=295
dbExperts PostgreSQL for Windows, Mac OS X, Linux
http://www.dbexperts.net/postgresql
PowerGres (threaded Windows PostgreSQL
http://osb.sra.co.jp/PowerGres/introduction-en.php