Postgresql Page Layout details

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