PostgreSQL의 pg_hba.conf 파일을 통해 외부접근에 대한 처리는 되도록 배재하는것이 좋습니다. 외부접근제어는 시스템 OS Kernel Level의 Packet Filter( iptable, ipchain...)를 이용하는 것이 PostgreSQL로의 불법로그인으로 인한PostgreSQL 인증체크처리 부하라던지 전체적인 성능에 이득이 됩니다. 즉, Kernel단에서 해당 IP나 IP Block에 대해서 접근제어를 해주는 것이 전체적인 성능에 좋습니다.

pg_hba.conf는 PostgreSQL의 인증시스템관련 정보를 담고 있는 설정파일로 설정방법이 간단한편으로 실제적인 계정에 대한 정보는PostgreSQL의 카탈로그 테이블인 pg_user에서 관리하고 접근 Host Host의 데이터 전송방식과 암호화 전송방식에 대한 설정을 가지고 있습니다그런 이유로 계정에 대한 권한이나 패스워드등의 변경은 실시간으로 적용이 가능하나 접근자의 접근방식과 암호전달 방식에 대해서는 pg_ctl reload나 pg_ctl restart를 통해 daemon이 다시 pg_hba.conf파일을 로드하게 해주어야 합니다불편성이 있는듯 하지만,불법접근에 대한 처리시에 빠른 응답으로 Postgres Server부담을 줄이기 위함으로 최대한의 성능을 위한 것입니다.


