"PostgreSQL" Table space

In PostgreSQL, the tablespace is actually specifying a storage directory for the table, which allows us to put different tables on different storage media or file systems. You can specify a tablespace when you create a database, table, or index.

1. Create a table space

-- The tablespace directory must be a directory that already exists in the system Test=Create'/opt/postgresql/data/pg_data' ; CREATE Tablespace

2. Create a database, specify a table space

Test=Createdatabase  test01 tablespace tb_01; CREATE DATABASE

3. Modify the tablespace of the database

Test=alterdatabaseset  tablespace tb_02; ALTER DATABASE
--After you modify the default tablespace for a database, the table space for the tables in the database does not change.

4. When you build a table, specify the table space

Test=createtableinttext) tablespace tb_01; CREATE TABLE

5. When creating an index, specify the tablespace

Test=Createindex on T1 (id) tablespace tb_02; CREATE INDEX

6. When adding constraints, specify the table space

Test=#Alter TableT1Add constraintunique_t1_idUnique(ID) usingIndextablespace tb_02;ALTER TABLETest=#Alter TableT1Add constraintpk_t1_idPrimary Key(ID) usingIndextablespace tb_02;ALTER TABLE

7. Move the table to a new table space

Test=altertableset  tablespace tb_02; ALTER TABLE -- The table is locked during the move and all operations are blocked, including select, so choose the appropriate time to move the table. 

The end!

2017-08-20

PostgreSQL Table Space