Re: unique constraint on 2 columns

On Fri, 2007-04-20 at 18:32 -0400, Jonathan Vanasco wrote:
> On Apr 20, 2007, at 6:13 PM, Jeff Davis wrote:
>
> > This is more correct structure, and yes, it would involve a join.
>
> I know thats the 'more correct' way -- but I can't do the join ,
> which is why I posted about a 2 column unique index.
> I tested with a join before posting - i have an already large table
> that is growing quickly. in order to use the join and keep current
> performance I'd need to scale out in hardware - which is just not an
> option right now. searching 100M records vs searching 100M records +
> a join is a huge difference. when you try to do analytics, its just
> not appropriate in my situation.
>

If possible, try to separate performance issues from logical design
issues.

Try a materialized view:
http://www.jonathangardner.net/PostgreSQL/materialized_views/matviews.html

those often help with analytical queries.

> > No, it does not mean you need to rewrite anything. Use a view; that's
> > the great benefit you get from using a relational database like
> > PostgreSQL.
>
> i'd have to rewrite everything that reads from that table to use the
> view instead of the current query, and then worry about inserts.

Name the view the same thing as your table, and you won't have that
problem, and use rules to make the view updatable. PostgreSQL has
transactional DDL so you can rearrange the table's physical structure
without interrupting your application.

There are some situations when performance issues do affect logical
design and the ability to enforce constraints. PostgreSQL provides a lot
of tools to make those situations as rare as possible.

Hopefully PostgreSQL has enough capabilities to get you decent
performance, a logical design, and meet the requirements of your pre-
existing code. If not, you can use some of the hacks suggested by others
to allow you to enforce the constraint.

Hope this helps,
Jeff Davis