2021年8月25日 星期三

PostgreSQL 使用的一些心得

最近為了避免 License 問題,終於開始使用這個授權最寬鬆的關聯式資料庫,有些技巧跟我以前使用的 MariaDB 都不一樣,記錄一下避免以後忘記。

01. 命令列工具登入方式

psql -U postgres -h localhost -d database_name

02. 安全性設定檔

pg_hba.comf

03. SQL count(*) 過慢

貌似因為 Lock 機制,PG 需要從頭搜尋一遍以得到精準的數字,如果只是想要知道大概,可以使用下面指令得到近似值。

SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname='table_name';

04. Linux 下得知目前資料庫硬碟使用空間

sudo du -sh /sda3/postgresql/

05. 使用指令得到某 table 使用空間

SELECT pg_total_relation_size('table_name');

06. 使用指令得到某 database 使用空間

此數字會比實際硬碟空間還少,因為實際空間還包括 PG 系統本身,以我的測試資料庫來看,大概還要乘上 2 倍。

SELECT pg_database_size('database_name');

07. 一些建立 table SQL 語法

CREATE TABLE users (
    user_id TEXT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    pwd TEXT NOT NULL,
    privilege TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE devices (
    device_id TEXT NOT NULL PRIMARY KEY,
    ip TEXT NOT NULL,
    port INTEGER NOT NULL,
    data JSON NOT NULL
);

CREATE UNIQUE INDEX idx_devices_ip_port
ON devices(ip, port);

CREATE TABLE groups (
    user_id TEXT NOT NULL,
    group_id TEXT NOT NULL PRIMARY KEY,
    group_name TEXT NOT NULL,

    CONSTRAINT fk_users
      FOREIGN KEY(user_id)
  REFERENCES users(user_id)
  ON DELETE CASCADE
);

沒有留言:

張貼留言