Slashdot Mirror
PostgreSQL Wins LJ Editor's Choice Award
Quickfoot writes "PostgreSQL has won the LJ Editor's choice award for database servers the second year in a row and three times total (2000, 2003 and 2004). With the upcoming features in version 8.0 PostgreSQL is posed to do even better in 2005."
I am very very impressed by PostgreSQL. I am waiting for the geeks to slap a programmable GUI onto it. I know there are several that I can mention here, but none comes even half close to what M$'s Jet engine has with Access. I once tried to put together one with PHP but the project became very complex for my managing. I abandoned the effort. Slashdotters, can we start another project to put a decent GUI onto this DB engine? When that is done, business logic can be programmed at form level, reports can be done in XML and published to PDF, even better, error handling at form level with input masks etc can be done. And before you go on, I'd like to remind you that there is Kexi, pgAdmin and so many others. What do you think?
Postgres has caused me alot of stife in installation, and their dump/restore scripts to this date still seem unreliable, which sure, may be due to that Debian Stable still being on 7.2 and I've never had the necessity to try out more recent releases.
However, there's many very cool things I hear you can do with it - I simply don't use them :P
I also think that the fact that postgres and mysql both compete is great - it motivates both teams to continually improve.
In summary, I have found MySQL to work perfectly in creating a leading edge content management system (http://www.silverstripe.com/); one where the development, selling and implementation of it has created jobs and forms a whole business. But I'm sure if Postgres had walked in the door four years ago and I had a more positive experience, I'd be touting that over Mysql in the same subjective vein.
If all you are doing is presenting web content without much in the way of heavy-duty transactional requirements (ie., no money is changing hands, mostly doing output as opposed to input), then MySQL is fine. Of course, PG would be fine as well. I don't get all of the zealotry on both sides. They are two fine databases, most stuff is not "enterprise" level requirements, so no suprise that you see MySQL all over the place. That doesn't make PG any "better" or "worse"!
A house divided against itself cannot stand.
About the only thing that PostgreSQL is not is auto-adaptive.
To some extent its query analyzer will "learn" about the tables in your database when you run a vacuum analyze.
After any major change in the number of rows in a table, I always run a vacuum analyze. Aside from cleaning up some of the space leftover (only a vacuum full can clean up everything, but that requires locking the database) it will record new values for the relative sizes of tables, which the query analyzer then uses to determine join orders and such.
It's not fully adaptive, but its a great help.
You know, generally the complaint about software patents being unreasonable has to do with how easy it is to do patented stuff. Have you considered that the only reason you know how to do multimaster asynchronous merge replication may be because someone figured out how to do it and told everyone else in a patent? So, what patents apply and when do they expire?
Whats the problem with running VACUUM? You just start VACUM as a cronjob. It's not as if it stops the database from working.
You are confusing checkpoints with savepoints.
The most expensive task of a database is I/O. If you have a transaction that alters data in 3 places in two tables and that transaction commits, the database has to wait until the data has hit the disk. (Data hitting the disk is the Durability part from ACID.) With an average disk latency of 7 miliseconds doing 6 I/O operations translates to 42 miliseconds waiting before the database can confirm the commit to the client.
Since this is way to long, these 6 writes are pushed out to the buffers in RAM, but writing of these changes to disk is not required. Instead, all six changes are sequentially written to a special file, the Write Ahead Log, and that log file hitting the disk is sufficient to guarantee durability. That is only 1 I/O operation, a significant win in performance.
Obviously at some point the buffers have to be written to disk anyway. This happens when they are so inactive that they drop out of cache the normal way. But to know that all changes to a file have been pushed out to disk, which we need to know for certain before we can recycle the WAL, files are occasionally forced to disk. This forcing to disk is called a checkpoint, and it happens every X seconds, when there has been a certain amount of activity or when a superuser issues the CHECKPOINT command.
The code examples you provided are a way of creating a SAVEPOINT, a point inside a transaction that you can roll back to without having to roll back to the beginning and restarting everything. The prefered syntax is:
BEGIN TRANSACTION;
SELECT foo FROM bar;
SAVEPOINT selectOK;
INSERT INTO bar (foo) VALUES (1); -- duplicate error, roll back to savepoint
ROLLBACK TO selectOK;
UPDATE bar SET foo = foo + 1 WHERE xyz;
COMMIT;
"What really irks most experience database developers about mysql is that mysql abandoned decades of standards and standard features - while insisting that 90% of the users didn't need transactions, triggers, views, etc. That's disingenuous misinformation."
What's even worse is that they are now scrambling to add all that after years of arguing it was pointless to do so.
After they add triggers, views, stored procs what they will have is just another OSS database in a field already crowded with extrememly capable databases such as postgres, sapdb, firebird, and now ingres. They should just keep mysql the way it is, a lightweight SQL interface to the filesystem. There is a role for that in building read-mostly web sites.
evil is as evil does
uses direct / raw i/o
That has been hashed out on the mailing lists time and time again.
PostgreSQL is not a filesystem.
Where is the real use-case? There is a huge amount of extra code and extra bugs, and a minor performance optimization at BEST.
Additionally, PostgreSQL would then not be able to make use of OS disk buffers.
So, it would ONLY be useful as a MINOR performance optimization on PostgreSQL only machines, running NO other applications. All that at the cost of so much code and so many bugs?
You could just as well argue for any application that it should implement it's own OS and filesystem.
That being said, I'm sure that some people benefit from the Orcale feature. I doubt you'll see it in PostgreSQL any time soon though.
Social scientists are inspired by theories; scientists are humbled by facts.