Postgresql – large number of tables and postgres query performace

Two hints for you:

1. The KEY uid is redundant, because it is covered by the PRIMARY KEY

2. 40,000 rows at a time might make for too large a transaction. Although these are very small rows (two INTs) this may cause the transaction to go to disk, depending on your settings. I usually go with around 1,000 rows at a time (I go as low as 100 and as high as 10,000). Please try doing 40 * 1,000 and see if this works better for you than 1 * 40,000

Few basic points could alleviate your fear.

1. Running autovaccum will eventually improve the performance of queries. It is not something to harm the performance. This is because autovaccum does the garbage collection of dead tuples in table. so queries need to scan though less number of tuples. In your question, it is not clear how you analysed that autovaccum is creating performance problem. if possible please provide details.

2. Autovaccum algorithms/logic in Postgres is continuouly improved in new versions. for example, Version 9.2 received a patch for skipping a table if there is lock. so autovaccum will do better job in handling bloat. This logic is further improved to skip blocks which are having locks. in latest version 9.6 (which is in development) received a patch to completely skip all the blocks where all tuples are already freezed. Robert Hass posted about this recently: http://rhaas.blogspot.in/2016/03/no-more-full-table-vacuums.html

3. You may want to fine tune autovaccum related parameters to suit your system. if possible take the help of expert DBA. Each parameter is documented very well in Postgres documentation : http://www.postgresql.org/docs/9.5/static/runtime-config-autovacuum.html

So Regarding your question : "Is there a way to make it not consume as much memory/CPU so that client queries can run more efficiently?"

Answer is yes. by fine tuning the parameters mentioned in the above link you can achieve the best results for your system

A general advice will be to always upgrade to latest stable version of PostgreSQL to take advantage of all the autovaccum improvements. Always we should keep in mind that autovaccum is very necessary for keeping the performance of the database. If there is CPU or Memory starvation in your system, consider getting better machine. otherwise tables will become bloated and performance goes down over a period of time.