Fast long-to-wide reshaping in PostgreSQL

Question

I am trying to change a really long table (1955451890 rows -- 278 Gb on the DB) to a wide format using crosstab from the tablefunc extension. Following several examples from SO (i.e. Crosstab with unknown format and the PostgreSQL official docs) I have come with a working code, but it's taking ages to run in fairly big AWS RDS instance.

The data looks like this:

| id_source | date       | t | geo_id | var_1 | var_2 |
|-----------|------------|---|--------|-------|-------|
| 3         | 2002-01-01 | 1 | 6086   | 3.4   | 4.5   |
| 3         | 2002-01-02 | 2 | 7888   | 6.7   | 2.9   |
| 3         | 2002-01-03 | 3 | 6732   | 5.8   | 6     |
| 4         | 2002-01-01 | 1 | 7888   | 5.8   | 3.4   |
| 4         | 2002-01-02 | 2 | 6015   | 6     | 10    |
| 4         | 2002-01-03 | 3 | 4332   | 8     | 12.5  |
| 7         | 2002-01-01 | 1 | 6086   | 5     | 7.9   |
| 7         | 2002-01-02 | 2 | 6732   | 5.7   | 7.9   |
| 7         | 2002-01-03 | 3 | 7888   | 2.3   | 6     |

And ideally, I would have something like this:

| geo_id | date       | t   | var_1_id_3 | var_1_id_4 | var_1_id_7 | var_2_id_3 | var_2_id_4 | var_2_id_7 |
|--------|------------|-----|------------|------------|------------|------------|------------|------------|
| 6086   | 2002-01-01 | 1   | 3.4        |            | 5          | 4.5        |            | 5          |
| 6086   | 2002-01-02 | 2   |            |            |            |            |            |            |
| 6086   | 2002-01-03 | 3   |            |            |            |            |            |            |
| 7888   | 2002-01-01 | 1   |            |            |            |            |            |            |
| 7888   | 2002-01-02 | 2   | 6.7        |            |            |            | 2.9        |            |
| 7888   | 2002-01-03 | 3   |            |            | 2.3        |            |            | 6          |
| 6732   | 2002-01-01 | 1   |            |            |            |            |            |            |
| 6732   | 2002-01-02 | 2   |            |            | 5.7        |            |            | 7.9        |
| 6732   | 2002-01-03 | 3   | 5.8        |            |            | 6          |            |            |
| 6015   | 2002-01-01 | 1   |            |            |            |            |            |            |
| 6015   | 2002-01-02 | 2   |            | 6          |            |            | 10         |            |
| 6015   | 2002-01-03 | 3   |            |            |            |            |            |            |
| ...    | ...        | ... | ...        | ...        | ...        | ...        | ...        | ...        |

[It seems really sparse, but is just the example I used].

Following the linked documentation I tried the following query. Here I use

1. CROSSTAB(text, text) with the first element being the data to reshape and the second element being the categories to represent as columns.

2. Building on the SO question, I add a pgpsql part to dynamically set the name of the columns. I have several id_sources, and stating categories manually would be painful

SELECT $$ SELECT * FROM crosstab (
    'SELECT source_id
            zcta_id,
            date_day,
            t,
            distance_nearest
     FROM results.distances_aod_coal_plants_energy
     ORDER BY 1, 2, 3',
    'SELECT DISTINCT "distance_to_plant" || facility_id AS col
    FROM hysplit_partitions.coal_plants'
    ) AS ct (zcta_id CHARACTER VARYING,
              date_day DATE,
              t INTEGER,
              $$
            || string_agg(quote_ident(id_source::CHARACTER), ' DOUBLE PRECISION, ' order BY id_source) || ' DOUBLE PRECISION )'
            from results.distances_aod_coal_plants_energy;

The query runs, but after ~3 hr of computing time I get the following error:

ERROR:  out of memory
DETAIL:  Cannot enlarge string buffer containing 1073741815 bytes by 19 more bytes.

I know PostreSQL is not particularly friendly to this reshaping, compared to Python's pandas or R's tidyr, but loading this sort of data is not straight forward on any of these options, and I do have a strong preference to keep my data inside my DB.

There's an alternative to do this reshapinng either using chunks or table partitioning?