张文升 - Happy Hacking in Tantan Using PostgreSQL - PostgreSQL in Tantan

2020-02-27 58浏览

1.Happy hacking in Tantan using PostgreSQL PostgreSQL in Tantan 张文升 2017.thegiac.com www.top100summit.com
2.个人简介 - 张文升探探科技PostgreSQL DBA。曾任职去哪儿网 PostgreSQL DBA。有多年的项目开发经验，曾参与多个大中型项目建设。因为对PostgreSQL的爱好与热爱投入PG开源社区的怀抱，成为PostgreSQL社区核心成员及社区志愿者，多次在PostgreSQL中国大会、各地分会场、企业及高校分享心得，服务多家使用PostgreSQL的企业。 2017.thegiac.com
3.How Tantan works 2017.thegiac.com
4.Relationships in Tantan swipes 9000亿的relationships总量 9亿滑动／天最高突破10亿滑动／天 (July 02) passbys 2000亿passby总量 2017.thegiac.com
5.Why PostgreSQL ● “The world’s most advanced open source database” ○ “It has more than 15 years of active development” ○ “It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (reliability, data integrity, and correctness)” ● PostGIS for Location Based Services (PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL) ○ Nearby users ○ Passby users ○ Distance with a user 2017.thegiac.com
6.Agenda ● Scaling Swipes ● PostgreSQL in Tantan 2017.thegiac.com
7.Scaling Swipes 2017.thegiac.com
8.#1 How Swipe works A B A liked AB B B liked A A B A liked B B liked A A B A disliked AB B B disliked A 2017.thegiac.com
9.#2 Product Requirements ● One user should only swipe another user once ● Mutual likes will create a Match ● Calculating nearby users ○ Liked users should be ranked higher ○ Disliked users must be filtered out 2017.thegiac.com
10.#3 Sharding Principles ● Scalability ○ Starting with fewer servers ○ Scaling to more servers with less effort ● Performance ○ Nearby users filtering in real-time ○ Large amount of swipes ● Simplicity ○ Easy to understand and implement ○ Sharding by user id 2017.thegiac.com
11.#4 Sharding Physical Shards #1 #2 #64 (Databases) Logical Shards (Schemas) Object ID (bigint) #128 #256 timestamp in milliseconds logical shard ID sequ ence #8192 INSERT INTO shard1.swipes (user_id, other_user_id, status) VALUES Swipes (100, 200, 'liked'); Redundancy INSERT INTO shard2.swipes (user_id, other_user_id, other_status)Reference:Sharding & ID at Instagram VALUES (200, 100, 'liked'); 41 bits 13 bits 10 bits 2017.thegiac.com
12.#5 Sharding (continued) 2017.thegiac.com
13.#6 Shards Split 2017.thegiac.com
14.#7 Swipes in Tantan 2017.thegiac.com
15.PostgreSQL in Tantan 2017.thegiac.com
16.#1 Overview core locati on chat 1M9S 1M2S 1M2S shasrsdhasrsdhasrd 1M1S x 64 2017.thegiac.com
17.#2 PostGiS ● GiST-based R-Tree spatial indexes ● Rich functions for analysis and processing of GIS objects ○ ST_Point, ST_Distance, ST_Contains etc. ● Scenarios ○ Find nearby users ○ Calculate distance between users ○ Construct regions database 2017.thegiac.com
18.#3 Partial indexes ● Avoid indexing common values ● Speed up update operations ● Scenarios ○ Find nearby users based on seach gender CREATE INDEX ON users USING gist (location) WHERE location IS NOT NULL AND gender = 'female'; CREATE INDEX ON users USING gist (location) WHERE location IS NOT NULL AND gender = 'male' AND (search_gender = ANY(ARRAY['both', 'male'])); 2017.thegiac.com
19.#4 Stored Procedures ● Separation of concerns ● Save extra round trips between client and server ● Flexibility in using PL/pgSQL ● Monitoring 2017.thegiac.com
20.#5 No Downtime Operations ● Create or replace a function ● Add a new column ● Add a new non-nullable column with a default value (4 steps) ● Add a default value to an existing column ● Add an index concurrently ● Drop a column ● Drop a constraint ● etc. 2017.thegiac.com
21.● 应用特点 ○ 高并发，小事务，查询多，写入多，更新多，删除少 ○ 性能要求毫秒以下 ● 各shards间数据紧耦合 ○ 单个shard出现性能问题影响到所有shards ■ 调整应用逻辑 ■ 拆解复杂函数； ○ 冗余数据过多 ■ 减少冗余，降低维护难度 ■ 优化设计，提升性能 ■ 批量写，减少写次数 ● 维护窗口 ○ 每天增长年龄近3亿 ○ 凌晨2:00~6:00只有4个小时 ● 单个pgbouncer的负载瓶颈 ● 缓存 ● HA 2017.thegiac.com
22.ios,android,web users && profiles core contacts user’s contacts location user’s location shard shsahsrahdradrd shards passby relationships 2017.thegiac.com
23.master potential hotstandby shsahsrahdsrashdrassdlrsadvse 2017.thegiac.com
24.fontend / webservice pgbouncer master fontend / webservice pgbouncer shards 2017.thegiac.com
25.2017.thegiac.com
26.扫码关注GIAC公众号 2017.thegiac.com www.top100summit.com