How to handle larger databases?

I am currently creating a database with less than 20 simple tables (only
SQL 92 types, simple constraints, no PostgreSQL specific stuff, no stored
procedures...)

Unfortunately, some of those tables will contain up to 4 Million entries,
making the size of the entire database 700-1000MB.

In order to maintain good query times (hopefully <1-3 seconds) I would
like to ask for some tips on how to manage and organize such a database.
Like what should I do and what should I avoid? Where and how should I use
indexes and all that stuff?

I know there are much larger PostgreSQL databases around. How do they
manage good query times?

Thanks a lot

Matt


P.S. The test system is a normal Win 2000 PC, the target machines will be
IA-32 based Linux machines.