PostgreSQL Bulk Jsonb Insert FAQ & Answers

COPY command with JSONB columns (fastest: 50,000-100,000 rows/sec). Bypasses query parsing, uses binary protocol, minimal WAL overhead. Example: COPY events(user_id, data) FROM 'events.csv' WITH (FORMAT csv). For application code: psycopg3 copy_expert() or pg-copy-streams (Node.js).

Use multi-row INSERT with batching: INSERT INTO events(data) VALUES ($1), ($2), ... ($1000). Optimal batch size: 500-1000 rows (balances parsing overhead vs lock duration). Wrap in transaction. Performance: 10,000-30,000 rows/sec. Disable indexes/triggers during bulk load for 3-5x speedup.

Key settings: (1) maintenance_work_mem=2GB (for index building), (2) synchronous_commit=off (disable during bulk load, 2-3x faster), (3) wal_buffers=64MB, (4) checkpoint_timeout=30min, (5) max_wal_size=8GB. Temporary: SET LOCAL synchronous_commit = OFF within transaction. Restore after bulk load.

COPY command with JSONB columns (fastest: 50,000-100,000 rows/sec). Bypasses query parsing, uses binary protocol, minimal WAL overhead. Example: COPY events(user_id, data) FROM 'events.csv' WITH (FORMAT csv). For application code: psycopg3 copy_expert() or pg-copy-streams (Node.js).

Use multi-row INSERT with batching: INSERT INTO events(data) VALUES ($1), ($2), ... ($1000). Optimal batch size: 500-1000 rows (balances parsing overhead vs lock duration). Wrap in transaction. Performance: 10,000-30,000 rows/sec. Disable indexes/triggers during bulk load for 3-5x speedup.

Key settings: (1) maintenance_work_mem=2GB (for index building), (2) synchronous_commit=off (disable during bulk load, 2-3x faster), (3) wal_buffers=64MB, (4) checkpoint_timeout=30min, (5) max_wal_size=8GB. Temporary: SET LOCAL synchronous_commit = OFF within transaction. Restore after bulk load.

UNLOGGED tables skip WAL writes (5-10x faster inserts). Use for staging: (1) Create UNLOGGED table, (2) Bulk insert JSONB data, (3) Convert to logged: ALTER TABLE events SET LOGGED, (4) Copy to production table. Warning: UNLOGGED tables lost on crash. Not for production data.

Combine INSERT with ON CONFLICT for upserts: INSERT INTO users(id, data) VALUES ($1, $2) ON CONFLICT (id) DO UPDATE SET data = EXCLUDED.data || users.data. Use || operator to merge JSONB. For bulk: multi-row INSERT with ON CONFLICT. Performance: 5,000-15,000 upserts/sec.

Strategies: (1) CHECK constraint with jsonb_typeof() or custom function, (2) Trigger with JSON schema validation (pg_jsonschema extension), (3) Pre-validate in application before insert. Trade-off: validation adds 20-40% overhead. For bulk: validate sample, use CHECK constraints for critical fields only.

UNLOGGED tables skip WAL writes (5-10x faster inserts). Use for staging: (1) Create UNLOGGED table, (2) Bulk insert JSONB data, (3) Convert to logged: ALTER TABLE events SET LOGGED, (4) Copy to production table. Warning: UNLOGGED tables lost on crash. Not for production data.

Combine INSERT with ON CONFLICT for upserts: INSERT INTO users(id, data) VALUES ($1, $2) ON CONFLICT (id) DO UPDATE SET data = EXCLUDED.data || users.data. Use || operator to merge JSONB. For bulk: multi-row INSERT with ON CONFLICT. Performance: 5,000-15,000 upserts/sec.

Strategies: (1) CHECK constraint with jsonb_typeof() or custom function, (2) Trigger with JSON schema validation (pg_jsonschema extension), (3) Pre-validate in application before insert. Trade-off: validation adds 20-40% overhead. For bulk: validate sample, use CHECK constraints for critical fields only.

