Skip site navigation (1) Skip section navigation (2)

Re: Database theory question

From: Jan Danielsson <jan(dot)m(dot)danielsson(at)gmail(dot)com>
To: Mag Gam <magawake(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Database theory question
Date: 2008-04-20 09:59:59
Message-ID: 480B141F.8090401@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Mag Gam wrote:
> While reading this article, History tables and event logging --
> http://www.ibm.com/developerworks/web/library/wa-dbdsgn2.html, I realized I
> try to do event logging in SQL.
> 
> My question are: Is SQL a good tool for event logging? Does anyone have a
> sample table sctucture for the most optimal way of event logging? Current I
> have 2 tables. 1 table with timestamps, another with event. Can anyone
> recommend a better way?

   It depends on what you want to accomplish in the end. Just storage,
or do you want to perform "complex" queries?

   I store my firewall & sshd authtentication attempt logs in a
postgreql database. And I've written a python based web front-end to
view the data, and pull out statistics and such. I also have a "Generate
abuse report" link for when there's been a break-in attempt.

   Personally, I see no reason to separate timestamps and event entries,
unless you're getting a lot of events during the same timestamp, and
want to save some space. But there are other factors to consider: How
many events do you get per timestamp? How long times does an insert into
a single table vs two tables take?

   You should probably try the simplest possibly solution first, and see
if you need to make it more complex as you can gather empirical data on
how it performs.

-- 
Kind regards,
Jan Danielsson


In response to

Responses

pgsql-novice by date

Next:From: Mag GamDate: 2008-04-20 18:33:33
Subject: Re: Database theory question
Previous:From: Decibel!Date: 2008-04-19 19:01:05
Subject: Re: Database theory question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group