[postgis-users] splitting a PostGIS db across multiple disks

[Ben Madin]

Puneet

I'd wonder if a view can do it off multiple tables ? Having said that, you might want to check the archives for inheritance. I recall someone ?Leo? mentioning something similar when I was talking about keeping an archive table without the current data, but being able to look up data from both as a way of dealing with Change of Support issues. I think you can have an inherited table with no data inheriting from a number of tables. You can stop the query searching all tables by having constraints on them - maybe they could reflect bounding boxes?


cheers

Ben




On 06/04/2010, at 22:33 , P Kishor wrote:

> I asked this question yesterday, and received a very helpful pointer
> from Ben Madin re. TABLESPACES. As noted in my reply in that thread, I
> am also investigating the possibility of splitting a single table
> across multiple disks.
> 
> However, I am going to post this question in a different way in this new thread.
> 
> Suppose I have a table FOO0 that stores info about every state in the
> union. I know that some of these states will have mongo number of
> rows, but I don't have to build all the states immediately. So, I
> start with a few states' worth data, putting it in the default
> /usr/local/pgsql/data location.
> 
> Then I start outgrowing that disk, and need to add another state, so I
> add another disk, create a new tablespace, and create a new table
> called FOO1 in this new tablespace. Then I can store the new states in
> FOO1. As long as I break up my table into FOO0, FOO1, FOO2, and so on,
> I can store each FOOn in a new tablespace. And, as long as I ensure
> that each FOOn table contains a geographically consistent spatial
> extent, I can build logic in my application to query the correct
> table.
> 
> So, lets say 0 lon to -10 lon data are stored in FOO0, and -10 lon to
> -20 lon in FOO1, then if the user requests data for -5 lon to -15 lon,
> I will have to query both FOO0 and FOO1.
> 
> More work for me, but it is doable, no? Any insights on how to handle
> something like this?
> 
> A corollary question -- are their any speed advantages to actually
> creating multiple PostGIS instances, perhaps even splitting them
> across multiple machines? Of course, it is going to be a pain in the
> ass for me to maintain more than one instance of PostGres/PostGIS, so
> I am not thrilled at that possibility. I'd rather have a single
> instance just be managing data across multiple locations as required.
> 
> 
> -- 
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> -----------------------------------------------------------------------
> Assertions are politics; backing up assertions with evidence is science
> =======================================================================
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users