Reddit reviews PostgreSQL (2nd Edition)

If these items are unique to a single user (that is, one user has many items; one item belongs to exactly one user) then I think what you're doing sounds good. ItemID would be your primary key (since it alone uniquely identifies the record), and UserID would be a foreign key reference to your Users table. You'll definitely want an index on UserID since it sounds like the vast majority of queries would be able to make use of it.

There's no reason that shouldn't perform well up to a fair number of records (the amount will depend on lots of other factors, but a few million rows is maybe a good first guess). If/when you do run into speed issues, you can look at partitioning the table, which would allow you to have something that behaves as if it was one big table, but is actually made up of a number of smaller tables under the hood. It's a little more work, and there are a few snags to watch out for, but it can be really helpful when you need it. And it's not something you need to start out with -- you can add it on later if/when you find you need it. Take a look at http://www.postgresql.org/docs/current/static/ddl-partitioning.html for some more information.

That said, while you can make some educated guesses about indexing/partitioning strategies, really the only way to know for sure is to analyze how it behaves in practice. Query performance is pretty dependent on things like the data itself, usage patterns, and server configuration. You can try loading up a lot of data (just generate so fake data if you need to), and analyze some of your expected queries -- that should give you a better glimpse of what is going on. Take a look at the docs for the EXPLAIN command to get started on that. http://www.postgresql.org/docs/8.4/static/using-explain.html

Lastly, I think the Douglas book on PostgreSQL might be helpful, if you wanted to read up on this some. http://www.amazon.com/PostgreSQL-2nd-Korry-Douglas/dp/0672327562 It has some general information about performance topics, as well as a lot of specifics about how these things work in PostgreSQL.

Hope that helps a bit :)

(Edit: my grammar is atrocious :()

I have this book, it's pretty thorough: http://www.amazon.co.uk/gp/product/0672327562/

Ofcourse you should also study the Postgres official docs, at http://www.postgresql.org/docs/

I don't know any books on database design or theory, does anyone have good tips?