Re: postgresql storage and performance questions

On Nov 20, 2007 11:13 AM, Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info> wrote:
> 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.
I ran vacuum full on this table already. I haven't re-indexed it. But
this will not affect the table size...right...since indexes are stored
separately?
> > 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.
Does changing the block size has any side effects on any other
operations in particular?

> Brad Nicholson 416-673-4106
> Database Administrator, Afilias Canada Corp.
>
>
>