experience with tags schemas

On Fri, Nov 5, 2010 at 10:07 AM, Matthieu Huin <matthieu.huin@wallix.com> wrote:

Does anyone here have any experience with tags schemas on postgresql ?

I don't, but this problem look strangely familiar to a
problem/solution found in Joe Celko book SQL puzzles.

Check out puzzle 17 answer #2:

http://sites.google.com/site/ankurpshah2/joe-celkos-sql-puzzles-and-answers-s.pdf

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

matthieu.huin@wallix.com (Matthieu Huin) writes:

Greetings,

Does anyone here have any experience with tags schemas on postgresql ?

I am struggling with a schema inspired by "scuttle" described here :

http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html

Performances drop dramatically when the data set increases. Is it
"normal" and I should look at other data structures ? Or am I doing
something wrong ?

If you need more information, I'll be happy to give it to you.

I'd expect the "Toxi" solution (where do they get the names!??!?!) to be
the fastest one of the litter, as most of the "burden" of work goes into
the narrow table, "tagmap." (It's also easily extended to cope with the
way Deli.cio.us does tagging, as you can attach user IDs to "tagmap")

In any case, it would make a lot of sense to run EXPLAIN ANALYZE on some
of the queries; that ought to provide some insight as to what parts are
behaving notably badly.

The article provides some good grist for the argument that proper
normalization frequently *IMPROVES* performance. (Contrary to the all
too common "NoSQL is Web Scale" arguments that we must "denormalize for
performance.")
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne&gt; rate me
http://www3.sympatico.ca/cbbrowne/lsf.html
"Like I've always said, if you don't have anything nice to say, come
sit by me." -- Steel Magnolias