本期數(shù)據(jù)庫(kù)相關(guān)演講-2015zhdba快速了解

發(fā)展歷程，圈子特性如何了解源碼如何 內(nèi)核進(jìn)程結(jié)構(gòu)，文件結(jié)構(gòu)如何做壓力測(cè)試版本升級(jí)備份恢復(fù)高可用讀寫(xiě)分離分布式挖掘擴(kuò)展功能TODO和軟肋性能優(yōu)化方法數(shù)據(jù)庫(kù)安全ben

ark學(xué)習(xí)資料PostgreSQL發(fā)展歷程2014于IBM

SystemR的一系列文檔,Michael

Stonebraker

and

Eugene

Wong)1973

University

INGRES

(1982

INGRES1985

post-Ingres1988

POSTGRES

version1

-

1993

version

4

(END)1995

Postgres95(伯克利大學(xué)學(xué)生Andrew

Yu,JollyChen重寫(xiě)SQL解釋器,替換原項(xiàng)目中的基于Ingres的SQL解釋器.為開(kāi)源奠定了基礎(chǔ))1996

更為PostgreSQL,

發(fā)布第一個(gè)開(kāi)源版本,

后交由PostgreSQL社區(qū)

.主要RDBMS發(fā)展歷程PostgreSQL

版本發(fā)布?xì)v程PostgreSQL

代碼活躍度數(shù)據(jù)取自

/postgres/postgres/graphsPostgreSQL

代碼活躍度數(shù)據(jù)取自PostgreSQL主代碼管理庫(kù)PostgreSQL

全球貢獻(xiàn)者Core

Team成員Josh

Berkus

(USA,

CEO@PostgreSQL

Experts

Inc.)主要負(fù)責(zé)PG推廣,性能測(cè)試,優(yōu)化,文檔編輯等工作.,以及其他增強(qiáng)性的代碼工作.Peter

Eisentraut

(USA,

)主要負(fù)責(zé)了系統(tǒng)建設(shè),移植,文檔編輯,Magnus

Hagander(Sweden,

redpill-linpro.se)幫助

PostgreSQL

WEB主站及基礎(chǔ)設(shè)施,win32的移植,

以及系統(tǒng)認(rèn)證等工作.Tom

Lane

(USA,

Salesforce)遍及PostgreSQL代碼的各個(gè)角落,包括BUG評(píng)估和修復(fù),性能改進(jìn),優(yōu)化等.Bruce

Momjian

(USA,

EnterpriseDB)負(fù)責(zé)

TODO和FAQ列表,

代碼,

發(fā)布版本補(bǔ)丁以及培訓(xùn).Dave

Page

(UnitedKingdom,

EnterpriseDB)負(fù)責(zé)pgadmin的開(kāi)發(fā)和

工作,

同時(shí)負(fù)責(zé)管理postgresql.主站工程,PostgreSQL的安裝程序等.主要貢獻(xiàn)者.

munity/contributors/Committers

(git@gitmaster.postgresql.

/postgresql.git)目前有

位

mitter.

(mitters)PostgreSQL

全球贊助商)PostgreSQL全球贊助商

(贊助商分級(jí)贊助商列表PostgreSQL中國(guó)PostgreSQL社區(qū)>10000人用戶會(huì)，圈，內(nèi)核研發(fā)>300人(華為,移動(dòng),國(guó)網(wǎng),,武大..)服務(wù)提供商(青云,阿里,神州)用戶(

,去哪兒,郵儲(chǔ),騰訊,移動(dòng),斯凱,同花順.,阿里...)PostgreSQL數(shù)據(jù)庫(kù)全球使用情況,俄羅斯杜馬…}e,

…},Disqus,

去生物制藥

{Affymetrix(

),

化學(xué)

,

gene(結(jié)構(gòu)生物學(xué)應(yīng)用案例),

…}電子商務(wù){(diào)CD

BABY,etsy(與淘寶類似),whitepages,

flightstats,

Endpoint

Corporation…}學(xué)校{加州大學(xué)伯克利分校,哈佛大學(xué)互聯(lián)網(wǎng)與社會(huì)中心,.LRN,莫斯科國(guó)立大學(xué),悉尼大學(xué),…}金融

