🥇PgGraph - PostgreSQL에서 테이블 종속성을 보관하고 검색하기 위한 유틸리티

오늘 저는 Habr 독자들에게 PostgreSQL DBMS에서 테이블 종속성 작업을 위해 Python으로 작성된 유틸리티를 제시하고 싶습니다.

유틸리티의 API는 간단하며 다음 세 가지 방법으로 구성됩니다.

archive_table - 지정된 기본 키를 사용하여 행을 재귀적으로 보관/삭제
get_table_references — 테이블에 대한 종속성을 검색합니다(지정된 테이블과 이를 참조하는 테이블이 표시됨).
get_rows_references - 원하는 테이블의 지정된 행을 참조하는 다른 테이블의 행을 검색합니다.

선사 시대

제 이름은 Oleg Borzov이고 Domklik의 모기지 대출 관리자를 위한 CRM 팀의 개발자입니다.

우리 CRM 시스템의 기본 데이터베이스는 회사에서 가장 큰 규모의 데이터베이스 중 하나입니다. 이것은 또한 가장 오래된 것 중 하나입니다. 프로젝트가 시작될 때 나타났습니다. 나무가 컸을 때 Domklik은 스타트업이었고 세련된 Python 비동기 프레임워크의 마이크로서비스 대신 PHP에 거대한 단일체가 있었습니다.

PHP에서 Python으로의 전환은 매우 길었고 두 시스템의 동시 지원이 필요했기 때문에 데이터베이스 설계에 영향을 미쳤습니다.

결과적으로 우리는 다양한 유형의 쿼리에 대한 많은 인덱스를 포함하는 고도로 연결되고 거대한 테이블이 많이 있는 데이터베이스를 보유하게 되었습니다. 이 모든 것은 데이터베이스 성능에 부정적인 영향을 미칩니다. 큰 테이블과 테이블 사이의 수많은 관계로 인해 쿼리의 복잡성이 지속적으로 증가하고 있으며 이는 가장 많이 로드된 테이블에 특히 중요합니다.

데이터베이스의 로드를 줄이기 위해 우리는 가장 방대하고 로드된 테이블의 오래된 레코드를 보관된 테이블(예: task в task_archive).

이 작업은 테이블 간의 관계가 너무 많기 때문에 복잡합니다. task в task_archive 충분하지 않습니다. 그 전에 참조하는 모든 항목에 대해 동일한 작업을 반복적으로 수행해야 합니다. task 테이블.

예시를 들어 설명하겠습니다 postgrespro.ru 사이트의 데모 데이터베이스:

테이블에서 레코드를 삭제해야 한다고 가정해 보겠습니다. Flights. Postgres는 그런 식으로 이를 수행하는 것을 허용하지 않습니다. 먼저 모든 참조 테이블에서 레코드를 삭제해야 하며, 그런 다음 누구도 참조하지 않는 테이블까지 반복적으로 삭제해야 합니다.

우리의 예에서는 Flights ~을 지칭하다 Ticket_flights, 그리고 그녀에게- Boarding_passes.

따라서 다음 순서로 삭제해야 합니다.

우리는 행의 기본 키(PK) 값을 얻습니다. Ticket_flights, 삭제될 행을 나타냅니다. Flights.
PK 행을 얻습니다. Boarding_passes, 이는 다음을 참조합니다. Ticket_flights.
테이블의 2단계에서 PK별로 행을 삭제합니다. Boarding_passes.
1단계에서 PK별로 라인을 삭제합니다. Ticket_flights.
다음에서 줄 제거 Flights.

그 결과 PgGraph라는 유틸리티가 탄생했고, 우리는 이를 오픈 소스로 만들기로 결정했습니다.

사용 방법

이 유틸리티는 두 가지 사용 모드를 지원합니다.

명령줄에서 호출(pggraph …).
Python 코드에서의 사용법(클래스 PgGraphApi).

설치 및 구성

먼저 Pypi 저장소에서 유틸리티를 설치해야 합니다.

pip3 install pggraph

그런 다음 데이터베이스 구성과 보관 스크립트를 사용하여 로컬 컴퓨터에 config.ini 파일을 만듭니다.

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Необязательный параметр, указано значение по умолчанию

[archive]  ; Данный раздел заполнять необязательно, ниже указаны значения по умолчанию
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

콘솔에서 실행

매개 변수

$ pggraph -h
usage: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
positional arguments:
  action        required action: archive_table, get_table_references, get_rows_references

optional arguments:
  -h, --help                    show this help message and exit
  --table TABLE                 table name
  --ids IDS                     primary key ids, separated by comma, e.g. 1,2,3
  --config_path CONFIG_PATH     path to config.ini
  --log_path LOG_PATH           path to log dir
  --log_level LOG_LEVEL         log level (debug, info, error)

위치 인수:

action - 필수 조치: archive_table, get_table_references 또는 get_rows_references.

명명된 인수:

