Export Register Data

Staff Portal can export the current register result, or a row selection, as a downloadable file hosted on S3.

The export file would include the register being browsed and every related parent and child register in that register's master_register_id tree. XLSX uses one worksheet per register. ZIP_CSV uses one CSV per register inside a zip.

This page is the shipped pipeline in registry-platform (Staff UI, Staff API, Celery, Helm). The original design note is Exporting to an XLS.

export_format is only XLSX or ZIP_CSV. XLSX is slower to write than ZIP of CSVs.

Staff Portal

On Browse Register, Export Records and Export History sit in the more menu.

Control	Behaviour
Scope: all / filtered	No checkboxes. The worker replays the current search, filters, and sort.
Scope: selected	Checkboxes accumulated across pages. The worker exports those main-register IDs, plus related rows.
Format	XLSX (default) or ZIP CSV (ZIP_CSV)
History panel	This user's jobs only. Polls every 10 seconds while any job is PENDING or PROCESSING. Download opens the MinIO presigned URL.
Retry in History	Enqueues a new job with the current Browse Register filters and scope all. It does not replay the original selection.

Staff UI lives in registry-platform ui/staff-ui, not in the separate staff-portal repo:

• Modal and queue: src/features/export/register/

• Browse Register wiring: src/app/[locale]/register/[type]/page.tsx

• BFF: export-register-records/route.ts, get-export-queue-records/route.ts

The BFF posts to Staff API POST /register-data/export_register_records and POST /register-data/get_export_queue_records.

Both API endpoints require register:export permission.

The list API returns rows where requested_by is the signed-in Keycloak sub and queued_at is within exportQueueVisibilityDays (default 2 days). Older rows stay in g2p_register_export_data_queue; they disappear from History. Presigned URLs expire separately (exportPresignedUrlExpiryHours, default 48 hours). A completed job can still list with no Download button.

Pipeline

Piece	Code
Enqueue / list	g2p_register_data_controller.py, g2p_register_export_service.py
Queue table	g2p_register_export_data_queue.py
Beat	register_export_beat_producer.py
Worker	register_export_worker.py
Hierarchy, filters, sort	helpers/register_export.py
Helm knobs	helm/openg2p-registry/values.yaml (global.export*)

Enqueue snapshots the caller's data policies onto the queue row. The worker reapplies them on every register it touches, so the file cannot contain rows that Browse Register would hide.

Selection vs search

Non-empty selected_internal_record_ids means mode SELECTED. Empty list or omit means mode SEARCH_FILTER. The worker does not treat Browse Register current_page / page_size as the export window. Those fields only carry search_text, filter_by, and sort_by.

If sort_by is empty, sort is last_approved_at DESC, then internal_record_id ASC (same default as Browse Register). A custom Browse Register sort is replayed on SEARCH_FILTER exports.

Unless filter_by already sets record_status, the worker keeps record_status = ACTIVE on the main register. Related parent and child rows are always restricted to ACTIVE.

Related registers

The worker walks master_register_id up to the root, then breadth-first down from the browsed register only. Ancestor sheets are included. Siblings of the browsed register are not. Exporting a child register does not pull other children of its parent.

Related rows for each main-register batch are loaded with link_internal_record_id IN (...) (children) or parent internal_record_id IN (...) (ancestors). Duplicate related rows across batches are skipped.

Output

export_format	Object key	Notes
XLSX	{prefix}/{export_id}.xlsx	One worksheet per register. openpyxl write-only. The worker raises if a sheet would exceed 1,048,575 data rows.
ZIP_CSV	{prefix}/{export_id}.zip	One CSV per register, then zip. Prefer this for large dumps.

Bucket name is the enum value export-files. It is not a Helm key. Object prefix defaults to register-exports/.

Each worker attempt sets last_processed_offset = 0 before paging, and again on failure. The in-loop update of last_processed_offset is progress for operators, not a resume checkpoint. After exportWorkerMaxAttempts (default 3) the row is FAILED. The temp file is discarded, so every retry rebuilds the file from the start.