{Journyx,

LLC,

merce(類似支付寶),

交易交所,

郵儲(chǔ)銀行,

同花順…}{MobyGames,

…}{

國(guó)家氣象局,

國(guó)家物理

,

兒童基金,

疾病控制和預(yù)防中心,醫(yī)療

{calorieking,

開(kāi)源電子 項(xiàng)目,

shannon醫(yī)學(xué)中心,

…}制造業(yè){Exoteric

Networks,

豐田,捷豹路虎}{

,

國(guó)會(huì)投票數(shù)據(jù)庫(kù),MacWorld,綠色和平組織,…}開(kāi)源項(xiàng)目

{Bricolage,

Debian,

FreshPorts,

FLPR,

PostGIS,SourceF e,

OpenACS,

Gf零售

{ ,

CTC,

Safeway,

Tsutaya,

Rockport,

…}科技

{Sony,

MySpace,

Yahoo,

Afilias,

APPLE,富士通,

Omniti,

Red

Hat,

Sirius

IT,SUN,

國(guó)際空間站,哪兒,

騰訊, ,

中興,

斯凱,

云游,

阿里

…}電信),德國(guó)電信,Optus,

Skype,Tlestra(澳洲電訊),中國(guó)移動(dòng)…}通信{Cisco,Juniper,NTT(物流{SF}More

:PostgreSQL

is

released

under

the

PostgreSQL

License,

a

liberal

Open

Source

license,

similar

to

the

BSD

or

MIT

licenses.PostgreSQL

Database

Management

System(formerly

known

as

Postgres,

then

as

Postgres95)Portions

Copyright

(c)

1996-2015,

The

PostgreSQL

Global

Development

GroupPortions

Copyright

(c)

1994,

The

Regents

of

the

University

of

CaliforniaPermission

to

use,

copy,

modify,

and

distribute

this

software

and

its ation

for

any

purpose,

without

fee,

andwithout

a

writtenagreementishereby

granted,

providedthat

the

above

copyrightnotice

andthisparagraphand

thefollowin

o

paragraphs

appear

in

all

copies.IN

NO

EVENT

SHALL

THE

UNIVERSITY

OF

CALIFORNIA

BE

LIABLE

TO

ANY

PARTY

FOR

DIRECT,

INDIRECT,SPECIAL,

INCIDENTAL,

OR

CONSEQUENTIAL

DAMAGES,

INCLUDINGLOST

PROFITS,

ARISING

OUT

OF

THE

USEOF

THIS

SOFTWARE

AND

ITS

ATION,

EVEN

IF

THE

UNIVERSITY

OF

CALIFORNIA

HAS

BEENADVISED

OF

THE

POSSIBILITY

OF

SUCH

DAMAGE.THE

UNIVERSITY

OF

CALIFORNIA

SPECIFICALLYDI S

ANY

WARRANTIES,INCLUDING,

BUT

NOTLIMITED

TO,

THE

IMPLIED

WARRANTIES

OF

MERCHANTABILITY

AND

FITNESS

FOR

A

PARTICULAR

PURPOSE.THE

SOFTWARE

PROVIDED

HEREUNDER

IS

ON

AN

"AS

IS"

BASIS,

AND

THE

UNIVERSITY

OF

CALIFORNIAHASNO

OBLIGATIONS

TO

PROVIDEMAINTENANCE,

SUPPORT,

UPDATES,

ENHANCEMENTS,

OR

MODIFICATIONS.特性SQL特性聚合窗口遞歸繼承外部表事件觸發(fā)器安全特性加密鏈路加密認(rèn)證方法行安全策略數(shù)據(jù)類型特性幾何類型網(wǎng)絡(luò)類型全文檢索類型JSON,

JSONB數(shù)組范圍復(fù)合，枚舉，域索引特性btreehashgistspgistginbrin條件索引/部分索引函數(shù)索引函數(shù)特性plpgsql,

C,

plR,

pljava,plpython,

plperl,

...功能特性流模塊化鉤子元表物化視圖,FDW,..特性例子聚合特性例子應(yīng)用例子統(tǒng)計(jì)聚合函數(shù)的回歸測(cè)試以及自變量:昨日收盤(pán)價(jià)因變量:今日收盤(pán)價(jià)公式

y=slope*x+interceptregr_slope,計(jì)算數(shù)據(jù)的相關(guān)性，截距，需要用到PostgreSQL統(tǒng)計(jì)學(xué)相關(guān)聚合函數(shù)，

regr_r2,

regr_intercept,斜率。使用相關(guān)性最高的截距和斜率計(jì)算下一天的收盤(pán)價(jià)。h

/digoal@126/blog/static/

41921/特性例子窗口輸出每位學(xué)生與各學(xué)科第一名成績(jī)的分差。特性例子窗口select

id,n,course,score,_value(score)

over(partition

by

course

order

by

score

desc)

-

score

as

difffrom

tbl;特性例子遞歸查詢異構(gòu)查詢，例如 線路信息，可能包含當(dāng)前站點(diǎn)，上一個(gè)站點(diǎn)的信息某些多 分類信息，包括大類，小類，每條記錄可能記錄了父類WITH(Common

Table

Expressions)WITH

RECURSIVE

t(n)

AS

(VALUES

(1)UNION

ALLSELECT

n+1

FROM

t

WHERE

n

<100)SELECT

sum(n)

FROM

t;非遞歸子句遞歸子句UNION

[ALL]TEMP

WorkingTABLEWITH語(yǔ)句的

OUTPUT,通過(guò)LIMIT可以跳出循環(huán)"遞歸"SQLWITH(Common

Table

Expressions)UNION

去重復(fù)(去重復(fù)時(shí)NULL

視為等同)圖中所有輸出都涉及UNION[ALL]的操作,包含以往返回的記錄和當(dāng)前返回的記錄非遞歸子句遞歸子句OUTPUTTEMP

WorkingTABLE1輸出

2TWT有無(wú)數(shù)據(jù)4有,遞歸4無(wú),結(jié)束遞歸遞歸子句5TEMP

Intermediate

TABLE(替換掉TWT的內(nèi)容后清空自己)6同時(shí)輸出3輸出7

TWT清空并被替換6輸出循環(huán)開(kāi)始特性例子遞歸查詢特性例子ltree異構(gòu)數(shù)據(jù)類型特性例子with原子操作例子,跨分區(qū)更新分區(qū)表的分區(qū)字段值measurement

按月分區(qū)字段logdate,

將logdate='2015-03-01'的值更新到另一個(gè)分區(qū),同時(shí)還需要更新其他某字段值為999with

t1

as(delete

from

measurement

where

logdate='2015-03-01'returning

city_id,'2015-04-01'::timestamp(0)without

time

zone,peaktemp,999)insert

into

measurement

select

*

from

t1;特性例子可以像操作本地表一樣join,read/write外部表i/FdwTable(s)NOT

NEEDServer(s)FDWFileForeignTable(s)UserMap

(s)Server(s)FDWOracleForeignTable(s)UserMap

(s)Server(s)FDWMySQLForeignTable(s)UserMap

(s)Server(s)FDWPostgreSQLForeignTable(s)UserMap

(s)Server(s)FDWHiveForeignTable(s)Server(s)FDW(s)JDBC,User

AUTHMap

(s)

INFOExternalDataSourceAPIConnINFOTABLEDEFINE特性例子事件觸發(fā)器例子,控制普通用戶沒(méi)有執(zhí)行DDL的權(quán)限特性例子事件觸發(fā)器例子,控制普通用戶沒(méi)有執(zhí)行DDL的權(quán)限目前支持的事件mand_startmand_endtable_rewritesql_drop支持的SQL,(未完全截取)特性例子LDAP認(rèn)證或AD域認(rèn)證支持simple或search

bind模式simple

bind

:host

all

new

/0

ldap

ldapserver=50

ldapport=389

ldapprefix="uid="ldapsuffix=",ou=People,d

"search

bind:（可選配置ldapbinddn和ldapbindpasswd

）host

all

new

/0

ldap

ldapserver=50

ldapport=389

ldapsearchattribute="uid"ldapbasedn="ou=People,d

"ClientPGLDAPServer特性例子行安全策略例子，數(shù)據(jù)共享場(chǎng)景，對(duì)同一個(gè)表操作時(shí)，不同的用戶能查看到不同的數(shù)據(jù)子集why

not

view?CREATE

POLICY

name

ON

table_name[

FOR

{

ALL

|

SELECT

|

INSERT

|

UPDATE|

DELETE

}

][

TO

{

role_name

|

PUBLIC

}

[,

...]

][

USING

(

using_expression

)

][

WITH

CHECK

(

check_expression

)

]using

指針對(duì)已經(jīng)存在的記錄的校驗(yàn)。因此可實(shí)施在select，update，delete，ALL上。whth

check

指針對(duì)將要新增的記錄的校驗(yàn)。因此可實(shí)施在insert，update，ALL上。子集子集 子集子集子集子集特性例子行安全策略例子，數(shù)據(jù)共享場(chǎng)景，對(duì)同一個(gè)表操作時(shí)，不同的用戶能查看到不同的數(shù)據(jù)子集創(chuàng)建一個(gè)新增數(shù)據(jù)的策略（使用with

check，檢測(cè)新數(shù)據(jù)）這個(gè)策略檢測(cè)test表的r字段，必須等于當(dāng)前用戶名才行。也就是說(shuō)任何用戶在使用一張表時(shí)不會(huì)test表時(shí)，r字段的值必須和當(dāng)前用戶名相同，這樣就可以很好的控制多個(gè)用戶在數(shù)據(jù)。postgres=#

create

policy

p

on

testfor

insert

to

r1

with

check(

r

=current_user);postgres=#

alter

table

test

enable

row

level

security;postgres=#

\c

postgres

r1postgres=>

insert

into

test

values(4,'r2');ERROR:

new

row

violates

WITHCHECK

OPTION

for

"test"postgres=>

insert

into

test

values(4,'r1');INSERT

0

1特性例子柱狀圖妙用(用作評(píng)估，和真實(shí)情況有偏差)例子，快速評(píng)估值的TOPx假設(shè)某表 了用戶

的APP數(shù)組，如何快速統(tǒng)計(jì)裝機(jī)前10的APP？mon_elems::text::int[])

ele,2

as

rmon_elem_freqs)

