在 SQL 裡面避免大量刪除資料的方式

看到 Percona 的「An Overview of Sharding in PostgreSQL and How it Relates to MongoDB’s」這篇,雖然是在講 PostgreSQL 上的 sharding (以及 partition),突然想到好像沒寫過要怎麼避免大量刪除資料的操作...

一個常見的情境是,想要讓某個表格只保留這一個月的資料,所以每個月開頭都會跑一隻 cron job 負責刪掉上個月的資料,像是 DELETE FROM xxx WHERE timestamp < yyy; 這樣的指令。

這個方式無論是在 PostgreSQL 或是 MySQL 都需要很多時間與 I/O 資源,而透過 partition 將不同時間區段切開到不同的表格,再用 TRUNCATE 直接清空表格剛好可以解這樣的問題。

Percona 的文章裡說了一些 PostgreSQL 的歷史與目前的進展。

在 PostgreSQL 9 或更早以前的版本,一個常見的作法是透過 table inheritance 實做 partition,然後用再用 function 實做 INSERT

CREATE TABLE temperature (
  id BIGSERIAL PRIMARY KEY NOT NULL,
  city_id INT NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  temp DECIMAL(5,2) NOT NULL
);

CREATE TABLE temperature_201901 (CHECK (timestamp >= DATE '2019-01-01' AND timestamp <= DATE '2019-01-31')) INHERITS (temperature);
CREATE TABLE temperature_201902 (CHECK (timestamp >= DATE '2019-02-01' AND timestamp <= DATE '2019-02-28')) INHERITS (temperature);
CREATE TABLE temperature_201903 (CHECK (timestamp >= DATE '2019-03-01' AND timestamp <= DATE '2019-03-31')) INHERITS (temperature);

CREATE OR REPLACE FUNCTION temperature_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.timestamp >= DATE '2019-01-01' AND NEW.timestamp <= DATE '2019-01-31' ) THEN INSERT INTO temperature_201901 VALUES (NEW.*);
    ELSIF ( NEW.timestamp >= DATE '2019-02-01' AND NEW.timestamp <= DATE '2019-02-28' ) THEN INSERT INTO temperature_201902 VALUES (NEW.*);
    ELSIF ( NEW.timestamp >= DATE '2019-03-01' AND NEW.timestamp <= DATE '2019-03-31' ) THEN INSERT INTO temperature_201903 VALUES (NEW.*);
    ELSE RAISE EXCEPTION 'Date out of range!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

在 PostgreSQL 10 之後,就直接支援一些與 partition 相關的設計,像是這樣:

CREATE TABLE temperature (
  id BIGSERIAL NOT NULL,
  city_id INT NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  temp DECIMAL(5,2) NOT NULL
) PARTITION BY RANGE (timestamp);

CREATE TABLE temperature_201901 PARTITION OF temperature FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');
CREATE TABLE temperature_201902 PARTITION OF temperature FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');
CREATE TABLE temperature_201903 PARTITION OF temperature FOR VALUES FROM ('2019-03-01') TO ('2019-04-01');

雖然還是有些限制,但可以看出比起以前簡單不少。

而有了 partition 後,文章的後續就在討論這跟 MongoDB 的 sharding 有什麼關係,但這就不是我關注的事情了...

CockroachDB 也拋棄 Open Source License 了

CockroachDB 的主力在於 PostgreSQL 的相容層 (包括底層資料結構,SQL 語法,以及 Protocol,所以原有的 client 不需要太多修改就可以用),並且提供橫向擴充的能力 (實作類似於 F1 與 Spanner 這些論文的功能)。

現在他們也宣佈拋棄 Open Source License 了,從本來的 Apache License 2.0 轉為他們自己定義的 Business Source License:「Why We’re Relicensing CockroachDB」。

最大的差異就是擋提供服務:

The one and only thing that you cannot do is offer a commercial version of CockroachDB as a service without buying a license.

商業版本最終會以 open source license 釋出,但會有三年延遲 (以現在的社群速度,基本上就等於不提供了),不算太意外,但這樣的話也需要先從可用的列表上移除了...

從 Microsoft SQL Server 轉移到 PostgreSQL 的工具

在「How to Migrate from Microsoft SQL Server to PostgreSQL」這邊看到作者的客戶需要把 Microsoft SQL Server 轉移到 PostgreSQL (但沒有提到原因)。

裡面主要是兩個階段的轉換,第一個階段是 schema 的轉換,作者提到了 dalibo/sqlserver2pgsql 這個用 Perl 寫的工具:

Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible http://dalibo.github.io/sqlserver2pgsql

第二個階段是資料的轉換,是選擇用 Pentaho Data Integration 的 Community Edition:

