资源描述：
《Postgres ConfChina2015_Victor_Handling_60million_likes_per_day_in_PostgreSQL.pdf》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。
1、PostgreSQL见证每日6千万次"一见钟情”Handling60millionlikesperdayinPostgreSQLVictorBlomqvistvb@viblo.seblomqvist@tantanapp.comTantan(探探)November20,PGConf.CN2015inBeijingHowTantanworkstantanapp.com2"Wehavealreadyused17%ofthespaceinourrelationshiptable,anditsgrowin
2、gquickly"EmailsentonOctober312014tantanapp.com3Totalrelationshipsstored2,500,000,000sizeofinteger(2147483647)2,000,000,0001,500,000,0001,000,000,000500,000,0000tantanapp.com4TodayIwilltalkabouthowwegotTantantoscalefrom0to100160milliondailyswipes175,0
3、00,000150,000,000125,000,000100,000,00075,000,00050,000,00025,000,0000tantanapp.com5Like/dislikeotheruserstantanapp.com6Like/dislikeotherusers-TableCREATETABLErelationships(idserialPRIMARYKEY,user1integer,user2integer,statevarcharCHECK(stateIN('liked
4、','disliked')),);tantanapp.com7Like/dislikeotherusers-QueryINSERTINTOrelationships(user1,user2,state)VALUES(1,2,'disliked');tantanapp.com8Suggestusersforyoutantanapp.com9Suggestusersforyou-TableCREATETABLEusers(idserialPRIMARYKEY,namevarchar,genderva
5、rcharCHECK(genderIN('male','female')),ageinteger,locationgeometry,last_activetimestamp);tantanapp.com10Suggestusersforyou-QuerysetbeijingST_Point(116.454,39.9187)SELECT*FROMusersWHEREgender='female'ANDageBETWEEN22AND34ORDERBYST_Distance(location,:be
6、ijing)*(now()-last_active);tantanapp.com11Suggestusersforyou-Query(2)SELECT*FROMusersWHEREgender='female'ANDageBETWEEN22AND34ANDNOTEXISTS(SELECT*FROMrelationshipsWHEREuser1=1ANDuser2=users.id)ORDERBYST_Distance(location,:beijing)*(now()-last_active)L
7、IMIT20;tantanapp.com12Werunthe“suggest”query12milliontimesperday14,000,00012,000,00010,000,0008,000,0006,000,0004,000,0002,000,0000tantanapp.com13Whatisthefirstthingtodowhenthedatabaseisslow?tantanapp.com14Whatisthefirstthingtodowhenthedatabaseisslow
8、?CREATEINDEXusers_location_idxONusersUSINGGIST(location);CREATEINDEXrelationships_user1_idxONrelationships(user1);tantanapp.com15WITHindexed_queryAS(SELECT*FROMusersWHEREgender='female’ANDageBETWEEN22AND34ANDNOTEXISTS(SELECT*FROMrelationshipsWHEREuse
