Sun buys MySQL....

Well, the news is pretty old now. Everyone has had his say on the deal: How its a great deal for both the parties involved, for the competitors, and even MySQL's competitors (read my employer, EnterpriseDB (though we cater to a different market share)).

But I came across this interesting take on the deal. How Oracle might have orchestrated this deal. The claims are unsubstantiated, a good read nonetheless:

http://www.marketwatch.com/news/story/john-dvoraks-second-opinion-sun-mysql/story.aspx?guid=%7B88606B4A-A4AF-46FC-9C80-6B186A622456%7D&dist=hplatest

Postgres can... Oracle can't (index NULLs)

Well, it was a revelation.... For almost ages, I have been made to believe that NULLs cannot be indexed; primarily because Oracle chose not to, and I had used Oracle quite a lot in the past (and worked for them too at one time).

A couple of days ago I was asked by a developer (Arie) this very question, but in context of Postgres:

Subject: Are nulls indexed?
Body:
If I have an index on column A (null-able) will null values be indexed? Basically, if not, does it mean that such a query "select * from T where A is null" will have to do a full table scan?

I blindly said no... that was until I had to test something out based on his next question. And I was in fact surprised! Postgres can use a non-unique index for 'IS NULL' and 'IS NOT NULL' predicates!

Here's a small session to demonstrate that Postgres stores index-records for NULLs and can use such indexes for IS [NOT] NULL predicates:

postgres=# create table t ( a int, b char(32) );
CREATE TABLE
postgres=# create index t_i on t( a );
CREATE INDEX
postgres=# insert into t select s, s+1 from generate_series( 1, 1000000 ) as s;
analyze t;
INSERT 0 1000000
postgres=# analyze t;
ANALYZE
postgres=# select relpages, relname from pg_class where relname like 't%';
relpages | relname
----------+--------------------------
<snip>
8334 | t
2745 | t_i
(7 rows)

postgres=# explain select * from t where a is null;
QUERY PLAN
--------------------------------------------------------------
Index Scan using t_i on t (cost=0.00..8.38 rows=1 width=37)
Index Cond: (a IS NULL)
(2 rows)

postgres=# truncate t;
TRUNCATE TABLE
postgres=# insert into t select case s%5 when 0 then null else s end, s+1 from generate_series( 1, 1000000 ) as s;
INSERT 0 1000000
postgres=# reindex index t_i;
REINDEX
postgres=# analyze t;
ANALYZE
postgres=# select relpages, relname from pg_class where relname like 't%'; relpages | relname
----------+--------------------------
<snip>
8334 | t
2745 | t_i
(7 rows)

postgres=# explain select * from t where a is null;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using t_i on t (cost=0.00..7332.37 rows=198972 width=37)
Index Cond: (a IS NULL)
(2 rows)

postgres=#

I hope you get the picture.

So, another good reason to choose Postgres (or for that matter EnterpriseDB) over Oracle... :)

PS: This works only with the B-Tree indexes though.

a NOOP WAL archiving command for PostgreSQL 8.3

Starting Postgres version 8.3, there is a little annoying feature (for the DBAs) in WAL archiving infrastructure of Postgres. In earlier versions you could change the archive_command on the fly to do different things at different times, or do nothing at all if you choose not to.

But 8.3 onwards, if you haven't switched on archive_mode to ON while starting the server, then you just can't change your archiving strategy while the server is running! So, I would recommend running a production database with this setting always turned ON. But wait, what do you set the archive_command to, if you do not want to archive?! The thing is, if archive_mode is ON and theres nothing in the archive_command you are likely to see the following WARNING in your log files:

WARNING: archive_mode enabled, yet archive_command is not set

And to worsen the situation, instead of recycling your transaction logs, it will keep them all because it is supposed to do archiving, but it is not able to since there's no command to execute... what happens? Your transaction log directory gets full!!!

And, if archive_command is set to something that Postgres can't execute, then you have a BIG problem.

So, here is a very simple solution: set the archive_command to something that does nothing and always returns 0 (zero). BUT (yeah there's always this big BUTT when handling databases :) ) you will see messages in your log files:

LOG: archived transaction log file "00000001000000000000003A"

And the problem with this that you actually didn't archive anything, and yet you see success messages; this can be confusing when you are analyzing the records later! So how do you keep a record of what actually happened? Did you actually archive a particular file!?

Here's a command:

archive_command = 'date >> dummy_archive.log && echo did dummy archiving for %f >> dummy_archive.log'

This will keep a separate log (named dummy_archive.log in your data directory) for the transaction logs that you didn't archive, and your database will run smoothly, without any warnings in the logs. And when you actually wish to start archiving, just change this command on the fly to start actual archiving. No downtimes.

So here's the final verdict:

archive_mode = on

archive_command = 'date >> dummy_archive.log && echo did dummy archiving for %f >> dummy_archive.log'

PS: I wish I could make this command's output go to the log_destination of postgres, in a generic manner! If you know of something that'll help achieve this, please comment here or write to me.

UPDATE: (Nixen (Unix like) systems only) The following archive_command lets you push these 'dummy archival messages' into Postres's server log:
archive_command = 'date >&2 && echo did dummy archiving for %f >&2 '

Postgres development notes

Tom Lane uses 'emacs + glimpse' for development (source)