Thread: Large Object Location in 7.3
I expect (actually hope) to have thousands and thousands of blob/clobs in the db I am designing. I would like such largeobjects to be stored in their own file system. Someone had said that there might be support for "tablespaces/locations" in 7.3. Is there a description somewhere of this work or a "spec"? Thanks Richard
Richard Emberson wrote: > > I expect (actually hope) to have thousands and thousands of blob/clobs > in the db I am designing. > I would like such largeobjects to be stored in their own file system. > Someone had said that there > might be support for "tablespaces/locations" in 7.3. Is there a > description somewhere of this work > or a "spec"? Sure, find the oid of pg_largeobject and symlink that to another file system. You need to do that toast table and any indexes for the table too. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian wrote: > Richard Emberson wrote: > > > > I expect (actually hope) to have thousands and thousands of blob/clobs > > in the db I am designing. > > I would like such largeobjects to be stored in their own file system. > > Someone had said that there > > might be support for "tablespaces/locations" in 7.3. Is there a > > description somewhere of this work > > or a "spec"? > > Sure, find the oid of pg_largeobject and symlink that to another file > system. You need to do that toast table and any indexes for the table > too. > Can this be done within a PL/pgsql function or does one have to stop the database (everytime a user enters a new blob) and do it by hand or external script? Also, this involves copying the blob (largeobject file) to the other file system which means that that the file was first created and written, and then had to be copied. Twice the work. Is there a way so that it only has to be written once? > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Richard Emberson wrote: > Bruce Momjian wrote: > > > Richard Emberson wrote: > > > > > > I expect (actually hope) to have thousands and thousands of blob/clobs > > > in the db I am designing. > > > I would like such largeobjects to be stored in their own file system. > > > Someone had said that there > > > might be support for "tablespaces/locations" in 7.3. Is there a > > > description somewhere of this work > > > or a "spec"? > > > > Sure, find the oid of pg_largeobject and symlink that to another file > > system. You need to do that toast table and any indexes for the table > > too. > > > > Can this be done within a PL/pgsql function or does one have to stop the > database (everytime a > user enters a new blob) and do it by hand or external script? pg_largeobject contains all large objects, at least in 7.1 and earlier. Don't remember about 7.0. Just has to be done once per database, with system down. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Richard Emberson wrote: >> I expect (actually hope) to have thousands and thousands of blob/clobs >> in the db I am designing. >> I would like such largeobjects to be stored in their own file system. > Sure, find the oid of pg_largeobject and symlink that to another file > system. You need to do that toast table and any indexes for the table > too. If Richard's envisioning more than 1GB of large objects, I don't think he's going to be very satisfied with manual symlinking. This does bring up an interesting point: the tablespace schemes we've discussed so far don't allow system catalogs to be moved out of the default tablespace for a database. That doesn't bother me for most of the system catalogs ... but pg_largeobject seems like it might be an exception. regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Richard Emberson wrote: > >> I expect (actually hope) to have thousands and thousands of blob/clobs > >> in the db I am designing. > >> I would like such largeobjects to be stored in their own file system. > > > Sure, find the oid of pg_largeobject and symlink that to another file > > system. You need to do that toast table and any indexes for the table > > too. > > If Richard's envisioning more than 1GB of large objects, I don't think > he's going to be very satisfied with manual symlinking. The system I am designing it is hoped will have 100s of GBs of large objects, a whole coda file system full of them. I believe that I can partition the DB into subsets (multiple instance of postgresql) so that each instance might have only 10GBs. > > > This does bring up an interesting point: the tablespace schemes we've > discussed so far don't allow system catalogs to be moved out of the > default tablespace for a database. That doesn't bother me for most > of the system catalogs ... but pg_largeobject seems like it might be > an exception. > > regards, tom lane