Top 5 Reasons People Dismiss PostgreSQL

Jane Walker writes "In an effort to dispel some of the FUD surrounding this impressive product, this article puts forth several of the most commonplace reasons for a user to dismiss PostgreSQL." From the article: "While PostgreSQL's adoption rate continues to accelerate, some folks wonder why that rate isn't even steeper given its impressive array of features. One can speculate that many of the reasons for not considering its adoption tend to be based on either outdated or misinformed sources."

The name

[smittyoneeach]

"Postgre" is three times as long as "My".

Then again, the P in LAMP has always been about the scripting language, not the database.

MySQL and PHP have been quite the dynamic duo of the internet.

That, and PostgreSQL took longer to have a native Lose32 port.

The fact that you can bring Python right into PostgreSQL for good stored procedure justice seems to go unnoticed.

Web developers...

[schon]

I think first and foremost is that is web developers who don't understand SQL, and so go about happily re-inventing its functionality in their web apps.

99% of the problems that web developers face have already been solved for them, but they think that SQL is just a data dump, and thus see no reason to use Postgres, because they think that MySQL does everything they need. In reality, their apps would be faster to write and easier to maintain if they used SQL features.

It's kind of like perl-syndrome, but on a larger scale.

Nobody's heard of it

[Dlugar]

The biggest reason I've found personally why people don't use postgres is because they've never heard of it. Everybody and their dog has heard of mysql, but I've never found somebody who knows about postgres who isn't actually using it. mysql, for whatever reason, just has better marketing.

Why that is I'm not entirely sure, since ever since I discovered postgres, mysql has been relegated to the role of "use-only-when-a-stupid-web-app-can't-use-anything -else". If I want a toy database, I'll use sqlite. If I want a real database, I'll use postgres. There really isn't much room in between the two.

Dlugar

Interesting article, but not the reasons I hear

[dskoll]

I don't hear those reasons when people dismiss PostgreSQL. The ones I hear are:

• VACUUM is a pain. It's true that VACUUM is annoying, but later releases (especially 8.0 and 8.1) make VACUUM much more tolerable; we have customers whose databases are busy 24/7, and although the load does go up during a VACUUM, the database is still perfectly usable.
• PostgreSQL is slow. That was true, but the 8.x series has improved performance dramatically.
• PostgreSQL is hard to install and administer. Really, I think this is a matter of taste. If you are used to MySQL, then yes, there is a learning curve. OTOH, I'm used to PostgreSQL and find myself having to learn MySQL, and MySQL feels just as weird and unintuitive to me as PostgreSQL might to a long-time PostgreSQL user.

rep-lih-kay-shun

[buddha42]

None of those five had anything to do with why we can't use it. Postgres's replication options are niche afterthought hacks. This immediatly makes it an unacceptable choice for anyone who's reliability or performance needs exceed that of one server. Which is pretty much any system where the cost of downtime is non-trivial.

Or this?

[Ex Machina]

Speed isn't everything but some of these are insane [sqlite.org].

Personal .02

[cliveholloway]

At OSCON, the Postgres people had postcards on their table of whatever their mascot is (I forget) roasting a dolphin on a spit over a fire.

Funny yes, but not something that inspires one to take them seriously.

Ironically, they have a better product on many levels, but that kind of zealotry just plain puts me off.

Why I'd rather not use PostgreSQL

[pestilence669]

I was a big fan... until I needed to use PostgreSQL 7 for a real (commercially available) product. To call it slow would be an abomination of the word. Slow doesn't even begin to describe b-tree insert times. Yes, I tuned the engine and dropped indexes at the appropriate times. Yes, my data structures were relational. Yes, this contradicts some published benchmarks. My use is real world and in reality, PostgreSQL is slow... and a bit buggy.

Nested parentheses in SQL can cause an engine crash. " like ... (SELECT A INNER JOIN B) INNER JOIN ..." But the crashing is tolerable. Hand-holding the query optimizer is not. Quite often, the optimizer gets the query plan wrong. Sending special commands to disable internal features is often the only resort.

While it's true that PostgreSQL is more database than most corporate weenies need, it falls down in moderate write environments. It's best used for systems that write data very infrequently, otherwise it fragments quickly. The only solution to table and database fragmentation is dump & reload.

Vacuum is asinine. Any command that needs to be run periodically under threat of complete and total data corruption should not be. That's right. Only PostgreSQL makes you vacuum or else your transaction ids overflow. This is modern? I'm shocked.

PostgreSQL is to Oracle what Gimp is to Photoshop

