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: [email protected] >[mailto:[email protected]] 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 [email protected] >http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
