Re: improvement suggestions for performance design

On 7/5/07, tfinneid(at)ifi(dot)uio(dot)no <tfinneid(at)ifi(dot)uio(dot)no> wrote:
>
> > I would strongly suggest that you use a proper relational schema,
> > instead of storing everything in two tables. I don't know your
> > application, but a schema like that is called an Entity-Attribute-Value
> > (though your entity seems to be just posx and posy) and it should raise
> > a big red flag in the mind of any database designer. In particular,
> > constructing queries against an EAV schema is a major pain in the ass.
> > This has been discussed before on postgresql lists as well, you might
> > want to search and read the previous discussions.
>
> I get your point, but the thing is the attributes have no particular
> relation to each other, other than belonging to same attribute groups.
> There are no specific rules that states that certain attributes are always
> used together, such as with an address record. It depends on what
> attributes the operator wants to study. This is why I don't find any
> reason to group the attributes into separate tables and columns.
>
> I am still looking into the design of the tables, but I need to get at
> proper test harness running before I can start ruling things out. And a
> part of that, is for example, efficient ways of transferring the insert
> data from the client to the db, instead of just single command inserts.
> This is where bulk transfer by arrays probably would be preferable.
>
> > Ignoring the EAV issue for a moment, it's hard to give advice without
> > knowing what kind of queries are going to executed. Are the lookups
> > always going to be by id? By posx/posy perhaps? By attribute?
>
> the query will be by attribute type and posx/y. So for position x,y, give
> me the following attributes...
>
> thomas
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

I don't know much about this EAV stuff. Except to say that my company is in
a situation with a lot of adds and bulk deletes and I wish the tables were
designed with partitioning in mind. That is if you know how much, order of
magnitude, data each table will hold or will pass through (add and delete),
you may want to design the table with partitioning in mind. I have not done
any partitioning so I cannot give you details but can tell you that mass
deletes are a breeze because you just "drop" that part of the table. I think
it is a sub table. And that alleviates table bloat and excessive vacuuming.

Good luck.

--
Yudhvir Singh Sidhu
408 375 3134 cell