How to handle larger databases?

#1

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)

#2

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

Quote:

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)

#3

On Nov 19, 2004, at 2:37 AM, Jerry III wrote:

Quote:

Do not use variable length types.

Why do you suggest not using variable length types?

Patrick B. Kelly
------------------------------------------------------
http://patrickbkelly.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#4

Yes, I would like to hear about this as well, especially since all my
character strings are defined as varchar.

On Monday 22 November 2004 02:09 am, Patrick B Kelly saith:

Quote:

On Nov 19, 2004, at 2:37 AM, Jerry III wrote:
Do not use variable length types.

Why do you suggest not using variable length types?

Patrick B. Kelly
------------------------------------------------------
http://patrickbkelly.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

--
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry (AT) esc1 (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#5

On Mon, Nov 22, 2004 at 02:09:49AM -0500, Patrick B Kelly wrote:

Quote:

On Nov 19, 2004, at 2:37 AM, Jerry III wrote:

Do not use variable length types.

Why do you suggest not using variable length types?

Especially since PostgreSQL has no fixed length string types, so
following that advice would exclude any strings. That's kind of
useless.
--
Martijn van Oosterhout <kleptog (AT) svana (DOT) org> http://svana.org/kleptog/

Quote:

Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBocGJY5Twig3Ge+YRAqptAJ41lZ0Us1La+72P5t9EGk sa+2wYoACeK9TS
G1OBcxv7S8UbcFOmVoy8EhM=
=nKx/
-----END PGP SIGNATURE-----

#6

Quote:

Especially since PostgreSQL has no fixed length string types, so
following that advice would exclude any strings. That's kind of
useless.

char(n) ?

---------------------------(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

#7

On Mon, Nov 22, 2004 at 11:33:35AM +0000, Matt wrote:

Quote:

Especially since PostgreSQL has no fixed length string types, so
following that advice would exclude any strings. That's kind of
useless.

char(n) ?

Is not fixed length. The actual size varies by encoding. Consider the
string:

zeeën

Latin-9 5 bytes
UTF-8 6 bytes
UTF-16 10 bytes

But it should still fit in a char(5), wouldn't you agree?

In postgresql there is no difference in storage method between text,
varchar(n) and char(n).
--
Martijn van Oosterhout <kleptog (AT) svana (DOT) org> http://svana.org/kleptog/

Quote:

Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBodC2Y5Twig3Ge+YRAiJ6AKC3m68frPN3NkU4VBeCXW RFzF7UTgCgocqH
rvQayR8mlYYKiwprKH9Ibwo=
=StxE
-----END PGP SIGNATURE-----

#8

Quote:

Latin-9 5 bytes
UTF-8 6 bytes
UTF-16 10 bytes

But it should still fit in a char(5), wouldn't you agree?

Got you.

Quote:

In postgresql there is no difference in storage method between text,
varchar(n) and char(n).

Learn something new every day. Thanks!

Matt

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#9

Matt wrote:

Quote:

Latin-9 5 bytes
UTF-8 6 bytes
UTF-16 10 bytes

But it should still fit in a char(5), wouldn't you agree?

Got you.

In postgresql there is no difference in storage method between text,
varchar(n) and char(n).

Learn something new every day. Thanks!

So that would say the previous statements are not accurate? That is,
there's no problem with using a varchar?

--
Until later, Geoffrey

---------------------------(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

#10

On Mon, 2004-11-22 at 08:59 -0500, Geoffrey wrote:

Quote:

So that would say the previous statements are not accurate? That is,
there's no problem with using a varchar?

Right; there is no reason to prefer CHAR(n) over VARCHAR(n), unless you
need whitespace padding.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend