Re: How to handle larger databases?

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

<matthias(at)cmklein(dot)de> wrote in message
news:E1CU4aB-0005Uh-00(at)www(dot)strato-webmail(dot)de(dot)(dot)(dot)
>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 majordomo(at)postgresql(dot)org)
>