freq,2

as

rselect

*

from(select

row_number()

over(partitionby

r)

as

rn,ele

from(selectunnest(from

pg_stats

where

tablename='test_2'

and

attname='appid')

t)t1join(select

row_number()

over(partition

by

r)

as

rn,freqfrom

(select

unnest(from

pg_stats

where

tablename='test_2'and

attname='appid')

t)

t2on

(t1.rn=t2.rn)order

by

t2.freqdesc

limit

10;特性例子hll(HyperLogLog)插件快速唯一值，增量評(píng)估例如統(tǒng)計(jì)用戶數(shù)，新增用戶數(shù)。selectcount(distinct

userid)from

access_log

where

date(crt_time)='2013-02-01';--非常耗時(shí).hll解決了耗時(shí)的問(wèn)題,

使用方法是將用戶ID聚合 到hll類型中.如下(假設(shè)user_id的類型為int)

:create

table

access_date

(acc_date

date

unique,

userids

hll);insert

into

access_date

select

date(crt_time),

hll_add_agg(hll_hash_integer(user_id))

from

access_log

group

by

1;select#userids

from

access_datewhereacc_date='2013-02-01';--這條語(yǔ)句返回只要1毫秒左右.(10億個(gè)唯一值返回也在1毫秒左右)而hll僅僅需要1.2KB就可以

1.6e+12的唯一值.特性例子hll(HyperLogLog)插件快速唯一值，增量評(píng)估例如統(tǒng)計(jì)用戶數(shù)，新增用戶數(shù)。特性例子json,jsonb類型{"guid":

"9c36adc1-7fb5-4d5b-83b4-90356a46061a","name":

"Angela

Barton","is_active":true,"company":

"Magnafone","address":

"178

Howard

Place,

Gulf,

Washington,

702","registered":"2009-11-07T08:53:22

+08:00","latitude":

19.793713,"longitude":

86.513373,"tags":

["enim","aliquip","qui"]}特性例子gin索引for

jsonbCREATE

INDEX

idxgin

ON

api

USING

gin

(jdoc);該索引支持的操作符--Find s

in

which

the

key

"company"

has

value

"Magnafone"SELECT

jdoc->'guid',

jdoc->'name'

FROM

api

WHERE

jdoc

@>

'{"company":

"Magnafone"}';--Find s

in

which

the

key

"tags"

contains

array

element

"qui"SELECT

jdoc->'guid',

jdoc->'name'

FROM

api

WHERE

jdoc

@>

'{"tags":

["qui"]}';特性例子gin索引for

