Re: postgresql storage and performance questions

On Tue, 2007-11-20 at 07:22 -0500, Josh Harrison wrote:

> There were a couple of things we noted.
> 1. Tablesize twice as much than oracle-- Im not sure if postgres null
> columns has any overhead since we have lots of null columns in our
> tables.Does postgresql has lots of overhead for null columns?

Did you by any chance have an aborted load of the data? If you load in
a table, and that load fails or does not commit, it will still occupy
the space until you vacuum. If you try to load again, the table will be
twice the size.

If you want to compact the physical space the table occupies, you can
try running VACUUM FULL on it, and possibly a redindex afterwards. This
will bring the physical space down to the minimum. Both of these
operations will lock out access to the tables though.

> 2. Oracle seems to be reading larger bocks than postgresql (when we
> examined the iostat and vmstat) (we had set postgres' db block size as
> 8 and oracle's is 16kb...)
> Do you have any comments on this?

8k is the defualt. You can change the block size if you need to. You
need to modify src/include/pg_config_manual.h recompile and re-initdb.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.