--config_path — 구성 파일의 경로;
--table — 작업을 수행하는 데 필요한 테이블
--ids — 쉼표로 구분된 ID 목록(예: 1,2,3 (선택적 매개변수);
--log_path — 로그 폴더 경로(선택적 매개변수, 기본적으로 홈 폴더)
--log_level — 로깅 수준(선택적 매개변수, 기본값은 INFO).

명령 예

테이블 보관

유틸리티의 주요 기능은 데이터 보관입니다. 기본 테이블에서 보관 테이블로 행 전송(예: 테이블에서) 서적 в 책_아카이브).

보관하지 않고 삭제하는 것도 지원됩니다. 이를 위해서는 config.ini에서 매개변수를 설정해야 합니다. to_archive = 거짓).

필수 매개변수 - config_path, 테이블 및 ID.

출시 후 기록은 반복적으로 삭제됩니다. ids 테이블에 table 그리고 그것을 참조하는 모든 테이블에서.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - START
2020-06-20 19:27:44 INFO: flights - start archive_recursive 3 rows (depth=0)
2020-06-20 19:27:44 INFO:       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 19:27:44 INFO:               START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 rows by id
2020-06-20 19:27:44 INFO: flights - END

지정된 테이블에 대한 종속성 찾기

지정된 테이블의 종속성을 찾는 기능 table. 필수 매개변수 - config_path и table.

실행 후 화면에 사전이 표시됩니다.

in_refs — 주어진 테이블을 참조하는 테이블 사전. 여기서 키는 테이블 이름이고 값은 외래 키 객체 목록입니다(pk_main - 메인 테이블의 기본 키, pk_ref - 참조 테이블의 기본 키 fk_ref — 소스 테이블에 대한 외래 키인 열의 이름)
out_refs — 이것이 참조하는 테이블 사전입니다.

$ pggraph get_table_references --config_path config.hw.local.ini --table flights
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='ticket_no, flight_id', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

지정된 기본 키를 사용하여 문자열에 대한 참조 찾기

외래키를 통해 행을 참조하는 다른 테이블의 행을 검색하는 기능 ids 테이블 table. 필수 매개변수 - config_path, table и ids.

실행 후 다음 구조의 사전이 화면에 표시됩니다.

{
	pk_id_1: {
		reffering_table_name_1: {
			foreign_key_1: [
				{row_pk_1: value, row_pk_2: value},
				...
			], 
			...
		},
		...
	},
	pk_id_2: {...},
	...
}

호출 예시:

$ pggraph get_rows_references --config_path config.hw.local.ini --table flights --ids 1,2,3
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

코드에서의 사용법

콘솔에서 실행하는 것 외에도 라이브러리를 Python 코드에서 사용할 수 있습니다. iPython 대화형 환경에서의 호출 예는 다음과 같습니다.

테이블 보관

>>> from pg_graph.main import setup_logging
>>> setup_logging(log_level='DEBUG')
>>> from pg_graph.api import PgGraphApi
>>> api = PgGraphApi('config.hw.local.ini')
>>> api.archive_table('flights', [4,5])
2020-06-20 23:12:08 INFO: flights - START
2020-06-20 23:12:08 INFO: flights - start archive_recursive 2 rows (depth=0)
2020-06-20 23:12:08 INFO: 	START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 	ticket_flights - ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')
2020-06-20 23:12:08 DEBUG: 	SQL('SELECT flight_id, ticket_no FROM bookings.ticket_flights WHERE (flight_id) IN (%s, %s)')
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 3 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 rows
2020-06-20 23:12:08 INFO: 	END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 rows by flight_id
2020-06-20 23:12:09 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.flights_archive (LIKE bookings.flights)')
2020-06-20 23:12:09 DEBUG: DELETE FROM flights by flight_id - 2 rows
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 rows
2020-06-20 23:12:09 INFO: flights - END

지정된 테이블에 대한 종속성 찾기

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> res = api.get_table_references('flights')
>>> pprint(res)
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

지정된 기본 키를 사용하여 문자열에 대한 참조 찾기

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> rows = api.get_rows_references('flights', [1,2,3])
>>> pprint(rows)
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

라이브러리 소스 코드는 다음에서 확인할 수 있습니다. GitHub의 MIT 라이선스와 저장소에 따라 PyPI.

나는 의견, 커밋 및 제안을 기꺼이 받아들일 것입니다.

저는 여기와 저장소에서 최선을 다해 질문에 답변하려고 노력할 것입니다.

출처 : habr.com

PgGraph는 PostgreSQL에서 테이블 종속성을 보관하고 찾는 유틸리티입니다.

선사 시대

사용 방법

설치 및 구성

콘솔에서 실행

매개 변수

명령 예

테이블 보관

지정된 테이블에 대한 종속성 찾기

지정된 기본 키를 사용하여 문자열에 대한 참조 찾기

코드에서의 사용법

테이블 보관

지정된 테이블에 대한 종속성 찾기

지정된 기본 키를 사용하여 문자열에 대한 참조 찾기

코멘트를 추가 답장을 취소