Postgres Data Matching on Amazon RDS: Find Duplicate Records Without Exporting Your Table

Watch the Walkthrough

The video below runs through the entire process against a live Amazon RDS instance, from the connection string to the finished match report. If you would rather read than watch, everything in it is covered below and in the step-by-step RDS documentation.

Why Exact Matching Misses Them

Duplicate records are rarely duplicates in the literal sense. They arrive through mergers, form submissions, imported vendor lists, manual entry, and integrations between systems that each had their own conventions. By the time they land in one RDS table, the same entity may be represented a dozen ways, none of them wrong, none of them identical. We catalogued how far this can go in One Hundred Ways to Say IBM.

The cost is not aesthetic. It shows up as inflated customer counts, revenue split across records that should have been one account, the same household receiving four copies of a mailing, and reports that quietly disagree with each other. We have written about the wider financial impact in the hidden cost of poor data quality, and about why it matters even more once that data feeds a model in the critical role of high-quality data in AI success.

Interzoid's approach is described in Simplifying Entity Resolution with Data Matching APIs. Rather than comparing strings, each value is converted into a similarity key representing the entity behind the text. Values referring to the same company, person, or place produce the same key regardless of spelling, abbreviation, punctuation, or word order. Grouping by that key turns an intractable comparison problem into an ordinary one.

The One Thing That Is Different About RDS

Almost everything in this process is identical to matching against any other PostgreSQL server. There is exactly one place where RDS asks something of you that a serverless Postgres platform does not, and it is responsible for the large majority of failed first attempts.

An RDS instance sits inside a VPC behind a security group that denies all inbound traffic by default. Reaching it from outside AWS requires an inbound rule permitting TCP port 5432. That much is expected. The part that catches people is which address needs permitting.

This produces a distinctive and initially baffling symptom: psql connects perfectly from your machine, proving the instance is running, publicly resolvable, listening, and accepting your credentials, and the wizard reports a connection timeout anyway. Both observations are correct. They are about different source addresses.

The fix is a second inbound rule, type PostgreSQL, protocol TCP, port 5432, with the Interzoid service address as a /32 CIDR block. The current address is published on the service IP addresses page and is shown on the wizard's connection screen. Rules take effect immediately, with no reboot and no downtime, so you can retry the moment you save.

Resist the temptation to use 0.0.0.0/0 to move things along. That makes the listener reachable by every host on the internet, leaving your password as the only control, and Postgres endpoints on the standard port get found by scanners in hours rather than days. If you do open it briefly against sample data, remove the rule when the test is done.

Composing the RDS Connection String

AWS does provide connection strings in the database's tab, and you can assemble one from the values on the instance's Connectivity & security tab:

postgresql://postgres:YOUR-PASSWORD@interzoid-demo.abcdefghijkl.us-east-1.rds.amazonaws.com:5432/interzoiddemo?sslmode=require

• Host: the RDS endpoint, in the form identifier.account-hash.region.rds.amazonaws.com. Copy it rather than typing it.
• Database: the initial database name, which is not the DB instance identifier. These are two different names and it is easy to reach for the wrong one. If the instance was created without an initial database name, only the built-in postgres database exists, and the string has nothing useful to point at.
• SSL: use sslmode=require. PostgreSQL 15 and later instances enforce TLS through the rds.force_ssl parameter, which defaults to on.

One detail worth knowing before it costs you twenty minutes: the connection string the AWS console displays specifies sslmode=verify-full along with a path to global-bundle.pem, Amazon's certificate bundle. If that file is not in the directory you are running from, psql fails immediately with a message about a missing root certificate. It is a client-side error raised before any connection is attempted, so it says nothing about your security group. Either download the bundle, or use require, which still encrypts the connection and is what the wizard's SSL mode setting expects.

Connect With a Read-Only Role

Since the wizard only reads, you can set up a read-only role if you prefer:

CREATE ROLE interzoid_reader LOGIN PASSWORD 'strong-password-here';
GRANT CONNECT ON DATABASE interzoiddemo TO interzoid_reader;
GRANT USAGE ON SCHEMA public TO interzoid_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO interzoid_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO interzoid_reader;

Run these after the tables exist. GRANT SELECT ON ALL TABLES is a snapshot of the tables present at that moment, so a role created too early will connect successfully and then find nothing to read. The ALTER DEFAULT PRIVILEGES statement covers tables created later. Access can be revoked at any time by dropping the role.

How the Matching Wizard Works

With the network open and a connection string in hand, the rest is a guided browser workflow. There is nothing to install and no connector to deploy inside your AWS account.

Every step is populated from your live database. After connecting, the wizard queries the instance for the schemas you can access, then the tables and views in the one you pick, then the columns of the table you select. You never type an object name or look one up in another window, and you cannot select something that is not actually there.

