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:
http://gnumonks.org/gnumonks/projects/project_details?p_id=1
(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):
CREATE OR REPLACE FUNCTION ulogtimecast(int4)
RETURNS timestamp AS
'select "timestamp"($1::abstime);'
LANGUAGE 'sql' VOLATILE;
CREATE CAST (int4 AS timestamp)
WITH FUNCTION ulogtimecast(int4)
AS IMPLICIT;
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
solution.
Thank you for your answers,
Jean-Michel Pouré