Thread: 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.
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@cmklein.de> wrote in message news:E1CU4aB-0005Uh-00@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 majordomo@postgresql.org) >
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
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 majordomo@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: terry@esc1.com
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 <kleptog@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.
Attachment
> Especially since PostgreSQL has no fixed length string types, so > following that advice would exclude any strings. That's kind of > useless. char(n) ?
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 <kleptog@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.
Attachment
> 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
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
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
> 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