Postgresql cannot drop a table when autovacuum holds lock on it

1. Are you 100% sure that this process is escalating to a tablock? You can verify this with Adam Machanic's sp_whoisactive. Take a snapshot (execute the proc) while the process is running (and presumably blocking). Collect the locks and the query plan - this will give you information needed to fix this issue. Please note what the spids waiting in the insert are waiting on...If they are waiting on latches -we have a page split situation locks its a blocking/locking issue

2. Remember lock escalation happens when a single statement graps 5K locks on an table or index. (http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx) I have found that using the paglock hint (so you take fewer coarser locks) can be used to avoid tablock escalation.

3. Take a look at the query plan collected in step 1. What is the cardinality estimate of the row being insert (i.e. the select statement)... Is it much greater than 5K? If it is you might want to code a loop - so that you are only inserting 2500 rows at a time...

On my PostgreSQL (8.3) I use this trick:

1. I get table's disk size using pg_total_relation_size() - this includes indexes and TOAST size, which is what VACUUM processes. This gives me the idea of how many bytes the VACUUM has to read.
2. I run VACUUM on the table.
3. I find the pid of the VACUUM process (in pg_catalog.pg_stat_activity).
4. In Linux shell I run while true; do cat /proc/123/io | grep read_bytes; sleep 60; done (where 123 is the pid) - this shows me bytes read by the process from the disk so far.

This gives me rough idea on how many bytes are processed (read) every minute by the VACUUM. I presume that the VACUUM must read through the whole table (including indexes and TOAST), whose disk size I know from the step 1.

I presume that the table is large enough so that the majority of it's pages must be read from disk (they are not present in Postgres shared memory), so the read_bytes field is good enough to be used as a progress counter.

Everytime I did this, the total bytes read by the process was no more than 5% from the total relation size, so I guess this approach may be good enough for You.