schema advice for event stream with tagging and filtering

From: Chris Withers <chris(at)simplistix(dot)co(dot)uk>
To: Postgresql General <pgsql-general(at)postgresql(dot)org>
Subject: schema advice for event stream with tagging and filtering
Date: 2016-08-16 08:38:16
Message-ID: f64ae2ef-50de-76cf-1cd7-82d6000980b8@simplistix.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All,

What would be the best schema to use when looking to implement an event
stream with tagging and filtering?

An event is a row with a primary key along the lines of (colo, host,
category) and an associated set of tags, where each tag has a type and a
value (eg: {"color": "red", "owner": "fred", "status": "open"...}).

Events come in as a streams of creates/updates as a cluster of http
posts to a web app.

We want to display events as a faceted list, with the facets being colo,
host, category and each of the tag types that exist, with counts against
each value for each facet (in an ideal world).

The idea is to be able to click a facet to include or exclude it from
the results. Furthermore, we want to do some other filtering (only show
events tagged for the team of the current user, exclude everything for
this colo between time x and y, etc).

I've deliberately tried to be abstract here as I'm trying to ask a
question rather than proposing a solution that might have problems, if
there's any more information that would help, please let me know!

cheers,

Chris

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn Tonies (Upscene Productions) 2016-08-16 09:01:00 PgAdmin: debugging stored function in v9.4 instable?
Previous Message Chris Withers 2016-08-16 08:30:07 schema advice for event stream with tagging and filtering