pg_hba.conf 내용

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     enterprisedb        127.0.0.1/32            md5
#host    replication     enterprisedb        ::1/128                 md5


  1. Host Type
    1. 접근자의 접근위치와 통신의 암호화 관련 설정
    2. local, host, hostssl, hostnossl 지원
    3. local은 Unix Domain Socket을 통한 접속에 해당
    4. hostssl은 ssl인증서를 통한 암호화 통신만 지원하고 localhost, 127.0.0.1과 같이 TCP/IP접속에 해당
    5. hostnossl은 ssl접속 불가능, TCP/IP통신 지원
    6. host나 hostssl로 설정한 상태에서 SSL기능을 사용하시려면 Postgres컴파일시 --with-openssl옵션을 주어야 하며, postgresql.conf ssl=true로 설정을 해주셔야만 합니다.
  2. Database Name
    1. 특정 디비에 대한 접속을 제한할 수 있고 ,를 사용하여 여러 개의 디비 접근 제어 가능
    2. 모든 디비에 대한 접근을 풀려면 all을 적어두면 됨
    3. 만약 설정할 디비가 수십 개라면 @test.txt와 같이 설정한 후 test.txt을 PGDATA로 설정한 폴더의 안에 넣어두면 됨
  3. User Name
    1. 계정설정으로 ,로 구분할 수 있음
    2. @파일명 과 같이 따로 파일을 만들어서 처리 가능
    3. PostgreSQL의 계정 그룹 카탈로그 테이블인 pg_group 또는 create_group 명령으로 그룹을 만들어 계정들은 하위(SYSID)에 묶어두었을때는 +(플러스키를 붙인 그룹명으로 설정하면 해당 그룹에 대한 모든 접근이 가능
  4. Address
    1. IPv4 CIDR구분으로 해당 C Class에 대해 모두 접근처리를 할 경우는 : xxx.xxx.xxx.0/24
    2. 해당 IP에 대한 접근처리를 할 경우는 : xxx.xxx.xxx.xxx/32
  5.  Method
    1. 실제 계정의 패스워드에 대해 서버로 전송을 어떻게 할 것인가를 정함
    2. trust : 패스워드 없이 접근 가능
    3. reject : 거부
    4. md5 : 패스워드를 md5로 암호화 전송
    5. crypt: crypt로 암호화해서 전송
    6. password: text로 패스워드를 전송
    7. krb4, krb5 :kerberOS V4, 5지원
    8. ident:  접속 클라이언트 유저 이름을 확인
    9. pam : 서비스인증

예시

로컬 시스템상의 모든 유저가 임의의 데이터베이스에 임의의 데이터베이스 유저명으로 Unix 도메인 소켓을 사용해 접속하는 것을 허가 (로컬 접속에서는 디폴트). 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 
local all all trust


IP주소 192.168. 93. x를 가지는 모든 호스트의 모든 유저가, ident가 그 접속에 대해 보고하는 것과 같은 유저명(전형적으로는 Unix 유저명)으로 데이터베이스 "postgres"에 접속하는 것을 허가. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 
host postgres all 192.168.93.0/24 ident sameuser


'DB > PostgreSQL' 카테고리의 다른 글

[PostgreSQL] first(), last()  (0) 2016.11.16
[PostgreSQL] ALTER  (1) 2016.11.03
[PostgreSQL] pg_hba.conf 설명  (0) 2016.10.28
[PostgreSQL] 대량의 Insert를 가장 빠르게 하는 방법  (0) 2016.10.24
[PostgreSQL] COPY  (0) 2016.10.21
[PostgreSQL]postgres_fdw  (0) 2016.09.27

데이터베이스에 연결해서 어떤 결과를 처리하고자 할때 데이터베이스의 쿼리상에서 처리하는 것이 나을지, 코드상에서 처리하는 것이 나을지에 대한 이슈를 정리해보고자 합니다.

SQL VS CODE

데이터베이스와의 통신을 최소화하는 것이 좋습니다.

  • 코드에서 대부분의 작업을 진행한 다음에만 데이터베이스와 통신을 하는 것이 좋습니다. 그렇지 않으면 현재 진행하고 있는 작업에 대한 많은 자원이 필요하게 됩니다.

쿼리의 복잡성을 최소화해야 합니다.

  • 데이터베이스와의 통신을 최소화하더라도, 이것은 지나치게 복잡하고 거대한 쿼리를 줄이는 작업을 의미하는 것이 아닙니다. 쿼리는 여전히 관리해야할 필요성이 있습니다. 만약 두 개의 단순한 쿼리가 하나의 거대한 쿼리를 개발하고 유지하는 골치를 덜 수있다면, 두 개의 단순한 쿼리를 거대한 한 개의 쿼리를 사용하는 것보다 좋습니다.

code상의 list 반복문을 최소화해야 합니다.

  • 코드는 아주 좋습니다. 비지니스 로직을 넣기에 아주 좋은 장소이고 깔끔한 라이브러리들 또한 아주 많이 존재합니다. 이와 같은 이유로 코드는 굉장하지만 코드를 사용하는 것이 쿼리상에서 join이나 기타 다른 statement를 제거된 단순한 결과를 가지고 코드 상에서 반복문을 많이 사용한 작업을 의미한다면, 위와 반대로 코드를 최소화하고 쿼리를 향상시키는 것이 좋습니다.

SQL CASE STATEMENT VS CODE IF STATEMENT

흔히 거대한 쿼리의 SELECT절에 CASE문이 많아지면 이를 쿼리로 처리할지, CODE상에서 IF조건문으로 처리할지 햇갈립니다.


만약 CASE문이 단지 query의 최종 출력을 변환하기 위해 사용되며, (만약 UI가 존재해서 코드상에서도 변경해도 결과가 같다고 하면), 데이터베이스 쿼리 / 프로시저가 처리하는 것을 코드에서 담당하는 것이 낫습니다. 여기서 명심해야 할 점은 성능 이슈보다 분리를 통해 문제가 발생할 수도 있습니다.

아래와 같은 쿼리가 있습니다.

SELECT InvoiceID, InvoiceDate,
    CASE WHEN PaidStatus = 0 THEN 'Unpaid' ELSE 'Paid' END
FROM ...

이러한 쿼리는 UI 또는 레이어의 어디서든지 해당 값을 변경할 수 있습니다. 즉 쿼리자체에서 이러한 로직은 필요가 없습니다.


반면 아래와 같은 CASE문은 꼭 필요한 경우입니다.

SELECT
    SUM(CASE WHEN PaidStatus = 0 THEN Amount ELSE 0 END) AS TotalUnpaid,
    SUM(CASE WHEN PaidStatus = 1 THEN Amount ELSE 0 END) AS TotalPaid
FROM ...

이러한 쿼리는 데이터베이스에서 처리하는 것이 더 낫습니다. 위의 CASE문은 의미가 존재하는 쿼리의 일부입니다. 이러한 논리를 괜히 코드에서 처리하도록 옮기는 것을 불필요합니다.


SQL에서 처리할 지 CODE에서 처리할 지 결정은 아래와 같이 생각하며 진행해야 합니다.

  1. 해당 논리가 달성하고자 하는 것이 어디에 속해있는지를 파악해야 합니다.
  2. 실제로 상당한 성능 문제를 알아 차리는 경우, 성능 문제만 논의를 통해야 합니다.

결론

일반적으로 이러한 것들은 case by case입니다. 수명이 긴 어플리케이션의 경우 유지보수는 거대한 비용이여서 단순함을 추구하고자 위에서 설명한 내용들을 따를지도 모릅니다.

'DB' 카테고리의 다른 글

[DB] DDL, DML, DCL 이란?  (1) 2016.11.18
[SQL] SELECT 결과 UPDATE  (0) 2016.11.18
[SQL] SQL VS CODE  (0) 2016.10.27
[SQL] Union과 Union all 차이  (0) 2016.10.12
[SQL] Joins  (0) 2016.08.24
[DB] DB Index 란?  (2) 2016.07.06

데이터를 insert한다고 할 때, 단순한 insert의 경우는 속도를 고려해야할 필요가 거의 없습니다. 하지만 10만, 100만, 혹은 억단위의 데이터를 postgresql에 insert한다고 하면 퍼포먼스를 고려해야 되는 이슈가 생깁니다.

이러한 해당 이슈에 대한 많은 해결책이 있습니다.

이상적인 해결책은 인덱스없이 로깅되지 않은 테이블을 import한 후 이 로깅을 변경하고 인덱스를 추가하는 것입니다. 아쉽게도 PostgreSQL의 9.4에서 로깅되지 않은 테이블을 로깅으로 변경하는 것을 지원하지 않습니다. 9.5  버전에서는 ALTER TABLE ... SET LOGGED와 같은 명령어를 추가해 사용자가 변경할 수 있도록 추가하였습니다.

PostgreSQL Tuning Point


만약 사용자가 bulk import를 위해 데이터베이스를 오프라인으로 할 수있는 경우엔, pg_bulkload를 사용하면 됩니다.


그렇지 않으면 아래와 같이 따라합니다.


테이블에 있는 트리거를 사용하지 않도록 설정합니다.

import가 시작하기 전에 걸려있는 인덱스들을 지웁니다. insert가 완료가 된 다음 인덱스들을 재생성합니다.(인덱스를 한번에 구축하는 것이 데이터와 함께 인덱스를 동시에 구축하는 것 보다 시간이 덜 걸리고, 인덱스를 다시 생성하는 것이 더 컴팩트합니다.)

만약 단일 트랜잭션내에서 import를 실행하면, 외래키 제약을 삭제하고 import한 다음 commit하기 전에 재생성하는 것이 안전합니다.

잘못된 데이터를 소개하는 것처럼 import가 여러 트랜잭션에 걸쳐 분할되는 경우, 이 작업을 수행하지 않는 것이 좋습니다.

가능하면 INSERT문들(단일 insert와 bulk insert 등?) 대신 COPY문 사용이 좋습니다.

만약 COPY문을 사용할 수 없을 경우, multi-valued INSERT문을 사용하는 것이 좋습니다.

단일 VALUES에 너무 많은 값을 나열하면 안됩니다. (이미 알겠지만)

이러한 값들은 몇 번 이상 메모리에 맞춰야 하고 하나의 statement 당 수 백을 보관해야 합니다.(those values have to fit in memory a couple of times over, so keep it to a few hundred per statement.)

fsync()비용을 줄이기 위해 synchronous_commit=off와 commit_delay을 사용하는 것이 좋습니다. 이 옵션들은 사용자가 거대한 트랜잭션을 처리하는 경우에는 그다지 도움이 되지 않습니다.

여러 연결을 통해 INSERT 또는 COPY를 병렬적으로 처리합니다. 이 처리는 사용자의 하드웨어 디스크 서브시스템에 따라 달라집니다: 직접 연결된 스토리지를 사용하는 경우 실제 하드 드라이브 당 하나의 연결을 사용할 수 있습니다.

checkpoint_segments 값을 높게 설정하고 log_checkpoints 값을 사용가능하게 설정합니다. PostgreSQL의 로그를 보고 너무 자주 발생하는 checkpoint에 대한 에러처리가 아니라는 것을 확신해야 합니다.

만약 import하는 동안 시스템이 충돌난다면 치명적인 손상으로 전체 PostgreSQL의 클러스터를 잃는 것에 대해 신경쓰지 않아도 됩니다. 사용자는 fsync=off를 사용해 PostgreSQL을 중단할 수 있고 import를 시작할 수 있습니다. import가 시작된다면 PostgreSQL를 중지하고 다시 fsync=on을 설정할 수 있습니다. 만약 PostgreSQL내에 중요한 데이터가 이미 있는 경우, 해당 작업을 사용하지 않는 것이 좋습니다. fsync=off를 설정하면 full_page_writes=off을 사용자가 설정할 수 있습니다. 데이터 손실과 데이터베이스 붕괴를 예방하기 위해 import 이후 처리하는 것이 좋습니다.

System Tuning Point


SSD를 사용하면 좋습니다. 전력 보호, write-back cach, 신뢰성이 있는 좋은 SSD는 겁나 빠른 속도를 만들어줍니다. disk flush / fsync()들의 수를 줄이는 것과 같은 위의 조언을 따를 때 효율이 적어보이지만 큰 도움이 됩니다. 만약 데이터의 보존을 생각한다면 전원 장애를 보호해주는 기능이 없는 값싼 SSD를 사용하지 마세요.

사용자가 스토리지에 직접 연결을 위해 RAID 5또는 RAID 6을 사용한다면, 즉시 중단해야 합니다. 데이터를 백업하여 RAID 어레이를 RAID 10으로 재구성한 다음, 다시 시도합니다. RAID 5와 6은 대량의 쓰기 퍼포먼스는 절막적입니다. -  물론 큰 캐시를 가진 좋은 Raid 컨트롤러는 도움이 될 수 있긴 합니다.

사용자가 거대한 배터리 백업을 위한 다시 쓰기 캐시(write-back cache)를 가진 하드웨어인 RAID 컨트롤러를 사용한다면 수 많은 commit 작업량에 대한 쓰기 퍼포먼스를 향상시킬 수 있습니다. 사용자가 bulk loading하는 동안 적은 수의 거대한 트랜잭션을 처리를 한다면 commit_delay를 가진 async commit을 사용해도 많은 도음이 되지 않습니다.

결론


속도를 올릴수 있는 단순한 방법은 인덱스와 트리거를 지우고 insert문이 아닌 copy문을 사용해야 할 것 같음. (간단한 테스트나 고려해야되는 사항이 적을 시)

실제 서비스를 접목하고자 할때는 하드웨어 튜닝까지 처리


참조문서: 

http://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql

https://www.postgresql.org/docs/current/static/populate.html

'DB > PostgreSQL' 카테고리의 다른 글

[PostgreSQL] ALTER  (1) 2016.11.03
[PostgreSQL] pg_hba.conf 설명  (0) 2016.10.28
[PostgreSQL] 대량의 Insert를 가장 빠르게 하는 방법  (0) 2016.10.24
[PostgreSQL] COPY  (0) 2016.10.21
[PostgreSQL]postgres_fdw  (0) 2016.09.27
[PostgreSQL] SELECT 결과 INSERT하기  (0) 2016.07.29
COPY -- copy data between a file and a table
copy란 말 그대로 파일과테이블 사이 간 데이터를 복사를 해주는 function입니다.

시놉시스

COPY table_name [ ( column [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column [, ...] ) ] | ( query ) }
    TO { 'filename' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

where option can be one of:

    FORMAT format_name
    OIDS [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column [, ...] ) | * }
    FORCE_NOT_NULL ( column [, ...] )
    ENCODING 'encoding_name'

설명

COPY는 PostgreSQL의 테이블과 표준 파일 시스템 파일 간에 데이터를 이동을 도와줍니다. COPY TO는 테이블의 내용을 복사해 테이블에 전달 하는 함수입니다. COPY FROM은 파일 내용을 복사해 테이블에 전달합니다. (테이블이 이미 데이터가 존재하면 데이터를 덧붙입니다.) 또한 COPY는 SELECT 쿼리의 결과를 복사 할 수 있습니다. 만약 컬럼의 리스트를 구체화한다면, COPY는 파일에서 또는 파일로부터 구체화된 컬럼의 데이터만 복사합니다. 만약 컬럼 리스트에 없는 테이블의 컬럼이 존재할 경우, COPY FROM은 해당 컬럼을 위해 기본값을 insert합니다. 파일 이름을 가진 COPY는 PostgreSQL서버에 직접적으로 파일을 읽거나 쓰는 것을 지시합니다. 이 파일은 서버에 액세스 할 수 있어야하고 이름은 서버의 관점에서 지정해야합니다. STDIN 또는 STDOUT이 구체화된다면, 데이터는 클라이언트와 서버 사이의 연결을 통해 전송됩니다.

파라미터

table_name

존재하는 테이블 이름

column

복사할 컬럼의 리스트 만약 컬럼리스트를 구체화한 것이 없으면 테이블의 모든 컬럼을 복사

query

SELECT 또는 VALUES 명령어의 결과를 복사할 수 있습니다. 이 경우 쿼리를 괄호로 감싸야 합니다.

filename

인풋 또는 아웃풋 파일의 절대경로. 윈도우 유저는 ''문자열을 사용해야 하며 파일 경로이름에 \\ (더블 백슬래시)를 사용해야합니다.

STDIN

클라이언트 어플리케이션으로부터 인풋을 구체화 받습니다. 

STDOUT

클라이언트 어플리케이션에 아웃풋을 구체화 합니다.

boolean

선택된 옵션을 킬지 끌지를 구체화합니다. 만약 TRUE라면 on 또는 1이며 옵션을 사용하겠다는 의미입니다. 반대로 FALSE라면 off 또는 0이며 사용하지 않겠다는 것입니다. 이 파라미터는 생략할 수 있으며, 생략할 시 TRUE가 기본값입니다.

FORMAT

어떤 포맷으로 읽을지 또는 쓸지에 대해 선택을 합니다.  text, csv (콤마로 값을 구분), binary. 기본은 text

OIDS

각 row를 위한 OID 복사를 구체화합니다. (만약 OIDs를 가지고 있지 않은 테이블을 구체화 하거나 copy하고자 query를 만들면 에러가 발생합니다.)

DELIMITER

파일의 각 row (line) 안에 컬럼들을 분리하는 문자를 나타냅니다. 기본값은 text 포맷에서 tab 문자이고 csv 포맷에서는 콤마입니다. 이 delimiter는 1바이트 문자열만 가능합니다. binary 포맷에서는 이 옵션을 허용하지 않습니다.

NULL

null값인 문자열을 구체화합니다. 기본값은 텍스트 포맷에서 \N(백슬래시 N), csv 포맷에서 인용되지 않은 문자열 (unquoted empty string)입니다. text 포맷에서 텅빈 문자열과 null값을 구분하는 것을 원하지 않아 텅 빈 문자열을 선호할 수있습니다. binary 포맷은 사용할 수 없습니다.

HEADER

파일안에 각 컬럼의 이름을 가지는 헤더 라인을 포함하도록 구체화합니다. 아웃풋에서 첫 라인은 테이블로부터의 컬럼이름, input을 포함하고 첫 라인은 무시됩니다. 이 옵션은 csv 포맷에서만 허용됩니다.

QUOTE

데이터 값이 인용될 때 인용 문자(quoting character)가 사용되도록 지정합니다. 기본값은 큰 따옴표(double-quote)입니다. 이것은 반드시 1바이트 문자열이여야 합니다. 이 옵션은 csv 포맷일떄만 허용됩니다.

ESCAPE

Specifies the character that should appear before a data character that matches the QUOTE value. The default is the same as the QUOTE value (so that the quoting character is doubled if it appears in the data). This must be a single one-byte character. This option is allowed only when using CSV format.

FORCE_QUOTE

Forces quoting to be used for all non-NULL values in each specified column. NULL output is never quoted. If * is specified, non-NULL values will be quoted in all columns. This option is allowed only in COPY TO, and only when using CSV format.

FORCE_NOT_NULL

Do not match the specified columns' values against the null string. In the default case where the null string is empty, this means that empty values will be read as zero-length strings rather than nulls, even when they are not quoted. This option is allowed only in COPY FROM, and only when using CSV format.

ENCODING

Specifies that the file is encoded in the encoding_name. If this option is omitted, the current client encoding is used. See the Notes below for more details.

COPY TO


copy emp to '~/path/to/emp1.csv';
7369    SMITH   CLERK   7902    1980-12-17      800     \N      20

7499    ALLEN   SALESMAN        7698    1981-02-20      1600    300     30

7521    WARD    SALESMAN        7698    1981-02-22      1250    500     30

7566    JONES   MANAGER 7839    1981-04-02      2975    \N      20

7654    MARTIN  SALESMAN        7698    1981-09-28      1250    1400    30
 
copy emp(empno,ename) to '~/path/to/emp2.csv';
7369    SMITH

7499    ALLEN

7521    WARD

7566    JONES

7654    MARTIN
 
copy emp(empno,ename) to '~/path/to/emp3.csv' with delimiter ',' csv header
empno,ename

7369,SMITH

7499,ALLEN

7521,WARD

7566,JONES

7654,MARTIN

COPY FROM

emp3.csv
 
empno,ename
7369,SMITH
7499,ALLEN
7521,WARD
7566,JONES
7654,MARTIN


copy customer(no,name) from '~/path/to/emp3.csv' with delimiter ',' csv header

select * from customer;

  no  |  name  
------+--------
 7369 | SMITH
 7499 | ALLEN
 7521 | WARD
 7566 | JONES
 7654 | MARTIN

필드값 내에 ","가 들어가 있을 경우

a.csv
1,2,"super,man","spider","man"
copy ccc(a,b,c,d,e) from '~/path/to/a.csv' with csv  quote '"'

select * from ccc;
 a | b |     c     |   d    |  e  
---+---+-----------+--------+-----
 1 | 2 | super,man | spider | man


컬럼변환이 필요할 때
copy ( SELECT CUST_NO ,TO_CHAR(REG_DTTM,'YYYY-MM-DD HH24:MI:SS') FROM ZZZ) to '/archive/dw.dat' with delimiter '|'


cf. export
copy (select * from from sql_trace
where snap_dt >= '$DATE2'
and snap_dt < '$DATE3'
order by snap_dt)
to '/Postgres/9.2/oradba/sql_move.csv' CSV QUOTE '"'

stdin을 사용한 COPY FROM

COPY test (pk, id, name) FROM stdin;
1	1	ee
2	1	ee
3	1	ee
4	1	ee
5	1	ee
6	1	ee
\.


레퍼런스

https://www.postgresql.org/docs/9.1/static/sql-copy.html

먼저 각 데이터베이스마다 다르겠지만 여기서는 postgresql 기준으로 설명하겠습니다.

UNION집합 연산은 다음처럼 두 가지가 있습니다.

  • UNION ALL
  • UNION DISTINCT

일반적으로 사용하는 UNION은 UNION DISTINCT의 줄임입니다.

UNION ALL과 UNION DISTINCT의 차이

UNION ALL은 중복을 제거하지 않고 그대로 합집합 연산을 해 결과를 보여주는 반면, UNION DISTINCT는 중복을 제거하여 결과를 보여줍니다.

여기서 중복을 처리하는 기준이 무엇인지 다음과 같은 질문이 발생합니다.

  • primary key
  • 전체 테이블의 모든 필드
  • select 절에서 나오는 튜플에 대한 필드


여기서 UNION은 이미 SELECT된 결과를 가지고 UNION하기 때문에 SELECT되기 전의 테이블이나 레코드에 대한 정보는 알 수 없습니다. 그래서, 중복 여부의 판단은 SELECT된 튜플들에 속해있는 모든 컬럼의 값들 자체가 중복 체크의 기준이 되는 것입니다.

UNION 처리과정

  1.  최종 UNION [ALL | DISTINCT] 결과에 적합한 임시 테이블(Temporary table)을 메모리 테이블로 생성
  2. UNION 또는 UNION DISTINCT 의 경우, Temporary 테이블의 모든 컬럼으로 Unique Hash 인덱스 생성
  3. 서브쿼리1 실행 후 결과를 Temporary 테이블에 복사
  4. 서브쿼리2 실행 후 결과를 Temporary 테이블에 복사
  5. 만약 3,4번 과정에서 Temporary 테이블이 특정 사이즈 이상으로 커지면 Temporary 테이블을 Disk Temporary 테이블로 변경  (이때 Unique Hash 인덱스는 Unique B-Tree 인덱스로 변경됨)
  6. Temporary 테이블을 읽어서 Client에 결과 전송
  7. Temporary 테이블 삭제


UNION 두 가지의 차이는 2번 과정 딱 하나입니다. 중복 제거를 위해서 Temporary 테이블에 인덱스를 생성하는지 안하는지 입니다. 별로 중요하지 않은 것 같지만, 이 인덱스로 인해서 3,4번 과정의 작업이 작지 않은 성능 차이가 만들어 내게 됩니다. 실제 UNION을 실행하는 데이터의 건수에 따라서 다르겠지만, 1.5 ~ 4배 가량의 성능 차이로 UNION ALL이 빠르게 처리된다. 만약 처리중 데이터의 크기가 작아서 5번 과정을 거치지 않는다면 메모리 Temporary 테이블에 Hash 인덱스를 사용하기 때문에 속도 차이가 아주 미세할 것입니다. 

하지만 데이터량이 커져서 5번 과정을 거치게 되면 Disk Temporary 테이블에 B-Tree 인덱스를 사용하기 때문에 큰 성능 차이를 보이게 됩니다. 이 성능 차이는 UNION 하는 두 집합에 중복되는 레코드가 있든 없든 관계 없이 발생합니다. 위에서 잠깐 알아보았던, "중복의 기준"을 생각하면, UNION 하는 컬럼들의 수가 많아지고 레코드의 사이즈가 커질수록 두 작업 모두에게 불리하겠지만, UNION ALL보다는 UNION에 더 악영향이 클 것이다.

결론

UNION 이든지 UNION ALL이든지 사실 그리 좋은 SQL 작성은 아님

UNION이 필요하다는 것은 사실 두 엔터티(테이블)가 하나의 엔터티(테이블)로 통합이 되었어야 할 엔터티들이었는데, 알 수 없는 이유로 분리 운영되었다는 경우임. 즉 모델링 차원에서 엔터티를 적절히 통합하여 UNION의 요건을 모두 제거하자

두 집합에 절대 중복된 튜플(레코드)가 발생할 수 없다는 보장이 있다면 UNION ALL을 꼭 사용하자. 두 집합에서 모두 각각의 PK를 조회하는데, 그 두 집합의 PK가 절대 중복되지 않는 형태

중복이 있다 하더라도 그리 문제되지 않는다면 UNION 보다는 UNION ALL을 사용하자.

만약 UNION이나 UNION ALL을 사용해야 한다면, 최소 필요 컬럼만 SELECT 하자.

'DB' 카테고리의 다른 글

[SQL] SELECT 결과 UPDATE  (0) 2016.11.18
[SQL] SQL VS CODE  (0) 2016.10.27
[SQL] Union과 Union all 차이  (0) 2016.10.12
[SQL] Joins  (0) 2016.08.24
[DB] DB Index 란?  (2) 2016.07.06
[DB] NoSQL이란?  (0) 2016.06.26

dblink와 마찬가지로 물리적으로 떨어져 있는 원격 테이블에 접속하기 위해 사용합니다. 차이점은 아래에서 설명하겠습니다.

CREATE EXTENSION

CREATE EXTENSION [ IF NOT EXISTS ] extension_name    [ WITH ] [ SCHEMA schema_name ]
             [ VERSION version ]
             [ FROM old_version ]

간단하게 설치하는 방법은 CREATE EXTENSION postgres_fdw 입니다. 또한 스키마를 지정하지 않으면 default로 public에 설치가 됩니다.

여기서 유의해야 할 것은 dblink extension이 데이터베이스 내에 설치가 되면 (사용자가 정한 디비 스키마라던지, default인 public 스키마 라던지..) 지우지 않은 이상 재설치가 불가능합니다.

따라서 스키마 별로 사용이 필요한 부분에서는 alter로 schema를 변경하여 사용하거나 지운 다음 사용 합니다.

CREATE EXTENSION  IF NOT EXISTS postgres_fdw SCHEMA other_db -- default 스키마 (public)에 설치를 하지 않을 경우엔 SCHEMA를 정의-- IF NOT EXISTS는 해당 디비에 EXTENSION이 없을 경우에만 설치합니다. 만약 해당 명령어가 없는데 이미 설치가 되어 있을 경우엔 에러를 뱉습니다.
 
ALTER EXTENSION SCHEMA other_db1 -- 다른 스키마로 dblink을 이동시켜 사용할 때 사용합니다.
 
DROP EXTENSION dblink -- dblink를 삭제합니다.

CREATE SERVER

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]