Basic marketing

[murderlegendre]

Stupid as it sounds, I don't think most people can intuitively pronounce "PostgreSQL" (I know I can't). It's much easier to say "My SQL" and not risk sounding (or feeling) like a dunce.

Check with the marketing folks - this kind of thing is really important when it comes to general acceptance. When it rolls easily off of the the tongue, it's more likely to be discussed.

Why my company didn't use it

[ZeekWatson]

My company has a semi-realtime application that needs to insert ~200-2000 rows/sec constantly. It isn't true realtime because the db can be shutoff, rebooted, etc and the inserts will queue waiting for the server to come back online.

Approx 1 year ago, we were doing some enhancements on the application and I tried replacing the mysql backend with postgresql. We needed plenty of expert help tweaking postgres to get it to a point where it could keep up with the inserts, however we could not run the vacuum job. There was no way to measure the exact performance difference between mysql and postgres, but I estimate mysql (innodb) to be able to handle 5x the load of postgres.

Why would I want to use a DB server that can only handle 20% the load of a competitor?

No newbie guides

[kyndig]

When I started programming a website, I knew I needed a database. I also knew absolutely nothing about php, sql, or even what they stood for. I was using a Perl based hacked link farm that used a flat-text database storage. Someone then pointed me to a php link farm that used MySQL. The installation text that came with the app was so easy to follow for a newbie, I had the link farm up and running in no time. I went to Books-A-Million a few weeks later, and found many books on PHP, MySQL, php/mysql - and nothing on PostgreSQL. When I finally did read up on RDBMS, found out that PostgreSQL did some functionality that MySQL didn't (at the time); I already had most of my site designed in php/mysql. I looked more into sub-queries in PostgreSQL, but the community structure was so scattered and non-newbie friendly, I decided to stick it out with MySQL (and havn't regretted it once). So my reasons for preference have nothing to do with wanting a windows version, different language, or other such assumption. Instead, my reasons are:
      * as everyone says, the name is catchy: MySQL
      * when I first was introduced to it, and to this day, Michael 'Monty' Widenius takes a personal interest in his work, and is a real down to earth guy ( had the pleasure of emailing him once) [you can probably still see him posting on the mysql dev lists these days..though I havn't followed it in a couple of years]
      * Extensive script language support for web development
      * Books for newbs and professionals (many books)
      * I like their website more..always have

My shallow reasons..

Re:Web developers...

[schon]

perl-syndrome is the nasty habit that perl programmers get into (some might call it a mild case of brain damage), wherein when presented with a problem, say "oh, that's easy - it will only take me 10 minutes to whip up a perl script" rather than using an existing tool that does the same thing, easier, with much less hassle and opportunity for error.

An example:

Newbie-admin asks "how do you make your syslog files go to a different machine?"

Perl-syndrome admin says "oh, that's easy - just write a quick perl script to tail the log files you want, then open a TCP connection to a perl script on the remote machine to write the data. I could write that in 15 minutes!"

Experienced-admin says "Why don't you just configure syslog to send the files to the remote machine? It takes all of 5 seconds."

perl-syndrome admin "TMTOWTDI!"

(and yes, this exchange *really* happened, but it's not the only one - I've seen lots of other examples of guys with perl-syndrome posting perl scripts that could be done much easier with things like sed and awk.)

postgres in astronomy

[hogghogg]

FWIW, I have had very good experiences running postgres in astronomy applications, including for of order millions of galaxies with of order hundreds of attributes in the Sloan Digital Sky Survey [sdss.org]. For scientific applications, open-source is a must, because (a) you have to be sure that the db is doing what you think it is, (b) you have to be able to rely on support or self-maintainance into the asymptotic future, and (c) (usually) you end up having to make small customizations.

Point (b) is especially big: in the Sloan Survey early days we had a proprietary database with our only copy of some of our data; when the company went belly-up (I think is was Objectivity), our data was effectively "encrypted" in whatever proprietary internal format was used by Objectivity and we had no way to reverse-engineer it, and no-one to call.

On point (c), try calling Oracle or Microsoft and asking for customizations that astronomers want. Evidently they don't consider us an important part of their market!?

Why it gets dismissed where I work.

[mpn14tech]

Although where I work we would like to use postgresql, we do not because it does not support case insensitive queries like mysql, sql server and sybase.

Competence required

[linuxwrangler]

One "problem" with PostgreSQL is that it assumes actual competence on the part of the administrator. The default ./configure ; make ; make install is designed to create a system that will actually start up on as many platforms as possible. After that, it is up to the competent administrator to tune it for the specifics of the installation. Using the default PG install in a performance comparison demonstration shows nothing but the incompetence of reviewer.

Have a 2 CPU AMD64 box and 16 GB RAM and fast SCSI drives as your dedicated DB server? Fine, make your settings appropriate for that. Running on a shared P200 with 128M RAM and a slow IDE drive? Different tuning entirely. I have production systems at both ends of the scale.

I am extremely happy with PostgreSQL. It's robust as hell - I've had individual PG connections to the DB up for over a year. On rare occasions I've had a machine running PostgreSQL get unceremoniously killed but every single time when the machine has been restarted, PostgreSQL has started up without any problem. This is not always the case [wikinews.org].

Re:Other things...

[antarctican]

Indeed. And once most people are familure with MySQL and the various tools and language support, there tends to be little reason to switch. PostgreSQL is a better database product, but many (all?) of the features that it's cheering section continue to tell us all about whenever the issue comes up, are simply not ones that the majority of MySQL users want or need. Maybe PostgreSQL fans should target Oracle usres.

Exactly. I know PostgreSQL is a better database engine, but I know mysql, I can't be bothered to learn something new when it seems everything supports MySQL.

I've tried to use PostgreSQL for a few packages which didn't support MySQL, and I just got tired of the learning curve. All the various different executables to do different tasks rather then one shell like MySQL, a permission system which seemed from my limited usage more perverse then MySQL's (and that's saying a lot considering how bad MySQL's is). I'm sure if I learned more there'd be dozens of tricks that would have made the tasks I was attempting trivial... but why? I have better things to do with my time, like write cool code that uses MySQL.

PostgreSQL is the Beta of databases. The superior system but the loser in the race because of reasons beyond it's control.

Re:Autovacuum

[jadavis]

It all depends on the situation, but PostgreSQL gives you a lot of options. Pretty much every database needs to have cleaner processes to clean free space.

With PostgreSQL, you can do it manually, or use autovacuum. You can also set the vacuum_cost_delay to change how much it interferes with concurrent access. Whatever works best.

Re:Other things...

[jadavis]

And what evidence did he have that MySQL users do not want consistency? I know MySQL users who want consistency and type checking, and are impressed when they try PostgreSQL.

Top 5 reasons not to not look at Postgres

[sk999]

Many years ago we looked at Postgres, and the developer at the time said that "he would not trust his payroll to it."

The database has improved a lot since then, and I currently support two Postgres databases, one on Linux and one on IRIX, both running in mission-critical situations. What that means is that if either one fails, I get phone calls in the middle of the night with complaints. In over 6 years, I have not fielded one phone call attributable to Postgres itself.

None of the issues raised in the article was even remotely a factor in my choice of Postgres. A big (and ultimately deciding) factor in its favor is that it can be compiled and installed on a broad range of hardware and operating system versions. MySQL fared less well in this regard.

Re:Availability

[ShieldW0lf]

That's why it's so popular. Older versions were fast and lightweight and $10/month hosting providers loved it because you couldn't bog their servers down with it because the features to do complex queries just weren't there. Because all the cheap hosting providers had it, everyone learned to use it for their rinky-dink little scripts. Postgres was always better, but it gave your ignorant little novice PHP scripters the power to run very complex queries that will put your server under load, and when you're trying to fit as many cheapo customers onto each server as possible, that's not good.

Re:Web developers...

[Al Dimond]

In the case of the syslog thing, I'd have to wholeheartedly agree with you. On the other hand, if you're a Perl ninja and a weakling at sed/awk you might be better off just using Perl if you only need sed/awk's particular functionality occasionally.

Maybe I'm a hopeless desktop-fed n00b, but I've only used awk once, to take some experimental data that I'd previously entered into a file by hand, transform it a bit to provide input to gnuplot, then mangle it into a TeX table. It took a bit of time to learn, and you know, if I had to use it again I'd have to learn most of it over! I'm not entirely sure it was worth the time. Perhaps people like me should try to learn Sprog instead... or maybe just give in to the supposed "dark side" and enter the table into a spreadsheet and paste it into one of those hellish beasts they call "word processors"... NEVER!

Re:Other things...

[jbolden]

What if you have a table and you need a functional index on a user defined function? MySQL can't even do that, so it will be forced to scan the whole table. PostgreSQL makes it trivial.

You are going to laugh but since MySQL basically uses a 1 application model you just de-normalize and have the functional field inside the table itself.

As for benchmarks there are plenty of benchmarks of Oracle vs. MySQL (a tie to slightly in MySQL's favor on what MySQL does well using cheap hardware. Anything else Oracle wins). There are plenty on benchmarks of MySQL vs. Postgres. The Postgres people seem to dispute these but they show MySQL killing Postgres. You can download Oracle for free. Benchmark it and go ahead and publish. That's a pure a 1st amendment case as I can imagine. Let them try and sue.

PostgreSQL can also combine indexes into in-memory bitmaps before looking in the table. That means you don't have to make a multi-column index for every combination of attributes you select. This is done automatically by the planner.

Oracle has a custom engine for star queries. It has some pretty substantial transformation [leidenuniv.nl] rules when needed. As for MySQL it doesn't even pretend to support Datawarehousing so ...
   

Re:The name

[soyarma]

While I agree that in a business environment the name of a product being the sole reason (though the author didn't specifically indicated it was his sole reason) not to choose something may be irresponsible, one must consider that people in general make base assumptions about all sorts of things from their initial impressions.

Take for instance how much time and money goes into marketing brands (of anything) and how fiercly a brand name is guarded once its established.

The problem with PostgreSQL as a name is twofold. Words in the English language that end with gres are rare, in day to day conversation you could probably go weeks without uttering one (digress or regress are ones you may use often, but they are obviously a bit different). Since our thinking and hearing patterns tend to form basic mappings of percieved words to words already known or in common internal use, many people actuall think 'postreg'. The eyes then inform the brain that what they see does not match 'postreg' and the brain then adds 'confusing' to the list of things about Postgres.

While you may poo poo the people and their internal mental correlations, if you want something to have wide appeal you have to consider things like that. The name needs to be short, simple and easy to remember and relate.

The second item is that it is pronounced Post-gres-Q-L. For the people who pronounce SQL as sequel, they think the word as Post Gre Sequel. The brain then thinks: 'what the heck is gre?'

If you think of the names behind other DB products: MySQL, Access, Oracle, MSSQL, etc... (with MSSQL being a possible exception) the names themselves introduce no pronounciation, word association or sylabic issues.

Task 1 complete. People can talk about these products without having to stop and explain how to pronounce the name. I'd imagine that's one of the first lessons in marketing.

The price of success

[Anonymous Coward]

Oh-oh. We must be getting successful. People are bashing us on Slashdot now.

--Josh Berkus
    PostgreSQL Project

The History of PostgreSQL

[mbirk]

For a better understanding of where PostgreSQL sits with respect to MySQL, it's worth reading the history of PostgreSQL on Wikipedia [wikipedia.org].

The short story is, it has deep roots in academia. It was Michael Stonebraker's experimental, "post-relational" database. It had "advanced" features, relative to its precursor INGRES [wikipedia.org], some of which still remain (e.g. extensible types). (Others, like built-in storage and querying of time-series data, do not.) After the academic project was abandoned, two of Stonebraker's grad students ripped out some of the more esoteric (and unstable) features and added a real SQL parser.

Anyway, I wasn't involved in any of the academic work. However, I was an early adopter in this transition period, circa 1995 (when it was called Postgres95). It was buggy, but it was very, very cool.

I think when MySQL came along, Postgres still had not fully shed it's "academic" pedigree, and still was complex, quirky, and buggy. MySQL was light-weight and simple, and "just worked."

I love PostgreSQL, use it daily, and have had no stability problems in the last five years. But, it was not quite the write DMBS and the right time.

Re:Reason number 6

[dj-nix]

Well, thats funny, because I also have had to write VoIP billing software, and am currently writing IP traffic rating and billing software. When I first started this type of business 5 years ago it was with MySQL, but I was frequently both crashing MySQL and getting junk data in my tables (The biggest problem being invalid dates!) When I started searching for a better option I tried a number of databases including FireBird and Postgresql but settled on Postgresql for 4 major reasons. 1) It has absolutely brilliant Date and Time handling, better even than Oracle. 2) It has native INET support which allows easy manipulation, sorting and searching of IP addresses. 3) It has SUB SELECT support which allowed me to reduce my application code a lot, by making the DB do the work (Always a good tradeoff in my opinion) 4) It has VIEW support which allowed me to generate some "simple" views of the data including some summaries which allowed the management to play to their hearts content with MS Access (As a frontend to PG) without having to understand the "real" data layout.
Of course since then I have discovered many more things to love about Postgresql, including triggers and stored procedures etc (To be fair, MySQL has some of these features now, but did not at that time)
Just to be clear my first Postgresql app handled ~5 million VoIP records per day on a single CPU, single disk desktop class machine and the only time I have EVER had Postgresql crash was due to a bad ram chip in server! Conversely, I can't count the number of time I and my customers have lost data with MySQL.

