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

[Jim Mlodgenski]

On Tue, Apr 6, 2010 at 10:33 AM, P Kishor <punk.kish at gmail.com> 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?

Use table partitioning
http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html

>
> 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
>



-- 
--
Jim Mlodgenski
EnterpriseDB (http://www.enterprisedb.com)