UPSERT

維基百科對 UPSERT 的說明:(取自「Merge (SQL)」條目)

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether or not a condition matches.

MySQL 裡的兩種語法其實就是在實做這個需求:

  • REPLACE INTO ...
  • INSERT INTO ... ON DUPLICATE KEY UPDATE ...

而前者其實是後者的一個特例 (當 INSERT 發現有 dupe key 時把現有的 record 改成與 INSERT 時相同的條件)。

而計數器是後者常見的 case 之一:當 record 不存在的時候塞一筆進去,並且將 counter 設為 1;當 record 存在的時候對 counter 加一更新。像是這樣的 SQL query:

INSERT INTO my_table SET id = ?, num = 1 ON DUPLICATE KEY UPDATE num = num + 1;

由於這是常見的需求,使得這個語法是目前少數 MySQL 比 PostgreSQL 好用的地方。

在「A Case for Upserts」這篇就看到抱怨 PostgreSQL 不實做這個功能...

不過我覺得作者寫得有點誇張,INSERT INTO ... ON DUPLICATE KEY UPDATE ... 應該是可以模擬出來的功能:當 INSERT 失敗後再跑 UPDATE。而 REPLACE INTO ... 是特例,也就當然可以模擬出來。

MySQL 裡儲存時間的方式...

這應該是 MySQL 的 best practice 之一,不知道為什麼 Baron Schwartz 又拿出來講:「A simple rule for sane timestamps in MySQL」。

MySQL 內可以儲存「日期與時間」的資料型態是 DATETIME 與 TIMESTAMP 兩種,不過 DATETIME 沒有時區觀念,而 TIMESTAMP 只能是 UTC (GMT+0)。

相較於隔壁棚 PostgreSQLDate/Time Types 就一種 TIMESTAMP,但支援 with timezone 與 without timezone 直接解決問題。

這使得 MySQL 上在儲存「日期與時間」以及處理的時候一直有種 WTF 的感覺...

就如同 Baron Schwartz 的建議,如果使用 MySQL,目前比較好的方法是用 INT UNSIGNED NOT NULL 儲存,把 timezone 的處理都放到應用程式端來處理,這樣產生的問題會比較少...

真的需要在 INSERT 或是 UPDATE 時更新欄位,可以用 trigger 處理,彈性反而比內建功能大不少。

Amazon RDS 支援 PostgreSQL 了...

AWS 的官方公告出來了:「Amazon RDS for PostgreSQL - Now Available」。

而且 AWS 上 MySQL 有的 feature,PostgreSQL 都有:

  • Multi-AZ Deployments
  • Provisioned IOPS
  • Virtual Private Cloud
  • Automated Backups
  • Point-in-time Recovery
  • Cross-Region Snapshot Copy

而且是 PostgreSQL 9.3 (雖然是 9.3.0),如果本來自己用 EC2 搞 PostgreSQL 的都可以切過去了 :p

關於 Linux 的 Disk I/O 調整...

Twitter 上看到 tka 的 retweet,介紹了 Linux 下 Disk I/O 的調整:「PostgreSQL: Linux kernel I/O tuning」。

文章裡介紹了三種 scheduler,NOOP、CFQ、Deadline,其中 CFQ 是系統預設值。

其實 MySQL 的結論也差不多,Percona 在 2009 年的時候做過 benchmark,就直接看圖講故事吧:「Linux schedulers in tpcc like benchmark」。


數字愈大愈好。

noop 與 deadline 相當接近,對於 i/o bound 的人都應該要調整 :p

PostgreSQL Studio (phpPgAdmin 替代品?)

Hacker News 文摘上看到「PostgreSQL Studio」這個軟體,看官網與畫面大概知道能做什麼事情:


官網的 screenshot...

看起來要做掉不少 phpPgAdmin 的事情,但畫面漂亮不少:


phpPgAdmin 官網的 screenshot...

不過 PostgreSQL Studio 是用 Java 寫的,要用 Apache Tomcat (或是其他 servlet 實做) 執行,先丟著好了...

PostgreSQL 9.3...

前幾天 PostgreSQL 宣佈 9.3 發佈:「PostgreSQL 9.3 released!」。

如同預測的,9.3 對 JSON 的處理能力變強了,加上可以自訂 index (寫 function 自訂 index 的內容),這使得 PostgreSQL 可以做 JSON-based document database,可以參考 9.3 的「PostgreSQL: Documentation: 9.3: JSON Functions and Operators」以及前一版 9.2 的「PostgreSQL: Documentation: 9.2: JSON Functions」。

MongoDB 就別來亂了... XD

MySQL InnoDB 與 PostgreSQL 的 Partial Index(es) 是不一樣的東西...

MySQL InnoDB 指的 Partial Index 是:

An index that represents only part of a column value, typically the first N characters (the prefix) of a long VARCHAR value.

PostgreSQL 指的 Partial Indexes 是:

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate. Partial indexes are a specialized feature, but there are several situations in which they are useful.

先講結論,PostgreSQL 可以做掉 MySQL InnoDB 的 Partial Index 想做的事情,而且還更多。

MySQL InnoDB 的 Partial Index 是設定對 prefix index (對字串前面的 n bytes),可能的情況是 CHAR(32) 只對前面 16 bytes 索引。

PostgreSQL 的 Partial Indexes 受益於許多方面而更強大。因為有 Indexes on Expressions,所以除了可以像 MySQL 對 prefix 索引外,也可以索引 suffix,甚至是索引透過 string function 得出來的值。

像是 PostgreSQL 可以設定「我只要索引一月一日出生的人的 username」:

CREATE INDEX test_index ON test_table (username) WHERE birth_month = 1 AND birth_day = 1;

在 MySQL 裡需要反正規化後下 index,或是拆出另外一個表格再下 index 的問題,在善用 PostgreSQL 這些功能就可以省下不少功夫...

PostgreSQL 筆記...

純粹是筆記...

對於架設 server 的文件可以參考 Ubuntu 這份「PostgreSQL - Community Ubuntu Documentation」,雖然 Debian 官方也有一份「PostgreSql - Debian Wiki」,不過沒講到遠端這塊...

設定的部份:

  • 要讓遠端可以存取有兩個地方要開,一個是 postgresql.conflisten_addresses 改成 "*",另外一個是增加 pg_hba.conf 遠端連線的權限。
  • CREATE USER test WITH PASSWORD 'test_password'; 以及 CREATE DATABASE test WITH OWNER = test; 把基本的東西建好。

然後把 firewall 打開,接下來就可以從其他台連進去了。

在 PostgreSQL 上用 GPU 加速計算...

看到 PGStorm 這個 PostgreSQL 上的惡搞套件,可以把本來 CPU 要做的事情丟到 GPU 上加速...

不過例子很怪啊,不是用 R-tree index 解決的事情嗎?PostgreSQL 明明就有支援 R-tree index 啊?為什麼會要這樣設計,然後用 table scan?我再回去想想好了...

PostgreSQL 安全性更新...

PostgreSQL 在官網最明顯的位置上放出更新公告:

說明在「PostgreSQL 9.2.4, 9.1.9, 9.0.13 and 8.4.17 released」這頁,更新的版本分別是 9.2.4 (9.2)、9.1.9 (9.1)、9.0.13 (9.0) 與 8.4.17 (8.4)。除了公告外,在 Security Information 的資料也可以交叉看。

這次最嚴重的問題是 CVE-2013-1899,等級分類在最嚴重的 A 級,官方對這個問題的描述是:

A connection request containing a database name that begins with "-" may be crafted to damage or destroy files within a server's data directory

hmmm...