Question

Executing vacuum in PostgreSQL via SQLExecutor

  • 20 June 2021
  • 2 replies
  • 21 views
dbryantgeo
Rank
Badge +6

I have a workbench that imports a lot of data into PostgreSQL (~90m records across 3 tables, 1 containing spatial data) and we've identified a possible need to vacuum the tables.

 

The data is "cleansed" post-import via a SQLExecutor that deletes data, creates data if necessary. However, we're unable to vacuum analyze (Analyze is fine) the data once that occurs as it appears that the SQLExecutor wraps commands in a Transaction Block - which means we cannot Vacuum.

 

Any ideas on how we can perform vacuum via the SQLExecutor or have another strategy we can utilise? I am unsure whether using the writer's post-write execution of SQL in a parameter is performed before or after the VacuumAnalyze option that FME provides.

 

I've seen some information around setting Autocommit to on/off within the SQLExecutor but this does not work in the newer versions of PostgreSQL.

 

We are using an Azure managed database

2 replies

steveatsafe
Rank
Badge +11

@dbryantgeo​ 

Thanks for sharing this. The SQL Statement post should run after all data has been sent to the database so that should work. Where are you attempting to use the SQLExecuter now? Is that after a FeatureWriter?

SteveatSafe
dbryantgeo
Rank
Badge +6

LandRegImport@steveatsafe​ 

Yes that's exactly what's happening at the moment.

 

The above might give a bit more visual context.

 

If we were to "merge" the writers into a single writer (the reason they're split even though the data ends up in the same DB was really an attempt at improving the speed of import, and that only 1 of the datasets is spatial).

 

At the end of the "CleanseImportedData" SQL we Analyze the imported data tables, as above the cleanse is deleting and creating records in those tables where necessary.

 

I know that if we were to merge the two writers I can use the SQL after write and dump the Cleanse SQL into that, but I wasn't necessarily sure if the VacuumAnalyze parameter triggers before or after that SQL after write, if its before then we gain nothing, if its after then we might gain something.

Reply


Community Stats

28,724
Posts
109,518
Replies
36,564
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.