MVCC and large objects

From: Scott Corscadden <scott(at)corscadden(dot)ca>
To: pgsql-hackers(at)postgresql(dot)org
Subject: MVCC and large objects
Date: 2012-10-02 13:41:50
Message-ID: C0B0DA1A-2C59-4D48-B4E4-CE3E539DDCAA@corscadden.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

While I'm referring to the 8.4 release, it's a slightly more general question. We're moving away from using pg_largeobject as fast as we can, and while doing so I'm seeing interesting behaviour. I'd like to confirm that this is is expected, 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 higher level 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 commensurate with what we were exporting! As we were doing this as the primary disk was nearly out of space, it was fortunate I 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 a part 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 BLOB fits into MVCC here.

./scc

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2012-10-02 14:19:17 Re: ALTER command reworks
Previous Message Peter Geoghegan 2012-10-02 13:15:46 Re: Hash id in pg_stat_statements