First run always lags behind about pgreplay HOT 3 CLOSED

I cannot prove that pgreplay is not the problem here, but it doesn't do anything different if you run it a second time with the same log file, so I would search for the problem on the database side.

My best guess is still caching. Try to reboot the database server machine between the runs (or drop the kernel page cache in a less invasive fashion) and see if you can still observe the increase in performance. If not, that will prove that the difference is on the server side. Use pg_stat_statements to determine which statements are slow the first time and fast the second time. Or you can run with auto_explain enabled and auto_explain.log_analyze = on and auto_explain.log_buffers = on, then you can see the execution plans of the slow queries in the log. These plans will help to determine the cause of the problem. Note that auto_explain slows down processing!

from pgreplay.

Hi @laurenz
So, I've finally found the problem. I forgot to mention that I'm using an RDS restored from a snapshot.

Today I learned: "If you access data that hasn't been loaded yet, the DB instance immediately downloads the requested data from Amazon S3, and then continues loading the rest of the data in the background."

AWS calls this "lazy load", and it's documented here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html

So basically, I was using the RDS from the moment it was "available", but all my queries were waiting a long time for the data to be fetched from S3 the first time they executed.
I've solved this, by importing the SQL dump the "good old way" with psql

from pgreplay.

That's interesting information.
Thanks for sharing this!

from pgreplay.