jsonbCREATE

INDEX

idxgintags

ON

api

USING

gin

((jdoc

->

'tags'));--Find s

in

which

the

key

"tags"

contains

key

or

array

element

"qui"SELECT

jdoc->'guid',

jdoc->'name'

FROM

api

WHERE

jdoc

->

'tags'?

'qui';特性例子hstore

類型Includes

zero

or

more

key

=>

value

pairs

separated

by

commas.例子h

/digoal@126/blog/static/

529358/insert,

update,

delete,

truncateclient_ip,

client_port,

username,

database,

schema,

object,

time,

NEW,

OLD,

timestamp,

TAG5|23731

|

public |

test |

AFTER |

ROW |

UPDATE

|

"id"=>"1",

"info"=>"digoal",

"crt_time"=>"2012-06-25

10:54:43"|

"id"=>"1",

"info"=>"DIGOAL",

"crt_time"=>"2012-06-25

10:54:43"

|

2012-06-25

10:55:41.006069

|

postgres特性例子table類型例子使用table類型和觸發(fā)器實(shí)現(xiàn)insert,update,delete,truncate

的flashbackh

/digoal@126/blog/static/

5442434/以事務(wù)為最小單位記錄NEW,OLD

for

insert,update,delete,truncate回滾時(shí)以事務(wù)為最小單位,封裝SQLbeginfor

v_op,

v_encoding_tmp,

v_old,

v_newinselect

op,encoding,old_rec::text,new_rec::text

from

undo_t

wherexid>=v_xid

order

by

xid

desc,id

descLOOP特性例子execute

'set

client_encoding='''||v_encoding_tmp||'''';case

v_opwhen

'INSERT'

thendelete

from

public."TBL"

t

where

t=v_new::public."TBL";when

'DELETE'

theninsert

into

public."TBL"

values

((v_old::public."TBL").*);when

'TRUNCATE'

theninsert

into

public."TBL"

values

((v_old::public."TBL").*);when

'UPDATE'

thendelete

from

public."TBL"

t

where

t=v_new::public."TBL";insert

into

public."TBL"

values

((v_old::public."TBL").*);elseend

case;end

loop;execute

'set

client_encoding='''||v_encoding_curr||'''';end;特性例子范圍類型例子，快速范圍查詢，例如某個(gè)IP是否在某個(gè)IP地址段內(nèi)postgres=#

create

table

tbl(id

int,ip_start

int8,ip_end

int8);CREATE

TABLEpostgres=#

create

index

idx_tbl

on

tbl

using

btree(ip_start,ip_end);CREATE

INDEXpostgres=#

create

table

tbl_r(id

int,ip_range

int8range);CREATE

TABLEpostgres=#

create

index

idx_tbl_r

on

tbl_r

using

spgist(ip_range);CREATE

INDEX或postgres=#

create

index

idx_tbl_r1

on

tbl_r

using

gist(ip_range);CREATE

INDEX特性例子范圍類型例子，快速范圍查詢，例如某個(gè)IP是否在某個(gè)IP地址段內(nèi)查詢postgres=#