Re:What an attitude

[koekepeer]

> > I can't be bothered to learn something new when it seems everything
> > supports MySQL.
>
> I'm glad you don't work for me with that attitude. I'd rather work
> with someone who is interested in learning new things and will bring
> some creativity to the job. People of your mentality have to be careful
> they don't fall into the "false laziness" trap--using some tool or
> technique or techology because you are too lazy to learn something new,
> only to end up doing load of extra work to avoid the shortcomings of your
> inappropriate design choices. The result is scads of legacy code at higher
> layers of an application to handle things like datatype verification, basic
> referential integrity and so on.

not to attack you, but i feel you are presenting the situation quite black and white. i had a similar talk with a friend the other night, who develops in MySQL/PHP in a very innovative and creative environment (art/new media).

the gist of his response was something like "creativity exists *because* of limitations and boundaries. you need them to be forced to think outside the box" (loosely paraphrasing).

"just getting the job done" is often a very important issue as well. if your company makes money by selling a product with a complex MySQL database and a PHP frontend that has been developed by someone else, it's a big big effort to change all that to Postgres and some more "up-to-date" scripting language like ruby... especially if you have to meet deadlines all the time. and i'm not even considering what other team members think of this sudden change.

having said that, i'm all for google-like policies, where people can invest 20% of their time in coding hobby projects. in this way you can invest part of your time on learning new fun stuff, and enlarge the array of possible solutions to the problems you need to tackle in the future.

