PostgreSQL Extension

database
,

Extension

  1. pg_cron
  2. pg_repack
  3. pg_squeeze

最常用的PostgreSQL擴展
Citus
ltree
pg_prewarm
pg_repack
pg_stat_statements
PostGIS
postgres_fdw
Swarm64
tablefunc
TimescaleDB
ZomboDB

pg_cron

用pg_trgm加速模糊查詢
安裝：create extension pg_trgm;
創建trgm索引: create index idx_trgm on t_location using GiST(name GiST_trgm_ops)

  1. 相似度計算

    select ‘abcde’ <-> ‘abcdeacb’;
    explain select * from t_location order by name <-> ‘Kramertneusiedel’

  2. 加速like查詢

    explain select * from t_location where name like ‘%neusi%’

  3. 處理正則表達式

    explain select * from t_location where name ~ ‘[A-C].neu.‘;