목차
개요
팀 내에서 대규모 데이터에 대해 일정 시간마다 신규 데이터가 인입되는 상황에서 집계 쿼리를 통해 대시보드를 보여주어야 하는 상황일 때, 매 요청마다 집계쿼리를 실행하여 보여주기에는 코스트가 너무 높다고 판단하여 방법을 찾던 중 Materialized View를 사용하기로 결정하였습니다. 이 과정에서 Materialized View라는 내용을 처음 듣게 되어 정리해보았습니다.
Materialized View란?
Materialized View는 데이터베이스에서 사용되는 객체로, 복잡한 쿼리 결과를 저장하고 이를 주기적으로 갱신하여 빠르게 접근할 수 있게 해주는 구조입니다. 기본적으로 View와 비슷하지만, View는 쿼리 결과를 실시간으로 계산해서 반환하는 반면, Materialized View는 결과를 물리적으로 저장해두고 필요할 때 그 데이터를 제공합니다.
특징
1. 쿼리 성능 향상
- Materialized View는 복잡한 계산이 포함된 쿼리의 결과를 저장해두기 때문에, 동일한 쿼리를 다시 실행할 때 물리적인 데이터 저장소에서 바로 데이터를 가져올 수 있어 쿼리 성능이 크게 향상됩니다.
2. 주기적인 갱신
- Materialized View는 저장된 데이터를 주기적으로 갱신할 수 있습니다. 갱신 방법은 전체 갱신(Full Refresh) 방법이 있습니다.
3. 디스크 공간 사용
- Materialized View는 데이터를 저장하는 형태이기 때문에 디스크 공간을 더 사용합니다. 이는 비효율적인 경우가 될 수 있습니다.
4. 실시간 데이터 반영 불가
- Materialized View는 저장된 데이터를 기반으로 작동하기 때문에, View와 달리 실시간 데이터 반영에는 제한이 있습니다. 갱신 주기 내에 변경된 데이터는 반영되지 않기 때문에 주의가 필요합니다.
https://www.alibabacloud.com/blog/598129
https://wiki.postgresql.org/wiki/Incremental_View_Maintenance
'DB' 카테고리의 다른 글
| [MariaDB] ELT (랜덤한 값 넣을 때, 사용한 함수) (0) | 2025.01.17 |
|---|---|
| TDE란? (0) | 2024.11.26 |
| DBeaver 설치 및 DB 연동하기 (0) | 2023.11.03 |
목차
개요
PoC를 위한 데이터 구성 중 유용한 함수를 발견하여 정리해보았습니다.
ELT
더미데이터 구성 중 랜덤하게 다양한 값을 넣어야 하는 상황에서 아래와 같은 MariaDB 함수를 발견했습니다.
ELT(N, str[, str2, str3, ...])
2번째 인자 이후에 있는 값들에 대해 N에 해당하는 값을 반환하는 함수 입니다.
아래와 같이 사용하였습니다.
UPDATE testDB
SET test_accuracy = ELT(FLOOR(1 + (RAND() * 4)), 98.5, 92.75, 99.2, 99.99);
참고자료
'DB' 카테고리의 다른 글
| Materialized View란? (0) | 2025.02.06 |
|---|---|
| TDE란? (0) | 2024.11.26 |
| DBeaver 설치 및 DB 연동하기 (0) | 2023.11.03 |
목차
개요
업무를 하다보니 TDE라는 용어를 듣게 되었습니다. 해당 내용이 무엇인지 궁금하여 찾아보았습니다.
TDE란?
TDE(Transparent Data Encryption)는 데이터베이스에서 저장된 데이터를 암호화하여 보안을 강화하는 기술입니다. 주요 특징은 데이터베이스 애플리케이션이나 쿼리 수정 없이 데이터 암호화를 적용할 수 있다는 점입니다.
TDE의 작동 방식
- 암호화 대상: 디스크에 저장되는 데이터베이스 파일, 로그 파일 등.
- 데이터를 디스크에 기록할 때 암호화하고, 메모리에서 읽을 때 자동으로 복호화합니다.
- 투명성: 애플리케이션에서 접근하는 데이터는 평문(복호화된 상태)으로 제공되므로 추가적인 코딩 작업이 필요하지 않습니다.
TDE의 주요 구성 요소
- 데이터 암호화 키(DEK, Data Encryption Key)
- 데이터를 암호화하는 데 사용됩니다.
- 보통 고성능을 위해 대칭 키 암호화 방식을 사용합니다.
- DEK는 별도의 **마스터 키(Master Key)**로 암호화되어 보호됩니다.
- 마스터 키(Master Key)
- 데이터베이스 외부(예: 하드웨어 보안 모듈(HSM) 또는 운영체제의 키 스토리지)에 저장되어 DEK를 보호합니다.
TDE의 주요 장점
- 데이터 보호
- 데이터베이스 파일이 탈취당하거나 디스크를 분실하더라도 암호화된 상태이므로 읽을 수 없습니다.
- 애플리케이션 변경 불필요
- 암호화/복호화 과정이 투명하게 처리되므로 기존 애플리케이션 로직 수정이 필요 없습니다.
- 법적 및 규제 준수
- PCI-DSS, GDPR 같은 규제에서 요구하는 데이터 보호 요건 충족 가능.
TDE의 한계
- 성능 저하
- 암호화/복호화 작업으로 인해 I/O 성능에 영향을 줄 수 있습니다.
- 전송 중 데이터 보안 미포함
- TDE는 디스크에 저장된 데이터를 보호하지만, 네트워크를 통해 전송되는 데이터는 별도의 암호화(예: TLS)가 필요합니다.
- 키 관리 중요성
- 마스터 키가 유출되거나 분실되면 암호화된 데이터를 복구할 수 없습니다.
주요 데이터베이스에서 TDE 지원 여부
- Oracle: Oracle Advanced Security 옵션으로 TDE 제공.
- Microsoft SQL Server: TDE 기본 지원.
- MySQL/MariaDB: MySQL 5.7.12 이상 및 MariaDB 10.1 이상에서 TDE 지원.
- PostgreSQL: TDE는 기본적으로 제공되지 않지만, 확장 모듈이나 파일 시스템 암호화를 통해 구현 가능.
DBMS 자체 제공 암호화 제품(TDE)의 보안 문제점 분석한 블로그 글도 있어 함께 읽어보면 좋을 것 같습니다.
https://m.blog.naver.com/hanajava/223158513771
DBMS 자체 제공 암호화 제품(TDE)의 보안 문제점 분석
DBMS 자체제공 암호화 제품(TDE)의 보안 문제점 분석 저자:조돈섭 * (현) 이글로벌시스템 재직 * 20...
blog.naver.com
MariaDB에 TDE 적용한 케이스
https://ongamedev.tistory.com/560
MariaDB TDE 적용
Transparent Data Encryption - 데이터 저장 시 암호화해서 저장합니다. (공식문서 링크) 원하는 Table만 암호화 할 수도 있고 테이블 마다 다른 암호화 키를 사용할 수 있으며 DB 전체, 그리고 로그도 선택
ongamedev.tistory.com
'DB' 카테고리의 다른 글
| Materialized View란? (0) | 2025.02.06 |
|---|---|
| [MariaDB] ELT (랜덤한 값 넣을 때, 사용한 함수) (0) | 2025.01.17 |
| DBeaver 설치 및 DB 연동하기 (0) | 2023.11.03 |
목차
개요
Spring에서 다중 DB를 설정하고 사용하는 법을 연습하면서 PostgreSQL은 PostgreSQL의 CLI환경에서 MariaDB는 HeidiSQL에서 따로 관리하려고했습니다. 이 과정에서 서로 다른 프로그램으로 관리하려니 불편한 점이 많았습니다. 이런 상황에서 쓸 수 있다는 DB 관리 툴인 DBeaver를 설치하고 만들어두었던 DB를 연동해보려고합니다.
DBeaver 설치
https://dbeaver.io/ 에 접속하여 Community Edition을 자신의 OS 설정에 맞게 설치합니다.
저 같은 경우에는 윈도우를 사용하므로 Windows installer를 설치하도록 하겠습니다.
설치한 파일을 실행합니다.
DBeaver에 DB 연결하기
이후에는 기본 설정으로 설치를 쭉 진행하시면 됩니다.
설치가 완료되었다면 아래와 같이 DBeaver 어플을 찾아 실행해줍니다.
실행하면 처음으로 Connection을 만들기 위해 데이터베이스를 선택하라고 뜹니다.
저는 먼저 MariaDB를 연결해보도록 하겠습니다.
다음과 같이 설정하는 창이 나옵니다.
자신이 만들었던 데이터베이스 설정에 맞게 작성합니다.
(저 같은 경우는 MySQL도 사용 중이기에 MariaDB Port를 3307로 설정해두어 변경했습니다.)
Server Time Zone도 Asia/Seoul로 설정하였고 설정이 완료되었다면 좌측 하단의 [Test Connection]을 클릭하여 연결에 대한 테스트를 진행해봅니다.
DBeaver는 JDBC로 MariaDB 연결을 진행하는데 MariaDB JDBC driver가 존재하지 않아 설치하라는 페이지가 뜹니다.
해당 페이지의 [Download]를 클릭하여 설치를 진행합니다.
이후 Connection test 탭에서 Connected라는 말과 함께 연결이 완료되었다는 점을 안내해줍니다.
연결이 완료된 후 확인해보면 MariaDB 데이터베이스에 연결되었고 이전에 주입했던 데이터를 확인할 수 있음을 확인할 수 있습니다.
만약 쿼리문을 실행하고 싶다면 빨간 네모칸 부분을 더블 클릭하시면 됩니다.
더블 클릭을 통해 나온 탭에서 좌측의 실행 버튼을 누르시면 아래에 결과물을 확인 할 수 있습니다.
이후 추가를 원하는 DB가 있으면 좌측 상단의 [새 데이터베이스 연결] 버튼을 클릭하여 위와 동일하게 설정을 하시면 됩니다.
제 경우에는 MariaDB와 PostgreSQL을 연결했습니다.
추가 정보
아래의 주소를 통해 DBeaver에 대한 더 많은 정보를 확인하실 수 있습니다.
DBeaver 소스 코드 확인하기
https://github.com/dbeaver/dbeaver
GitHub - dbeaver/dbeaver: Free universal database tool and SQL client
Free universal database tool and SQL client. Contribute to dbeaver/dbeaver development by creating an account on GitHub.
github.com
DBeaver 위키
https://github.com/dbeaver/dbeaver/wiki
Home
Free universal database tool and SQL client. Contribute to dbeaver/dbeaver development by creating an account on GitHub.
github.com
Reference
'DB' 카테고리의 다른 글
| Materialized View란? (0) | 2025.02.06 |
|---|---|
| [MariaDB] ELT (랜덤한 값 넣을 때, 사용한 함수) (0) | 2025.01.17 |
| TDE란? (0) | 2024.11.26 |
목차
PostgreSQL 설치하기
아래의 주소에 방문합니다.
https://www.postgresql.org/download/windows/
1. 페이지 접속 후 Download the installer 링크를 클릭합니다.
2. Windows x86-64에서 원하는 PostgreSQL Version을 선택하여 설치 버튼을 클릭합니다.
3. [다운로드] 폴더에 가시면 설치 파일이 생성되었음을 확인하실 수 있습니다. 해당 파일을 실행하여 설치를 진행합니다.
4. [Next] 버튼을 클릭하여 설치를 시작합니다.
5. 설치 경로를 지정합니다. (저는 기본 설정 값으로 진행했습니다.)
6. 모두 설치합니다.
7. Data Directory를 지정합니다. (저는 기본 경로로 설정하였습니다.)
8. Root 사용자와 슈퍼 사용자의 비밀번호를 설정합니다. 잃어버리시면 안됩니다!!
9. 포트 번호를 지정합니다. (저는 기본 포트인 5432로 설정했습니다.)
10. Locale을 [Korean, Korea]로 선택합니다.
11. 2번 정도의 [Next] 버튼을 더 클릭하시면 다음과 같이 설치가 진행됩니다.
12. 설치가 완료되면 체크 박스를 해제하고 [Finish] 버튼을 누릅니다. 설치가 끝났습니다!!
'DB > PostgreSQL' 카테고리의 다른 글
| PostgreSQL이란? (내부 구조와 장단점 및 MySQL과의 차이) (1) | 2023.10.23 |
|---|
목차
PostgreSQL이란?
PostgreSQL은 오픈소스 객체-관계형 데이터베이스 시스템(ORDBMS)으로, Enterprise급 DBMS의 기능과 차세대 DBMS에서나 볼 수 있을 법한 기능들을 제공합니다. 약 20여년의 오랜 역사를 갖는 PostgreSQL은 다른 관계형 데이터베이스 시스템과 달리 연산자, 복합 자료형, 집계 함수, 자료형 변환자, 확장 기능 등 다양한 데이터베이스 객체를 사용자가 임의로 만들 수 있는 기능을 제공함으로써 마치 새로운 하나의 프로그래밍 언어처럼 무한한 기능을 손쉽게 구현할 수 있습니다.
PostgreSQL 내부 구조
postgreSQL의 프로세스 구조를 간단히 살펴보면 다음과 같습니다.
클라이언트는 (1)인터페이스 라이브러리(libpg, JDBC, ODBC 등의 다양한 인터페이스)를 통해 서버와의 연결을 요청하면, (2)Postmaster 프로세스가 서버와의 연결을 중계합니다. 이후 (3)클라이언트는 할당된 서버와의 연결을 통해 질의를 수행합니다. Shared Memory를 이용하여 GC와 같은 작업을 통해 MVCC를 관리합니다.
서버 내부의 질의 수행 과정을 간단히 살펴보면 다음과 같습니다.
클라이언트로부터 질의 요청이 들어오면 (1)구문 분석 과정을 통해 Parse Tree를 생성하고 (2)의미 분석 과정을 통해 새로운 트랜잭션을 시작하고 Query Tree를 생성합니다.
이후 (3)서버에 정의된 Rule에 따라 Query Tree가 재생성되고 (4)실행 가능한 여러 수행 계획 중 가장 최적화된 Plan Tree를 생성합니다. (5) 서버는 이를 수행하여 요청된 질의에 대한 결과를 클라이언트로 전달하게 됩니다.
서버의 쿼리 수행 과정에서는 데이터베이스 내부의 시스템 카탈로그가 많이 사용되는데, 사용자가 함수나 데이터 타입은 물론 인덱스 접근 방식 및 RULE 등을 시스템 카탈로그에 직접 정의할 수도 있습니다. 따라서 PostegreSQL에서는 이것이 기능을 새로 추가하거나 확장하는데 있어 중요한 포인트로 활용됩니다.
이러한 작업 방식이 방대한 데이터에 복잡한 쿼리에서는 높은 성능을 보이지만 작은 데이터에 간단한 쿼리에는 오히려 약점을 보이는 이유인 것으로 보입니다.
데이터가 저장되는 파일들은 여러개의 페이지들로 구성되며, 하나의 페이지는 확장 가능한 slotted page 구조를 가집니다.
- 데이터 페이지 구조
- 인덱스 페이지 구조
PostgreSQL 장단점 및 MySQL과의 차이점
PostgreSQL 장단점
| 장점 | 단점 |
| 함수, 데이터 유형, 언어 등을 추가할 수 있는 확장성이 높음 | 단순한 CRUD, 작은 데이터 셋에서는 MySQL보다 낮은 성능을 제공하는 경우가 있음 |
| 구조화되지 않은 데이터 유형 지원(예: 오디오, 동영상, 이미지) | 독창적인 자료형과 문법 때문에 러닝 커브가 있음 |
| 교착 상태가 거의 발생하지 않고 동시 처리가 가능하며 트랜잭션 속도가 빠른 MVCC | 독창적인 자료형 및 문법으로 인해 다른 DB로 migration이 쉽지 않 |
| 고가용성 및 서버 장애 복구 | |
| 데이터 암호화, SSL 인증서, 고급 인증 방법과 같은 보안 기능 | |
| 활발한 오픈소스 커뮤니티가 지속적으로 솔루션을 개선하고 업데이트 |
MySQL과의 차이점
MySQL8.0 업데이트를 기준으로 PostgreSQL과의 차이점이 많이 사라졌다고 합니다. 그럼에도 존재하는 MySQL과의 차이점을 소개드립니다.
1. MVCC 성능이 PostgreSQL에서 더 좋다.
2. PostgreSQL은 고급 인덱싱 기법들을 제공한다. (GIN, GiST 등)
결론
PostgreSQL에 대해 간단히 알아보았습니다. 공부하다보니 Java의 GC와 같은 Vaccum이 있고 저장하는 페이지 자료형에 대한 내용도 있고 공부할 것이 많더군요. 찬찬히 공부해보며 정리해보려고합니다.
그리고 장단점과 차이점등에 대해서는 구글링을 통해 얻은 지식을 정리했지만 실무에서 사용해보며 몸으로 체감하는 차이점을 적을 수 있는 날이 왔으면 좋겠습니다.
Reference
https://d2.naver.com/helloworld/227936
https://mangkyu.tistory.com/71
https://mystory-blog.vercel.app/blog/mysql-to-postgres/mysql-versus-postgres
https://cloud.google.com/learn/postgresql-vs-sql?hl=ko
https://codecamp.tistory.com/2
https://gksdudrb922.tistory.com/245
'DB > PostgreSQL' 카테고리의 다른 글
| 윈도우에 PostgreSQL 설치하기 (0) | 2023.10.24 |
|---|