PostgreSQL is a grown-ups database

[mrphewitt]

My take is that "mySQL" is a marketing wonks dream name, "postgreSQL" says difficult and geeky. PostgreSQL is also a grown up database and has a different target audience to mySQL aiming itself at the Oracle and DB2 market. mySQL is aiming at a different market. I examined the strengths and weaknesses of mySQL and PosgtreSQL when deciding which OS database to use for my business and chose PostgreSQL because of its better support for transactional processing and ACID. My current applications built on the rock of postgreSQL include a 250GB datawarehouse modelling the UK electricity market which is used by major players in that market. It has never gone wrong, performs with impressive speed and has never written a record incorrectly or returned an incorrect row. Without postgreSQL I wouldn't be in business. It is the best OS database out there and competes with many of the paid for databases very well.

Re:Interesting article, but not the reasons I hear

[DrXym]

PostgreSQL is hard to install and administer. Really, I think this is a matter of taste. If you are used to MySQL, then yes, there is a learning curve. OTOH, I'm used to PostgreSQL and find myself having to learn MySQL, and MySQL feels just as weird and unintuitive to me as PostgreSQL might to a long-time PostgreSQL user.

Can't speak of Linux / Unix but on XP, Postgres is a doddle to install and administer. After a fairly small download, double click the installer, answer a few questions about ports and its installed as a service. To administer it you fire up pgAdminIII and get an excellent GUI for creating users, running queries and so forth. It also has excellent online documentation in HTML format, plus all the drivers, libs & headers you need to get it work in a dozen different ways.

