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

);