CREATE SERVER {1}
	FOREIGN DATA WRAPPER postgres_fdw
	OPTIONS (host '{2}', port '{3}', dbname '{4}');

{1}: 앞으로 원격 db를 사용할 server name (사용자 임의로 입력가능)

{2}: 연결할 원격디비의 주소

{3}: 연결할 원격디비의 포트

{4}: 연결할 원격디비의 데이터베이스명

CREATE USER MAPPING

CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
    SERVER server_name
    [ OPTIONS ( option 'value' [ , ... ] ) ]


CREATE USER MAPPING FOR {1}
	SERVER {2}
    OPTIONS (user '{3}', password '{4}');

{1}: 원격 서버와 맵핑시킬 현재 데이터베이스 내에 존재하는 user name (ex, postgres)

{2}: 위에서 정한 원격 db server name

{3}: 원격 디비의 user name

{4}: 원격 디비 user의 password

CREATE FOREIGN TABLE

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    [, ... ]
] )
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  DEFAULT default_expr }


접근하고자 하는 원격 디비의 테이블 스키마를 가져옵니다. (constraint 제외)

CREATE FOREIGN TABLE {1} (
    name text,
	id text,
	...
)
SERVER {2}
OPTIONS (schema_name '{3}', table_name '{4}');

{1}: 사용할 테이블명

