Hmmm, just wondering - what would be needed to build such 'workload library'? Building it from scratch is not feasible IMHO, but I guess people could provide their own scripts (as simple as 'set up a a bunch of tables, fill it with data, run some queries') and there's a pile of such examples in the pgsql-performance list.
The easiest place to start is by re-using the work already done by the TPC for benchmarking commercial databases. There are ports of the TPC workloads to PostgreSQL available in the DBT-2, DBT-3, and DBT-5 tests; see http://wiki.postgresql.org/wiki/Category:Benchmarking for initial information on those (the page on TPC-H is quite relevant too). I'd like to see all three of those DBT tests running regularly, as well as two tests it's possible to simulate with pgbench or sysbench: an in-cache read-only test, and a write as fast as possible test.
The main problem with re-using posts from this list for workload testing is getting an appropriately sized data set for them that stays relevant. The nature of this sort of benchmark always includes some notion of the size of the database, and you get different results based on how large things are relative to RAM and the database parameters. That said, some sort of systematic collection of "hard queries" would also be a very useful project for someone to take on.
People show up regularly who want to play with the optimizer in some way. It's still possible to do that by targeting specific queries you want to accelerate, where it's obvious (or, more likely, hard but still straightforward) how to do better. But I don't think any of these proposed exercises adjusting the caching model or default optimizer parameters in the database is going anywhere without some sort of benchmarking framework for evaluating the results. And the TPC tests are a reasonable place to start. They're a good mixed set of queries, and improving results on those does turn into a real commercial benefit to PostgreSQL in the future too.
