Re: Performance Question - Table Row Size

On 7/9/07, Mike <akiany(at)gmail(dot)com> wrote:
> I am designing my database and I was wondering whether my table row
> size effects the performance of querying my table. Please note that my
> table is being designed to hold high volume of records and I do not
> plan to do (select *) for retrieving them. That is I plan to only
> query a few of those fields at a given time but each row contains
> significantly more data that are not being queried at the time.

Obvious observation: Since PostgreSQL's unit of data storage is the
page, selects -- even on single attributes -- result in entire pages
being loaded into memory and then read.

Since the cache (PostgreSQL's shared buffers plus the OS file system
cache) holds pages, not individual attributes, more data per tuple
(row) means fewer tuples to fit in the cache.

As far as the CPU cache goes, as I understand it, the fact that you're
reading just a few attributes from each tuple (maybe even just a few
from each page) is inefficient -- you will be forcing lots of data
into the cache that is never used.

In general, you might be better off normalizing your table, if
possible, or partitioning it into subtables.

But these are the broad strokes -- how many columns are we talking
about exactly, and of what data types?

Alexander.