Amazon EC2 + S3 Doesn't Cut it for Real Applications

By keeping the indexes under the memory allocated to postgres, I mean the total memory that can be set aside to the DB on the machine. If you look at the pg data files you will find the indexes are just large flat files like BerkeleyDB or whatever homegrown file system thing you want to create.

So you do not need to increase the shared buffers to some crazy amount. In fact, you will have a negative performance result if you do that. The kernel will automatically cache these files upon repeated use. So if you have a machine with 4GB of memory and you can allocate 3GB to the db, then these indexes will just remain cached in memory forever. And you can easily get relatively cheap machines now with 16GB and higher, so this gets you really far.

If you only run sql queries that are indexed appropriately and the indexes are in memory, then I assure you postgres will not be the bottleneck in your app. This is not hard to do; it just takes a little postgresql.conf tweaking and data model forethought. In particular, you turn of sequential scans and decrease the index tuple cost and run select explain on all your queries to ensure that the indexes are always being used. As for the data model, use the smallest data types for indexes, and numeric types whenever possible. That will keep the size down to a minimum. PostgreSQL takes care of the rest.

Again, it depends on what you are doing, but this configuration will usually tie or be significantly better than some home grown file system thing when you get some scale because, like I said, postgres primarily uses the file system memory cache as its memory cache. But if you have some file system process that is accessing a ton of different files you will get an I/O bottleneck just from the lookups alone. And if you use a big flat file, you might as well stick with postgres because of its reliability, security, concurrency, network and backup features.

That being said, I still wouldn't store big static blobs in the db. I would just put them on another machine and let apache serve them up statically and put references to them in the db (indexed by some number id). That way you get the best of both worlds.