Skip site navigation (1) Skip section navigation (2)

Re: Conceptual Design Question

From: "Medi Montaseri" <montaseri(at)gmail(dot)com>
To: "Steve Midgley" <public(at)misuse(dot)org>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Conceptual Design Question
Date: 2008-06-10 20:11:23
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
Assuming common semantics for a given field then the question of breaking it
to many parts is also a function of its size as related to I/O.

We know that memory allocation and I/O read/writes are not granular to bytes
and are rather blocks of bytes as it travels from VM (virtual memory) all
the way down to sectors on disk.

Hence a common field of say 2000 bytes will most likely cause multiple I/O
requests where application layer did not have any use for  80% of it,  80%
of the times.

Having said that, 1 Gig of RAM is about $25 at your local Cosco with a free
slice of pizza....performance tuning paradigms are in big time flux and are
really uncle Bob's war stories


On Tue, Jun 10, 2008 at 11:35 AM, Steve Midgley <public(at)misuse(dot)org> wrote:

> At 10:52 AM 6/10/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>> Date: Tue, 10 Jun 2008 05:05:24 -0700
>> From: Bryan Emrys <bryan(dot)emrys(at)gmail(dot)com>
>> To: pgsql-sql(at)postgresql(dot)org
>> Subject: Conceptual Design Question
>> Message-ID: <200806100505(dot)24491(dot)bryan(dot)emrys(at)gmail(dot)com>
>> Hello Everyone,
>> In a text-heavy database, I'm trying to make an initial design decision in
>> the following context.
>> There is a lot of long text that I could break down into three different
>> categories:
> [snip]
>> The conceptual question is what are the trade-offs between having one
>> textual table compared with multiple text tables? Any help on pointing out
>> practical considerations would be appreciated.
>> Thanks.
>> Bryan
> Hi Bryan,
> Firstly, I might investigate the GiST index and TSearch2 in this regard.
> I'm not an expert on them, and it maybe is cart before the horse, but if
> those tools are applicable and are easier to implement/maintain with one
> design approach or the other, I might use their design "preferences" as my
> guide for picking the "right" relationships.
> Beyond that advice, it does seem to me that a polymorphic relationship
> (where one table holds multiple entities) *could* describe laws and
> treaties, though they are kind of different in their relations. Commentaries
> seem pretty distinct from these two things.
> My overall opinion would also depend on the architecture. Will you have a
> unified middleware/ORM layer that can manage the business rules for the
> polymorphic data retrieval? Or will developers be going directly into the
> database to pull items directly?
> If you have a unified ORM that stores the business rules, you can be more
> aggressive about using polymorphism, b/c the complexity can be hidden from
> most developers.
> All in all, I think your model is really describing three distinct data
> entities, and should be stored in three separate tables, but that's a very
> high level and uninformed opinion! I'd let TSearch2 drive your design if
> that's a relevant consideration. Of course TSearch2 is very flexible so it
> might not really care much about this. :)
> In general, I find that a data model that "looks like" the real data is the
> one that I'm happiest with - the systems I've seen with too much UML
> optimization and collapsing of sets of data into single tables tend to be
> harder to maintain, etc.
> Just some random opinions for you there. I'm sure others have different
> perspectives which are equally or more valid!
> Best,
> Steve
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:

In response to

pgsql-sql by date

Next:From: Medi MontaseriDate: 2008-06-11 01:45:51
Subject: Update and trigger
Previous:From: maria sDate: 2008-06-10 19:51:50
Subject: Re: help in writing query

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group