Search Postgresql Archives

Avoiding io penalty when updating large objects

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux