Unkillable processes creating millions of tiny temp files

[Jeremy Finzel <finzelj@xxxxxxxxx>]

Greetings!

We are running postgres 11.9 (were running 11.7 prior to recent restart) on a large db (10s of TB) with 5 or 6 tablespaces and 1000s of tables/indexes.

Within the past few days we have started to see a few queries running for over 8 hours which we then attempt to terminate, but will not terminate.  These queries are also generating hundreds of thousands of tiny/empty temp files.  In fact, before the restart there were over 23 million files in pg_tmp which were removed.  We also have verified no server settings have changed for at least a couple weeks, well before this issue started happening only in the past few days.

Looking back awhile at the same query a couple weeks ago, we see this (large file sizes):

2021-02-18 12:01:59.195 GMT,"foo_user","mydb",81780,"0.0.0.0:49926",602e5716.13f74,3,"SELECT",2021-02-18 12:01:26 GMT,164/1009590,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp81780.0"", size 21299836",,,,,,"
2021-02-18 12:01:59.446 GMT,"foo_user","mydb",81780,"0.0.0.0:49926",602e5716.13f74,4,"SELECT",2021-02-18 12:01:26 GMT,164/1009590,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp81780.5"", size 4138272",,,,,,"
2021-02-18 12:01:59.496 GMT,"foo_user","mydb",81780,"0.0.0.0:49926",602e5716.13f74,5,"SELECT",2021-02-18 12:01:26 GMT,164/1009590,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp81780.1"", size 10706416",,,,,,"
2021-02-18 12:01:59.747 GMT,"foo_user","mydb",81780,"0.0.0.0:49926",602e5716.13f74,6,"SELECT",2021-02-18 12:01:26 GMT,164/1009590,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp81780.3"", size 4150920",,,,,,"
2021-02-18 12:01:59.797 GMT,"foo_user","mydb",81780,"0.0.0.0:49926",602e5716.13f74,7,"SELECT",2021-02-18 12:01:26 GMT,164/1009590,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp81780.2"", size 10660908",,,,,,"
2021-02-18 12:02:00.050 GMT,"foo_user","mydb",81780,"0.0.0.0:49926",602e5716.13f74,8,"SELECT",2021-02-18 12:01:26 GMT,164/1009590,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp81780.4"", size 4132224",,,,,,"

But here is what we see presently (tiny files):

2021-03-05 21:30:52.712 GMT,"foo_user","mydb",67713,"0.0.0.0:48482",60424a1d.10881,73562,"SELECT",2021-03-05 15:11:25 GMT,80/2860,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp67713.177717"", size 24",,,,,,"
2021-03-05 21:30:52.735 GMT,"foo_user","mydb",67713,"0.0.0.0:48482",60424a1d.10881,73563,"SELECT",2021-03-05 15:11:25 GMT,80/2860,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp67713.990067"", size 92",,,,,,"
2021-03-05 21:30:52.950 GMT,"foo_user","mydb",67713,"0.0.0.0:48482",60424a1d.10881,73564,"SELECT",2021-03-05 15:11:25 GMT,80/2860,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp67713.490"", size 24",,,,,,"
2021-03-05 21:30:53.072 GMT,"foo_user","mydb",67713,"0.0.0.0:48482",60424a1d.10881,73565,"SELECT",2021-03-05 15:11:25 GMT,80/2860,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp67713.800016"", size 140",,,,,,"
2021-03-05 21:30:53.522 GMT,"foo_user","mydb",67713,"0.0.0.0:48482",60424a1d.10881,73566,"SELECT",2021-03-05 15:11:25 GMT,80/2860,0,LOG,00000,"temporary file: path ""pg_tblspc/16428/PG_11_201809051/pgsql_tmp/pgsql_tmp67713.500266"", size 48",,,,,,"

After the restart of the db and upgrade to 11.9, we see the same problem.

There are 2 separate queries that have had this behavior with seemingly unrelated objects.  Here is the plan for one of them:

                                                                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2654520.75..2654520.76 rows=1 width=336)
   ->  Hash Left Join  (cost=1087907.49..2019675.12 rows=14107680 width=69)
         Hash Cond: (t1.team_stadium_id = ra.team_stadium_id)
         ->  Hash Left Join  (cost=1079740.68..1955870.37 rows=14107680 width=69)
               Hash Cond: (t1.team_stadium_id = frg.team_stadium_id)
               ->  Hash Right Join  (cost=1073614.70..1894916.60 rows=14107680 width=69)
                     Hash Cond: (sm_1.id = t1.id)
                     ->  Hash Join  (cost=47373.90..695007.85 rows=170011 width=4)
                           Hash Cond: (sm_1.id = l.id)
                           ->  Index Scan using index_foo_fact_on_id_first_win on foo_fact sm_1  (cost=0.42..647177.59 rows=174010 width=12)
                                 Filter: (CASE WHEN (basketball_renewal_date <= now()) THEN true ELSE false END AND (NOT CASE WHEN (basketball_renewal_date <= (now() - '5 days'::interval)) THEN false ELSE true END))
                           ->  Hash  (cost=34408.14..34408.14 rows=1037227 width=4)
                                 ->  Index Only Scan using index_wins_on_team_covering on wins l  (cost=0.43..34408.14 rows=1037227 width=4)
                                       Filter: (team_status_id <> ALL ('{1,2}'::integer[]))
                     ->  Hash  (cost=684569.80..684569.80 rows=14107680 width=69)
                           ->  Seq Scan on bar_team_stadiums_info t1  (cost=0.00..684569.80 rows=14107680 width=69)
               ->  Hash  (cost=3720.99..3720.99 rows=192399 width=4)
                     ->  Seq Scan on bar_team_stadiums_frg frg  (cost=0.00..3720.99 rows=192399 width=4)
         ->  Hash  (cost=4749.14..4749.14 rows=273414 width=8)
               ->  Seq Scan on sports_team_stadiums ra  (cost=0.00..4749.14 rows=273414 width=8)
(20 rows)

Any ideas here would be much appreciated!!!

Thanks,

Jeremy