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

[Brian Modra]

On 06/04/2010, Chris Hermansen <chris.hermansen at timberline.ca> wrote:
> What about using RAID?  No data reorganization necessary.
>
> http://wiki.postgresql.org/wiki/Community_Disk_Tuning_Guide
>
> As the above notes, disk performance (eg spindle speed) is important.
>
> Also, to get good performance, you need to understand your application's
> performance needs.  Do you have a lot of small queries, etc etc.  If you
> don't understand your application's performance needs, then you may be
> better off running your application for awhile and measuring its
> bottlenecks before you decide on a storage strategy.

very good points.
Regarding RAID, as a general rule of thumb, RAID 1+0 is best for a
database, also called RAID 10.

To configure a good RAID system, make sure you have a multi-processor
mother-board, lots of memory, and an Adaptec RAID controller (hardware
RAID, not software RAID.)

>
> 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.
>>
>>
>>
>
> --
> Regards,
>
> Chris Hermansen    ·    mailto:chris.hermansen at timberline.ca
> tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625
> Timberline Natural Resource Group · http://www.timberline.ca
> 401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


-- 
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/