469,613 Members | 1,827 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

Using PostgreSQL to store ip traffic information

Dear friends,

I am currently testing Ulogd ip traffic logging system with PostgreSQL. It
works in conjunction with GNU/Linux iptables.

The Ulogd project can be found here:

(If you try the project, fetch it from CVS as the PostgreSQL code was broken
until today.)

My database may aquire up to 100 entries / second.
If possible, I would like to know your point of view about these issues:

1) int4->timestamp cast

uLogd send and int4 (epoq timestamp) to PostgreSQL. I would like to store the
int4 in a timestamp after cast.

The developpers from Ulogd may not accept a cast written in the SQL query
using ::abstime because they want to keep the ability to use an int4.

Therefore, I used the following trick (from PostgreSQL bits):

RETURNS timestamp AS
'select "timestamp"($1::abstime);'

CREATE CAST (int4 AS timestamp)
WITH FUNCTION ulogtimecast(int4)

I am worried that the SQL query may slow down PostgreSQL during heavy INSERTs.
What is the impact on performance? Do you see a smarted way of doing this
cast from in4 to timestamp?

2) Partial indexes

Using a con job, I plan to create partial indexes on Inetd, timestamps and
various columns everyday. This will allow me to index the information from
the past 10 days, but not dig further in indexes.

Is there a smarter way to proceed? Are there special indexes in development
for deep indexing?

3) Hash indexes
is there any interest in storing TCP ports and simple information in hash
indexes? I heard that they were not faster than b-tree.

4) PostgreSQL analysis code
Did anyone work on server-side analysis code written in PL? I plan to use
pgAdmin III for direct queries, but would be interested in any server-side

Thank you for your answers,
Jean-Michel Pouré
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #1
0 1513

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Torsten Schmidt | last post: by
3 posts views Thread by Robert Abi Saab | last post: by
2 posts views Thread by matt | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.