우리가 66ms에 16.8M SIRENE 사업장을 조회하는 방법

Source: Dev.to

위에 제공된 링크에 포함된 전체 텍스트를 알려주시면, 해당 내용을 한국어로 번역해 드리겠습니다. (코드 블록, URL 및 마크다운 형식은 그대로 유지됩니다.)

도전 과제

Our establishment table has 16.8 million rows. Users need to search by:

The name search must handle:

• Partial matches – “Total” should find “TotalEnergies SE”
• Typos – “Miclein” should find “Michelin”
• Accent insensitivity – “Societe Generale” should match “Société Générale”

순진한 접근법: ILIKE

SELECT *
FROM georefer.establishment
WHERE company_name ILIKE '%total%'
LIMIT 25;

EXPLAIN ANALYZE

Seq Scan on establishment
  Filter: (company_name ~~* '%total%')
  Rows Removed by Filter: 16799975
  Planning Time: 0.1ms
  Execution Time: 12,847ms

12.8 초 → 1,680만 행에 대한 전체 순차 스캔. 사용 불가.

Source:

pg_trgm 사용

PostgreSQL의 pg_trgm 확장은 문자열을 3‑문자 시퀀스(트라이그램)로 분할하고, GIN 인덱스를 활용해 유사 문자열을 효율적으로 찾습니다.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_establishment_name_trgm
ON georefer.establishment
USING GIN (company_name gin_trgm_ops);

이제 트라이그램 유사성을 사용할 수 있습니다:

SELECT *,
       similarity(company_name, 'total') AS sim
FROM georefer.establishment
WHERE company_name % 'total'
ORDER BY sim DESC
LIMIT 25;

EXPLAIN ANALYZE

Bitmap Heap Scan on establishment
  Recheck Cond: (company_name % 'total')
  -> Bitmap Index Scan on idx_establishment_name_trgm
       Index Cond: (company_name % 'total')
  Planning Time: 0.3ms
  Execution Time: 66ms

66 ms → 194배 향상 (naïve 접근 방식 대비).

가져오기 전략: 3단계

1,680만 행을 가져오는 일은 간단하지 않습니다. 우리는 3단계 접근 방식을 사용합니다.

1단계 – 스키마 + 스테이징

CREATE TABLE georefer.establishment (
    id               SERIAL PRIMARY KEY,
    siren            VARCHAR(9)  NOT NULL,
    siret            VARCHAR(14) NOT NULL UNIQUE,
    company_name     VARCHAR(255),
    commercial_name  VARCHAR(255),
    legal_form       VARCHAR(10),
    naf_code         VARCHAR(6),
    employee_range   VARCHAR(5),
    postal_code      VARCHAR(5),
    city             VARCHAR(100),
    department_code  VARCHAR(3),
    is_headquarters  BOOLEAN DEFAULT FALSE,
    is_active        BOOLEAN DEFAULT TRUE,
    created_date     DATE,
    last_update      DATE
);

2단계 – 대량 COPY

COPY georefer.establishment
    (siren, siret, company_name, ...)
FROM '/tmp/sirene_active.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

COPY는 배치 INSERT보다 10~50배 빠릅니다. 1,680만 행을 약 8 분 안에 로드합니다.

3단계 – 인덱스 생성

대량 가져오기 후에 인덱스를 생성합니다(미리 만들면 로드가 느려집니다).

-- Exact lookups
CREATE INDEX idx_establishment_siren ON georefer.establishment(siren);
CREATE INDEX idx_establishment_siret ON georefer.establishment(siret);

-- Geographic filtering
CREATE INDEX idx_establishment_postal ON georefer.establishment(postal_code);
CREATE INDEX idx_establishment_dept   ON georefer.establishment(department_code);
CREATE INDEX idx_establishment_city   ON georefer.establishment(city);

-- Fuzzy name search
CREATE INDEX idx_establishment_naf   ON georefer.establishment(naf_code);
CREATE INDEX idx_establishment_name_trgm
    ON georefer.establishment USING GIN (company_name gin_trgm_ops);

결합된 쿼리: 이름 + 지리적 필터

SELECT *,
       similarity(company_name, 'boulangerie') AS sim
FROM georefer.establishment
WHERE company_name % 'boulangerie'
  AND department_code = '75'
  AND is_active = true
ORDER BY sim DESC
LIMIT 25;

Result: 파리의 모든 베이커리를 약 45 ms에, 1,680만 행을 넘어서는 데이터에서도.

API 레이어

Spring Boot 서비스는 REST를 통해 검색 기능을 제공합니다.

# Search by SIREN
curl 'https://georefer.io/geographical_repository/v1/companies?siren=552120222' \
  -H 'X-Georefer-API-Key: YOUR_API_KEY'

# Search by name + department
curl 'https://georefer.io/geographical_repository/v1/companies/search?name=michelin&department_code=63' \
  -H 'X-Georefer-API-Key: YOUR_API_KEY'

샘플 JSON 응답

{
  "success": true,
  "data": [
    {
      "siren": "855200507",
      "siret": "85520050700046",
      "company_name": "MANUFACTURE FRANCAISE DES PNEUMATIQUES MICHELIN",
      "naf_code": "22.11Z",
      "employee_range": "5000+",
      "postal_code": "63000",
      "city": "CLERMONT-FERRAND",
      "is_headquarters": true
    }
  ]
}

성능 요약

숫자는 예시이며, 실제 시간은 하드웨어 및 부하에 따라 달라질 수 있습니다.

개선

Lessons Learned

• Always create indexes after bulk import – creating them before can make the import 10× slower.
• pg_trgm GIN indexes use a lot of disk – our 16.8 M‑row trigram index is ~2.3 GB.
• Set maintenance_work_mem high during index creation – SET maintenance_work_mem = '1GB' cuts index creation time in half.
• COPY beats INSERT every time for bulk loading – use COPY for anything over 10 K rows.

사용해 보기

GEOREFER는 간단한 REST API를 통해 16.8 M SIRENE 사업장을 제공합니다:

• 무료 티어: 100 req/day, 신용카드 필요 없음.
• 문서:
• 가입:

AZMORIS Engineering – “오래 지속되는 소프트웨어”