By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,691 Members | 2,041 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,691 IT Pros & Developers. It's quick & easy.

How to handle larger databases?

P: n/a
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 ma*******@postgresql.org)

Nov 23 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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

<ma******@cmklein.de> wrote in message
news:E1***************@www.strato-webmail.de...
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 ma*******@postgresql.org)

Nov 23 '05 #2

P: n/a

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

P: n/a
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:
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly


--
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: te***@esc1.com

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

http://archives.postgresql.org

Nov 23 '05 #4

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

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 <kl*****@svana.org> http://svana.org/kleptog/ 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-----

Nov 23 '05 #5

P: n/a
> 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

Nov 23 '05 #6

P: n/a
On Mon, Nov 22, 2004 at 11:33:35AM +0000, Matt wrote:
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 <kl*****@svana.org> http://svana.org/kleptog/ 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-----

Nov 23 '05 #7

P: n/a
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!

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

Nov 23 '05 #8

P: n/a
Matt wrote:
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

Nov 23 '05 #9

P: n/a
On Mon, 2004-11-22 at 08:59 -0500, Geoffrey wrote:
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

Nov 23 '05 #10

P: n/a
> In postgresql there is no difference in storage method between text,
varchar(n) and char(n).

However, there's an additional length check for (var)char(n)
involved meaning an ever so tiny performance penalty if that's
of concern.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

Nov 23 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.