CODEWITHSUNDEEP
postgresqlamazon-rdsdatabase-performanceread-write
elvingt
elvingt

Reputation: 161

Slow bulk read from Postgres Read replica while updating the rows we read

We have on RDS a main Postgres server and a read replica. We constantly write and update new data for the last couple of days.

Reading from the read-replica works fine when looking at older data but when trying to read from the last couple of days, where we keep updating the data on the main server, is painfully slow.

Queries that take 2-3 minutes on old data can timeout after 20 minutes when querying data from the last day or two.

Looking at the monitors like CPU I don't see any extra load on the read replica. Is there a solution for this?

Upvotes: 0

Views: 1104

Answers (2)

elvingt
elvingt

Reputation: 161

As suggested, the problem was bloat.

When you update a record in an ACID database the database creates a new version of the record with the new updated record.

After the update you end with a "dead record" (AKA dead tuple)

Once in a while the database will do autovacuum and clean the table from the dead tuples.

Usually the autovacuum should be fine but if your table is really large and updated often you should consider changing the autovacuum analysis and size to be more aggressive.

Upvotes: 0

jjanes
jjanes

Reputation: 44137

You are accessing over 65 buffers for ever 1 visible row found in the index scan (and over 500 buffers for each row which is returned by the index scan, since 90% are filtered out by the mmsi criterion).

One issue is that your index is not as well selective as it could be. If you had the index on (day, mmsi) rather than just (day) it should be about 10 times faster.

But it also looks like you have a massive amount of bloat.

You are probably not vacuuming the table often enough. With your described UPDATE pattern, all the vacuum needs are accumulating in the newest data, but the activity counters are evaluated based on the full table size, so autovacuum is not done often enough to suit the needs of the new data. You could lower the scale factor for this table:

alter table simplified_blips set (autovacuum_vacuum_scale_factor = 0.01)

Or if you partition the data based on "day", then the partitions for newer days will naturally get vacuumed more often because the occurrence of updates will be judged against the size of each partition, it won't get diluted out by the size of all the older inactive partitions. Also, each vacuum run will take less work, as it won't have to scan all of the indexes of the entire table, just the indexes of the active partitions.

Upvotes: 1

Related Questions