select

*

from

tbl

where

?between

ip_start

and

ip_end;postgres=#

select

*

fromtbl_r

whereip_range

@>

?;效率可提升幾十倍.ORcreate

index

idx

on

tbl

using

gist

(int8range(ip_start,ip_end+1));select

*

from

tbl

whereint8range(ip_start,ip_end+1)

@>

?;特性例子全文檢索例子,中文分詞與檢索分詞類型：tsvector，支持分詞，位置，段落查詢條件類型：tsquery，支持與，或，位置，段落，前綴等組合分詞索引：GINto_tsvector('testzhcfg','“今年保障房新開(kāi)工數(shù)量雖然有所下調(diào)，但實(shí)際的年度在建規(guī)模以及竣工規(guī)模會(huì)超以往年份，相對(duì)應(yīng)的對(duì) 的需求也會(huì)創(chuàng)歷史 。”

說(shuō)。在他看來(lái)，與2011年相比，2012年的保障房建設(shè)在 配套上的壓力將更為嚴(yán)峻。');'2011':27'2012':29'上':35'下調(diào)':7'嚴(yán)峻':37'會(huì)':14'會(huì)創(chuàng)':20'保障':1,30'歷史':21'壓力':36'國(guó)強(qiáng)':24'在建':10'實(shí)際':8'對(duì)應(yīng)':17'年份':16'年度':9'開(kāi)工':4'房':2'房建':31'數(shù)量':5'新':3'有所':6'相比':28'看來(lái)':26'竣工':12'':22

'規(guī)模':11,13

'設(shè)在':32'說(shuō)':25

' ':18,33'超':15'配套':34'陳':23

'需求':19特性例子to_tsquery('testzhcfg','保障房壓力');to_tsquery'保障'

&

'房'

&

' '

&

'壓力'SELECT'super:*'::tsquery;--super開(kāi)頭的單詞tsquery'super':*查詢舉例：tsvector

@@

to_tsquery('testzhcfg','保障房壓力');--包含查詢條件特性例子自定義字典h

/digoal@126/blog/static/

77689/全文檢索包括幾個(gè)重要的步驟：1.parsers將文本按照一定的規(guī)則分拆成多個(gè)token，并且給token按類型歸類例如（url,word,number,file,tag,version)。CREATE

TEXT

SEARCH

PARSER

name

(START

=

start_function

,GETTOKEN

=gettoken_function

,END

=

end_function

,LEXTYPES

=

lextypes_function[,

HEADLINE

=headline_function

])特性例子2.dictionaries將token轉(zhuǎn)換為規(guī)則化的分詞（即lexeme）（例如去除復(fù)數(shù)，大小寫(xiě)轉(zhuǎn)換），同時(shí)去除一些沒(méi)有意義的詞如stop

word。CREATE

TEXT

SEARCH

DICTIONARY

name

(TEMPLATE

=

template[,

option

=

value

[,

...

]])CREATE

TEXT

SEARCH

DICTIONARY

my_russian

(template

=

snowball,language

=

russian,stopwords

=

myrussian);特性例子ALTER

TEXT

SEARCH

DICTIONARYname

(option

[

=

value

]

[,

...

])The

following

example

command

changes

the

stopword

listfor

a

Snowball-based

dictionary.

Other

parametersremain

unchanged.ALTER

TEXT

SEARCH

DICTIONARY

my_dict

(

StopWords

=

newrussian

);The

following

example

command

changes

the

language

option

to

dutch,

and

removes

the

stopword

option

entirely.ALTER

TEXT

SEARCH

DICTIONARY

my_dict

(

language

=

dutch,

StopWords

);特性例子3.templates提供dictionaries下層的功能，創(chuàng)建字典時(shí)需指定一個(gè)模板，以及一些參數(shù)。CREATE

TEXT

SEARCH

TEMPLATEname

([

INIT

=

init_function

,

]LEXIZE=

lexize_function)4.configurations選擇一個(gè)parser，并配置token類型和字典的對(duì)應(yīng)關(guān)系（如每種token

type可以使用不同的字典來(lái)處理)。CREATE

TEXT

SEARCH

CONFIGURATION

name

(PARSER

=

parser_name

|COPY

=

source_config)ALTER

TEXT

SEARCH

CONFIGURATION

nameADD

MAP FORtoken_type

[,

...

]

WITH

dictionary_name

[,

...

]特性例子ALTER

TEXT

SEARCH

CONFIGURATION

nameALTER

MAP FOR

token_type

[,

...

]

WITH

dictionary_name

[,

...

]ALTER

TEXT

SEARCH

CONFIGURATION

nameALTER

MAP REPLACE

old_dictionary

WITHnew_dictionaryALTER

TEXT

SEARCH

CONFIGURATION

nameALTER

MAP FOR

token_type

[,

...

]

REPLACE

old_dictionary

WITH

new_dictionaryALTER

TEXT

SEARCH

CONFIGURATION

nameDROP

MAP [

IF

EXISTS

]

FOR

token_type

