Avoiding io penalty when updating large objects

From: Mark Dilger <pgsql(at)markdilger(dot)com>
To: pgsql-hackers(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Avoiding io penalty when updating large objects
Date: 2005-06-29 02:38:43
Message-ID: 42C209B3.5010901@markdilger.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

I would like to write a postgres extension type which represents a btree of data
and allows me to access and modify elements within that logical btree. Assume
the type is named btree_extension, and I have the table:

CREATE TABLE example (
a TEXT,
b TEXT,
c BTREE_EXTENSION,
UNIQUE(a,b)
);

If, for a given row, the value of c is, say, approximately 2^30 bytes large,
then I would expect it to be divided up into 8K chunks in an external table, and
I should be able to fetch individual chunks of that object (by offset) rather
than having to detoast the whole thing.

But what if I want to update a single chunk, or only a couple chunks? How can I
go about loading chunks, modifying them, and writing them back to disk, without
incurring the overhead of writing 2^30 bytes back out to disk? And if I can do
this in a hand coded c function, what does the corresponding SQL statement look
like to call the function? Is it an update statement?

Also, is it possible that only the rows in the *external* table get marked as
updated during my transaction, or will the row in the "example" table be marked
as updated?

I expect this is not possible, but it would be really great if it were, and I
haven't found a definitive "No, you can't do this" in the documentation yet.
The idea is to store the first and second level entries of a tree directly in
columns "a" and "b", but then to store arbitrarily deep children in a btree type
stored in column "c". It doesn't make sense to have a really wide table to
represent the tree for multiple reasons, mostly involving data duplication in
the leftward columns but also because you can't know ahead of time how wide to
make the table.

I look forward to any useful responses.

Thanks,

Mark Dilger

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2005-06-29 02:53:17 Re: [HACKERS] Avoiding io penalty when updating large objects
Previous Message Steve - DND 2005-06-29 01:39:16 Connection local variables?

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2005-06-29 02:47:11 Re: Open items
Previous Message Bruce Momjian 2005-06-29 02:02:53 Open items