Pentaho offers various stable data-​centric products. Pentaho Data Integration (PDI) is an ETL tool which provides great support for migrating data between different databases without manual intervention. The community edition of PDI is good enough to perform our task here. It needs to establish a connection to both the source and destination databases. Then it will do the rest of work on migrating data from SQL server to Postgres database by executing a PDI job.

所以用兩個工具串起來... 另外在文章裡面沒提到 stored procedure 之類的問題,應該是他們的客戶沒用到或是很少用到?

大家在猜 Amazon DocumentDB 的底層是不是 PostgreSQL...

Amazon DocumentDB 的出現讓人驚訝的倒不是 AWS 推出這塊服務,而是 AWS 對於這類對 PaaS 有攻擊性的 license model 的反擊姿態。這也導致了在 AWS 推出後 MongoDB 的股價掉了 13%。

另外一方面,大家也都想要知道 AWS 怎麼堆底層的系統,畢竟要從頭開發一個所需要的功夫應該不小... (雖然 AWS 應該有這個能力)

從「Is DocumentDB really PostgreSQL?」這邊看到 Hacker News 上的「My bet is that it is built on top of Aurora PostgreSQL.」這篇討論,透過目前 DocumentDB 的限制,大家在猜 Amazon DocumentDB 是不是拿 PostgreSQL 改出來的...

目前看起來 Identifiers 的 63 chars 限制,單一 collection 的 32TB 限制 (對應到表格),以及 UTF-8 null character 限制,都跟 PostgreSQL 一樣。

也許過一陣子 AWS 的人會找個地方透漏,不過目前看起來只能猜...

Mixnode:又一個可以搜尋整個 Web 的服務

看到「Turn the web into a database: An alternative to web crawling/scraping」這篇,在介紹自家 Mixnode 這個產品,看起來是提供 SQL 界面分析整個 Web 的服務...

這類服務最重要的反而不是搜尋界面 (有可以讓程式接的 API 其實就 ok 了),重要的是後面的資料庫有多豐富...

在「用 PublicWWW 分析網站」這邊有提到類似的服務 PublicWWW,而且也一樣有提供 API,先把 Mixnode 丟著記錄起來就好,等有需要的時候再去申請 trial account...

Google 的 Cloud SQL 總算能藏到內部網路了...

Google 總算提供把 Cloud SQL 放進內部網路的選項了,雖然目前還是 beta 版本在測試:「Introducing private networking connection for Cloud SQL」。

透過 VPC peering 把自己的網路與 Cloud SQL 的網路接起來,就不需要有 Public Endpoint,於是就有更低的 latency 與更好的安全性。

Amazon Aurora 支援 Parallel Query 加速

Amazon Aurora 推出了 Parallel Query,可以加速計算速度:「New – Parallel Query for Amazon Aurora」。原理是利用 Aurora 把 storage 層打散的前提,所以有機會透過螞蟻雄兵處理:

官方給的範例可以連到原文去看,可以看到有打開 aurora_pq 與沒打開的效能差異:

15 rows in set (1 min 53.36 sec)
15 rows in set (1 hour 25 min 51.89 sec)

打開後大約是原來的 1/45 時間,提昇超多...

不過還是有些限制,我最在意的就是目前只支援相容於 MySQL 5.6 的版本 (居然不是先支援 5.7):

Engine Support – We are launching with support for MySQL 5.6, and are working on support for MySQL 5.7 and PostgreSQL.

然後沒有多餘費用,只是 i/o cost 可能會增加:

Cost – You can make use of Parallel Query at no extra charge. However, because it makes direct access to storage, there is a possibility that your IO cost will increase.

SQL 的設計與寫作規範

看到「SQL Style Guide」這個網站,把 SQL 常見的行為都列出來,寫了一份規範... 每個團隊未必都要照這個規範走,可以透過他條列的項目思考,再改成自己團隊的規範。

附註一下,最底下有繁體中文的翻譯版本,如果懶的看英文的版本可以看這份:「SQL樣式指南 · SQL Style Guide」。

MySQL 版本的 Amazon Aurora 會將各種記錄丟到 CloudWatch Logs 了...

剛好今天才被問是不是可以在 Amazon Aurora (MySQL-Compatible Edition) 裡面翻出有哪些 Slow Query,剛好想到這幾天發表了這個功能:「Amazon Aurora Publishes General, Slow Query and Error Logs to Amazon CloudWatch」。

You can now configure the MySQL-compatible edition of Amazon Aurora to publish general logs, slow query logs, and error logs to Amazon CloudWatch Logs. Previously, you could only publish audit logs.

看起來是要另外開 (畢竟 CloudWatch Logs 不是免費的 XD),不過以這類型的 log 產生速度與數量來說應該還行...