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

[Sufficool, Stanley]

Option #1: Hardware RAID
Pros: Fast, application agnostic performance and failover. Can resize on some controllers and use a file system capable of dynamic resizing (ZFS, XFS, NTFS)
Cons: Expensive controller hardware, possibly complicated file system resizing on cheaper controllers. Filesystem may be offline during resize (unless ZFS).

Option #2: Software RAID
Pros: Linux and Windows has built in resizing for these volumes. Protected data when using RAID 10, 5, 1.
Cons: Can steal processor time when using RAID 5 & 6.

Option #3: Dedicated iSCSI, AoE, FC SAN
Pros: Not stealing processor from application server. Can use software RAID on SAN controller without application server performance penalty. Can be cheap if using existing server and LAN network/fabric.
Cons: You're managing another server, possibly storage fabric and switches. Can get expensive.

Option #4: Cheap onboard SATA JBOD RAID with resizable filesystem.
Pros: Not much if any processor overhead. Can hot-add disks on most SATA boards.
Cons: One disk goes and the whole dataset is shot.

Option #5: Table inheritance or View with INSTEAD OF triggers on insert, delete and update.
Pros: Can create your partitioned table on any disk.
Cons: Complicated setup and many potential pitfalls. May not be able to take full advantage of PostgreSQL indexing in some cases.

Option #6: Compressed file system
Pros: Cheap space savings. Can INCREASE read times where processor is faster than the disk subsystem.
Cons: May steal processor when disk subsystem is sufficiently fast (ie RAID).


My suggestion: Use a filesystem that allows online resizing (EXT3+, XFS, etc..) on a Hardware/Software RAID that allows hot drive addition. This will get you your 99.9% uptime.



>-----Original Message-----
>From: postgis-users-bounces at postgis.refractions.net
>[mailto:postgis-users-bounces at postgis.refractions.net] On
>Behalf Of P Kishor
>Sent: Tuesday, April 06, 2010 7:34 AM
>To: PostGIS Users Discussion
>Subject: [postgis-users] splitting a PostGIS db across multiple disks
>
>
>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
>