- Newest
- Most votes
- Most comments
If a PK is defined then the PK columns are used to look up rows in target and when matched then update else insert from source. If no PK is defined then, as you have seen, all columns from source need to be used, which can be more resource intensive operation.
You can use the AWS Schema Conversion Tool to with DMS to help make this mapping. If no direct conversion is possible, AWS SCT will give you an assessment report with a list of possible actions for you might take take. With SCT, you can manage your keys, map data types , and perform manual conversions. https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Converting.DW.html
I know I can use define-primary-key rule-action to define a primary key in a transformation rule. I’m asking how dms acts when I do that.
Relevant content
- asked a month ago
AWS OFFICIALUpdated 7 months ago- AWS OFFICIALUpdated 6 months ago
- AWS OFFICIALUpdated 2 years ago
But what if the source does not have a primary key but you add a primary key to the target in a dms transformation? The docs for redshift say source and target need primary key, the docs for postgres only mentioned target.
If the source does not have a primary key but you add a primary key to the target in a DMS transformation then the PK you defined will be used during updates on target table.
For large tables it is highly recommended to have PK. For small tables not having PK could be okay but a good OLTP database design always includes PK for each table.
DMS will still proceed without PK's but you will see slow performance especially if the source data is changing frequently.
@MilindOke, thanks for confirming it will act that way with postgres as a target. will it behave that way with redshift as a target? The docs for redshift as target say BOTH source and target need a primary key.