PostgreSQL unlogged table 特性解析

PostgreSQL 在 9.1 中推出了一种特殊的表：unlogged table，使用 unlogged table 最大的特点是涉及到表的更新，删除等操作不会记录 WAL 日志，这样可以大大的提高性能。不过需要特别注意的是，在数据库异常宕机时，unlogged tables 中的数据可能会丢失。

unlogged table 使用场景

1. 可以接受数据丢失的风险，数据可以从其他源进行导入。
2. unlogged table 通常用于中间结果，频繁变更的会话数据

unlogged table 特性

1. 对 unlogged table 的操作是不记录 WAL 日志的，因此写入速度相较于普通表要快。

2. 如果有 hot standby，备库中只有表结构而没有数据。

3. 当数据库 crash 后，如运行 kill -9 postgresql_pid，数据库重启时自动清空unlogged table的数据。

4. 如果正常关闭数据库，如(pg_ctl stop -D /home/pgsql11.5/data/ -m fast)，再启动时，unlogged table 中是有数据的。

同时，unlogged table 与临时表是有区别的，临时表会在事务/session 结束时进行删除，而 unlogged table 是不会的。

创建表：

rudonx=# CREATE UNLOGGED TABLE test0329(id int);
CREATE TABLE
rudonx=# insert into test0329 values(1);
INSERT 0 1
rudonx=# insert into test0329 values(2);
INSERT 0 1
rudonx=# insert into test0329 values(3);
INSERT 0 1
rudonx=# select * from test0329;
 id 
----
  1
  2
  3
(3 rows)

rudonx=# \d+ test0329
                             Unlogged table "public.test0329"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              | 
 

场景1：重启实例

在控制台上点击重启按钮，然后连接到实例上进行查询，发现表中数据丢失，而表结构是存在的：

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

rudonx=# \d+ test0329
                             Unlogged table "public.test0329"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              | 

rudonx=# select * from test0329;
 id 
----
(0 rows)

场景二：查看备份恢复中是否存在

再次插入数据，并在控制台上做一次完整的备份，等待备份完成之后，使用此备份恢复出一台新的实例并查看数据是否存在，答案是不存在的。

场景三：使用 pg_dump 并查看文件中是否存在

如果我们使用 pg_dump 去备份数据，数据文件中是存在的：

**[postgres@ip-10-0-0-22 ~]$ pg_dump   -h 111.62.xx.xx -U rudonx -p 5432  -d rudonx -t test0329 > backup.sql
Password: 

[postgres@ip-10-0-0-22 ~]$ more backup.sql 
--
-- PostgreSQL database dump
--

-- Dumped from database version 11.14
-- Dumped by pg_dump version 11.5

--
-- Name: test0329; Type: TABLE; Schema: public; Owner: rudonx
--

CREATE UNLOGGED TABLE public.test0329 (
    id integer
);


ALTER TABLE public.test0329 OWNER TO rudonx;

--
-- Data for Name: test0329; Type: TABLE DATA; Schema: public; Owner: rudonx
--

COPY public.test0329 (id) FROM stdin;
3
2
1
\.


--
-- PostgreSQL database dump complete
--

unlogged table 是 PostgreSQL 中比较特殊的一种表，优点是性能相较于普通表更优秀，不过由于有数据丢失的风险，在使用时需要根据实际业务进行选择。

[1] https://www.postgresql.org/docs/current/sql-createtable.html

如果您有其他问题，欢迎您联系火山引擎技术支持服务