[Snort-devel] database schema: postgresql

Kreimendahl, Chad J Chad.Kreimendahl at ...1167...
Tue Mar 5 15:01:27 EST 2002

It may be a good idea for there to be some extra included schema files for
the different databases.  I'm more than willing to build a file for creation
of indexes and integrity constraints for Oracle.

We tried using our Oracle with the constraints on, and discovered that for
selects the performance was slightly increased (Oracle is helped when doing
a join because of FKs... i was amazed the indexes didn't do most of the
work)... deletes were much easier (being that I set up a cascading
delete)... but weren't much faster... since every delete had to test the
FKs...  Inserts were vastly slower... but not a problem, because we weren't
inserting fast enough to bog down the db server.

Most of our problem with speed was directly related to using a partition
that also included /var (which was logging many other things as well).  My
guess is that fragmentation and I/O time, and other kernel related stuff was
a great cause.

We just got our new DB machines for this project, and will be running all
the tablespaces on raw partitions... I'll post some stats on the old vs. the
new (ufs vs. raw), and some info about our system and oracle setup... just
as soon as we can finish building these machines.


-----Original Message-----
From: Edward Balas [mailto:ebalas at ...1183...]
Sent: Tuesday, March 05, 2002 7:20 PM
To: snort-devel at lists.sourceforge.net
Subject: [Snort-devel] database schema: postgresql

Hey all,

After looking at the schema as defined for postgresql, I was a bit
suprized to find that there is no referential integrity  present.
Further I have noticed some funny behavior when applicatons(acid)
attempt to delete specific events, ie it takes them 3 or so transactions
for each event.  I had an episode the other day where 56k bogus alerts
found their way into the database... The delete queries as generated by
acid ran for a very long time, before I bailed and tried an alternative

I ginned up a modified schema that included referential integrety and also
used cascading deletes.  With this schema I am able to delete all events
of a given signature with one transaction, where the deletes cascade from
the signature to all referencing events, even down to the iphdr and other
such tables.  This also means I can delete a specific event and have the
rows in other tables that reference the cid,sid pair automatically

More information about the Snort-devel mailing list