Thread: Large object + FREEZE?
Now that we have COPY FREEZE, I'm thinking about adding similar option to creating large objects. In 9.3 the maximum size of large objects are increased. That means, the first access to a large object will trigger more writes because of hint bit updation. Also subsequent VACUUM may trigger that as well. If we could freeze arge objects while creating, it could reduce the writes dramatically as COPY FREEZE already does. Opinions? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Tue, Jul 2, 2013 at 12:16:18PM +0900, Tatsuo Ishii wrote: > Now that we have COPY FREEZE, I'm thinking about adding similar option > to creating large objects. In 9.3 the maximum size of large objects > are increased. That means, the first access to a large object will > trigger more writes because of hint bit updation. Also subsequent > VACUUM may trigger that as well. If we could freeze arge objects while > creating, it could reduce the writes dramatically as COPY FREEZE > already does. I was not aware that large objects had to feeze each 8k segment. Do they? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Mon, Jul 1, 2013 at 11:16 PM, Tatsuo Ishii <ishii@postgresql.org> wrote: > Now that we have COPY FREEZE, I'm thinking about adding similar option > to creating large objects. In 9.3 the maximum size of large objects > are increased. That means, the first access to a large object will > trigger more writes because of hint bit updation. Also subsequent > VACUUM may trigger that as well. If we could freeze arge objects while > creating, it could reduce the writes dramatically as COPY FREEZE > already does. > > Opinions? COPY FREEZE only works if the table was created in the same transaction. Otherwise, an error midway through could leave the table and index inconsistent. I think that's a killer for this proposal, because obviously when a large object is created, pg_largeobject will be pre-existing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> On Mon, Jul 1, 2013 at 11:16 PM, Tatsuo Ishii <ishii@postgresql.org> wrote: >> Now that we have COPY FREEZE, I'm thinking about adding similar option >> to creating large objects. In 9.3 the maximum size of large objects >> are increased. That means, the first access to a large object will >> trigger more writes because of hint bit updation. Also subsequent >> VACUUM may trigger that as well. If we could freeze arge objects while >> creating, it could reduce the writes dramatically as COPY FREEZE >> already does. >> >> Opinions? > > COPY FREEZE only works if the table was created in the same > transaction. Otherwise, an error midway through could leave the table > and index inconsistent. I think that's a killer for this proposal, > because obviously when a large object is created, pg_largeobject will > be pre-existing. Argh. You are right. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp