472,805 Members | 4,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

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 1614

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Torsten Schmidt | last post by:
Hi, I'm trying to connect to a mysql-Server using PHP's mysql-function mysql_connect. The host on which the mysql-server is running is not the same as the host apache and php are running on. The...
by: Mike Nolan | last post by:
I may need to move a web store to another hosting facility or rewrite it. I will need to be able to tie it to the in-house order entry system (which is/will be in Postgresql) for inventory status...
by: Bob.Henkel | last post by:
I write this to tell you why we won't use postgresql even though we wish we could at a large company. Don't get me wrong I love postgresql in many ways and for many reasons , but fact is fact. If...
by: jbi130 | last post by:
I have a 3 tier client/server application where the client connection to the server which then uses PostgreSQL. I'd like to extend the client to have direct access to PostgreSQL but do not want to...
by: Keith G. Murphy | last post by:
I'm trying to get a feel for what most people are doing or consider best practice. Given a mod_perl application talking to a PostgreSQL database on the same host, where different users are...
by: Robert Abi Saab | last post by:
Hi everyone. I just finished a course on PostgreSQL and I found out that PostgreSQL doesn't provide any object relational features (as claimed in the official documentation), except table...
by: Rob Oakley | last post by:
I have an embedded system running FreeBSD (5.1) that does not have any local (rotating) storage (i.e disk drives). PostgreSQL also runs on this box and (at this point) has two tables. It is an...
by: Mike Cox | last post by:
Obviously, there cannot be 21 postgresql groups in the comp.* hierarchy. Many of the 21 are not used that often, and would not be of much popularity to those on usenet. I did a check on...
by: matt | last post by:
Does anyone know of a service, that provides driving traffic information in the form of xml? I see several large cities have information on the web, including traffic cameras. traffic.com has an...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.