一般來說,現在為了各司其職,都把硬碟做成了san,raid,至少lvm。但是仍然有一些使用者希望可以指定表空間和索引。在lightdb裡面也支援該特性。如下:
lightdb@postgres=# create tablespace tab_tbs location '/data1/cn/tab_tbs';
WARNING: tablespace location should not be inside the data directory
ERROR: directory "/data1/cn/tab_tbs" does not exist
lightdb@postgres=# create tablespace tab_tbs location '/data1/cn/';
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
lightdb@postgres=# create table tab_for_tbs(id int) tablespace tab_tbs;
CREATE TABLE
lightdb@postgres=# \dS+ tab_for_tbs
Table "public.tab_for_tbs"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
Tablespace: "tab_tbs"
Access method: heap
這樣就在tab_tbs下建立了表。
索引也是一樣的,如下:
lightdb@postgres=# create index idx_tab_for_tbs on tab_for_tbs(id) tablespace tab_tbs;
CREATE INDEX
需要注意的是,在lightdb-x分布式版本中,表空間屬性不支援透傳到DN節點。索引不能聲明tablespace xxx子句,如下:
lightdb@postgres=# \dS+ tab_for_tbs_103402
Table "public.tab_for_tbs_103402"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
Access method: heap
lightdb@postgres=# create index idx_tab_for_tbs on tab_for_tbs(id) tablespace tab_tbs;
ERROR: specifying tablespaces with CREATE INDEX statements is
注:雖然如此,一般來說,我們是強烈建議資料檔案,WAL日志,臨時檔案目錄分開的。