The wizard reads only. It issues SELECT statements against the columns you choose and nothing else. No table is written, no schema object is created, and your RDS instance is left exactly as it was found.

Six Ways to Match

Different data calls for different matching. The wizard offers three single-column functions and three combinations that raise precision by requiring agreement on two fields at once.

Combination functions matter more than they might appear. A large organization may have hundreds of locations sharing one company name, and matching on the name alone collapses them into a single cluster. Requiring the address to agree as well separates the branch in Dallas from the branch in Phoenix while still recognizing that both spell the parent company four different ways.

Reading the Results

Results arrive as clusters. Every record sharing a similarity key with at least one other record is shown, grouped together, with a blank line between clusters. The last column of each row is the key itself, which makes the grouping auditable rather than something you have to take on faith.

IBM Corporation,Technology,d477E1d7sG6dja3hDNsk9P
I.B.M. Corp,Information Technology,d477E1d7sG6dja3hDNsk9P

Microsoft Inc.,Software,k8Rp2mNx4wQjL9vB3cYh7T
Microsoft Corporation,Software,k8Rp2mNx4wQjL9vB3cYh7T
MSFT Corp,Technology,k8Rp2mNx4wQjL9vB3cYh7T

Notice that the category values disagree within a cluster as well. That is the second thing a match report surfaces: not just that these rows are the same company, but that your data says different things about it depending on which row you read.

You choose which columns appear alongside the matched values, so the report can carry the primary keys, account numbers, or region codes your team needs to act on it. Including the primary key is particularly worthwhile on RDS, since it lets you load the report back into a table and join it against the original.

Loading the Report Back Into RDS

If that is your goal, turn off the Matches Only option and leave similarity keys enabled. Every input row then appears in the output with its key appended, not just the rows that landed in a cluster, which gives you a complete one-to-one copy of your source data with one new column. Records that matched nothing still carry a key, so nothing is silently dropped and row counts reconcile.

Once that table exists, fuzzy matching becomes an ordinary SQL operation, and tables that share no common identifier can be joined on the similarity key:

-- Count the records in each match cluster
SELECT similarity_key, COUNT(*) AS record_count
FROM match_results
GROUP BY similarity_key
HAVING COUNT(*) > 1
ORDER BY record_count DESC;

-- Fuzzy join: match customers to prospects on entity
-- similarity rather than on exact text
SELECT c.company, p.company, c.similarity_key
FROM customer_matches c
JOIN prospect_matches p ON c.similarity_key = p.similarity_key;

A single job can process up to 500,000 records, and each record consumes one API credit. The interface is available in 17 languages, selectable from the header menu or through a URL parameter such as postgres-match.interzoid.com/?lang=de.

A Note on Read Replicas and Aurora

A match against a large table is a sustained sequential read. On a busy production instance that competes with application traffic, and there is a simple answer: matching is read-only, so point the connection string at a read replica endpoint instead. Nothing else about the process changes.

The same reasoning applies with more force to Aurora PostgreSQL, which publishes a dedicated reader endpoint alongside the writer, distinguished by -ro in the host name. On a single-instance cluster the reader endpoint resolves to the writer anyway, so using it from the start costs nothing and needs no change later. Aurora is otherwise identical for this purpose, and has its own step-by-step guide.

Where RDS Matching Pays Off

Where Matching Fits in the Workflow

Matching is one step in a sequence, and it works best when the steps around it are in place:

• Before matching, the Data Profiler tells you whether the columns you plan to match on are populated, consistently formatted, and free of placeholder values.
• For files rather than databases, the Match Wizard performs the same matching against CSV and TSV data.
• Snowflake users have a direct equivalent in the Snowflake Data Matching Wizard, which follows the same workflow against a Snowflake warehouse.
• Connecting two datasets whose join values disagree is the job of the Merge Wizard.
• Normalizing company names to their official forms is handled by organization name standardization.
• Filling out sparse records is the role of the Data Enrichment Wizard.
• The broader path from raw data to trustworthy inputs is described in Turn Enterprise Data Into AI-Ready Data.

Every one of these is available through the Interzoid API directory, and the complete set of tools can be launched from Interzoid Central Command.

Frequently Asked Questions

Getting Started

A first match takes about twenty minutes on RDS, most of which is the security group and deciding which table to start with.

1. Register for an Interzoid API key if you do not already have one; trial credits are included.
2. Add an inbound rule to the instance's security group permitting port 5432 from the Interzoid service address.
3. Assemble your connection string from the RDS endpoint, port, database name, and credentials, with sslmode=require.
4. Open the Postgres Data Matching Wizard, enter your API key, choose a matching function, and paste the connection string.
5. Choose the table, assign the match columns, run the match, and download the CSV report. The step-by-step RDS guide covers every option in detail.

A good first table is the one people complain about: the customer list, the contact table, the vendor master. The duplicates are already there, and the only question is how many.