Thread: Postgresql Page Layout details

Dear all,

i was reading the postgres docs concerning the
Database Physical Storage. I found that the
information present there is not enough to satisfy my
curiosity.
Are there any documentation out there that describes
in more details the Database Physical Storage of
potgres ?

In the Page Layout details, it is specified that a
page is usually 8KB each. Does the page here means a
file system block ? Can the page size in postgres be
different from the file system block size ?


Thanks for any help,

Najib.



      ____________________________________________________________________________________
Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs

Najib Abi Fadel wrote:
> Dear all,
>
> i was reading the postgres docs concerning the
> Database Physical Storage. I found that the
> information present there is not enough to satisfy my
> curiosity.
> Are there any documentation out there that describes
> in more details the Database Physical Storage of
> potgres ?

Indeed - there are a lot of comments in the source code. If you really
need more details than is in the manuals you'll want to start looking at
the code anyway. The mailing list archives (particularly the hackers
list) should give some more insight too.

> In the Page Layout details, it is specified that a
> page is usually 8KB each. Does the page here means a
> file system block ? Can the page size in postgres be
> different from the file system block size ?

This is PG's block size and is unconnected from filesystem block sizes.
This 8KB limit used to mean our maximum row size was less than that, but
the introduction of the TOAST system means that larger columns are moved
out-of-line. The 8KB limit on a row is still there, but since that means
  over a thousand integers / toast-pointers it's not much of a problem.

Some people used to suggest that a larger blocksize helped with specific
disk systems & disk block sizes. This means changing the setting in one
of the header files and recompiling. It also means your database files
aren't compatible with a normally-compiled version of PostgreSQL. I've
not seen anyone mention it recently, so maybe it's just not worth the
trouble any more.

--
   Richard Huxton
   Archonet Ltd

Concerning the 8KB page size, as i understood postgres
Page Size is different from the file system Block
size.
If the system block size is 4kB are there any
mechanism that guaranties that a postgres page is
stored on 2 adjacent file system blocks ?

Cause otherwise performance may suffer since accessing
1 page in postgres  may require reading 2 distant
blocks!

According to my understanding of how file system
works, a file system block is the unit of exchange
between the hard disk and the main memory.

am i missing something ??

--- Richard Huxton <dev@archonet.com> wrote:

> Najib Abi Fadel wrote:
> > Dear all,
> >
> > i was reading the postgres docs concerning the
> > Database Physical Storage. I found that the
> > information present there is not enough to satisfy
> my
> > curiosity.
> > Are there any documentation out there that
> describes
> > in more details the Database Physical Storage of
> > potgres ?
>
> Indeed - there are a lot of comments in the source
> code. If you really
> need more details than is in the manuals you'll want
> to start looking at
> the code anyway. The mailing list archives
> (particularly the hackers
> list) should give some more insight too.
>
> > In the Page Layout details, it is specified that a
> > page is usually 8KB each. Does the page here means
> a
> > file system block ? Can the page size in postgres
> be
> > different from the file system block size ?
>
> This is PG's block size and is unconnected from
> filesystem block sizes.
> This 8KB limit used to mean our maximum row size was
> less than that, but
> the introduction of the TOAST system means that
> larger columns are moved
> out-of-line. The 8KB limit on a row is still there,
> but since that means
>   over a thousand integers / toast-pointers it's not
> much of a problem.
>
> Some people used to suggest that a larger blocksize
> helped with specific
> disk systems & disk block sizes. This means changing
> the setting in one
> of the header files and recompiling. It also means
> your database files
> aren't compatible with a normally-compiled version
> of PostgreSQL. I've
> not seen anyone mention it recently, so maybe it's
> just not worth the
> trouble any more.
>
> --
>    Richard Huxton
>    Archonet Ltd
>



      ____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Najib Abi Fadel wrote:
> Concerning the 8KB page size, as i understood postgres
> Page Size is different from the file system Block
> size.
> If the system block size is 4kB are there any
> mechanism that guaranties that a postgres page is
> stored on 2 adjacent file system blocks ?

That's the job of the filesystem, and is a problem that could affect any
file that's 5KB or larger.

> Cause otherwise performance may suffer since accessing
> 1 page in postgres  may require reading 2 distant
> blocks!

Yes. And how many users would a filesystem have if it did that on a
regular basis :-)

> According to my understanding of how file system
> works, a file system block is the unit of exchange
> between the hard disk and the main memory.

Yes. Well, it's the unit of exchange between the filesystem and the
disk. Otherwise you couldn't have different filesystem block sizes. And
you could probably argue that a multi-disk RAID setup's stripe-size is a
more logical unit to work with.

--
   Richard Huxton
   Archonet Ltd

In article <47CFAD1F.7080801@archonet.com>,
Richard Huxton <dev@archonet.com> wrote:

% Some people used to suggest that a larger blocksize helped with specific
% disk systems & disk block sizes. This means changing the setting in one
% of the header files and recompiling. It also means your database files
% aren't compatible with a normally-compiled version of PostgreSQL. I've
% not seen anyone mention it recently, so maybe it's just not worth the
% trouble any more.

I suspect there's just not much to say about it. It makes good
sense to match the database block size to the filesystem block size,
particularly if the filesystem blocks are larger than 8k. It's
not exactly a lot of trouble to set it up, assuming you compile the
database yourself anyway, and it allows the database to do a better
job of I/O management.

--

Patrick TJ McPhee
North York  Canada
ptjm@interlog.com