Tagging rows into collections?

From: Steve Wampler <swampler(at)noao(dot)edu>
To: postgres-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Tagging rows into collections?
Date: 2002-06-19 16:02:03
Message-ID: 3D10AAFB.D51B1B66@noao.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


I've got what I think is a fairly simple SQL question.

Frist, some background.

I'm building a database to log events produced by some
scientific instruments. The postgresql database is
attached to by muliple java programs using JDBC.

An event has: timestamp,event_name,list_of_attributes
The list_of_attributes are simple (string) name,value pairs.
An event may have 1 or more attributes in its list (no upper
limit is imposed by the system).

Insertion performance is critical - selection performance
*much* less so.

I wrote a simple test version using a single table with
columns:

lab.devel.eventdb=# \d events_table
Table "events_table"
Attribute | Type | Modifier
-----------+--------------------------+---------------
time | timestamp with time zone | default now()
event | character varying(64) | not null
name | character varying(64) | not null
svalue | character varying(80) |

The Java code accepts (via CORBA) events and then splits
the events into multiple rows in this table (one row per
attribute in the list_of_attributes).

This works fine for insertion - it's simple and fast enough with
transactions.

However, although selection performance isn't a priority, the
ability to reconstruct the events from the database is needed
and the above simple table doesn't provide enough information
to do so. (The resolution on the timestamp field isn't
enough to distinquish separate events that have the same name.)

Is there an obvious way to solve this (preserving insert
performance as much as possible) at the database level? Or
should I bite the bullet and solve it at the java level?
I'm partial to a solution solving it at the database level
because the fact that there are multiple event sources (java
apps distributed across a network).

I'm leaning toward two tables - one with rows of "timestamp,event"
and an internal tag that can be used to identify rows of "name,svalue"
in a second table, but I'm new enough to SQL to be uncertain
of the best way to set this up and still get good insert performance.
(And whether this would really be better than some approach using a
single table.)

Thanks for any guidance!

Steve
--
Steve Wampler -- swampler(at)noao(dot)edu
The gods that smiled on your birth are now laughing out loud.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bruce Momjian 2002-06-19 16:43:04 Re: SQL performance issue with PostgreSQL compared to MySQL
Previous Message Josh Berkus 2002-06-19 14:58:25 Re: SQL performance issue with PostgreSQL compared to