Re: Compare rows

Greg,

On Wed, Oct 08, 2003 at 03:07:30PM -0400, Greg Spiegelberg wrote:
> Dror,
>
> I gave this some serious thought at first.  I only deal with
> int8, numeric(24,12) and varchar(32) columns which I could
> reduce to 3 different tables.  Problem was going from 1700-3000

I'm not sure how the data types come into play here. I was for the most
part following your examples.

> rows to around 300,000-1,000,000 rows per system per day that
> is sending data to our database.
>

Depending on the distribution of your data you can end up with more,
less or roughly the same amount of data in the end. It all depends on
how many of the 600+ columns change every time you insert a row. If only
a few of them do, then you'll clearly end up with less total data, since
you'll be writing several rows that are very short instead of one
huge row that contains all the information. In other words, you're
tracking changes better.

It also sounds like you feel that having a few thousand rows in a very
"wide" table is better than having 300,000 - 1,00,000 rows in a "narrow"
table. My gut feeling is that it's the other way around, but there are
plenty of people on this list who can provide a more informed answer.

Using the above eample, assuming that both tables roughly have the same
number of pages in them, would postgres deal better with a table with
3-4 columns with 300,000 - 1,000,000 rows or with a table with several
hundred columns with only 3000 or so rows?

Regards,

Dror


> BTW, the int8 and numeric(24,12) are for future expansion.
> I hate limits.
>
> Greg
>
>
> Dror Matalon wrote:
> >It's still not quite clear what you're trying to do. Many people's gut
> >reaction is that you're doing something strange with so many columns in
> >a table.
> >
> >Using your example, a different approach might be to do this instead:
> >
> > Day  |      Name     |   Value
> > ------+-------------+-----------
> > Oct 1 | OS          | Solaris 5.8
> > Oct 1 | Patch       | 108528-12
> > Oct 3 | Patch       | 108528-13
> >
> >
> >You end up with lots more rows, fewer columns, but it might be
> >harder to query the table. On the other hand, queries should run quite
> >fast, since it's a much more "normal" table.
> >
> >But without knowing more, and seeing what the other columns look like,
> >it's hard to tell.
> >
> >Dror
>
>
> --
> Greg Spiegelberg
>  Sr. Product Development Engineer
>  Cranel, Incorporated.
>  Phone: 614.318.4314
>  Fax:   614.431.8388
>  Email: gspiegelberg@Cranel.com
> Cranel. Technology. Integrity. Focus.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
Dror Matalon
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com