数据库表空间允许数据库管理员定义存储数据库对象的文件在文件系统中的位置。一旦创建了表空间,当创建数据库时就可以引用这个表空间。

通过表空间来控制数据库的磁盘存储位置有一下好处:

  1. 如果数据库初始安装所在的分区或卷耗尽了空间,并且已经无法扩展,可以在另外的分区上面创建和使用一个新的表空间,直到系统重新被配置。
  2. 表空间可以允许管理员根据已知的数据库对象使用模式优化系统性能。比如,将频繁使用的index放在快速高可用的固态硬盘上;将存储很少使用或者对性能要求不高的数据放在廉价低速的磁盘上。

下面以PostgresSQL为例讲一下表空间的使用。

创建表空间

1
CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION 'directory';

参数

  • tablespacename 表空间的名字,不能以pg开头,这是为系统表空间保留的。
  • user_name 表空间所有者的名字,如果省略,缺省为执行命令的用户。只有数据库超级用户才可以创建表空间
  • directory 表空间使用的路径,目录必须是空的,并且owner为PostgreSQL操作系统用户。通常情况PostgreSQL默认安装的系统用户为postgres。目录必须是一个绝对路径。

注意

  • 只有支持符号链接的系统才能创建表空间
  • CREATE TABLESPACE不能在一个事务块中执行

示例

1
CREATE TABLESPACE ts_mydbspace OWNER testuser LOCATION '/var/data/postgres';

使用表空间

只有数据库超级用户才可以创建表空间,但是创建之后,普通的数据库用户就可以使用它了,只要用户有相应的CREATE权限。比如:

1
CREATE DATABASE testdb TABLESPACE ts_mydbspace;

设置缺省的表空间参数

1
2
SET default_tablespace = ts_mydbspace;
CREATE DATABASE testdb;

数据库创建时指定的表空间为其默认表空间,如果没有指定,则其默认表空间与生成该数据库的模板数据库的默认表空间是同一个。

查看表空间

当PostgeSQL初始化时,自动创建两个表空间。pg_global用于存储共享的系统目录信息。pg_default是模板数据库template0和template1默认的数据库表空间,也是其他数据库默认的表空间。

也就是说在执行initdb时,pg的后端(无需经过SQL查询编译)以bootstrap模式执行,在bootstrap模式下,从零开始创建数据库模板template1、创建系统视图、系统表、template0和postgres数据库。template0和postgres数据库都是从template1创建的,这3个都是pg的系统数据库,其中template1在initdb完成之后是可以由用户修改的,template0则始终提供一个未被修改的干净模板。postgres数据库提供一个初始的可供用户连接的数据库。

由于CREATE DATABASE dbname语句并没有指明数据库模板,所以系统将默认克隆template1数据库,得到新的数据库dbname。(By default, the new database will be created by cloning the standard system database template1).而template1数据库的默认表空间是pg_default,这个表空间是在数据库初始化时创建的,所以所有template1中的对象将被同步克隆到新的数据库中,新的数据库的表空间也是pg_default。相对完整的语法如下:

1
CREATE DATABASE dbname OWNER username TEMPLATE template1 TABLESPACE tablespacename;

查看表空间:

1
2
3
4
5
6
postgres=# select oid, spcname from pg_tablespace;
oid | spcname
------+------------
1663 | pg_default
1664 | pg_global
(2 rows)

或者

1
2
3
4
5
6
7
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)

PG数据库的文件目录

PGDATA下的目录,最基本的是base和global。base中放了每个数据的文件,每个数据库会在base目录下有一个以该数据库oid(object id,pg具有面向对象数据库的一些特性,其中的数据库、视图、数据表,甚至某些元组都被作为数据对象管理,每个数据对象有一个唯一oid)命名的子目录。其中命名为1的子目录是pg中的模板数据库template1,pg中的数据库都会以template1作为模板,也就是说template1中的内容会被复制到所有的数据库中。global目录下存放了该实例的共享系统表,如pg_database(存放该实例中所有数据库的元信息)。

可以通过 查看 pg_database 这张表查看每一个 数据库的 oid

1
2
3
4
5
6
7
8
postgres=# select oid , datname from pg_database;
oid | datname
-------+-----------
1 | template1
12067 | template0
12072 | postgres
16385 | testdb
(4 rows)

每一张表的数据(大部分)又是放在 base/(dboid)/(relfilenode) 这个文件里面,连接testdb数据库:\c testdb,查看该数据库表信息:

1
2
3
4
5
6
7
testdb=# select relname, relowner, relfilenode from pg_class where relowner = 16384;
relname | relowner | relfilenode
--------------------+----------+-------------
book | 16384 | 16402
book_pkey | 16384 | 16405
hibernate_sequence | 16384 | 16407
(3 rows)

其中16384是数据库用户的oid。由此可见book这张表的数据存放在base/16385/16402文件中。也可以用 pg_relation_filepath 这个函数查询:

1
2
3
4
5
testdb=# select pg_relation_filepath('book');
pg_relation_filepath
----------------------
base/16385/16402
(1 row)

数据库中的每张数据表的索引和数据都存放同一个文件中。每张表除了存放数据和索引的文件外,还会有一个_fsm文件(free space map),其中存放了数据表文件中空闲空间的信息,还有一个_vm文件(visibility map),标记了数据表文件中哪些文件块没有失效的元组。

对于数据表文件,pg采取段页式管理方式,每个段的默认大小是1GB。每个段都是一个文件,也就是说,默认配置下,pg中的一个数据表超过1GB时会分为多个1GB大小的文件,第一个段文件名依然是filenode number,第二个段文件是filenode number.1,依此类推。如 24589 24589.1 24589.2。

对于数据表中的大对象,pg会将其存放在另外的一个TOAST表中,因为大对象不适合与其他属性一起按行存放在段页中。