PostgreSQL 進階教學

role:

PostgreSQL 裡的 role 就跟其他 RDBMS 的 user 是一樣的意思，只是這邊稱之為 role。

角色屬性:

LOGIN: 允許是否可以登入至資料庫。CREATE USER 預設會帶入 LOGIN attribute, 但是 CREATE ROLE 不會。

SUPERUSER: 超級使用者權限，只有擁有 SUPERUSER 的角色才可以建立

CREATEDB: 建立 database 的權限

CREATEROLE: 建立 role 的權限

REPLICATION: 賦予角色複製的權限，此屬性必須搭配 LOGIN

PASSWORD：設定 Role 的 password

INHERIT: 是否允許 Role 繼承別人的屬性

psql:

為 postgresql command line 指令

1. 顯示角色以其屬性列表

\du

2. 列出目前登入的使用者屬性

\du :USER

創建 role

創建 Role 並且賦予屬性

CREATER ROLE <role_name> WITH <角色屬性>

更改密碼：

ALTER ROLE <role> WITH PASSWORD <password>;

建議使用 psql 更改密碼

-- 更改目前使用者的密碼
\password

更改特定角色的密碼

\password <role>

角色重新命名

ALTER ROLE <role_name> RENAME TO <new role name>

刪除 role：

在刪除 Role 之前要確保 role 已經沒有資料庫的任何權限以及物件的所有權。

以下指令為將物件擁有權指派給 postgres

REASSIGN OWNED BY "user" TO "postgres";

撤銷所授予的物件權限

DROP OWNED BY "user";

刪除角色

DROP ROLE <role name>;

新增權限 (Grant):

格式:

GRANT <priviliege_list>
ON ALL TABLES
IN SCHEMA "public"
TO role_name;

privilege_list: 可以設定 SELECT、INSERT、UPDATE、DELETE、TRUNCATE 也可以設定 ALL

範例:

允許 user1 使用者針對 public schema 內的所有 table 擁有 select 權限。

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;

將權限授與給別人

首先我們先創建一個 developer ROLE 並且給予 NOINHERIT

CREATE ROLE developer WITH NOINHERIT;

(建立 role group) 將 developer role 的權限給予 user1 去繼承

GRANT developer TO user1;

撤銷 user1 的 developer 權限

REVOKE developer FROM user1;

參考資源:

[PSQL] PostgreSQL 角色權限 Roles and Privileges | PJCHENder 未整理筆記

https://prisma.dev.org.tw/dataguide/postgresql/authentication-and-authorization/role-management

https://prisma.dev.org.tw/dataguide/postgresql/authentication-and-authorization/managing-privileges

index 種類:

可以加快 SQL 的查詢速度，而 PostgreSQL 提供以下 Index

sequential index:

循序的找尋資料。通常就算有該欄位有上 index, 但是資料量不多的情況下還是會使用 sequentail index。

bitmap index:

當資料量比較大的情形會使用 bitmap index, 因為 sequential index 可能會導致一個 page 重複讀取。

index scan only:

直接透過 index 即可取得資料。

partial index:

將部分的欄位的值上 index，假如有個欄位叫做 is_active , is_active = true 經常被使用那可以針對 is_active = true 上索引。

語法如下:

create index <index name> on table_name(column)
where condition = ....;

針對 is_active = true 上索引

create index idx_is_active on users(is_active)
where is_active = true;

優點:

1. 減少硬碟空間的佔用，因為只針對部分欄位的值上索引
2. 快速的 update、insert ，因為只有更少的資料需要索引

index on expression:

通常在有下 index 的欄位使用 SQL expression 的話只會採用 sequential index，如果希望能套用 index 的話就需要使用 index on expression。

語法如下：

create index <index name> on t(lower(b));

參考資源:

PostgreSQL Partial Index

Postgres - 深入探討 index engine 的行為

顯示 table index:

以下語法為，顯示 tablename 為 cars 的 index 資訊出來

select
 *
from pg_indexes
where tablename = 'cars';

結果:

索引注意事項:

建立索引:

PostgreSQL 在建立索引的時候會進行 shared lock ，也就是可讀但是不可以進行 insert、update。若想要避免 shared lock 的話則需要進行 concurrently 操作。

語法如下:

create index <index name> concurrently on table_name(column name);

刪除索引:

PostgreSQL 在刪除索引的時候進行的是 exclusive lock，也就是不可讀不可寫。一樣要避免 exclusive lock 則需進行 concurrently 操作。

drop index concurrently if exists <index name>

假如在使用 concurrently 的時候發生索引建立失敗等情況的話，需要使用進行刪除 index, 因為 concurrently 的情況下失敗是不會 rollback 的。

參考資源:

PostgreSQL DROP INDEX Statement

Postgres - 深入探討 index engine 的行為

schema:

可以將不同的類型放置同一個 database 但是不同 schema 內，預設在建立database 的時候會建立 public schema。

建立 Schema 語法如下

CREATE SCHEMA <schema name>

刪除 Schema 語法如下

DROP SCHEMA <schema name>

參考資源:

PostgreSQL 模式（SCHEMA）

psql cli 常用指令、pg 相關 table :

查看 database 大小:

SELECT pg_size_pretty(pg_database_size('<table name>'));

結果:

透過 pg_database 取得 database 大小

SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database;

結果:

查看該 table 大小:

SELECT pg_relation_size('<table name>');

結果:

將輸出的數值變成比較好閱讀的資訊 (MB、GB等)

SELECT pg_pretty_size(pg_relation_size('<table name>'));

結果:

pg_relation_size 只會回傳資料表的大小，並不會包含 index 等其他 object 的大小，想要取得完整的大小需使用 pg_total_relation_size

