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

Re: Database theory question

From: Decibel! <decibel(at)decibel(dot)org>
To: Mag Gam <magawake(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Database theory question
Date: 2008-04-19 19:01:05
Message-ID: 0EFD0120-1B67-4C45-9D36-A9BD96E8BD13@decibel.org (view raw or flat)
Thread:
Lists: pgsql-novice
On Apr 17, 2008, at 2:43 PM, Mag Gam wrote:
> Hi All,
>
> 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?


Hrm... I'm on a plane so I can't look at the article right now, but a  
separate table for timestamps doesn't sound so useful. If you had a  
*lot* of events for each timestamp, maybe... since timestamps take 8  
bytes you could possibly save space by referencing them with an int  
instead; you would save 4 bytes per event. But you'd use at least 24  
extra bytes to store the timestamp in a separate table, depending on  
Postgres version and CPU architecture.

As for "does it make sense", that depends both on the data that  
you're storing and how you're using it. From a space standpoint,  
you'll be hard-pressed to beat a text logfile and gzip/bzip2. Even if  
you need to search the data, grep can often suffice. On the other  
hand, if you're doing a lot of searching or other processing, or if  
you have a lot of numeric values that you can store in either int or  
float4, a database makes more sense.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828


In response to

pgsql-novice by date

Next:From: Jan DanielssonDate: 2008-04-20 09:59:59
Subject: Re: Database theory question
Previous:From: Mike EllsworthDate: 2008-04-18 20:42:18
Subject: Re: Expression on an Expression alias

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