Aggregate rows into JSONB array, insert single row, then unnest. Example: INSERT INTO events(data) SELECT jsonb_agg(row_to_json(t)) FROM temp_table t; Then: INSERT INTO final SELECT jsonb_array_elements(data) FROM events. Useful for API batch processing. Performance: moderate (parsing overhead).

PostgreSQL TOAST automatically compresses JSONB >2KB. Optimization: (1) Use jsonb_strip_nulls() to remove null fields before insert (reduces size 10-30%), (2) Normalize repeated data to separate columns, (3) Use jsonb_set() for partial updates vs full replacement. Compression: LZ family (configurable via default_toast_compression).

Best practices: (1) Use transactions with SAVEPOINT for partial rollback, (2) Log failed rows to error table, (3) ON CONFLICT DO NOTHING to skip duplicates, (4) Validate JSONB structure before INSERT (jsonb_typeof()), (5) Set statement_timeout to prevent runaway queries. For COPY: use ON_ERROR IGNORE (PostgreSQL 14+).

Aggregate rows into JSONB array, insert single row, then unnest. Example: INSERT INTO events(data) SELECT jsonb_agg(row_to_json(t)) FROM temp_table t; Then: INSERT INTO final SELECT jsonb_array_elements(data) FROM events. Useful for API batch processing. Performance: moderate (parsing overhead).

PostgreSQL TOAST automatically compresses JSONB >2KB. Optimization: (1) Use jsonb_strip_nulls() to remove null fields before insert (reduces size 10-30%), (2) Normalize repeated data to separate columns, (3) Use jsonb_set() for partial updates vs full replacement. Compression: LZ family (configurable via default_toast_compression).

Best practices: (1) Use transactions with SAVEPOINT for partial rollback, (2) Log failed rows to error table, (3) ON CONFLICT DO NOTHING to skip duplicates, (4) Validate JSONB structure before INSERT (jsonb_typeof()), (5) Set statement_timeout to prevent runaway queries. For COPY: use ON_ERROR IGNORE (PostgreSQL 14+).

Yes for large bulk loads (>100,000 rows). Drop GIN indexes on JSONB before insert, recreate after. Index maintenance during insert: 40-60% overhead. Rebuild after: CREATE INDEX CONCURRENTLY (allows reads during build). For smaller batches (<10,000 rows), keep indexes.

Key metrics: (1) WAL generation rate (pg_stat_wal), (2) Buffer cache hit ratio (should drop during bulk load), (3) Index bloat (pg_stat_user_indexes), (4) Checkpoint activity (pg_stat_bgwriter), (5) Lock waits (pg_locks). Use pg_stat_progress_copy for COPY monitoring. Auto-vacuum disabled during load recommended.

Yes for large bulk loads (>100,000 rows). Drop GIN indexes on JSONB before insert, recreate after. Index maintenance during insert: 40-60% overhead. Rebuild after: CREATE INDEX CONCURRENTLY (allows reads during build). For smaller batches (<10,000 rows), keep indexes.

Key metrics: (1) WAL generation rate (pg_stat_wal), (2) Buffer cache hit ratio (should drop during bulk load), (3) Index bloat (pg_stat_user_indexes), (4) Checkpoint activity (pg_stat_bgwriter), (5) Lock waits (pg_locks). Use pg_stat_progress_copy for COPY monitoring. Auto-vacuum disabled during load recommended.

Strategies: (1) Partition table by hash/range, insert into partitions concurrently (4-8 connections), (2) Use connection pooling (pgBouncer), (3) Foreign data wrapper (postgres_fdw) with parallel copy. Performance: near-linear scaling up to 8 connections, diminishing returns beyond. Watch for lock contention on indexes.

Strategies: (1) Partition table by hash/range, insert into partitions concurrently (4-8 connections), (2) Use connection pooling (pgBouncer), (3) Foreign data wrapper (postgres_fdw) with parallel copy. Performance: near-linear scaling up to 8 connections, diminishing returns beyond. Watch for lock contention on indexes.