Configuration

Helm global keys in helm/openg2p-registry/values.yaml:

Helm	Default	Used by	Role
exportBatchSize	500	Staff API REGISTRY_STAFF_PORTAL_API_EXPORT_BATCH_SIZE	Written onto the queue row at enqueue. This is the batch size the worker uses.
same		Celery worker REGISTRY_CELERY_WORKERS_EXPORT_BATCH_SIZE	Fallback only if the row's batch_size is missing or 0.
exportQueueVisibilityDays	2	Staff API	History list cutoff
exportPresignedUrlExpiryHours	48	Celery worker	Download URL lifetime
exportFilesPrefix	register-exports/	Celery worker	MinIO key prefix
exportBeatProducerFrequency	20 (seconds)	Celery beat	How often PENDING rows are claimed
exportNoOfTasksToProcess	5	Celery beat	Max jobs claimed per tick
exportWorkerMaxAttempts	3	Celery worker	Retry ceiling

Changing exportBatchSize does not rewrite in-flight rows. Check g2p_register_export_data_queue.batch_size for the job you are timing.

Raising beat frequency or Celery replica count will not speed a single large dump. One export is one task. Beat only bounds how long a new job sits in PENDING (about one tick). REGISTRY_CELERY_WORKERS_BATCH_SIZE is ingest, not export.

Speeding up large exports

Some pointers to follow for speeding up large exports run regularly by operators

A SEARCH_FILTER export, pages the main register with OFFSET / LIMIT, then loads related rows for that batch. Wall time is usually a mix of:

• Postgres walking later OFFSET pages (and sorting if there is no matching index)

• Related-register IN (...) lookups per batch

• Writing the file in Python (openpyxl for XLSX is much more expensive than CSV)

Format and batch size

Use ZIP_CSV for full-register or otherwise large dumps. XLSX will stay slower even when the SQL plan is good.

Raise exportBatchSize (for example 2000 instead of 500) so the worker makes fewer OFFSET round-trips. The value is snapshotted onto the queue row at enqueue. Going much higher increases celery-worker RSS (helm chart memory limit is 2560Mi).

Give the celery-worker pod a real CPU request if the writer is the bottleneck. The chart default is 100m, and the ZIP/XLSX loop is Python.

Create indexes for the operation you regularly run

The worker's default SEARCH_FILTER predicate (no search, no extra filters) is:

WHERE record_status = 'ACTIVE'
ORDER BY last_approved_at DESC, internal_record_id
OFFSET n LIMIT batch_size

The platform declares a partial index for that default on every concrete register table (g2p_register.py):

idx_<tablename>_export_active
  (last_approved_at DESC, internal_record_id)
  WHERE record_status = 'ACTIVE'

SQLAlchemy create_all / create_migrate() will not add this index to tables that already exist. Create it on a live database with CREATE INDEX CONCURRENTLY (see below).

That index only helps exports that match it. For example, if operators mostly export INACTIVE, ARCHIVED, another filter_by on record_status, or a non-default Browse Register sort, add your own index that matches that WHERE and ORDER BY. The platform will not invent those.

Examples (replace <register_table> with the physical table, such as g2p_register_individuals):

Default ACTIVE export, if the table predated the platform index:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_<register_table>_export_active
ON <register_table> (last_approved_at DESC, internal_record_id)
WHERE record_status = 'ACTIVE';

Mostly exporting INACTIVE with the same default sort:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_<register_table>_export_inactive
ON <register_table> (last_approved_at DESC, internal_record_id)
WHERE record_status = 'INACTIVE';

A custom sort, for example created_at descending, for ACTIVE rows:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_<register_table>_export_active_created
ON <register_table> (created_at DESC, internal_record_id)
WHERE record_status = 'ACTIVE';

CONCURRENTLY avoids a long write lock. It cannot run inside a transaction. Keep one index per real export shape. Duplicate partial indexes on the same columns and predicate only cost writes.

