We're working with PostgreSQL very successfully on a moderately large project (approximately 12GB of data in our working set)

Currently we're on a 2GB RAM machine with 7200RPM disks. You can imagine the performance goes to hell quite quickly, even with clustered tables and proper indexes, optimised queries and design, etc. We spend most of our time waiting on I/O, for both read and write operations.

We're putting another 2GB of RAM in the box and a 10kRPM Velociraptor disk but those are just stopgap measures while we work out how to go on from here. The whole set is updated very often, so SSDs are out (too expensive too- this project is being run by two students with no money!), and I'm kind of interested to hear if anyone else has any suggestions for cheap (<£100/mo) server/VPS solutions which would involve 12-16GB of RAM and/or snappy hard disks. Or, even better, an alternative solution to the problem. Are there any hosts who specialise in database hosting?

This is kind of a 'oh god there has to be a better way' post, but the basic gist of this is- are there hosts or solutions available at this cost point, if not why not, and what are the cheap solutions to this sort of problem?

  • Regarding hosted servers, I quite happy with the dual quad-core Xeon server with 8GB of RAM and two 500GB 7.2k RPM SATA-II drives in a RAID-1 config from serverloft.com for $179/mo (very close, in current exchange rate terms, to your <£100/mo). Mine's in a DC in the States, but they have a DC in Frankfurt as well. 8GB of RAM offers a lot of room for memory caching from a 12GB working set... – T.J. Crowder Dec 20 '09 at 9:32

It's probably worth double checking that you've tuned the server appropriately for the hardware you're on. Settings to be concerned with include shared_buffers, effective_cache_size, your checkpoint_settings, wal_buffer size, and I'd also make sure your work_mem isnt set too high. There's a wiki page I helped to write which covers most of this stuff, it's a good place to start: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

The next question might be to look at the queries that your running to make sure the query plans aren't causing more i/o than necessary. Better queries and improved indexes might help here.

Oh another thought; you didn't explain what the underlying disks actually were (and/or what you plan to do with the new disks), but it might be worth setting up some type of raid system, or splitting your xlog files to a separate set of disks.

Personally I don't know of any database hosting for Postgres at that price point that would include deeper investigation of what's going on with your database; most of them would leave that to you.

  • Tuning we've been working on for months now and we're not entirely convinced we can wring any more performance out of things down that route. Forgot to mention the disks; they're standard 7200 RPM IDE disks. Less than ideal. They are server grade but not SATA or 10kRPM disks. We're getting one additional 10kRPM disk which will be the new home for some of the larger tables. We'd love to set up RAID but we can't; the server was set up nearly 5 years ago without it, and there's close to 600GB of data to worry about on there. Backups are becoming interesting. – JamesHarrison Dec 20 '09 at 13:44

Hosting is a no-brainer - check hetzner. I'm using them, my friends are using them, and we are really happy with the offering.

On the other hand - to give you some perspective - 12GB is definitely not "moderately large" dataset. I would have hard time classifying it even as medium.

This is not to talk down to you - there are a lot of very important databases that are small. And a lot of big ones that are not that crucial. It all depends on how important the data and/or operations on the data are.

As for tuning - check what xzilla said, and read about EXPLAIN ANALYZE command/output. This is the single most useful part of PostgreSQL.

  • Indeed, it's a small database in the grand scheme of things, but it's certainly out of the range of smaller scale stuff. We'll have a look at hetzner, though. – JamesHarrison Dec 20 '09 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.