While I'm referring to the 8.4 release, it's a slightly more general question. We're moving away from using
pg_largeobjectas fast as we can, and while doing so I'm seeing interesting behaviour. I'd like to confirm that this is
isexpected, or perhaps I don't have tuning parameters set quite correctly. I believe we have standard autovacuum
running.
* Table Foo has an oid "data" column, points to a valid BLOB. We modified this table to also have a "datafilepath"
column.
* We wrote a throttleable "copier" process which walks rows, reads the BLOB data out to a file and then UPDATEs the
"datafilepath"column with where it wrote it. We did not alter the BLOB data in any way. When asked for byte data, the
higherlevel code will first return it from the datafilepath if it's there, and fall back on the lo otherwise.
While the above was working away, we nearly missed the fact that the "public.pg_largeobject" table seemed to be growing
commensuratewith what we were exporting! As we were doing this as the primary disk was nearly out of space, it was
fortunateI could pause this work. We were able to move the entire system and it's now continuing along, but my
question:
Is this expected? I'm a little surprised. My theory is that MVCC seems to be including the pg_largeobject referenced as
apart of the row, and even though we're not updating the BLOB at all, a snapshot is getting created. *Is this
expected*?
Many thanks - single-link RTFM answers welcome, I have seen the MVCC through pictures, and I get it - just not how a
BLOBfits into MVCC here.
./scc