[,

...

]ALTER

TEXT

SEARCH

CONFIGURATION

name

RENAME

TO

new_nameALTER

TEXT

SEARCH

CONFIGURATION

name

OWNERTO

{

new_owner

|

CURRENT_USER

|

SESSION_USER

}ALTER

TEXT

SEARCH

CONFIGURATIONname

SET

SCHEMA

new_schema特性例子自定義字典的例子h

/digoal@126/blog/static/

77689/例子對(duì)于word,先使用別名字典處理，再使用其他字典處理。替換為四大。將劉德華，黎明，

，將 ，周正中替換為digoal。postgres@db-192-168-173-33->

cd

/opt/pgsql/share/tsearch_data/postgres@db-192-168-173-33->

vi

digoal.syn劉德華四大四大四大黎明四大digoal周正中digoal特性例子digoal=#

CREATE

TEXT

SEARCH

DICTIONARY

my_synonym

(TEMPLATE

=

synonym,SYNONYMS

=

digoal);黎明周正中');':2'':5'':4'黎明':3在修改englishconfig前:digoal=#select

*

from

to_tsvector('english','劉德華to_tsvector'劉德華':1'周正中':6'(1

row)特性例子修改englishconfig的

word類型的字典關(guān)系，將 字典放最前面。digoal=#

ALTER

TEXT

SEARCH

CONFIGURATION

englishALTER

MAP FOR

wordWITH

my_synonym,

english_stem;ALTER

TEXT

SEARCH

CONFIGURATION黎明周正中');':1,2,3,4現(xiàn)在分詞結(jié)果變了，都替換成了想要的結(jié)果：digoal=#select

*

from

to_tsvector('english','劉德華to_tsvector'digoal':5,6'四大(1

row)特性例子len:11,pos:20;/*

MAX

2Kb

*//*

MAX

1Mb

*/全文檢索類型的限制：1.

Thelength

of

each

lexeme

must

be

less

than

2K

bytes規(guī)則化后的lexeme必須要與2K，2.

The

length

of

a

tsvector

(lexemes

+

positions)

must

be

less

than

1

megabytetsvector類型長(zhǎng)度不超過(guò)1MB。這兩個(gè)限制見(jiàn)src/include/tsearch/ts_type.htypedef

struct{uint32haspos:1,}

WordEntry;3.

Thenumber

of

lexemes

must

be

less

than

2^64一個(gè)tsvector中不能 超過(guò)2的64次方個(gè)lexeme.特性例子4.

Position

values

in

tsvector

must

be

greater

than

0

and

no

more

than

16,383位置值不超過(guò)#define

MAXENTRYPOS

(1<<14)if

(res

-

a

>=

MAXNUMPOS

-

1

||_GETPOS(*res)

==

MAXENTRYPOS

-

1)break;。。。。。。5.

No

more

than

256

positions

per

lexeme每個(gè)lexeme

過(guò)256個(gè)位置。#define

MAXNUMPOS

(256)6.

The

number

of

nodes

(lexemes

+

operators)

in

a

tsquery

must

be

less

than

32,768tsquery，lexeme和operators累計(jì) 過(guò)32,768個(gè)。特性例子分詞性能指標(biāo)英語(yǔ)分詞性能：~

900萬(wàn)

words每秒

(

In (R)

Xeon(R)

CPU中文分詞性能：~

400萬(wàn)

字每秒

(In (R)

Xeon(R)

CPUX7460

@

2.66GHz

)X7460

@

2.66GHz

)英文分詞+中文分詞+性能：~

666萬(wàn)字每秒(In性能：~

290萬(wàn)字每秒(In(R)

Xeon(R)

CPU(R)

Xeon(R)

CPUX7460

@

2.66GHz

)X7460

@

2.66GHz

)查詢性能和查詢條件，數(shù)據(jù)量都有關(guān)系，沒(méi)有很好的評(píng)估標(biāo)準(zhǔn)，大多數(shù)查詢可以在毫秒級(jí)返回。特性例子pg_trgm近似度匹配，支持GIN索引檢索字符串前后各加2個(gè)空格，每連續(xù)的3個(gè)字符一組進(jìn)行拆分并去重復(fù)，不區(qū)分大小寫(xiě)digoal=>

select

show_trgm('digoal');show_trgm{"

d","

di","al",dig,goa,igo,oal}digoal=>

select

show_trgm('DIGOAL123456');show_trgm{"

d","

di",123,234,345,456,"56

",al1,dig,goa,igo,l12,oal}(1

row)近似度算法兩個(gè)字符串相同trigram個(gè)數(shù)除以總共被拆成多少個(gè)trigram特性例子大于等于近似度限制時(shí)，返回TRUE，同樣可根據(jù)近似度高低，反映檢索條件和數(shù)據(jù)之間的相關(guān)度。digoal=>

select

show_limit();show_limit0.3(1

row)postgres=#

select

similarity('postregsql','postgresql');similarity0.375(1

row)postgres=#

select

'postregsql'

%

'postgresql';?column?--

在

出現(xiàn)問(wèn)題時(shí)，例如輸錯(cuò)幾個(gè)依舊可以匹配t(1

row)特性例子域或約束例子，限制輸入格式，確保輸入為一個(gè)正確的地址。域（不支持?jǐn)?shù)組）postgres=#

createCREATEpostgres=#as

text

constraint

ck

check

(value

~

'^.+@.+\..+$'););postgres=#

