Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tdhfh-0003Lf-G6 for pgsql-performance@arkaria.postgresql.org; Wed, 28 Nov 2012 13:27:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Tdhfg-0002nP-Sd for pgsql-performance@arkaria.postgresql.org; Wed, 28 Nov 2012 13:27:24 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tdhfe-0002lb-A1 for pgsql-performance@postgresql.org; Wed, 28 Nov 2012 13:27:22 +0000 Received: from dub0-omc3-s4.dub0.hotmail.com ([157.55.2.13]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tdhfb-0002zk-Ni for pgsql-performance@postgresql.org; Wed, 28 Nov 2012 13:27:21 +0000 Received: from DUB104-W43 ([157.55.2.9]) by dub0-omc3-s4.dub0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Wed, 28 Nov 2012 05:27:18 -0800 X-Originating-IP: [31.151.116.201] X-EIP: [gNDUPwSaxAVCwFdkKplejDVMAhKULgZJ] X-Originating-Email: [willem_leenen@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_97ef7003-0ef3-49c3-b707-447cb48f2384_" From: Willem Leenen To: CC: Subject: Re: Database design - best practice Date: Wed, 28 Nov 2012 13:27:18 +0000 Importance: Normal In-Reply-To: <6575B359-EB18-4A6A-9D0A-7EACC4DB26D4@autouncle.com> References: <1101C331-4930-4C1C-BF4D-46BBA2717081@autouncle.com> , <6575B359-EB18-4A6A-9D0A-7EACC4DB26D4@autouncle.com> MIME-Version: 1.0 X-OriginalArrivalTime: 28 Nov 2012 13:27:18.0727 (UTC) FILETIME=[16DBAD70:01CDCD6C] X-Pg-Spam-Score: -2.3 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_97ef7003-0ef3-49c3-b707-447cb48f2384_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In relation to question 2=2C I read somewhere in the documentation that=20 because of MVCC=2C the whole row has to be rewritten even though I just=20 update one single column in that row. Hence if the table is wider (has=20 more columns)=2C the update will be slower. Does this match your=20 understanding? No. I don't count the number of rows=2C but number of blocks (pages) that a= re modified=2C which are 8K each.=20 My advice would be to first establish a solutiondirection via diagnosing th= e problem. My experience is that most solutions are not obscure at all. Subject: Re: [PERFORM] Database design - best practice From: nielskristian@autouncle.com Date: Wed=2C 28 Nov 2012 14:20:27 +0100 CC: pgsql-performance@postgresql.org To: willem_leenen@hotmail.com Thanks for the advice. Currently I see a lot of I/O related to update/inserts=2C so I'm trying to = track down these guys at first. In relation to question 2=2C I read somewhe= re in the documentation that because of MVCC=2C the whole row has to be rew= ritten even though I just update one single column in that row. Hence if th= e table is wider (has more columns)=2C the update will be slower. Does this= match your understanding? Den 28/11/2012 kl. 14.10 skrev Willem Leenen :Ni= els=2C " I can't see why it would make sense to put that into a separate table and= join in the values "=20 You don't normalize for performance. People DEnormalize for performance. Questions: (AFAIK) 1) This is a way to disaster. Get yourself a book on RDBMS from for example= Celko. Do NOT go against the flow of the RDBMS rules=2C as here in rule #1= atomic values of a column.=20 2) This is not the big fish you are after. First benchmark your setup and c= ompare the results with your desired performance level. First quantify your= problem=2C if there is any=2C before using tricks. 3) A row will need more memory when it is wider=2C this may be amplified du= ring hash joins.=20 4) People DEnormalize for performance.=20 5) " Is it significantly faster to select * from a table with 20 columns=2C= than selecting the same 20 in a table with 150 columns?"=20 I know the answer=2C but i encourage you to simply test this. I have seen l= ot's of urban legends about performance ( including the dropping of the ref= erential integrity be cause that would make a difference.... ).=20 Of course =2C when it's a full table scan=2C and it are ALL disk reads=2C (= or ALL memory reads_) you can simply calculate it too. But just get into th= e habit of testing for learning. My advice: - know what performance you need. - test if you have this=2C varying tablecontent and systemload - do not tamper with the RDBMS rules=2C this will haunt you. - if you have the latest postgres version=2C you can use covering indexes: = tables aren't accessed at all=2C bypassing most of your questions. Check wi= th peers if you've got the indexes right. Regards=2C Willem > From: nielskristian@autouncle.com > Subject: [PERFORM] Database design - best practice > Date: Wed=2C 28 Nov 2012 13:41:14 +0100 > To: pgsql-performance@postgresql.org >=20 > Hi=2C >=20 > I'm on the hunt for some solid knowledge on a theoretical level about the= performance of postgresql. My question is regarding best practices=2C and = how architectural decisions might influence the performance. First a little= background: >=20 > The setup: > I have a database which holds informations on used cars. The database has= mainly 3 tables of interest for this case: > A cars table=2C an adverts table and a sellers table. One car has many ad= verts and one seller has many adverts. One advert belongs to one car and on= e seller. > The database is powering a website for searching used cars. When searchin= g for used cars=2C the cars table is mainly used=2C and a lot of the column= s should be directly available for searching e.g. color=2C milage=2C price= =2C has_automatic_transmission etc. >=20 > So my main concern is actually about the cars table=2C since this one cur= rently has a lot of columns (151 - I expect thats quite a lot?)=2C and a lo= t of data (4 mil. rows=2C and growing). Now you might start by thinking=2C = this could sound like a regular need for some normalization=2C but wait a s= econd and let me explain :-) > The columns in this table is for the most very short stings=2C integers= =2C decimals or booleans. So take for an example has_automatic_transmission= (boolean) I can't see why it would make sense to put that into a separate = table and join in the values. Or the milage or the price as another example= . The cars table used for search is indexed quite a lot. >=20 > The questions: > Having the above setup in mind=2C what impact on performance=2C in terms = of read performance and write performance=2C does it have=2C whether I do t= he following: > 1) In general would the read and/or the write on the database be faster= =2C if I serialized some of the not searched columns in the table into a si= ngle text columns instead of let's say 20 booleans? > 2) Lets say I'm updating a timestamp in a single one of the 151 columns i= n the cars table. The update statement is using the id to find the car. Wou= ld the write performance of that UPDATE be affected=2C if the table had few= er columns? > 3) When adding a new column to the table i know that it becomes slower th= e more rows is in the table=2C but what about the "width" of the table does= that affect the performance when adding new columns? > 4) In general what performance downsides do you get when adding a lot of = columns to one table instead of having them in separate tables? > 5) Is it significantly faster to select * from a table with 20 columns=2C= than selecting the same 20 in a table with 150 columns? >=20 > Hope there is some good answers out there :-) >=20 > --=20 > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org= ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance = --_97ef7003-0ef3-49c3-b707-447cb48f2384_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
In relation to question 2=2C I read somewhere in the documentation that=20 because of MVCC=2C the whole row has to be rewritten even though I just=20 update one single column in that row. Hence if the table is wider (has=20 more columns)=2C the update will be slower. Does this match your=20 understanding?

No. I don't count the number of rows=2C but number of= blocks (pages) that are modified=2C which are 8K each.

My advice w= ould be to first establish a solutiondirection via diagnosing the problem. = My experience is that most solutions are not obscure at all.


Subject= : Re: [PERFORM] Database design - best practice
From: nielskristian@auto= uncle.com
Date: Wed=2C 28 Nov 2012 14:20:27 +0100
CC: pgsql-performan= ce@postgresql.org
To: willem_leenen@hotmail.com

Thanks for the ad= vice.

Currently I see a lot of I/O related to update/ins= erts=2C so I'm trying to track down these guys at first. In relation to que= stion 2=2C I read somewhere in the documentation that because of MVCC=2C th= e whole row has to be rewritten even though I just update one single column= in that row. Hence if the table is wider (has more columns)=2C the update = will be slower. Does this match your understanding?

Den 28/11/2012 kl. 14.10 skrev Willem Leenen <=3Bwillem_leenen@hotmail.com>=3B:

N= iels=2C

" I can't see why it would make sense to put that into a sep= arate table and join in the values " =3B
You don't normalize for performance. People DEnormaliz= e for performance.


Questions: (AFAIK)

1) This is a way to= disaster. Get yourself a book on RDBMS from for example Celko. Do NOT go a= gainst the flow of the RDBMS rules=2C as here in rule #1 atomic values of a= column. =3B

