Question 1

Why move Redshift results into Postgres?

Accepted Answer

Because the systems that need the answer usually cannot query a warehouse. An internal admin tool, a service deciding what a customer can access, or a customer facing view all read from the application database, and pointing them at Redshift is either too slow or not allowed. Landing the modelled result in Postgres puts it where those systems already look, refreshed on a schedule rather than by a script somebody maintains by hand.

Question 2

Will syncing compete with our analytics workload?

Accepted Answer

It can, and that is the thing to manage on a cluster rather than a per query engine. Sync queries draw on the same finite compute your analysts use, so a wide read on a tight schedule shows up as contention rather than as a line on a bill. Polytomic does not set workload management queues, so the queries inherit whatever the connecting user has: putting that user in an appropriate queue is the single most effective control. Beyond that, use a tracking field, select only mapped columns, and run large initial scans away from peak hours.

Question 3

How does incremental reading work?

Accepted Answer

Through a tracking field such as an updated timestamp, with the first run reading in full and later runs resuming from the saved boundary. One detail specific to Redshift is worth knowing before you start: a primary key is required for incremental identity and merging, and Redshift key metadata is not detected automatically, so you will usually need to nominate the key yourself. Without one, runs fall back to full scans. Tracking field reads also do not detect deletes, since a removed row simply stops appearing.

Question 4

How does the write into Postgres behave?

Accepted Answer

Model syncs write directly with no staging table, batching inserts and issuing updates per row, and a failed batch is retried individually so errors point at specific records. Bulk table replication stages into a scratch table and merges instead, so readers never see a half written result. Modes are create, update, update or create, and replace, and replace clears the entire target table rather than removing only the rows missing from your query.

Question 5

How do I connect Redshift to PostgreSQL?

Accepted Answer

Connect Redshift with cluster details, credentials and an S3 staging bucket, using an SSH tunnel if the cluster is not publicly reachable, and connect PostgreSQL with host, port, database and credentials. Then build a model over the Redshift table or query you want, map its fields onto the destination table, choose a write mode and identity field, and set a schedule. The destination table is created if it does not already exist.

Connect Redshift with PostgreSQL

Connect Redshift and PostgreSQL with Polytomic

Light on cluster time

Tables created for you

Private clusters reachable

Trusted by businesses worldwide

Step-by-step integration guide

Connect Redshift in Polytomic

Connect PostgreSQL in Polytomic

Create a sync between Redshift and PostgreSQL

Map fields and enable the sync

Frequently asked questions

The sync and ETL platform that does it all

Powerful data sync

Advanced analytics

Enterprise security

Easy setup

Ready to connect Redshift with PostgreSQL?