Object - 테이블, 테이블파티셔닝, 인덱스

테이블

데이터베이스에서 실제 데이터가 저장되는 논리적 구조

행과 열로 구성되어 있으며, 행은 튜플이라고 합니다.

 

생성구문

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] 
table_name ( [{ column_name data_type [ COMPRESSION compression_method ] 
[ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } 
[ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

 

설명

TEMPORARY / TEMP : 지정된 경우 임시테이블로 생성

UNLOGGED : 지정되면 로깅되지 않는 테이블로 생성

IF NOT EXISTS : 동일한 이름의 테이블이 이미 존재하는 경우 아무것도 하지 않음.

COLLATE : 데이터 정렬을 할당

COMPRESSION : 열에 대한 압축 방법을 설정(pglz 및 lz4 지원)

INHERITS : 상속하는 부모 테이블 지정

PARTITION BY : 파티션 방법 지정(range,list,hash)

LIKE : 모든 열 이름, 데이터 유형 및 제약조건을 자동으로 복사하는 테이블 지정

USUNG : 테이블의 내용을 저장하는데 사용할 테이블 접근방법 지정

WITH : 테이블 또는 인덱스에 대한 선택적 스토리지 매개변수 지정

ON COMMIT : 트랜잭션 블록 끝에서 임시 테이블의 동작은 이를 사용하여 제어 가능(preseve rows, delete rows, drop)

perserve rows : 트랜잭션 종료 시 특별한 조치가 취해지지 않음

delete rows : 임시테이블의 모든 행은 각 트랜잭션 블록이 끝날 때 삭제

drop : 임시테이블은 현재 트랜잭션 블록이 끝나면 삭제

TABLESPACE : 테이블스페이스 이름 지정

 

생성 예제

 

postgres=# create table my_table (col1 text,col2 integer);
CREATE TABLE

********* QUERY **********
SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhrelid AND i.inhparent = '16416'
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
**************************

 col1   | text    |           |          |
 col2   | integer |           |          |
 
postgres=# create table my_table2 (
postgres(# id char(5) constraint firstkey primary key,
postgres(# col1 varchar(40) not null,
postgres(# col2 integer
postgres(# );

\d my_table2
                    Table "public.my_table2"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | character(5)          |           | not null |
 col1   | character varying(40) |           | not null |
 col2   | integer               |           |          |
Indexes:
    "firstkey" PRIMARY KEY, btree (id)
    

postgres=# create table distributors (
postgres(# did integer primary key generated by default as identity,
postgres(# name varchar(40) not null check(name <> '')
postgres(# );
CREATE TABLE

\d distributors
                               Table "public.distributors"
 Column |         Type          | Collation | Nullable |             Default

--------+-----------------------+-----------+----------+---------------------
-------------
 did    | integer               |           | not null | generated by default
 as identity
 name   | character varying(40) |           | not null |
Indexes:
    "distributors_pkey" PRIMARY KEY, btree (did)
Check constraints:
    "distributors_name_check" CHECK (name::text <> ''::text)

 

생성시 generated by default as identity 옵션 사용시 자동으로 값이 부여됨.

 

데이터가 위반이어서 안들어 갈 시 실패하고 값이 자동으로 1이 부여된것을 볼 수 있습니다.

postgres=# insert into distributors(name) values('');
ERROR:  new row for relation "distributors" violates check constraint "distributors_name_check"
DETAIL:  Failing row contains (1, ).

 

다시 값을 제대로 입력하면 did에 2가 입력되어 있습니다.

postgres=# insert into distributors(name) values ('1');
INSERT 0 1
postgres=# select * from distributors;
 did | name
-----+------
   2 | 1
(1 row)

 

테이블 변경 구문

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]

 

설명

action : ADD,DROP,Alter column 으로 컬렴명,데이터 타입 변경 가능

RENAME : 컬럼,제약조건, 테이블 본인 이름 변경

SET : 스키마,테이블스페이트 변경 가능

 

예제

postgres=# alter table distributors add column address varchar(30)
postgres-# ;
ALTER TABLE

                                Table "public.distributors"
 Column  |         Type          | Collation | Nullable |             Default
---------+-----------------------+-----------+----------+----------------------------------
 did     | integer               |           | not null | generated by default as identity
 name    | character varying(40) |           | not null |
 address | character varying(30) |           |          |
Indexes:
    "distributors_pkey" PRIMARY KEY, btree (did)
Check constraints:
    "distributors_name_check" CHECK (name::text <> ''::text)
    

postgres=# alter table distributors rename to people;
ALTER TABLE

                                   Table "public.people"
 Column  |         Type          | Collation | Nullable |             Default
---------+-----------------------+-----------+----------+----------------------------------
 did     | integer               |           | not null | generated by default as identity
 name    | character varying(40) |           | not null |
 address | character varying(30) |           |          |
Indexes:
    "distributors_pkey" PRIMARY KEY, btree (did)
Check constraints:
    "distributors_name_check" CHECK (name::text <> ''::text)

 

삭제구문

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

 

설명

IF EXISTS : 존재하지 않을 경우 오류 발생 안함

name : 테이블이름

CASCADE : 테이블에 의존하는 오브젝트를 함께 삭제

RESTRICT : 기본값으로 의존된 오브젝트가 있는 경우 테이블 삭제를 거부

 

예제

postgres=# drop table people cascade;
DROP TABLE

 

 

테이블 파티셔닝

생성 예시

name 을 기준으로 리스트 파티셔닝 테이블 생성. 오라클과 다르게 따로 파티션 테이블을 생성해서 attach 해줘야 함.

postgres=# create table cities (
postgres(# city_id integer not null,
postgres(# name text not null,
postgres(# col1 integer
postgres(# ) partition by list (name);
CREATE TABLE

postgres=# create table cities_korea partition of cities for values in ('korea');
CREATE TABLE
postgres=# create table cities_japan partition of cities for values in ('japan');
CREATE TABLE
postgres=# create table cities_china partition of cities for values in ('china');
CREATE TABLE

 

같은 name value 값으로 생성시도시 에러 발생

postgres=# create table cities_eu partition of cities for values in ('china');
ERROR:  partition "cities_eu" would overlap partition "cities_china"
LINE 1: ...table cities_eu partition of cities for values in ('china');

 

값입력 후 테이블 데이터 확인

postgres=# insert into cities values
postgres-# (1,'korea'),
postgres-# (2,'japan'),
postgres-# (3,'china');
INSERT 0 3

postgres=# select * from cities;
 city_id | name  | col1
---------+-------+------
       3 | china |
       2 | japan |
       1 | korea |
(3 rows)

postgres=# select * from cities_korea;
 city_id | name  | col1
---------+-------+------
       1 | korea |
(1 row)

postgres=# select * from cities_japan;
 city_id | name  | col1
---------+-------+------
       2 | japan |
(1 row)

postgres=# select * from cities_china;
 city_id | name  | col1
---------+-------+------
       3 | china |
(1 row)

 

파티셔닝 테이블 삭제

detach를 수행하면 자동으로 원본 테이블에서 데이터가 사라집니다.

postgres=# alter table cities detach partition cities_china;
ALTER TABLE

postgres=# select * from cities;
 city_id | name  | col1
---------+-------+------
       2 | japan |
       1 | korea |
(2 rows)

postgres=# drop table cities_china;
DROP TABLE

postgres=# select * from cities;
 city_id | name  | col1
---------+-------+------
       2 | japan |
       1 | ko

 

테이블 파티셔닝 변경

eu라는 새로운 파티셔닝 테이블을 추가합니다

postgres=# create table cities_eu partition of cities for values in ('eu');
CREATE TABLE

postgres=# insert into cities values (99,'eu');
INSERT 0 1

 

list 값에 없는 값 insert 시도시 에러발생합니다.

postgres=# insert into cities values (99,'omg');
ERROR:  no partition of relation "cities" found for row
DETAIL:  Partition key of the failing row contains (name) = (omg).

 

postgres=# select * from cities_eu;
 city_id | name | col1
---------+------+------
      99 | eu   |
(1 row)

postgres=# select * from cities;
 city_id | name  | col1
---------+-------+------
      99 | eu    |
       2 | japan |
       1 | korea |
(3 rows)

 

인덱스

테이블에 대한 검색속도를 높이기 위한 오브젝트.

종류는 아래와 같습니다.

B-tree : 기본적으로 생성되는 인덱스. tree 구조

Hash : Hash 테이블 형태의 key,value를 가지는 자료 구조 형태로 되어 있음. equal 조건 속도 빠름. 범위 조회 작업은 느림.

GIST : 지리적 데이터를 조회하는데 유용한 인덱스. 지리적 데이터를 2차원 좌표로 나누어 DB에 저장하고 인덱스 생성

SP-GIST : GIST로 분류한 공간을 또 공간단위로 나누어 관리하는 개념. 지속적으로 분할된 공간안에서 데이터 분포에 따라 인덱스 구조를 만듬

GIN : Full Text 조회에 두각을 나타내는 인덱스. B-tree와 유사하나 페이지 저장구조가 다름.

BRIN :개별의 데이터를 추출하여 키워드로 쓰지않고 단위 블록들을 속해있는 데이터들의 최소값과 최대값만 추출하여 키워드로 저장

 

생성예제

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] 
table_name [ USING method ] ( { column_name | ( expression ) } [ COLLATE collation ] 
[ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

 

설명

UNIQUE : 중복값 없이 생성하는 인덱스

CONCURRENTLY : 삽입,업데이트 또는 삭제를 방지하는 LOCK을 사용하지 않고 인덱스 구축

INCLUDE : 키가 아닌 열로 인덱스에 포함될 목록을 지정

ONLY : 파티션 테이블에서 인덱스 생성을 재귀적으로 수행하지 않음

ASC/DESC : 오름차순/내림차순 정렬

NULL FIRST/LAST : null이 먼저,나중에 정렬되도록 지정

 

생성 예제

postgres=# create index my3_idx on my_table3(name);


                            Table "public.my_table3"
  Column  |  Type   | Collation | Nullable |                Default

----------+---------+-----------+----------+----------------------------------
-----
 id       | integer |           | not null | nextval('my_table3_id_seq'::regcl
ass)
 name     | text    |           |          |
 location | text    |           |          |
Indexes:
    "my3_idx" btree (name)
Tablespace: "new_dbspace"

 

변경구문

인덱스 이름이나 테이블스페이스를 옮길 수 있습니다.

ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name

 

예제

postgres=# alter index my3_idx rename to my3_idx2;

 

삭제구문

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT

설명

CONCURRENTLY : 삭제시 테이블을 잠그지 않고 인덱스를 삭제

CASCADE : 인덱스에 의존하는 오브젝트 함께 삭제

RESTRICT : 기본값으로 의존된 오브젝트가 있는 경우 삭제 거부

 

예제

postgres=# drop index my3_idx2 cascade;