Thread: Status of tablespaces
Looking at the TODO document saw the pointer to the tablespaces discussions. In there saw an email from march 2002 by Jim Buttafuoco with some patches for implementing table spaces. Saw some feedback about the the email, but did not see any final resolutions. I was also a bit surprised that this feature was under the Exotic section. Isn't this something which can be very helpfull? For instance I recently got a new server for a project, but now the machine which used to be the primary machine, soon to become backup, may not be big enough to hold the data. It has two drives, but unless I use symlinks currently there will be no easy way to use both drives unless we get a RAID controller for the machine.
It is badly needed yet. Not sure why it is under Exotic. Probably means at the time I added it, it was "way out there". I suspect it should be moved to administration. I will do that. --------------------------------------------------------------------------- Francisco Reyes wrote: > Looking at the TODO document saw the pointer to the tablespaces > discussions. In there saw an email from march 2002 by Jim > Buttafuoco with some patches for implementing table spaces. > Saw some feedback about the the email, but did not see any final > resolutions. > > > I was also a bit surprised that this feature was under the Exotic section. > Isn't this something which can be very helpfull? > > For instance I recently got a new server for a project, but now the > machine which used to be the primary machine, soon to become backup, may > not be big enough to hold the data. It has two drives, but unless I use > symlinks currently there will be no easy way to use both drives unless we > get a RAID controller for the machine. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Mon, 27 Jan 2003, Bruce Momjian wrote: > It is badly needed yet. Not sure why it is under Exotic. Probably > means at the time I added it, it was "way out there". I suspect it > should be moved to administration. I will do that. Very glad to read this. Althought the ultimate solution is a RAID disk subsystem for smaller systems tablespaces are a real helper.
> Date: Mon, 27 Jan 2003 18:16:31 -0500 (EST) > From: Bruce Momjian <pgman@candle.pha.pa.us> > > It is badly needed yet. Not sure why it is under Exotic. Probably > means at the time I added it, it was "way out there". I suspect it > should be moved to administration. I will do that. > I only know the Oracle implementation of tablespaces and have found it rather annoying: when the tablespace is created Oracle grabs the entire(!!!) space from the system, independent of the actual amount of data currently stored in the tablespace. This causes trouble on small systems because the database needs all disk space in advance. What are the plans for Postgres tablespaces? Only an upper bound for the storage space? Will tablespaces be optional or mandatory? Thanks, Christoph Dalitz
Christoph Dalitz <christoph.dalitz@hs-niederrhein.de> writes:
> What are the plans for Postgres tablespaces?
Well, what I envision is only a cleaner reimplementation of the
existing LOCATION facility: the DBA will be able to designate certain
directories as tablespaces and then assign individual tables, indexes,
etc to particular tablespaces. Presumably, the DBA places each
tablespace directory on a different disk (otherwise there's not much
point in the exercise), and then the arrangement lets him control what
lives where.
We have no intention of changing over to Oracle-style disk space
management, if that's what you were asking.
regards, tom lane
On Tue, 28 Jan 2003 10:19:23 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Christoph Dalitz <christoph.dalitz@hs-niederrhein.de> writes: > > What are the plans for Postgres tablespaces? > > Well, what I envision is only a cleaner reimplementation of the > existing LOCATION facility: the DBA will be able to designate certain > directories as tablespaces and then assign individual tables, indexes, > etc to particular tablespaces. Presumably, the DBA places each > tablespace directory on a different disk (otherwise there's not much > point in the exercise), and then the arrangement lets him control what > lives where. > What might be useful were some kind of quota (on the database side, not the operating system) for a tablespace. Any plans to implement something in that direction? > We have no intention of changing over to Oracle-style disk space > management, if that's what you were asking. > Ok, I see there is no reason to worry. Christoph Dalitz
Christoph Dalitz <christoph.dalitz@hs-niederrhein.de> writes:
> What might be useful were some kind of quota (on the database
> side, not the operating system) for a tablespace.
> Any plans to implement something in that direction?
No. Enforcing a quota would imply creating a synchronization bottleneck
among all update operations, so I'd resist any such proposal...
especially seeing that the problem can already be solved at the operating
system level.
regards, tom lane
hi Dnia wto 28. stycznia 2003 16:19, Tom Lane napisał: > > Well, what I envision is only a cleaner reimplementation of the > existing LOCATION facility: the DBA will be able to designate certain > directories as tablespaces and then assign individual tables, indexes, > etc to particular tablespaces. Presumably, the DBA places each > tablespace directory on a different disk (otherwise there's not much > point in the exercise), and then the arrangement lets him control what > lives where. > Does this mean (in long term) ability to: CREATE SCHEMA xyz DEFAULT TABLESPACE ts_data INDEX TABLESPACE ts_index; (to define place for created objects) or ALTER TABLE tab1 TABLESPACE ts_data1; (to change location of datafile for 'tab1') ? -- Mariusz Czulada
Please unsubscribe me from this list. The WWW page will not let me do this without a password, which I don't have. Thank you, Travis Bauer
Francisco J Reyes <fran@natserv.net> writes:
> I also wonder if the syntax of the current create DB won't have to be
> changed once tablespaces are created.
I'd be inclined to remove the WITH LOCATION option the instant we have
an adequate tablespace substitute. It's such an ugly, messy, unsafe
way to do things --- depending on environment variables is bad news.
There are things I'm willing to keep around in the name of backward
compatibility, but this isn't one of them.
It's probably true that some other WITH options would have to be added
to CREATE DATABASE (and/or ALTER DATABASE) instead. For instance, I'd
imagine that we'd want to be able to specify a default tablespace for
a database; perhaps separate defaults for its tables and indexes.
regards, tom lane
All, This is exactly what my patch added. The ability to do the following CREATE DATABASE foo TABLESPACE X INDEX_TABLESPACE Y TEMP_TABLESPACE Z; CREATE SCHEMA foo TABLESPACE X INDEX_TABLESPACE Y TEMP_TABLESPACE Z; -- override the database setting CREATE TABLE T (...) TABLESPACE X; -- override the SCHEMA setting CREATE INDEX I (...) TABLESPACE X; -- override the SCHEMA setting CREATE TABLESPACE A location '/XXX/YYY/ZZZ'; -- define a tablespace If any of the TABLESPACE options are missing take from DATABASE/SCHEMA setting. I don't remember exactly why it was rejected at the time. Jim > hi > > Dnia wto 28. stycznia 2003 16:19, Tom Lane napisa³: > > > > Well, what I envision is only a cleaner reimplementation of the > > existing LOCATION facility: the DBA will be able to designate certain > > directories as tablespaces and then assign individual tables, indexes, > > etc to particular tablespaces. Presumably, the DBA places each > > tablespace directory on a different disk (otherwise there's not much > > point in the exercise), and then the arrangement lets him control what > > lives where. > > > > Does this mean (in long term) ability to: > > CREATE SCHEMA xyz > DEFAULT TABLESPACE ts_data > INDEX TABLESPACE ts_index; > (to define place for created objects) > > or > > ALTER TABLE tab1 TABLESPACE ts_data1; > (to change location of datafile for 'tab1') > > ? > > -- > > Mariusz Czulada > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
"Jim Buttafuoco" <jim@contactbda.com> writes:
> I don't remember exactly why it was rejected at the time.
Because it was hardwired to do *only* that, and not any more general
notion of tablespaces. Or at least that's my recollection of the
discussion.
regards, tom lane
Tom, Can you explain what you would be looking for in tablespaces. I tried to keep my idea of what a tablespace means to postgresql it as simple as posible. Jim > "Jim Buttafuoco" <jim@contactbda.com> writes: > > I don't remember exactly why it was rejected at the time. > > Because it was hardwired to do *only* that, and not any more general > notion of tablespaces. Or at least that's my recollection of the > discussion. > > regards, tom lane
> > What are the plans for Postgres tablespaces? > > Well, what I envision is only a cleaner reimplementation of the > existing LOCATION facility: the DBA will be able to designate > certain directories as tablespaces and then assign individual > tables, indexes, etc to particular tablespaces. Presumably, the DBA > places each tablespace directory on a different disk (otherwise > there's not much point in the exercise), and then the arrangement > lets him control what lives where. I'm reaching back four years now, but I found a very viable use for extents and table spaces (albeit Oracle's implementation and interface really blew): 1) extents - appending/writing to already allocated, zero'ed out data allowed for significantly faster inserts (don't know if this is really the case or not, or if it's still valid, but that was the Gospel for the time) 2) extents in table spaces - were invaluable when creating cyclical data tables. For example: in a table space, allocate extents 8 extents (each extent big enough for the day's worth of data - an extent being pre-allocated disk space), then with a stored procedure/rule, each day had its data inserted into an extent. On the 8th day, drop the oldest extent, reformat the extent, then re-add the extent to the table space: instant daily truncation without having to do a delete on 200M rows. Being able to do this was immensely useful for time driven cyclical data structures (25 extents for the daily data, 8 extents for the weekly data, 32 extents for the monthly data, 5 extents for the quarterly data, etc.). Hrm, now that I think about it, I suppose this could be done with tables instead of extents and a view on top of them... ::ponders:: -sc -- Sean Chittenden
hi Dnia wto 28. stycznia 2003 16:19, Tom Lane napisał: > > Well, what I envision is only a cleaner reimplementation of the > existing LOCATION facility: the DBA will be able to designate certain > directories as tablespaces and then assign individual tables, indexes, > etc to particular tablespaces. Presumably, the DBA places each > tablespace directory on a different disk (otherwise there's not much > point in the exercise), and then the arrangement lets him control what > lives where. > Does this mean (in long term) ability to: CREATE SCHEMA xyz DEFAULT TABLESPACE ts_data INDEX TABLESPACE ts_index; (to define place for created objects) or ALTER TABLE tab1 TABLESPACE ts_data1; (to change location of datafile for 'tab1') ? -- Mariusz Czulada Sekcja Utrzymania Systemów Informacyjnych, Pion Technologii i Bezpieczenstwa TP Internet Sp. z o. o. ---------------------------------------------------------- Windows 98 is like a gun - unless it's loaded, it's harmless.
On Tue, 28 Jan 2003, Tom Lane wrote: > Well, what I envision is only a cleaner reimplementation of the > existing LOCATION facility: the DBA will be able to designate certain > directories as tablespaces and then assign individual tables, indexes, > etc to particular tablespaces. After reading your post went out to look for the LOCATION facility. Is that "create <DB> wit location". Definitely a step forward, but doesn't help with large databases. I also wonder if the syntax of the current create DB won't have to be changed once tablespaces are created.
On Tue, 28 Jan 2003, Sean Chittenden wrote: > 1) extents - appending/writing to already allocated, zero'ed out data > allowed for significantly faster inserts (don't know if this is > really the case or not, or if it's still valid, but that was the > Gospel for the time) With postgres using the filesystem, it's probably quite the opposite. If a file has had the space allocated already, when "appending" into this allocated space, the OS has only once choice of block on the disk to update, and you are reasonably likely to have to wait to read that block before writing it. If the OS is allocating new blocks to the end of the file, you will definitely not do a read of the block before writing it, and the OS also has the opportunity to chose from any free blocks on the disk, rather than the block that happened to be the most convenient free block when the file was zeroed. Possibly, if your writes fragmented across a lot of tables, and you do the zeroing-out early enough, and you happen to do a lot of long linear scans on the tables, you'd end up with better performance by creating the files first. But I wouldn't anticipate that to be a commmon case. Note, too, that one of the reasons pre-allocating extents in Oracle gives such an advantage is because its block allocation system is not so intelligent as modern (well, if you call Berkeley FFS "modern" :-)) filesystems. > 2) extents in table spaces - were invaluable when creating cyclical > data tables. For example: in a table space, allocate extents 8 > extents (each extent big enough for the day's worth of data - an > extent being pre-allocated disk space), then with a stored > procedure/rule, each day had its data inserted into an extent. On > the 8th day, drop the oldest extent, reformat the extent, then > re-add the extent to the table space: instant daily truncation > without having to do a delete on 200M rows. Cool...but what does that do to the indexes and foreign key relationships? > Hrm, now that I think about it, I suppose this could be done with > tables instead of extents and a view on top of them... ::ponders:: It can make a lot of queries work not so well, because the optimizer won't do certain optimizations across a UNION SELECT that it will do on a single table. But you could always just re-code your queries to work directly against the appropriate tables. You'd probably want to build some little query generator to do this sort of thing. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
> Note, too, that one of the reasons pre-allocating extents in Oracle > gives such an advantage is because its block allocation system is > not so intelligent as modern (well, if you call Berkeley FFS > "modern" :-)) filesystems. Do you think this would do better with UFS2? I'm confused if you're implying that FFS is better than _____ or if you think that FFS leaves a lot to be desired in this department. > > 2) extents in table spaces - were invaluable when creating cyclical > > data tables. For example: in a table space, allocate extents 8 > > extents (each extent big enough for the day's worth of data - an > > extent being pre-allocated disk space), then with a stored > > procedure/rule, each day had its data inserted into an extent. On > > the 8th day, drop the oldest extent, reformat the extent, then > > re-add the extent to the table space: instant daily truncation > > without having to do a delete on 200M rows. > > Cool...but what does that do to the indexes and foreign key relationships? This was some magic as far as I could tell, but cheers to pulling from the far reaches of my memory. In the extents model of the universe, let's say you have one extent for an index, and 8 for the various days of the week (+1 for dropping/formatting). I don't know where the mapping of data to extent resides, but I'd imagine that if the index has data being stored on various extents, the index would be aware of the ranges of data that are stored on what extents. Off the top of my head, I can only imagine that they'd do something similar to the following. Each extent had its own index with its own respective range. The ranges of the indexes were stored in a system catalog such that when an index lookup is performed, it only queries the index on the appropriate extent that the database is querying over. Then when an extent is dropped, all you have to do is update the system catalog and dd if=/dev/zero to your hearts content. Anyway, the point of my original post is that being able to do stuff like that and have it return near instantly is wonderful when dealing with very large quantities of data. Seriously, try deleting 100M rows without this. With this, it'll happen in less than a second. :) > > Hrm, now that I think about it, I suppose this could be done with > > tables instead of extents and a view on top of them... > > ::ponders:: > > It can make a lot of queries work not so well, because the optimizer > won't do certain optimizations across a UNION SELECT that it will do > on a single table. But you could always just re-code your queries to > work directly against the appropriate tables. You'd probably want to > build some little query generator to do this sort of thing. Very very true. Selecting data across time boundary extents would be super inefficient, but easier than nothing. -- Sean Chittenden
On Tue, 28 Jan 2003, Sean Chittenden wrote: > > Note, too, that one of the reasons pre-allocating extents in Oracle > > gives such an advantage is because its block allocation system is > > not so intelligent as modern (well, if you call Berkeley FFS > > "modern" :-)) filesystems. > > Do you think this would do better with UFS2? I'm confused if you're > implying that FFS is better than _____ or if you think that FFS leaves > a lot to be desired in this department. I think that FFS does a pretty good job of this. Certainly better than Oracle does (at least as of my last look at Oracle block allocation, a couple of years ago). My comment was related to the fact that FFS is far from new technology. > Anyway, the point of my original post is that being able to do stuff > like that and have it return near instantly is wonderful when dealing > with very large quantities of data. Seriously, try deleting 100M rows > without this. With this, it'll happen in less than a second. :) Without even getting into the index side of things (because I don't want to get into a big long explanation here), there's no way it could delete all that data that quickly if another table had foreign keys referencing it. You have to check *every* other table for records matching the ones you're deleting, which at the very least means reading all of the ones you're deleting and doing index searches or scans on the other tables. Basically, this looks like PostgreSQL's TRUNCATE, which also ignores the FK problems (though it can deal with the indexes, since you just delete all of them as well). Because of this, the technique is usable only in a limited number of situations. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
Tom, 1.On the surface the work Jim has done seem to me to be a great start. Could this be included in a future release? 2.I know that oracle has control files that have all the tablespace to datafiles and log files info in it. Putting the WAL on it own or a quiet disk must be a good thing. So may be a config file that could override the default location for system/pg_catalog stuff and WAL. Thanks. Regards, Simon Jim Buttafuoco wrote: >All, > >This is exactly what my patch added. The ability to do the following > >CREATE DATABASE foo TABLESPACE X INDEX_TABLESPACE Y TEMP_TABLESPACE Z; >CREATE SCHEMA foo TABLESPACE X INDEX_TABLESPACE Y TEMP_TABLESPACE Z; -- override the database setting >CREATE TABLE T (...) TABLESPACE X; -- override the SCHEMA setting >CREATE INDEX I (...) TABLESPACE X; -- override the SCHEMA setting > >CREATE TABLESPACE A location '/XXX/YYY/ZZZ'; -- define a tablespace > >If any of the TABLESPACE options are missing take from DATABASE/SCHEMA setting. > >I don't remember exactly why it was rejected at the time. > >Jim > > > > > >>hi >> >>Dnia wto 28. stycznia 2003 16:19, Tom Lane napisa³: >> >> >>>Well, what I envision is only a cleaner reimplementation of the >>>existing LOCATION facility: the DBA will be able to designate certain >>>directories as tablespaces and then assign individual tables, indexes, >>>etc to particular tablespaces. Presumably, the DBA places each >>>tablespace directory on a different disk (otherwise there's not much >>>point in the exercise), and then the arrangement lets him control what >>>lives where. >>> >>> >>> >>Does this mean (in long term) ability to: >> >>CREATE SCHEMA xyz >> DEFAULT TABLESPACE ts_data >> INDEX TABLESPACE ts_index; >>(to define place for created objects) >> >>or >> >>ALTER TABLE tab1 TABLESPACE ts_data1; >>(to change location of datafile for 'tab1') >> >>? >> >>-- >> >>Mariusz Czulada >> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/users-lounge/docs/faq.html >> >> > > > > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > >
> > Anyway, the point of my original post is that being able to do > > stuff like that and have it return near instantly is wonderful > > when dealing with very large quantities of data. Seriously, try > > deleting 100M rows without this. With this, it'll happen in less > > than a second. :) > > Without even getting into the index side of things (because I don't > want to get into a big long explanation here), there's no way it > could delete all that data that quickly if another table had foreign > keys referencing it. You have to check *every* other table for > records matching the ones you're deleting, which at the very least > means reading all of the ones you're deleting and doing index > searches or scans on the other tables. Agreed. With the volume of data necessary to want to use this kind of a technique, you wouldn't want foreign keys anyway. > Basically, this looks like PostgreSQL's TRUNCATE, which also ignores > the FK problems (though it can deal with the indexes, since you just > delete all of them as well). > > Because of this, the technique is usable only in a limited number of > situations. But also invaluable in the cases where it is used. :) SQL backed Cricket/MRTG anyone? -sc -- Sean Chittenden
On Fri, 31 Jan 2003, Sean Chittenden wrote: > But also invaluable in the cases where it is used. :) SQL backed > Cricket/MRTG anyone? -sc Actually, I've found that under some circumstances like this (lots of data, import and deletion speed are the hightest priority, and you're willing to do sequential scans across ranges for searches), not using a database at all, but instead storing your data in compressed flat files is by far the fastest solution. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
On Tue, 28 Jan 2003, Tom Lane wrote: > Francisco J Reyes <fran@natserv.net> writes: > > I also wonder if the syntax of the current create DB won't have to be > > changed once tablespaces are created. > > I'd be inclined to remove the WITH LOCATION option the instant we have > an adequate tablespace substitute. It's such an ugly, messy, unsafe > way to do things --- depending on environment variables is bad news. > There are things I'm willing to keep around in the name of backward > compatibility, but this isn't one of them. How about putting a warning on the docs that this functionality may be removed once Tablespaces are implemented. One commonly doesn't go around creating tables every day, but for those considering this function may be good to warn then in advance.
Is the functionality that Jim describes going to be available in a future release? Jim Buttafuoco wrote: >All, > >This is exactly what my patch added. The ability to do the following > >CREATE DATABASE foo TABLESPACE X INDEX_TABLESPACE Y TEMP_TABLESPACE Z; >CREATE SCHEMA foo TABLESPACE X INDEX_TABLESPACE Y TEMP_TABLESPACE Z; -- override the database setting >CREATE TABLE T (...) TABLESPACE X; -- override the SCHEMA setting >CREATE INDEX I (...) TABLESPACE X; -- override the SCHEMA setting > >CREATE TABLESPACE A location '/XXX/YYY/ZZZ'; -- define a tablespace > >If any of the TABLESPACE options are missing take from DATABASE/SCHEMA setting. > >I don't remember exactly why it was rejected at the time. > >Jim > > > > > >>hi >> >>Dnia wto 28. stycznia 2003 16:19, Tom Lane napisa³: >> >> >>>Well, what I envision is only a cleaner reimplementation of the >>>existing LOCATION facility: the DBA will be able to designate certain >>>directories as tablespaces and then assign individual tables, indexes, >>>etc to particular tablespaces. Presumably, the DBA places each >>>tablespace directory on a different disk (otherwise there's not much >>>point in the exercise), and then the arrangement lets him control what >>>lives where. >>> >>> >>> >>Does this mean (in long term) ability to: >> >>CREATE SCHEMA xyz >> DEFAULT TABLESPACE ts_data >> INDEX TABLESPACE ts_index; >>(to define place for created objects) >> >>or >> >>ALTER TABLE tab1 TABLESPACE ts_data1; >>(to change location of datafile for 'tab1') >> >>? >> >>-- >> >>Mariusz Czulada >> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/users-lounge/docs/faq.html >> >> > > > > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > >