설계 의도
- Django에서는 단일 Fact 모델(FactIncomeStatementTransaction) 만 유지
- Postgres의 native partitioning (by RANGE year) 활용 → 연도별 파티션 테이블 자동 분리
- ORM 호환성 유지: Django에서는 마치 단일 테이블처럼 사용하지만, 실제로는 파티션별로 저장/조회됨
이점
- ORM 호환성 유지
- Django 코드에서 FactIncomeStatementTransaction 모델만 사용
- FactIncomeStatementTransaction_2025, _2026 같은 모델 정의 불필요
- 자동 라우팅
- INSERT → Postgres가 year 값 보고 해당 파티션에 저장
- SELECT → year=2025 조건을 주면 *_2025 파티션만 스캔
- 쿼리 성능
qs = FactIncomeStatementTransaction.objects.filter(year=2025, company_id=22066)
Index Scan using fact_income_statement_transaction_2025_pkey on fact_income_statement_transaction_2025 fact_income_statement_transaction (cost=0.14..8.16 rows=1 width=328)
Index Cond: (year = 2025)
Filter: (company_id = 22066)
- 실행계획 확인 결과, 마스터 테이블이 아닌 연도별 파티션 테이블만 접근
- 필요 없는 연도의 파티션은 쿼리 스킵됨 (Partition Pruning*)
- 테이블 관리 단순화
- 마스터 테이블 (fact_income_statement_transaction) → 데이터 저장 X, 스키마 메타 정보만 가짐
- 연도별 파티션(fact_income_statement_transaction_2025) → 실제 데이터 저장
운영 고려사항
- 파티션
테이블 생성 자동화 필요매년 ~~> 해당부분 한번에 30년까지 만들기로 결정 -> ~~일 0시, 새로운 연도 파티션 테이블 자동 생성 스실행
CREATE TABLE fact_income_statement_transaction_2026 PARTITION OF fact_income_statement_transaction FOR VALUES FROM (2026) TO (2027);- 마스터 테이블에는 데이터 없음
- SELECT * FROM fact_income_statement_transaction → 모든 파티션 합쳐진 결과 반환
- SELECT * FROM ONLY fact_income_statement_transaction → 항상 빈 결과 (확인용)
- 마이그레이션 관리
- Django migrations.SeparateDatabaseAndState 활용
- 스키마는 ORM에 반영되지만, 실제 파티션 생성은 RunSQL로 관리
- 예시 마이그레이션 코드
# apps/finance/migrations/0001_create_income_statement_transaction.py from django.db import migrations, models import django.db.models.deletion import django_extensions.db.fields class Migration(migrations.Migration): """ BEGIN; -- -- Custom state/database change combination -- CREATE TABLE IF NOT EXISTS finance_income_statement ( id BIGSERIAL, transacted_at DATE NOT NULL, created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(), modified TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(), year INT NOT NULL, month INT NOT NULL, company_id BIGINT NULL, evidence_type VARCHAR(50) NOT NULL, account_subject_id BIGINT NULL, amount_in_krw NUMERIC(20,2), oversea_amount NUMERIC(20,2), currency_code VARCHAR(10) DEFAULT 'KRW', bank_transaction_id BIGINT NULL, invoice_purchase_id BIGINT NULL, invoice_sales_id BIGINT NULL, card_purchase_id BIGINT NULL, card_sales_id BIGINT NULL, cash_purchase_id BIGINT NULL, cash_sales_id BIGINT NULL, delivery_order_id BIGINT NULL, pg_id BIGINT NULL, allra_pg_id BIGINT NULL, PRIMARY KEY (year, id) ) PARTITION BY RANGE (year); CREATE INDEX IF NOT EXISTS finance_inc_company_a61a79_idx ON finance_income_statement (company_id, account_subject_id, month); CREATE TABLE IF NOT EXISTS finance_income_statement_2025 PARTITION OF finance_income_statement FOR VALUES FROM (2025) TO (2026); CREATE TABLE IF NOT EXISTS finance_income_statement_2026 PARTITION OF finance_income_statement FOR VALUES FROM (2026) TO (2027); CREATE TABLE IF NOT EXISTS finance_income_statement_2027 PARTITION OF finance_income_statement FOR VALUES FROM (2027) TO (2028); CREATE TABLE IF NOT EXISTS finance_income_statement_2028 PARTITION OF finance_income_statement FOR VALUES FROM (2028) TO (2029); CREATE TABLE IF NOT EXISTS finance_income_statement_2029 PARTITION OF finance_income_statement FOR VALUES FROM (2029) TO (2030); CREATE TABLE IF NOT EXISTS finance_income_statement_2030 PARTITION OF finance_income_statement FOR VALUES FROM (2030) TO (2031); COMMIT; """ dependencies = [ ('accounting', '0014_taxinvoicepurchaseaccountsubjecthistory_and_more'), ('evidences', '0072_invoicepurchase_latest_origin_and_more'), ] operations = [ migrations.SeparateDatabaseAndState( database_operations=[ migrations.RunSQL( """ CREATE TABLE IF NOT EXISTS finance_income_statement ( id BIGSERIAL, transacted_at DATE NOT NULL, created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(), modified TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(), year INT NOT NULL, month INT NOT NULL, company_id BIGINT NULL, evidence_type VARCHAR(50) NOT NULL, account_subject_id BIGINT NULL, amount_in_krw NUMERIC(20,2), oversea_amount NUMERIC(20,2), currency_code VARCHAR(10) DEFAULT 'KRW', bank_transaction_id BIGINT NULL, invoice_purchase_id BIGINT NULL, invoice_sales_id BIGINT NULL, card_purchase_id BIGINT NULL, card_sales_id BIGINT NULL, cash_purchase_id BIGINT NULL, cash_sales_id BIGINT NULL, delivery_order_id BIGINT NULL, pg_id BIGINT NULL, allra_pg_id BIGINT NULL, PRIMARY KEY (year, id) ) PARTITION BY RANGE (year); CREATE INDEX IF NOT EXISTS finance_inc_company_a61a79_idx ON finance_income_statement (company_id, account_subject_id, month); """, reverse_sql="DROP TABLE IF EXISTS finance_income_statement CASCADE;", ), migrations.RunSQL( """ CREATE TABLE IF NOT EXISTS finance_income_statement_2025 PARTITION OF finance_income_statement FOR VALUES FROM (2025) TO (2026); """ ), migrations.RunSQL( """ CREATE TABLE IF NOT EXISTS finance_income_statement_2026 PARTITION OF finance_income_statement FOR VALUES FROM (2026) TO (2027); """ ), migrations.RunSQL( """ CREATE TABLE IF NOT EXISTS finance_income_statement_2027 PARTITION OF finance_income_statement FOR VALUES FROM (2027) TO (2028); """ ), migrations.RunSQL( """ CREATE TABLE IF NOT EXISTS finance_income_statement_2028 PARTITION OF finance_income_statement FOR VALUES FROM (2028) TO (2029); """ ), migrations.RunSQL( """ CREATE TABLE IF NOT EXISTS finance_income_statement_2029 PARTITION OF finance_income_statement FOR VALUES FROM (2029) TO (2030); """ ), migrations.RunSQL( """ CREATE TABLE IF NOT EXISTS finance_income_statement_2030 PARTITION OF finance_income_statement FOR VALUES FROM (2030) TO (2031); """ ), ], state_operations=[ migrations.CreateModel( name='IncomeStatement', fields=[ ('id', models.BigAutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')), ('created', django_extensions.db.fields.CreationDateTimeField(auto_now_add=True, verbose_name='created')), ('modified', django_extensions.db.fields.ModificationDateTimeField(auto_now=True, verbose_name='modified')), ('year', models.IntegerField(help_text='거래 년도')), ('transacted_at', models.DateField(help_text='거래 일자')), ('month', models.IntegerField(help_text='거래 월')), ('evidence_type', models.CharField( choices=[ ('BANK_TX', '은행 입출금'), ('INVOICE_PURCHASE', '매입 세금계산서'), ('INVOICE_SALES', '매출 세금계산서'), ('CASH_PURCHASE', '매입 현금영수증'), ('CASH_SALES', '매출 현금영수증'), ('CARD_PURCHASE', '매입 신용카드'), ('CARD_SALES', '매출 신용카드'), ('DELIVERY_ORDER', '배달 주문'), ('PG_DAILY', 'PG 일일매출'), ('PG_RECONCILIATION', 'PG 결제정산'), ], help_text='증빙 종류', max_length=50, )), ('amount_in_krw', models.DecimalField(blank=True, decimal_places=2, help_text='거래 금액 (원화 기준)', max_digits=20, null=True)), ('oversea_amount', models.DecimalField(blank=True, decimal_places=2, help_text='해외 거래 금액', max_digits=20, null=True)), ('currency_code', models.CharField(default='KRW', help_text='통화 코드', max_length=10)), ('account_subject', models.ForeignKey( blank=True, null=True, db_constraint=False, help_text='계정과목', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='accounting.companyaccountsubject' )), ('allra_pg', models.OneToOneField( blank=True, null=True, db_constraint=False, help_text='allra(쇼핑몰) 매출', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.allrapaymentgatewaydaily' )), ('bank_transaction', models.OneToOneField( blank=True, null=True, db_constraint=False, help_text='계좌 입출금', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.banktransaction' )), ('card_purchase', models.OneToOneField( blank=True, null=True, db_constraint=False, help_text='매입 신용카드', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.cardpurchase' )), ('card_sales', models.OneToOneField( blank=True, null=True, db_constraint=False, help_text='매출 신용카드', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.cardsales' )), ('cash_purchase', models.OneToOneField( blank=True, null=True, db_constraint=False, help_text='매입 현금영수증', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.cashpurchase' )), ('cash_sales', models.OneToOneField( blank=True, null=True, db_constraint=False, help_text='매출 현금영수증', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.cashsales' )), ('company', models.ForeignKey( blank=True, null=True, db_constraint=False, on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.company' )), ('delivery_order', models.OneToOneField( blank=True, null=True, db_constraint=False, help_text='배달 주문', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.tilkodeliveryorder' )), ('invoice_purchase', models.OneToOneField( blank=True, null=True, db_constraint=False, help_text='매입 세금계산서', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.invoicepurchase' )), ('invoice_sales', models.OneToOneField( blank=True, null=True, db_constraint=False, help_text='매출 세금계산서', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.invoicesales' )), ('pg', models.OneToOneField( blank=True, null=True, db_constraint=False, help_text='PG 매출', on_delete=django.db.models.deletion.DO_NOTHING, related_name='income_statement', to='evidences.pgreconciliation' )), ], options={ 'verbose_name': '손익계산서', 'db_table': 'finance_income_statement', 'indexes': [models.Index(fields=['company', 'account_subject', 'month'], name='finance_inc_company_a61a79_idx')], }, ), ], ), ]
참고
- PostgreSQL의 Partition Pruning
- PostgreSQL이 쿼리 실행 시 불필요한 파티션을 건너뛰고 필요한 파티션만 스캔하는 최적화 기법.
깔끔하게 코딩하고싶어요