2) = This is not the big fish you are after. First benchmark your setup and comp= are the results with your desired performance level. First quantify your pr= oblem=2C if there is any=2C before using tricks.

3) A row will need = more memory when it is wider=2C this may be amplified during hash joins. =3B

4) People DEno= rmalize for performance. =3B

5) " Is it significantly faster to select * from a table with = 20 columns=2C than selecting the same 20 in a table with 150 columns?" =3B

I know the answe= r=2C but i encourage you to simply test this. I have seen lot's of urban le= gends about performance ( including the dropping of the referential integri= ty be cause that would make a difference.... ). =3B
Of course =2C when it's a full table scan= =2C and it are ALL disk reads=2C (or ALL memory reads_) you can simply calc= ulate it too. But just get into the habit of =3B testing for learning.<= br>

My advice:
- know what performance you need.
- test if you= have this=2C varying tablecontent and systemload
- do not tamper with t= he RDBMS rules=2C this will haunt you.
- if you have the latest postgres= version=2C you can use covering indexes: tables aren't accessed at all=2C = bypassing most of your questions. Check with peers if you've got the indexe= s right.

Regards=2C
Willem



>=3B From: =3Bnielskristia= n@autouncle.com
>=3B Subject: [PERFORM] Database design - best pra= ctice
>=3B Date: Wed=2C 28 Nov 2012 13:41:14 +0100
>=3B To: =3Bpgsql-performance@postgresql.org
>=3B =3B
>=3B Hi=2C
&= gt=3B =3B
>=3B I'm= on the hunt for some solid knowledge on a theoretical level about the perf= ormance of postgresql. My question is regarding best practices=2C and how a= rchitectural decisions might influence the performance. First a little back= ground:
>=3B =3B>=3B The setup:
>=3B I have a database which holds informations on= used cars. The database has mainly 3 tables of interest for this case:
= >=3B A cars table=2C an adverts table and a sellers table. One car has ma= ny adverts and one seller has many adverts. One advert belongs to one car a= nd one seller.
>=3B The database is powering a website for searching u= sed cars. When searching for used cars=2C the cars table is mainly used=2C = and a lot of the columns should be directly available for searching e.g. co= lor=2C milage=2C price=2C has_automatic_transmission etc.
>=3B =3B
>=3B So my main conce= rn is actually about the cars table=2C since this one currently has a lot o= f columns (151 - I expect thats quite a lot?)=2C and a lot of data (4 mil. = rows=2C and growing). Now you might start by thinking=2C this could sound l= ike a regular need for some normalization=2C but wait a second and let me e= xplain :-)
>=3B The columns in this table is for the most very short s= tings=2C integers=2C decimals or booleans. So take for an example has_autom= atic_transmission (boolean) I can't see why it would make sense to put that= into a separate table and join in the values. Or the milage or the price a= s another example. The cars table used for search is indexed quite a lot.>=3B =3B
>=3B = The questions:
>=3B Having the above setup in mind=2C what impact on p= erformance=2C in terms of read performance and write performance=2C does it= have=2C whether I do the following:
>=3B 1) In general would the read= and/or the write on the database be faster=2C if I serialized some of the = not searched columns in the table into a single text columns instead of let= 's say 20 booleans?
>=3B 2) Lets say I'm updating a timestamp in a sin= gle one of the 151 columns in the cars table. The update statement is using= the id to find the car. Would the write performance of that UPDATE be affe= cted=2C if the table had fewer columns?
>=3B 3) When adding a new colu= mn to the table i know that it becomes slower the more rows is in the table= =2C but what about the "width" of the table does that affect the performanc= e when adding new columns?
>=3B 4) In general what performance downsid= es do you get when adding a lot of columns to one table instead of having t= hem in separate tables?
>=3B 5) Is it significantly faster to select *= from a table with 20 columns=2C than selecting the same 20 in a table with= 150 columns?
>=3B =3B
>=3B Hope there is some good answers out there :-)
>=3B =3B
>=3B -- =3B
>=3B Sent via pgsql-perf= ormance mailing list (p= gsql-performance@postgresql.org)
>=3B To make changes to your subs= cription:
>=3B =3B= http://www.postgresql.org/mailpref/pgsql-performance

= --_97ef7003-0ef3-49c3-b707-447cb48f2384_--