There's some basic database issues you should follow, no matter what your database engine.
Index your columns, but with reason, do not index a column that only contains several distinct values (such as yes/no fields). Inde xover several columns if your queries can narrow the resultset using more than one field. If you're updating your data always put logs (WAL logs in pgsql) on a separate physical disc. If you can also split indexes out on their own disc (to speedup bookmark lookups). Use common sense types, such as timestamps instead of storing dates as strings. Do not use variable length types. Of course if you can post specific information about your setup you would get you a more specific answer. And of course, usng a database engine that handles its own caching will always get you better performance, since caching the top levels of your indexes is more important than caching actual data files (for a database engine anwyways). But since pqsl relies on the OS to cache, the OS doesn't really know which files are higher priority to keep them in the cache. Jerry <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >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. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
