CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03

UPDATE

Please read also about this change in Pg 9.1, and this change in Pg 9.2 posts, as they explain that since Pg 9.1 some of the limitations listed in this post are no longer there.

END OF UPDATE

Fight!

But more seriously – people tend to use various data types, and there have been some myths about them, so let's see how it really boils down.

First of all – All those data types are internally saved using the same C data structure – varlena.

Thanks to this we can be nearly sure that there are no performance differences. Are there no performance differences in reality? Let's test.

Continue reading CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03

Installing PostgreSQL

First of all – I base information in this post on Linux, so if you are using another operating system – it's quite likely that it will not help you.

Second – if you're using Linux, you have probably some package manager – dpkg, rpm, pacman, poldek, whatever. Right? So, it should be easy to install Pg using this package manager?

Well, yes and no. Yes – of course all (known to me) Linux distributions include PostgreSQL, but I do not use their prepackaged version.

Why, and how do I install?

Continue reading Installing PostgreSQL

Waiting for 9.0 – extended frames for window functions

On 12th of February Tom Lane committed patch by Hitoshi Harada:

Log Message:
-----------
Extend the set of frame options supported for window functions.
 
This patch allows the frame to start from CURRENT ROW (in either RANGE or
ROWS mode), and it also adds support for ROWS n PRECEDING and ROWS n FOLLOWING
start and end points.  (RANGE value PRECEDING/FOLLOWING isn't there yet ---
the grammar works, but that's all.)
 
Hitoshi Harada, reviewed by Pavel Stehule

Continue reading Waiting for 9.0 – extended frames for window functions

Waiting for 9.0 – string_agg

On 1st of Februyary, Takahiro Itagaki committed a patch by Pavel Stehule which adds string_agg aggregate:

Log Message:
-----------
Add string_agg aggregate functions. The one argument version concatenates
the input values into a string. The two argument version also does the same
thing, but inserts delimiters between elements.
 
Original patch by Pavel Stehule, reviewed by David E. Wheeler and me.

Continue reading Waiting for 9.0 – string_agg

Waiting for 9.0 – removal of 0000000001.history check

I tend to write about new features in new versions of PostgreSQL, but this patch actually fixes one of the things that annoy me a lot, so here it goes:

On 26th of January, Simon Riggs committed:

Log Message:
-----------
Fix longstanding gripe that we check for 0000000001.history at start of
archive recovery, even when we know it is never present.

If you've ever tried to roll your own restore_command script ( like pg_standby ) then you know that to the algorithm that's presented in docs You always had to add special case for file “0000000001.history" – which was never there, but somehow PostgreSQL always asked for it – despite the fact that it could never arrive.

Now, thanks to this small patch we will no longer need to add this “if" in any script. It's small, and it's not a new feature, but I am SO happy to see it.

Waiting for 9.0 – table and index sizes

On 19th of January Tom Lane committed really brilliant patch:

Log Message:
-----------
Add pg_table_size() and pg_indexes_size() to provide more user-friendly
wrappers around the pg_relation_size() function.
 
Bernd Helmle, reviewed by Greg Smith

Continue reading Waiting for 9.0 – table and index sizes

Waiting for 9.0 – Streaming replication

The BIG feature. The feature that made PostgreSQL leap from 8.4 to 9.0. Patch was written by Fujii Masao, and committed by Heikki Linnakangas on 15th of January 2010:

Log Message:
-----------
Introduce Streaming Replication.
 
This includes two new kinds of postmaster processes, walsenders and
walreceiver. Walreceiver is responsible for connecting to the primary server
and streaming WAL to disk, while walsender runs in the primary server and
streams WAL from disk to the client.
 
Documentation still needs work, but the basics are there. We will probably
pull the replication section to a new chapter later on, as well as the
sections describing file-based replication. But let's do that as a separate
patch, so that it's easier to see what has been added/changed. This patch
also adds a new section to the chapter about FE/BE protocol, documenting the
protocol used by walsender/walreceivxer.
 
Bump catalog version because of two new functions,
pg_last_xlog_receive_location() and pg_last_xlog_replay_location(), for
monitoring the progress of replication.
 
Fujii Masao, with additional hacking by me

Continue reading Waiting for 9.0 – Streaming replication

I just *HATE* Hardware Abstraction Layer

I'm quite happy user of Arch Linux. BUT…

Recent upgrade changed Xorg to new version which requires HAL (Hardware Abstraction Layer).

Problem: when HAL is turned on, it totally breaks my keyboard – generates extra “key presses" when using “special" keys – I press windows key, and get letter “F" extra. I also lost ability to self-repeat keys by pressing them longer. Right alt – stopped working. Down arrow – generated also “M" letter. Right shift – doesn't work. All in all one big mess.

Solution was “trivial": remove hal, and add to xorg.conf section to disable hotplugging:

Section "ServerFlags"
    Option "AutoAddDevices" "False"
EndSection

I just have to write it, because I might hit the same problem someplace else as well, so I will now know what to do.

Side note – I happen to use wireless Logitech keyboard, which apparently is a bad thing, because any kind of mention “I have a problem with keyboard and X" prompts questions about my Logitech – even if the same keyboard works flawlessly on console, or in X without this HAL thing.

Maybe I'm getting grumpy, but Linux on desktop actually worked for me much better 2 years ago, than it does now.