In fact Postgres is such a breeze to install on XP that it is easier by miles than MS SQL Sever, or Oracle, both of which require lengthy and complex installs to complete.

MySQL is also fairly easy to install XP (it has an installer too), and also installs as a service and HTML help. But I wouldn't say its as easy for Postgres. For one thing you don't get an admin tool (or the dev kit) which means it's considerably more difficult to administer.

I'm sure this would pose no problem for people who love being stuck in a console based admin tool, but personally I prefer the more productive environment offered by pgAdminIII... when it works which is usually most of the time although it has the odd quirk.

One thing I appreciate about both products is how small they are. SQL Server is basically an entire CD to install. Oracle is multiple CD. Both are far too huge for pretty much most uses any individual or small outfit might have for a database.

So definitely on XP, Postgres is far easier to install, administer and use than MySQL. I can't say I've used either in anger, but I was able to hook Postgres up via the ODBC driver to OpenOffice Base and use that as a front end for data entry for some experimental work I was doing.

Re:Because Firebird is here

[fok]

I converted my Delphi app from firebird to postgresql because of scalability problems. Serious scalability problems. Firebird is good, but not nearly as grate as PostgreSQL (as a database engine). It simply cannot work with tables holding a few million records (the server crawls). An almost direct conversion to PostgreSQL gave the app the speed it needed (and afterlife to the server).

