Tag: Free Software

Still quite enthusiastic about my success with PostgreSQL erlier today and after reading the first comment on that entry, I think, it’s time for a little list describing the highlights why I prefer PostgreSQL to mySQL and another one describing what mySQL does better:

PostgreSQL

• psql, the command line tool for accessing the database is much better than the mySQL pendant. What many don’t seem to know is x. Try it and you will ask yourself, why mysql can’t do that. Also, I really like that a pager is invoked when dealing with large result sets. MySQL does not do that either
• The license. While I certailny prefer any free software license to any proprietary one, I much prefer the more free BSD one. But I better leave the flam^Wphilosophying about this to others…
• All those “professional” database-features like VIEWs, stored procedures (which can even be written in Perl or Python), triggers, rules, enforced referential integrity and all that stuff. I could never ever imagine going back to a database without VIEWs. Those things are so incredibly useful both for much friendlier interface to complex data and integrating different pieces of software.
• The community around PostgreSQL is very strong. Reading the “general” and “developers” mailinglists is very interesting and many times provides a very good insight in database design

Back in 2002 where I was working on the new adsl.ch, I used VIEWs to merge satisfy the needs both PostNuke and phpBB2 had concerning their table containing the user accounts. With a view and a little bit cusomized scripting I was able to integrate both without the need for any patching around in either of them which makes applying security-updates so much easier. This is where I deceided that I will never use anything else but PostgreSQL for my database needs.

The mySQL list

• mysqli is an object oriented interface for PHP scripts directly built into the language (and thus fast). Too bad it requires MySQL 4.1 as Gentoo does not have fitting ebuilds yet. And don’t get me wrong: Postgres’ interface is not bad either.
• Seems easier to handle. Just install and run. ALTER TABLE is much more powerful than in PostgreSQL, so changing the structure after the fact is easy. Nothing must be configured to get quite the optimum performance
• Clustering built into the core of the database, though it’s still a master-slave replication which provides fail-safety, but no (real) load balancing.

ALTER TABLE in PostgreSQL 8 is about as powerful as the one of MySQL, but PostgreSQL 8 suffers from the same problem as MySQL 4.1: No Gentoo ebuild. Here, on my iMac I’m already running the latest BETA of 8.0

The decision to go with PostgreSQL is an easy one: None of the advantages of MySQL are big enough to outweigh the missing features. Oh and if you ask for benchmarks and tell me that PostgreSQL is slower than MySQL, let me tell you this: While I doubt that this statement is still true (mySQL got slower due to the transaction support and PostgreSQL got much faster), I can say one thing for certain: PostgreSQL is fast enough for my needs. What is it worth giving up data integrity and writing lots of dirty code that should really be stored directly in the database just because of a percent more performance or so?

Another thing is how those systems perform under high load. While I certainly know that PostgreSQL handles it well and stays fast for many more concurrent connections, I always hear problems form people using mySQL: Corrupted tables (sometimes beyond repair), hanging connections,… Nothing I want to happen to me even if it would mean to live with one or two percent less performance under unrealistical-benchmarky load.

Oh and everything I told about performance is quite un-scientific. While I did some load-tests with Postgres, all my expirience with MySQL under same conditions comes from other people. I never tried it myself. Why should I? PostgreSQL is perfect.

I told so before, but I have to again: PostgreSQL is incredibly cool.

Today I had this job of importing around 11’000’000 datasets distributed to 15 tables. 10 millions of them went into one big table. And after importing the whole thing should still respond fast to queries involving JOINS with this large table.

What surprises me: After a bit tweaking of the settings (one of them would be moving the beast to a partition where there’s enough space on to store the indexes ;-) ), the queries I did on a much smaller amount of data before, remain as fast as ever. PostgreSQL really makes great use of its indexes

Granted: Importing all those datasets was somewhat slow (I could and can not use COPY because I’m just receiving differences), but tweaking around with the indexes helped a lot (tip: drop them while inserting)

While processing the import, Postgres still was as responsive as ever while working with other parts of the database.

I know that all this is nothing fancy – I mean: I expect nothing less from a good RDBMS, but still… it’s amazing how good and flawless this worked and how fast it is.

Maybe, I could get faster INSERT/UPDATE performance if I’d be using MySQL instead, but I absolutely want to use all those features a real database should have that MySQL lacks: Views, referential integrity, subselects (still using 4.0 until Gentoo releases a more current ebuild).

Yes. Postgres is just great.

Just for your interest:

pilif@fangorn /home % sudo du -chs pgdata
1.8G    pgdata
1.8G    total

And it’s still as fast as your common little webboard-application. I still cannot quite believe it.

last time I talked about my favourite extensions to Firefox and while this list is outdated already (I’ve got some more on the list), I think it’s time for the Thunderbird list:

• Enigmail is the all-you-need solution for encryption matters. Unfortunately, not many of my common adressees have GPG-keys already, but maybe that’s going to change. Important emails I’m sending out are signed.
• QuoteColors is a must-have for me as only with this extension Thunderbird complies to point 7 on my list of features I want in a mail client.
• ClearSearch re-adds the Clear-button to the search toolbar. It’s quite click-intensive to clear the filter without this button and it even serves as another indicator of whether a filter ist active or not (it’s disabled, if not).

So: Not many Extensions, but absolutely important to me. I wonder: What are others using? The same? More? Different?

Friendly error messages?

Then tell me, what PHP wants to say me with this:

Parse error: parse error, unexpected T_PAAMAYIM_NEKUDOTAYIM in /home/pilif/…/include/simple_news.inc on line 131

Whatever. Back to work.