互聯(lián)網(wǎng)企業(yè)postgresql實戰(zhàn)多年的項目開發(fā)經(jīng)驗曾參與中石油長慶油田、烽火科技

去哪兒網(wǎng)PostgreSQLDBA 幾年前因為對PostgreSQL 在PostgreSQL 目前任職去哪兒網(wǎng)PostgreSQLDBA，與PostgreSQLWhoam單臺X86服務(wù)器 PostgreSQL9.6版本開始支持Parallelsequentialscansjoins，aggregates單臺X86服務(wù)器,內(nèi)存容量達到TB-2路x86服務(wù)器,24根內(nèi)存條最大支持768GB4路x86服務(wù)器64根內(nèi)存條最大支持2TB高IOPS,對OLTP業(yè)務(wù),-低時延高并發(fā)，小事務(wù)查詢多寫入多更新多 設(shè)-拆表(表拆分的好處可以分布到不同的表空間autovacuum顆粒度更小,preventwrap 更加方便,如添加默認值字段時間縮短,拆成多表后還為以后數(shù)據(jù)庫服務(wù)器擴展提供方便了拆庫邏輯規(guī)則(業(yè)務(wù)數(shù)據(jù)和日志數(shù)據(jù)分離各業(yè)務(wù)子模塊拆分,按用戶ID2^n取模拆分盡量精 數(shù)安裝編譯時建議調(diào)整塊大小為settableblocksizeinkB./configure--with-略 輕量實用 難度低使 wsdb=#\!wsdb=#CREATEROLE"xxx.xx"LOGINENCRYPTEDPASSWORD'2bea68b4-CREATE ）前不留痕psqlnsetsetlog_duration=off;set：《數(shù)據(jù)庫安全（周正中）動態(tài)性能視圖pg_stat_user_indexes，pg_stat_statementswsdb=#ex inselectconcat(A.first_name,'',A.last_name)asname,concat(B.first_name,'',B.last_name)asleaderFROMemployeesALEFTJOINemployeesBONA.manager_id=B.employee_id; HashLeftJoin(cost=3.41..7.47rows=107HashCond:(a.manager_id=->SeqScanonemployeesa(cost=0.00..2.07rows=107->Hash(cost=2.07..2.07rows=107->SeqScanonemployeesb(cost=0.00..2.07rows=107(5 Index&&wsdbselectcount(*fromproductdetailwhereattention~踏踏米Time:110131.295wsdbselectcount(*fromproductdetailwhereattentionlike踏踏米Time:69760.021wsdb=#selectcount(*)fromproductdetailwhereTime:1.290wsdb=CREATEINDEXONproductdetailUSINGCREATE Fulltext wsdb=#selectshow_trgm(' {"1","13",139,187,384,391,473,"73wsdbselect similarity(texttextwsdb=#selectshow_limit();show_limitpg_trgmwsdb=#select '% twsdb=#selectset_limit(0.9);set_limit wsdb=#select '