Re:Web developers...

[pimpimpim]

yup, another one who fell into the useless use of cat [sial.org] trap!

I WANT associates links

[bill_mcgonigle]

Amazon offers just about every book under the sun, so posting a link to Amazon is not an example of editorial bias. Clearly nobody forces anybody to buy from Amazon, so the only potential effect is on Amazon customers. So, what kind of idiot would post an amazon link to Slashdot without an associates ID?

Here are the possibilities:

• Amazon keeps all the money
  
• A fellow slashdotter gets a miniscule amount of money

Now we have to ask, "who benefits by complaining about people posting links with associates ID's?" The obvious answer would be employees and/or stockholders of Amazon. Now we have potential bias.

In fact, I'd argue Slashcode should parse Amazon URL's and add associate ID's for Slashdot if none already exist. That could potentially be a better revenue base than subscriptions.

It's not like Amazon is going to lower its pricing if everybody on Slashdot refrains from the practice, so consider an associates link a sign of an intelligent poster.

Re:Why I'd rather not use PostgreSQL

[Anonymous Coward]

It should be pointed out that MySQL isn't 100% immune from vacuum like issues. We have a large MySQL installation where I work, 4 servers, over 400gigs of data. One of the servers started to run out of disk space last week... It was using about 200 gigs for the InnoDB data file, the solution to the problem was to do an ALTER table TYPE=InnoDB; command on each table in the database, this of course took a very long time, slowed response time on the replicated servers (since they serialize all transactions), and was a general pain in the ass. At the end of doing this we had 90 gigs of the 200 free though! InnoDB will have horrible space efficiency with a very fragmented B-Tree if you never 'compact' the tables in this manner. Keeping active database tables compacted and unfragmented is just not a simple problem, I haven't used PostgresQL myself, but I can tell you that MySQL could use with some improvement in this area as well.

Garbage in, garbage out

[WebCowboy]

When i have a programming project, i want my contractors to get the job done. They have specifications and functional requirements and they can be as creative with that as they want to be.

[...]

IT professionals are no longer the new surgeons. They're plumbers.

Wow. Perhaps you are personally a fine person, but I bet as a boss people think you are a bit of a jerk.

In my experience the result of this sort of work environment is often mediocre and sometimes disastrous, particularly in a "waterfall" project management environment. My comments don't relate excluseively to programmers, they include the people who write the specs and functional requirements--ESPECIALLY the latter, because poor design and planning can doom a project before one line of code is written. And as for programmers, I do not want them to be confrontational, but I fully expect them to be creative and make suggestions to improve upon a specification.

Although a person can get lost in "creative" pursuits and mired in details that do not contribute to end goals, the opposite can happen too. Engineers and developers can pigeon-hole themselves into doing things a certain way, using certain tools. Sometimes you cannot avoid it because you are working with an existing system, but if you are developing from the ground up you should ALWAYS use some creativity.

I expect professionalism, and something that will stand the test of time. And i pay them accordingly.

Professionalism goes both ways you know. If you expect professionalism from your developers, then you should respect them as professionals, not deride them with opinions that they are just "plumbers" and "aren't paid for creativity". Just as is the case with open source coding, "many eyes make bugs shallow" in a specification, and when a programmer asks why it is the way it is and "wouldn't it work better this way" it can be very beneficial.

PostgreSQL date handling is far from perfect

[lhoriman]

Anyone that lauds date handling in PostgreSQL should take a closer look at the wire protocol. Here's loadCalendar() in the JDBC driver:

http://gborg.postgresql.org/cgi-bin/cvsweb.cgi/pgj dbc/org/postgresql/jdbc2/TimestampUtils.java?rev=1 .18;cvsroot=pgjdbc [postgresql.org]

HORRID. Aside from being complicated and verbose, it changes format pretty dramatically depending, presumably, on how the server was built and what OS it runs on. Some years ago I found myself patching the damn thing because somehow the standard RPM on RedHat6.2 used yet another slightly different format.

32-bit UNIX-style seconds-since-1970-GMT might be inadequate, but a 64-bit milliseconds-since-epoch is just fine and much, much more reliable. And if you really want to be guaranteed infinite future-proofing, send the number as a string.

I moved to Firebird for a while, but now I use MySQL predominantly for one reason only - integrated, robust, proven database replication. When and if PGSQL catches up, I'll consider migrating back.

Jeff Schnitzer