Confirm with EXPLAIN (ANALYZE, BUFFERS) using the same WHERE / ORDER BY / OFFSET / LIMIT as the worker, including a late offset (not only offset 0):

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM <register_table>
WHERE record_status = 'ACTIVE'   -- or INACTIVE, or your filter
ORDER BY last_approved_at DESC, internal_record_id
OFFSET <n> LIMIT <batch_size>;

You want an index scan (ideally index-only) on the matching export index, not a sequential scan plus sort.

Vacuum

After creating an index, and after bulk loads or large updates, run:

VACUUM (ANALYZE) <register_table>;

Index-only scans need an up-to-date visibility map. If EXPLAIN shows an index-only scan with a large Heap Fetches count, vacuum again. Stale statistics also make the planner skip a perfectly good partial index.

Configure work_mem

This is a PostgreSQL setting, not a Helm env var.

If EXPLAIN shows Sort Method: external merge (disk spill), the sort does not fit in work_mem (often 4MB by default). Raise it until that sort becomes quicksort in memory. A starting point for large register sorts is 32MB. Read the Memory: figure in EXPLAIN rather than guessing.

Raising work_mem does not pick an index. A predicate the planner cannot use (for example search_text ILIKE '%%' on an old worker) still seq-scans.

Set it on the registry database (Helm global.registryDB) or on the role celery uses, not as a huge ALTER SYSTEM default:

ALTER DATABASE <registryDB> SET work_mem = '32MB';

Existing sessions keep the old value. Bounce celery-worker so new connections pick it up. work_mem is per sort/hash operation, per query. Do not set it to hundreds of MB globally.

What will not help

• Beat frequency and celery-worker replica count, for one in-flight dump

• Changing only the worker env REGISTRY_CELERY_WORKERS_EXPORT_BATCH_SIZE after the job is already queued (the row already has batch_size)

• An ACTIVE partial index when the export filters INACTIVE or sorts on a different column

Example metrics for NSR Individual exports

Tested on national-social-registry installation of registry-platform with ~250,000 individual records and ~60,000 households with an approximate of 2-5 records per child table

National Social Registry. Browse Register Individual, blank search, SEARCH_FILTER, record_status = ACTIVE. Main table 258,984 rows on the tuned run. File also includes Household plus 7 Individual child tables (9 sheets). Celery worker CPU request 100m.

Setup	Format	Wall clock	Main-row throughput
exportBatchSize=500, no DB tuning	XLSX	40 to 50 min	~90 rows/s
exportBatchSize=500, no DB tuning	ZIP_CSV	~23 min	~180 rows/s
exportBatchSize=2000, no DB tuning	XLSX	~24 to 26 min	~170 rows/s
exportBatchSize=2000, no DB tuning	ZIP_CSV	~10 min	~420 rows/s
exportBatchSize=2000 plus index, vacuum, no blank ILIKE	XLSX	~22 min	~200 rows/s
exportBatchSize=2000 plus index, vacuum, no blank ILIKE	ZIP_CSV	6 min 42 sec	~640 rows/s

Main-register page, OFFSET n LIMIT 2000:

Query	Time
OFFSET 0, no ILIKE	~3.6 ms, index scan
OFFSET 200000 with search_text ILIKE '%%'	~1.5 to 1.8 s, seq scan + disk sort
OFFSET 200000 after the steps below	~98 ms, index-only, Heap Fetches: 0

What produced the 6 min 42 sec / ~22 min pair:

Change	Value
exportBatchSize	2000 (was 500)
Blank-search ILIKE '%%'	omitted in the worker
Partial index	idx_g2p_register_individuals_export_active on (last_approved_at DESC, internal_record_id) WHERE record_status = 'ACTIVE', CREATE INDEX CONCURRENTLY
Vacuum	VACUUM (ANALYZE) g2p_register_individuals
work_mem	ALTER DATABASE <registryDB> SET work_mem = '32MB', then bounce celery-worker

Index, vacuum, and skipping ILIKE saved about 3 to 4 minutes on both formats (SQL). The remaining XLSX vs ZIP gap (~15 min) is openpyxl. Beat frequency and extra celery replicas were not used.