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