{2}: 위에서 정한 원격 db server name

{3}: 원격 디비의 schema

{4}: 원격 디비에서 가져오고자 하는 원본 테이블 명

postgresql_fdw 사용예시

CREATE EXTENSION IF NOT EXISTS postgres_fdw; 
ALTER EXTENSION dblink SCHEMA other_db;
 
CREATE SERVER test1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '199.199.199.123', port '5432', dbname 'db2');

CREATE USER MAPPING FOR postgres SERVER test1 OPTIONS (user 'remote_user', password '1234')
CREATE FOREIGN TABLE IF NOT EXISTS remote_table
(
	id bigint not null,
	name text,
	pwd text
 
)

    SERVER test1
    OPTIONS (schema_name 'remote_schema', table_name 'original_table')
 
 
select * from remote_table

dblink와의 차이점

동일한 쿼리를 dblink와 postgres_fdw 둘다 20번 정도 실행한 결과 속도차이는 크게 있지 않음

단, orm을 사용하기엔 postgres_fdw가 훨씬 수월하고 쿼리 모양새도 더 깔끔함

  • dblink를 사용하여 orm을 적용하기 위해선 dblink 쿼리 부분을 view로 저장해야함
    • 조회 될 때마다 view가 계속 생성되기 때문에 속도저하 가능성 있음

postgres_fdw는 맨 처음 프로시저만 생성하면 다음부터는 일반 테이블 사용하는 것과 같이 사용법 동일

dblink를 사용하기 위해선 주체가 되는 디비의 유저가 superuser 권한이 있어야 하지만 postgres_fdw는 없어도 가능

참조페이지

foreign table : https://www.postgresql.org/docs/9.4/static/sql-createforeigntable.html

user mapping: https://www.postgresql.org/docs/9.3/static/sql-createusermapping.html

server : https://www.postgresql.org/docs/9.1/static/sql-createserver.html

+ Random Posts