60K updates to RDS Postgres - performance challenge
A customer is wanting to process approx 60,000 updates to a table in their RDS Postgres instance and experiencing performance issues in testing meaning that the update would take upwards of 30 hours to complete. The updates are to read usernames (email address) from a single table and change them from mixed case to lower case and write them back to the database.
The process to do the updates is a bash script running in a container, and each update is taking a few minutes to process. RDS instance are PostrGres (not Aurora) and running db.m5.large instance.
Whilst I investigate with the customer (getting them to look at metrics for the RDS instance, etc) I am looking for thoughts on likely bottlenecks. One obvious thought is to increase the instance size for the duration of the update and then reduce once completed.
Any other thoughts on PostGres tuning that may help?
- 최신
- 최다 투표
- 가장 많은 댓글
Since the customer is updating a value in a JSON doc, just use the built in PostgreSQL JSON functions
postgres=> CREATE TABLE test_json (a int, b jsonb);
CREATE TABLE
postgres=> INSERT INTO test_json
postgres-> VALUES (1, '{"key1": "abc"}'), (2, '{"key1": "xyz"}');
INSERT 0 2
postgres=> UPDATE test_json
postgres-> SET b = jsonb_set(b, '{key1}', (upper((b->'key1')::text))::jsonb);
UPDATE 2
postgres=> SELECT * FROM test_json;
a | b
---+-----------------
1 | {"key1": "ABC"}
2 | {"key1": "XYZ"}
(2 rows)
