james@unifiedmind.com (James Thornton) writes:
> I want to understand how Postgres organizes data and handles IO
> operations so that I will better know how to optimize a Postgres
> database server. I am looking for answers to specific questions and
> pointers to where this stuff is documented.
If you haven't read the Postgres docs in detail that would be a good
place to start. :)
> How does Postgres organize its data? For example, is it grouped
> together on the disk, or is it prone to be spread out over the disk?
> Does vacuum reorganize the data? (Seeking to minimize disk head
> movement.)
Tables and indexes are stored in disk files in the filesystem, so PG
relies on the OS to lay out data on the disk.
> How does Postgres handle sequential IO? Does it treat is specially
> such as issuing large IO operations that span block boundaries?
The WAL (write-ahead log), a sort of journal, is written sequentially.
I"m not too familiar with whether WAL writes are ganged together if
possible, but I would think so.
> How does Postgres handle direct IOs (operations directly to disk,
> bypassing the buffer cache)? Will it issue multiple asynchronous IO
> operations?
No direct I/O, no async I/O. A background checkpoint process handles
a lot of the data writeback I/O.
> Is Postgres always one process per client, or can it spawn additional
> processes to parallelise some operations such as a nested loops join
> operation?
One process per client connection. Right now there is no spawning of
additional "worker" processes.
> Is there a recommended file system to use for Postgres data, such as
> ext2 or another non-journaling FS?
You definitely want a journaled FS or the equivalent, since losing
filesystem metadata on a crash can ruin your whole day, not to mention
the fsck times...
There doesn't seem to be a clear winner in the "which FS" debate. If
you use ext3, it's probably fastest to mount with 'data=writeback' for
your DB partition, since you can rely on PG to journal the data
writes. Most other FS's only journal metadata anyway.
Hope this helps!
-Doug
