從Uber切換Potgres說起（22頁）.pdf

1、李元佳云徙科技聯合創始人及CTO從Uber切換Potgres說起事件的背景事件的背景事件的背景Postgres served us well in the early days of Uber,but we ran into significant problems scaling Postgres with our growth.Today,we have some legacy Postgres instances,but the bulk of our databases are either built on top of MySQL(typically using our Schem

2、aless layer)or,in some specialized cases,NoSQL databases like Cassandra.事件的背景Postgres數據的存儲Postgres的數據更新記錄的多版本機制及更新寫的路徑比較長中途需要讀舊版本的話，代價比較大舊版本回收和管理問題比較大Postgres索引與數據的關系（B-Tree+Heap）MySQL索引與數據的關系(Clustered Index)索引結構導致的差異二級索引的檢索需要進行兩次索引檢索如果主索引的數據量大的話，比較消耗空間記錄物理位置的變更，會導致所有索引的變更Uber宣稱的寫放大問題-表結構表結構主索引二級索引

3、二級索引Uber宣稱的寫放大問題（一次更新、四次寫）寫數據 Write the new row tuple to the tablespace更新主索引 Update the primary key index to add a record for the new tuple更新二級索引 Update the(first,last)index to add a record for the new tuple更新二級索引 Update the birth_year index to add a record for the new tupleUber宣稱的寫放大問題數據的更新需要更新所有索引

4、 PostgreSQL always needs to update all indexes on a table when updating rows in the table.MySQL with InnoDB,on the other hand,needs to update only those indexes that contain updated columns.“if we have a table with a dozen indexes defined on it,an update to a field that is only covered by a single i

5、ndex must be propagated into all 12 indexes to reflect the ctid for the new row”.Postgres的免索引更新機制（HOT更新）Postgres的流復制Uber宣稱的Postgres的流復制的問題寫放大：This write amplification issue naturally translates into the replication layer as well because replication occurs at the level of on-disk changes.物理復制帶來的潛在數據損

6、壞的危險：During a routine master database promotion to increase database capacity,we ran into a Postgres 9.2 bug.版本的升級問題：Because replication records work at the physical level,its not possible to replicate data between different general availability releases of Postgres.對于事務的影響：Postgres does not have tr

7、ue replica MVCC support.Uber宣稱的Postgres的流復制的問題寫放大：This write amplification issue naturally translates into the replication layer as well because replication occurs at the level of on-disk changes.物理復制帶來的潛在數據損壞的危險：During a routine master database promotion to increase database capacity,we ran into a Postgres 9.2 bug.版本的升級問題：Because replication records work at the physical level,its not possible to replicate data between different general availability releases of Postgres.對于事務的影響：Postgres does not have true replica MVCC support.Postgres的邏輯復制解決方案Postgres的邏輯復制解決方案Postgres的復制的事務問題解決方案