create

table

test1(id

int,

mailCREATE

TABLEpostgres=#

insert

into

test1

values

(1,

'abc');ERROR:

value

for violates

check

constraint

"ck"postgres=#

insert

into

test1

values

(1,

'

');INSERT

0

1特性例子域或約束例子，限制輸入格式，確保輸入為一個(gè)正確的地址。約束（支持?jǐn)?shù)組，需自定義操作符配合數(shù)組約束使用）postgres=#

create

or

replace

function

u_textregexeq(text,text)

returns

boolean

as

$$select

textregexeq($2,$1);$$

language

sql

strict;postgres=#

CREATE

OPERATOR

~~~~

(procedure

=

u_textregexeq,

leftarg=text,rightarg=text);CREATE

OPERATORpostgres=#

select

'-[

RECORD

1

]?column?

|f'

~~~~

'^.+@.+\..+$';postgres=#

select

'^.+@.+\..+$'

~~~~

'-[

RECORD

1

]?column?

|

t';特性例子域或約束例子，限制輸入格式，確保輸入為一個(gè)正確的地址。約束（支持?jǐn)?shù)組，需自定義操作符）(id

int,postgres=#

create

table

t_CREATE

TABLEpostgres=#

insert

into

t_INSERT

0

1postgres=#

insert

into

t_text[]

check

('^.+@.+\..+$'

~~~~

all

()));values

(1,

array['','']::text[]);values

(1,

array['','a@e']::text[]);_check"ERROR:

new

row

for

relation

"t_DETAIL:

Failing

row

contains

(1,

{"

violates

check

constraint

"t_

_,a@e}).特性例子GIN索引例子，快速檢索某個(gè)值包含在哪些數(shù)組中支持?jǐn)?shù)組，全文檢索等類型pending

listpageselement

keypages

Btreeheapdatactid(0,1)

[1,2,3,4,5]ctid(0,2)

[5,6,7,8,9]ctid(0,3)

[5,6,10,11,12]list1

(0,1),(0,2),(0,3)...leaf

page信息key->1,

ItemPoint->(0,1)...key->5,

ItemPoint->list1posting

listpagespending

page信息無(wú)序key->5,

ItemPoint->(0,3)...key->6,

ItemPoint->(0,3)Merge特性例子GIN索引應(yīng)用場(chǎng)景舉例labelmatchcon1

&&

con2

&&

!con3

||

con4

特性例子BRIN索引（非常?。?-127 mintime=?

maxtime=?128-255

mintime=?

maxtime=?...

mintime=?

maxtime=?查詢select

*

from

tbl

where

crt_timebetween?and?;or

where

crt_time=?;掃描符合條件的范圍區(qū)塊，recheck條件。適合流式數(shù)據(jù)字段，不適合隨機(jī)數(shù)據(jù)字段block123nx.........BRIN(block

range

index)索引（lossy索引）例子，流式大數(shù)據(jù)的快速范圍檢索datafile假設(shè)crt_time時(shí)間值特性例子鉤子,例如auth_delay_PG_init,模塊啟動(dòng)時(shí)調(diào)用_PG_fini,backend

process

退出前調(diào)用配置,隨數(shù)據(jù)庫(kù)啟動(dòng)的模塊shared_preload_libraries

=

''特性例子鉤子,例如auth_delaysrc/include/libpq/auth.h/*

Hook

forplugins

to

get

control

in

ClientAuthentication()

*/typedef

void

(*ClientAuthentication_hook_type)

(Port

*,

int);extern

PGDLLIMPORT

ClientAuthentication_hook_type

ClientAuthentication_hook;特性例子鉤子,例如auth_delaysrc/backend/libpq/auth.c/**

This

hook

allows

plugins

to

get

control

following

clientauthentication,*

but

before

the

user

has

been

informed

about

the

results.

It

could

beused*

to

record

login

events,

insert

adelay

after

failed

authentication,

etc.*/ClientAuthentication_hook_type

ClientAuthentication_hook

=

