[GENERAL] Speed of lo_unlink vs. DELETE on BYTEA

[Reuven M. Lerner]

Hi, everyone. I'm working with someone who has a database application currently running under PostgreSQL 8.3.  Among other things, there is a main table that is referenced by a number of other tables via a foreign key.  One of those tables has a field of type "oid," which then points to a large object.   When a record in the main table is deleted, there is a rule (yes a rule -- not a trigger) in the referencing table that performs a lo_unlink on the associated object. This means that for every DELETE we perform on the main table, we're doing an lo_unlink on the large objects.  This also means that if we do a mass delete from that main table, we're executing lo_unlike once for every deleted row in the main table, which is taking a heckuva long time.  I ran EXPLAIN ANALYZE, and a good 40-50 percent of our time spent deleting is in the execution of this rule. I just want to check that my intuition is correct: Wouldn't it be way faster and more efficient for us to use BYTEA columns to store the data (which can get into the 20-50 MB range), and for us to just depend on ON DELETE CASCADE, rather than a rule?  Or are we going to encounter performance issues regardless of which technique we use, and we need to find a way to delete these large pieces of data in the background  Or should we be using large objects, and then find a way other than a rule to deal with deleting them on this sort of scale?  Or (of course) am I missing another good option? Thanks for any and all advice, as usual! Reuven -- Reuven M. Lerner -- Web development, consulting, and training Mobile: +972-54-496-8405 * US phone: 847-230-9795 Skype/AIM: reuvenlerner