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

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

ark學習資料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(伯克利大學學生Andrew

Yu,JollyChen重寫SQL解釋器,替換原項目中的基于Ingres的SQL解釋器.為開源奠定了基礎)1996

更為PostgreSQL,

發(fā)布第一個開源版本,

后交由PostgreSQL社區(qū)

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

版本發(fā)布歷程PostgreSQL

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

/postgres/postgres/graphsPostgreSQL

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

全球貢獻者Core

Team成員Josh

Berkus

(USA,

CEO@PostgreSQL

Experts

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

Eisentraut

(USA,

)主要負責了系統(tǒng)建設,移植,文檔編輯,Magnus

Hagander(Sweden,

redpill-linpro.se)幫助

PostgreSQL

WEB主站及基礎設施,win32的移植,

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

Lane

(USA,

Salesforce)遍及PostgreSQL代碼的各個角落,包括BUG評估和修復,性能改進,優(yōu)化等.Bruce

Momjian

(USA,

EnterpriseDB)負責

TODO和FAQ列表,

代碼,

發(fā)布版本補丁以及培訓.Dave

Page

(UnitedKingdom,

EnterpriseDB)負責pgadmin的開發(fā)和

工作,

同時負責管理postgresql.主站工程,PostgreSQL的安裝程序等.主要貢獻者.

munity/contributors/Committers

(git@gitmaster.postgresql.

/postgresql.git)目前有

位

mitter.

(mitters)PostgreSQL

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

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

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

…},Disqus,

去生物制藥

{Affymetrix(

),

化學

,

gene(結構生物學應用案例),

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

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

flightstats,

Endpoint

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

{Journyx,

LLC,

merce(類似支付寶),

交易交所,

郵儲銀行,

同花順…}{MobyGames,

…}{

國家氣象局,

國家物理

,

兒童基金,

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

{calorieking,

開源電子 項目,

shannon醫(yī)學中心,

…}制造業(yè){Exoteric

Networks,

豐田,捷豹路虎}{

,

國會投票數(shù)據(jù)庫,MacWorld,綠色和平組織,…}開源項目

{Bricolage,

Debian,

FreshPorts,

FLPR,

PostGIS,SourceF e,

OpenACS,

Gf零售

{ ,

CTC,

Safeway,

Tsutaya,

Rockport,

…}科技

{Sony,

MySpace,

Yahoo,

Afilias,

APPLE,富士通,

Omniti,

Red

Hat,

Sirius

IT,SUN,

國際空間站,哪兒,

騰訊, ,

中興,

斯凱,

云游,

阿里

…}電信),德國電信,Optus,

Skype,Tlestra(澳洲電訊),中國移動…}通信{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ā)器安全特性加密鏈路加密認證方法行安全策略數(shù)據(jù)類型特性幾何類型網(wǎng)絡類型全文檢索類型JSON,

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

C,

plR,

pljava,plpython,

plperl,

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

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

regr_r2,

regr_intercept,斜率。使用相關性最高的截距和斜率計算下一天的收盤價。h

/digoal@126/blog/static/

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

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

over(partition

by

course

order

by

score

desc)

-

score

as

difffrom

tbl;特性例子遞歸查詢異構查詢，例如 線路信息，可能包含當前站點，上一個站點的信息某些多 分類信息，包括大類，小類，每條記錄可能記錄了父類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語句的

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

Table

Expressions)UNION

去重復(去重復時NULL

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

WorkingTABLE1輸出

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

Intermediate

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

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

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

將logdate='2015-03-01'的值更新到另一個分區(qū),同時還需要更新其他某字段值為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ā)器例子,控制普通用戶沒有執(zhí)行DDL的權限特性例子事件觸發(fā)器例子,控制普通用戶沒有執(zhí)行DDL的權限目前支持的事件mand_startmand_endtable_rewritesql_drop支持的SQL,(未完全截取)特性例子LDAP認證或AD域認證支持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ù)共享場景，對同一個表操作時，不同的用戶能查看到不同的數(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

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

check

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

check，檢測新數(shù)據(jù)）這個策略檢測test表的r字段，必須等于當前用戶名才行。也就是說任何用戶在使用一張表時不會test表時，r字段的值必須和當前用戶名相同，這樣就可以很好的控制多個用戶在數(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特性例子柱狀圖妙用(用作評估，和真實情況有偏差)例子，快速評估值的TOPx假設某表 了用戶

的APP數(shù)組，如何快速統(tǒng)計裝機前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)插件快速唯一值，增量評估例如統(tǒng)計用戶數(shù)，新增用戶數(shù)。selectcount(distinct

userid)from

access_log

where

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

使用方法是將用戶ID聚合 到hll類型中.如下(假設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';--這條語句返回只要1毫秒左右.(10億個唯一值返回也在1毫秒左右)而hll僅僅需要1.2KB就可以

1.6e+12的唯一值.特性例子hll(HyperLogLog)插件快速唯一值，增量評估例如統(tǒng)計用戶數(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ā)器實現(xiàn)insert,update,delete,truncate

的flashbackh

/digoal@126/blog/static/

5442434/以事務為最小單位記錄NEW,OLD

for

insert,update,delete,truncate回滾時以事務為最小單位,封裝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;特性例子范圍類型例子，快速范圍查詢，例如某個IP是否在某個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特性例子范圍類型例子，快速范圍查詢，例如某個IP是否在某個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','“今年保障房新開工數(shù)量雖然有所下調(diào)，但實際的年度在建規(guī)模以及竣工規(guī)模會超以往年份，相對應的對 的需求也會創(chuàng)歷史 ?！?/p>

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

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

'設在':32'說':25

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

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

&

'房'

&

' '

&

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

@@

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

/digoal@126/blog/static/

77689/全文檢索包括幾個重要的步驟：1.parsers將文本按照一定的規(guī)則分拆成多個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轉換為規(guī)則化的分詞（即lexeme）（例如去除復數(shù)，大小寫轉換），同時去除一些沒有意義的詞如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ù)。CREATE

TEXT

SEARCH

TEMPLATEname

([

INIT

=

init_function

,

]LEXIZE=

lexize_function)4.configurations選擇一個parser，并配置token類型和字典的對應關系（如每種token

type可以使用不同的字典來處理)。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/例子對于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類型的字典關系，將 字典放最前面。digoal=#

