Hardware requirements for a PostGIS server

Dear PostgreSQL users,

I am posting here a question that I initially asked on the PostGIS list
[1], where I was advised to try here too (I will keep both lists updated
about the developments on this issue).

I am currently planning to set up a PostgreSQL + PostGIS instance for my
lab. Turns out I believe this would be useful for the whole center, so that
I'm now considering setting up the server for everyone—if interest is
shared of course. At the moment, I am however struggling with what would be
required in terms of hardware, and of course, the cost will depend on
that—at the end of the day, it's really a matter of money well spent. I
have then a series of questions/remarks, and I would welcome any feedback
from people with existing experience on setting up a multi-user PostGIS
server. I'm insisting on the PostGIS aspect, since the most heavy requests
will be GIS requests (intersections, spatial queries, etc.). However,
people with similar PostgreSQL setup may have very relevant comments about
their own configuration.

* My own experience about servers is rather limited: I used PostGIS quite a
bit, but only on a desktop, with only 2 users. The desktop was quite good
(quad-core Xeon, 12 Go RAM, 500 GB hd), running Debian, and we never had
any performance issue (although some queries were rather long, but still
acceptable).

* The use case I'm envisioning would be (at least in the foreseeable future):
- About 10 faculty users (which means potentially a little bit more
students using it); I would have hard time considering more than 4
concurrent users;
- Data would primarily involve a lot (hundreds/thousands) of high
resolution (spatial and temporal) raster and vector maps, possibly over
large areas (Florida / USA / continental), as well as potentially millions
of GPS records (animals individually monitored);
- Queries will primarily involve retrieving points/maps over given
areas/time, as well as intersecting points over environmental layers [from
what I understand, a lot of I/O, with many intermediary tables involved];
other use cases will involve working with steps, i.e. the straight line
segment connecting two successive locations, and intersecting them with
environmental layers;

* I couldn't find comprehensive or detailed guidelines on-line about
hardware, but from what I could see, it seems that memory wouldn't be the
main issue, but the number of cores would be (one core per database
connection if I'm not mistaken). At the same time, we want to make sure
that the experience is smooth for everyone... I was advised on the PostGIS
list to give a look at pgpool (however, UNIX only).

* Is there a difference in terms of possibilities, performance and
usability between a Linux-based and a MS-based server (from the user
perspective)? My center is unfortunately MS-centered, and existing
equipment runs with MS systems... It would thus be easier for them to set
up a MS-based server. Does it change anything for the user? (I insist on
the user perspective, since I and others will not admin the system, but
only use it)

* Does anyone have worked with a server running the DB engine, while the DB
itself was stored on another box/server? That would likely be the case here
since we already have a dedicated box for file storage. Along these lines,
does the system of the file storage box matter (Linux vs. MS)?

* We may also use the server as a workstation to streamline PostGIS
processing with further R analyses/modeling (or even use R from within the
database using PL/R). Again, does anyone have experience doing it? Is a
single workstation the recommended way to work with such workflow? Or would
it be better (but more costly) to have one server dedicated to PostGIS and
another one, with different specs, dedicated to analyses (R)?

I realize my questions and comments may be a confusing, likely because of
the lack of experience about these issues on my side. I really welcome any
feedback of people working with PostgreSQL servers (+ PostGIS ideally!) in
a small unit, or any similar setting that could be informative!

In advance, thank you very much!

Sincerely,
Mathieu Basille.

[1] Start of the thread here:
http://lists.osgeo.org/pipermail/postgis-users/2015-February/040120.html

--

~$ whoami
Mathieu Basille
http://ase-research.org/basille

~$ locate --details
University of Florida \\
Fort Lauderdale Research and Education Center
(+1) 954-577-6314

~$ fortune
« Le tout est de tout dire, et je manque de mots
Et je manque de temps, et je manque d'audace. »
-- Paul Éluard

_______________________________________________
postgis-users mailing list
postgis-users(at)lists(dot)osgeo(dot)org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
--

~$ whoami
Mathieu Basille
http://ase-research.org/basille

~$ locate --details
University of Florida \\
Fort Lauderdale Research and Education Center
(+1) 954-577-6314

~$ fortune
« Le tout est de tout dire, et je manque de mots
Et je manque de temps, et je manque d'audace. »
-- Paul Éluard