NULL;voidClientAuthentication(Port

*port){if

(ClientAuthentication_hook)(*ClientAuthentication_hook)

(port,

status);特性例子/**

Check

authentication*/static

voidauth_delay_checks(Port

*port,

int

status){/**

Any

other

plugins

which

use

ClientAuthentication_hook.*/if(original_client_auth_hook)original_client_auth_hook(port,status);hort

delay

if

authentication

failed./**

Inje*/if

(status

!=

STATUS_OK){pg_usleep(1000L

*auth_delay_milliseconds);}}特性例子/**

Module

Load

Callback*/void_PG_init(void){/*

Define

custom

GUC

variables

*/DefineCustomIntVariable("auth_liseconds","Milliseconds

to

delay

before

reporting

authentication

failure",/*

Install

Hooks

*/original_client_auth_hook

=

ClientAuthentication_hook;ClientAuthentication_hook

=

auth_delay_checks;}特性例子其他鉤子auto_explain,

pg_stat_statement,

passwordcheck,

sepgsql特性例子直接修改元表，繞過(guò)rewrite

table，例如修改numeric精度,varchar長(zhǎng)度.(修改元表有風(fēng)險(xiǎn)，操作需謹(jǐn)慎)postgres=#

create

table

tbl(idint,c1

numeric(6,3),

c2

varchar(5));postgres=#

insertinto

tbl

select

1,100.5555,'test'

fromgenerate_series(1,5000000);INSERT

0

5000000postgres=#

select

*

from

tbl

limit

1;id

|

c1 |

c21

|

100.556

|

testpostgres=#

alter

table

tbl

alter

column

c1

typenumeric(6,2);Time:

4362.482

ms

--rewrite

table,同時(shí)精度壓縮postgres=#

select

*

from

tbl

limit

1;id

|

c1 |

c21

|

100.56

|

testpostgres=#

alter

table

tbl

alter

column

c1

typenumeric(6,3);Time:

4565.196

ms

--rewritetable,同時(shí)精度無(wú)法恢復(fù)postgres=#

select

*

from

tbl

limit

1;id

|

c1 |

c21

|

100.560

|

test特性例子postgres=#

alter

table

tbl

alter

column

c2

typevarchar(1);WARNING:

value:test

too

long

for

type

character

varying(1)...

--rewrite

table,同時(shí)字符串截?cái)郃LTER

TABLEpostgres=#

select

*

from

tbl

limit

1;id

|

c1 |

c2

+

+1

|

100.560

|

t(1

row)postgres=#

alter

table

tbl

alter

column

c2

typevarchar(6);ALTER

TABLETime:

0.793

ms

--不需要rewrite

table.postgres=#

select

*

from

tbl

limit

1;id

|

c1 |

c2

+

+1

|

100.560

|

t特性例子變長(zhǎng)字段長(zhǎng)度相關(guān)的元表信息postgres=#

select

atttypmod

frompg_attribute

where

attrelid='tbl'::regclass

and

attname='c1';atttypmod |

393223

--需計(jì)算postgres=#

select

atttypmod

frompg_attribute

where

attrelid='tbl'::regclass

and

attname='c2';atttypmod |

10

--varchar變長(zhǎng)字段,附加4字節(jié)頭,6+4=10.numeric精度轉(zhuǎn)換postgres=#

select

oid

from

pg_type

where

typname='numeric';1700postgres=#

select

information_schema._pg_numeric_scale(1700,393223);3postgres=#

select

information_schema._pg_numeric_precision(1700,393223);6postgres=#

select

information_schema._pg_numeric_precision_radix(1700,393223);10postgres=#select

numerictypmodin('{6,3}');--從精度計(jì)算typmode393223特性例子postgres=#

select

numerictypmodin('{6,2}');393222postgres=#

select

numerictypmodin('{6,4}');393224修改元表postgres=#update

pg_attribute

set

atttypmod=393222

where

attrelid='tbl'::regclass

and

attname='c1';--更新為numeric(6,2)postgres=#select

*

from

tbl

limit

1;--不需要rewrite

table,不影響已有數(shù)據(jù)id

|

c1 |

c21

|

100.556

|

testpostgres=#insertinto

tbl

values(0,100.55555,'test');--精度修改已生效postgres=#

select

*

from

tbl

whereid=0;id

|

c1 |

c20

|

100.56

|

test

--精度修改已生效postgres=#update

pg_attribute

set

atttypmod=393224

where

attrelid='tbl'::regclass

and

attname='c1';--更新為numeric(6,4)postgres=#

insert

into

tbl

values

(0,1.55555,'test');postgres=#

select

*

from

tbl

whereid=0;id

|

c1 |

c20

|

1.5556

|

test

--精度修改已生效.

.

.特性例子postgres=#update

pg_attribute

set

atttypmod=5

where

attrelid='tbl'::regclass

and

attname='c2';--修改為varchar(1)postgres=#select

*

from

tbl

where

id=0;

--不需要rewrite

table,

現(xiàn)有數(shù)據(jù)不變id

|

c1 |

c20

|

100.56

|

test0

|

1.5556

|

testpostgres=#

insert

into

tbl

values

(0,1.55555,'test');--

忽略,

此處因我修改過(guò)源碼，所以允許

，但是會(huì)TRUNC,

正常情況應(yīng)該是ERROR不允許WARNING:

value:test

too

long

for

type

character

varying(1)INSERT

0

1postgres=#

insert

into

tbl

values

(0,1.55555,'t');INSERT

0

1postgres=#

select

*

from

tbl

where

id=0;id

|

c1 |

c2

+

+...0

|

1.5556

|

t0

|

1.5556

|

t特性例子postgres=#update

pg_attribute

set

atttypmod=10

where

attrelid='tbl'::regc