[Snort-devel] database schema: postgresql

Roman Danyliw roman at ...49...
Tue Mar 5 13:10:17 EST 2002


Referential integerity has been discussed in some detail in the past.  It was
explicitly avoided in the schema used in Snort to decrease the INSERTion time.

As you point out, this omission can be painful from the analysis perspective. 
If you look in ACID CVS, I committed some code this morning which provides the
DDL commands to create the necessary referential integrity to do ON DELETE
CASCADE-ing. 

Roman

On Tue, 5 Mar 2002 20:20:10 -0500 (EST), Edward Balas <ebalas at ...1183...> wrote
:

> 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
> approach.
> 
> 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
> removed.
> 
> From what I can gather from limited testing this not only helps preserve
> the integrity of the data, but also seems to significanly improve delete
> performance(due to the cascading).OB
> 
> Anywho I havent performed extensive testing but was wondering what
> people's thoughts where wrt to modifying the schema in this manner?
> 
> Attached is a draft copy of the schema, if such a modification is of
> interest to folks I would be willing to perform more elaborate testing
> of the performance impacts of such a modification...
> 
> 
> Ed
> 
> 
> 
> 
> 
> 




More information about the Snort-devel mailing list