500M rows doesn't have to be a lot of records. Are the tuples large? If I were to partition those tables, I would partition them on an existing PK field.
Until then, I would:
disable AUTOVACUUM on those tables immediately before the ETL job starts
As I highlighted the records count for these tables which are quite high, would it be a best practice if we change the vacuum and analyze scale factor at the table level?
Also, I am trying to understand if partitioning is required for these tables, or at least for the one which has over 500million records?
We had a performance issue with a SQL that used to complete in a few milliseconds, was taking over 14seconds. We had to run analyze on 3 tables to get the idle performance back.
When the performance was not optimal, we noticed sequential scans even with indexes created.
PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit
One more information is that we noticed this started happening (in the destination) after an ETL job completed the load (regular load). Just wanted to know if any follow up actions we should do after such data loads, eg., analyze or vacuum. We do have autovacuum on, with default values.
Yes, you should run "VACUUM ANALYZE" after running a batch. autovacuum could be not fast enough to do it itself before you start querying the new data.