SELECT pg_total_relation_size('cars');

列出該 database 所有的 table

psql:

首先切至指定的 database

\c <database>

顯示所有資料表詳細資訊

\dt+

顯示特定資料表資訊

\d <table name>

列出所有角色

\du

顯示特定角色資訊

\du <role name>

參考資源:

PostgreSQL Administration

event:

regular trigger:

當 SQL 的 insert、update、delete、truncate 發生的時候觸發 event function。

建立 regular event trigger 語法如下

CREATE EVENT TRIGGER <trigger_name>
BEFORE|AFTER <event>
ON <table_name>
<FOR EACH ROW|FOR EACH STATEMENT>
EXECUTE FUNCTION <function_name>

參數說明:

trigger_name: event trigger 的名稱

BEFORE、AFTER: event 發生前或者後觸發 event trigger

event: SQL 的 insert、update、delete、truncate 等事件

table_name: 資料表名稱

FOR EACH ROW: row-level 就是每一筆 row 被觸發的時候會發動 event trigger

FOR EACH STATEMENT: statement-level 就是執行每一個 statement 時觸發 event trigger

function_name: 建立的 function 名稱

event trigger:

用於觸發 database 層級相關的 event。

擁有以下 event 可以定義:

DDL(Database Definition Language) command: CREATE、ALTER、DROP、REVOKE 等指令

1. ddl_command_start: 執行 DDL command 前觸發
2. ddl_command_end: 執行 DDL command 後觸發
3. table_rewrite: 執行 alter table command 前觸發
4. sql_drop: 執行 DROP command 觸發

語法如下:

CREATE EVENT TRIGGER <trigger_name>
ON <event_name>
EXECUTE FUNCTION <function_name>();

參數說明:

trigger_name: trigger 名稱

event_name: 上面的 ddl_command_start 等 event

function_name: 定義的 function 名稱

創建 function 的方法:

CREATE OR REPLACE FUNCTION function_name()
RETURNS EVENT_TRIGGER AS $$
BEGIN
  sql statement....
END;
$$ LANGUAGE plpgsql;

範例:

insert data 觸發 event trigger

首先新增 audit 的資料表用於紀錄操作等資訊

drop table if exists audits;
create table audits (
 id INT GENERATED ALWAYS AS IDENTITY,
 foreign_id INT NOT NULL,
 tg_name VARCHAR(255) NOT NULL,
 tg_when VARCHAR(255) NOT NULL,
 tg_op VARCHAR(255) NOT NULL,
 old_value JSONB NULL,
 new_value JSONB NOT NULL
);

創建 trigger 的 function

CREATE OR REPLACE FUNCTION log_data_changed()
RETURNS TRIGGER AS 
$$
BEGIN
 INSERT INTO audits(foreign_id, tg_name, tg_op, tg_when, old_value, new_value)
 VALUES (
  NEW.id,
  TG_NAME,
  TG_OP,
  TG_WHEN,
  row_to_json(OLD),
  row_to_json(NEW)
 );

 RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

創建 orders 資料表

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id INT GENERATED ALWAYS AS IDENTITY,
  json JSONB NOT NULL
)

創建 regular trigger

CREATE OR REPLACE TRIGGER log_order_changed
BEFORE INSERT OR UPDATE
ON orders
FOR EACH ROW
EXECUTE FUNCTION log_data_changed();

新增/更新資料至 orders 資料表

insert into orders(info)
values ('{"items": "hello world"}');

update orders
set info = '{"customer": "gary"}'
where id = 5;

檢查 audits table 是否有資料

event trigger local variable 說明:

TG_WHEN: 何時觸發 trigger (BEFORE、AFTER)

TG_NAME: trigger 名稱

TG_OP: trigger 觸發的 event (INSERT、UPDATE、TRUNCATE)

PostgreSQL BEFORE UPDATE Trigger

41.10. Trigger Functions

參考資源:

PostgreSQL CREATE TRIGGER Statement

stored procedure:

創建 procedure 的 語法如下:

CREATE OR REPLACE PROCEDURE <procedure_name>
AS $$
BEGIN
   SQL statement....
END;
$$ LANGUAGE plpgsql;

view:

可以把一些常用的 SQL 指令建立成 view。

優點:

1. SQL 語法的 reusable
2. 將一些敏感資訊隱藏起來

新增 view 的語法如下

CREATE OR REPLACE VIEW <view_name>
AS
 SQL statement.....
;

創建 get_order view

CREATE OR REPLACE VIEW get_order
AS 
SELECT * FROM orders;

參考資源:

PostgreSQL Materialized Views

Performance 改善 & 監控:

啟用 slow query:

log_min_duration_statement: 設定執行超過多少 ms 要紀錄 log

log_statement: 要紀錄的 statement 類型

all: 所有類型

ddl: 只紀錄修改 schema 相關的語法 (ALTER、CREATE、DROP)

mod: 紀錄除了 ddl statement 外也會紀錄資料操作相關的 statement (INSERT、UPDATE、DELETE)

none: 不紀錄 statement

2–2 pg_stat_statements extension

啟用 pg_stat_statements extension

CREATE EXTENSION pg_stat_statements;

參數說明:

pg_stat_statements.utility_function: 是否紀錄非 DML 的語句, on 表示追蹤， 反之 off 表示不追蹤

pg_stat_statements.max: 最多保留多少條訊息

shared_preload_libraries: 表示啟動的時候要載入的 library

清理 pg_stat_statements 資料

SELECT pg_stat_statements_reset();

參考資源:

Find slow queries in PostgreSQL