ALTER

TEXT

SEARCH

CONFIGURATION

englishALTER

MAP FOR

wordWITH

my_synonym,

english_stem;ALTER

TEXT

SEARCH

CONFIGURATION黎明周正中');':1,2,3,4現(xiàn)在分詞結果變了，都替換成了想要的結果：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類型長度不超過1MB。這兩個限制見src/include/tsearch/ts_type.htypedef

struct{uint32haspos:1,}

WordEntry;3.

Thenumber

of

lexemes

must

be

less

than

2^64一個tsvector中不能 超過2的64次方個lexeme.特性例子4.

Position

values

in

tsvector

must

be

greater

than

0

and

no

more

than

16,383位置值不超過#define

MAXENTRYPOS

(1<<14)if

(res

-

a

>=

MAXNUMPOS

-

1

||_GETPOS(*res)

==

MAXENTRYPOS

-

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

No

more

than

256

positions

per

lexeme每個lexeme

過256個位置。#define

MAXNUMPOS

(256)6.

The

number

of

nodes

(lexemes

+

operators)

in

a

tsquery

must

be

less

than

32,768tsquery，lexeme和operators累計 過32,768個。特性例子分詞性能指標英語分詞性能：~

900萬

words每秒

(

In (R)

Xeon(R)

CPU中文分詞性能：~

400萬

字每秒

(In (R)

Xeon(R)

CPUX7460

@

2.66GHz

)X7460

@

2.66GHz

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

666萬字每秒(In性能：~

290萬字每秒(In(R)

Xeon(R)

CPU(R)

Xeon(R)

CPUX7460

@

2.66GHz

)X7460

@

2.66GHz

)查詢性能和查詢條件，數(shù)據(jù)量都有關系，沒有很好的評估標準，大多數(shù)查詢可以在毫秒級返回。特性例子pg_trgm近似度匹配，支持GIN索引檢索字符串前后各加2個空格，每連續(xù)的3個字符一組進行拆分并去重復，不區(qū)分大小寫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)近似度算法兩個字符串相同trigram個數(shù)除以總共被拆成多少個trigram特性例子大于等于近似度限制時，返回TRUE，同樣可根據(jù)近似度高低，反映檢索條件和數(shù)據(jù)之間的相關度。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)問題時，例如輸錯幾個依舊可以匹配t(1

row)特性例子域或約束例子，限制輸入格式，確保輸入為一個正確的地址。域（不支持數(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特性例子域或約束例子，限制輸入格式，確保輸入為一個正確的地址。約束（支持數(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';特性例子域或約束例子，限制輸入格式，確保輸入為一個正確的地址。約束（支持數(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索引例子，快速檢索某個值包含在哪些數(shù)組中支持數(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信息無序key->5,

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

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

&&

con2

&&

!con3

||

con4

特性例子BRIN索引（非常小）1-127 mintime=?

maxtime=?128-255

mintime=?

maxtime=?...

mintime=?

maxtime=?查詢select

*

from

tbl

where

crt_timebetween?and?;or

where

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

range

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

process

退出前調(diào)用配置,隨數(shù)據(jù)庫啟動的模塊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特性例子直接修改元表，繞過rewrite

table，例如修改numeric精度,varchar長度.(修改元表有風險，操作需謹慎)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,同時精度壓縮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,同時精度無法恢復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,同時字符串截斷ALTER

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特性例子變長字段長度相關的元表信息postgres=#

select

atttypmod

frompg_attribute

where

attrelid='tbl'::regclass

and

attname='c1';atttypmod |

393223

--需計算postgres=#

select

atttypmod

frompg_attribute

where

attrelid='tbl'::regclass

and

attname='c2';atttypmod |

10

--varchar變長字段,附加4字節(jié)頭,6+4=10.numeric精度轉換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}');--從精度計算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');--

忽略,

此處因我修改過源碼，所以允許

，但是會TRUNC,

正常情況應該是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