+1 (726) 227-3027

Loading PostgreSQL from Talend: COPY, Staging Tables, and Upserts That Rerun Safely

PostgreSQL is the target we see most often once a Talend estate leaves the cloud warehouse conversation: operational data stores, reporting replicas, application databases and pgvector side projects. We already cover Snowflake, Redshift and BigQuery. This tutorial fills the Postgres gap: how to load it fast, and how to make the load safe to rerun.

Why row-by-row inserts are the wrong default

A tPostgresqlOutput with "Insert" and a commit every 10,000 rows works, but each row is a round trip. Past a few million rows you want Postgres's COPY protocol, which streams rows in a single statement. Talend exposes it in two ways:

  • tPostgresqlOutputBulkExec: writes a temporary delimited file, then runs COPY ... FROM for you. Simple, but the file must be reachable by the database server unless you use the client-side variant, so check where your job runs relative to the database.
  • tPostgresqlOutputBulk + tPostgresqlBulkExec: the same thing split into two components, so you can keep, inspect or compress the file between steps. Prefer this when you need an audit copy of what was loaded.

On managed Postgres (RDS, Cloud SQL, Azure Database for PostgreSQL) you cannot place files on the server, so confirm that the bulk component in your Talend version uses client-side \copy semantics. If it does not, fall back to tDBOutput with batch mode enabled (see below) or stage through the cloud's own import feature.

Pattern: stage, then merge

Never load straight into the target table. Load into a staging table and then apply one set-based statement. That gives you reruns, deduplication and a single transaction around the change.

create table if not exists stg.orders (like public.orders including defaults);
create unique index if not exists orders_pk on public.orders (order_id);

Job layout:

  1. tDBRow: truncate table stg.orders;
  2. Source component -> tMap -> bulk load into stg.orders.
  3. On OnSubjobOk, a tDBRow runs the upsert.
insert into public.orders as t (order_id, customer_id, status, amount, updated_at)
select order_id, customer_id, status, amount, updated_at
from (
  select distinct on (order_id) *
  from stg.orders
  order by order_id, updated_at desc
) s
on conflict (order_id) do update
  set customer_id = excluded.customer_id,
      status      = excluded.status,
      amount      = excluded.amount,
      updated_at  = excluded.updated_at
where t.updated_at < excluded.updated_at;

Three details matter here:

  • distinct on first. ON CONFLICT DO UPDATE raises an error if the same key appears twice in a single statement. Collapse duplicates in staging before the upsert.
  • The where clause stops an old replayed batch from overwriting newer data, which is what makes reruns and out-of-order files safe.
  • PostgreSQL 15 and later also support MERGE. It is fine for the same job, but ON CONFLICT is available on every version you are likely to meet and is simpler when you have a unique key.

When you must use tDBOutput

For smaller volumes, or when the bulk components do not fit your environment, use tPostgresqlOutput or tDBOutput configured for performance:

  • Enable Use batch and set a batch size in the low thousands.
  • Set the JDBC parameter reWriteBatchedInserts=true under Additional JDBC Parameters. The PostgreSQL driver then rewrites batched inserts into multi-row statements, which often gives a large speedup for no other change.
  • Commit every N rows, not every row, and keep the commit interval at least as large as the batch size.
  • Use Action on data: Insert into staging only, and do the upsert in SQL as above. The built-in "Insert or update" action issues a statement per row and is the slowest option.

Types that bite

  • Timestamps. Decide between timestamp and timestamptz per column and set the Talend schema to match. Loading local-time strings into timestamptz silently applies the database session time zone.
  • Numerics. Map numeric to BigDecimal, not Double, for money. Set precision and scale explicitly in the schema.
  • Text with delimiters and newlines. In the bulk file settings, use CSV mode with a proper quote and escape character rather than a bare delimiter, or one embedded newline will shift every column after it.
  • Nulls. Make sure the null marker in the bulk file is distinct from an empty string, or you will convert blanks to NULL (or the reverse) without noticing.

Keep the load from hurting the database

  • Indexes. For a full reload of a large table, drop secondary indexes (or load into a fresh table) and rebuild afterwards. For incremental loads, leave them in place and keep batches modest.
  • Statistics. Run analyze public.orders; from a final tDBRow after a big load so the planner does not work from stale statistics.
  • Bloat. Frequent upserts leave dead tuples. Watch autovacuum on hot tables and consider a lower autovacuum_vacuum_scale_factor for them.
  • Locks. A long truncate or alter on the target blocks readers. Keep truncates on staging only.
  • Connection count. Parallelized jobs open one connection per component. Use a shared connection (tDBConnection plus "Use an existing connection") and cap parallel subjobs, or put PgBouncer in front of the database.

Verify the load

End the job with a reconciliation step rather than trusting the component's row counter:

select count(*) as staged,
       count(*) filter (where order_id is null) as null_keys
from stg.orders;

Fail the job through tDie when null_keys > 0 or the staged count is zero, and write the counts to your load-audit table (the pattern in Talend and dbt Together). Pair this with the structure in Error Handling and Observability for Talend Jobs so failures are visible.

Checklist

  1. Load into staging with COPY-based bulk components where the environment allows.
  2. Deduplicate in staging, then upsert with ON CONFLICT (or MERGE on 15+).
  3. Guard updates with a timestamp or version comparison.
  4. Match types explicitly, especially timestamps and numerics.
  5. Analyze after large loads, and reconcile counts before declaring success.

If you are moving Postgres-bound jobs from Talend Open Studio or tuning a slow nightly load, get in touch. We have done this on estates of every size.