Making citation-map an order of magnitude smaller and faster

[devlux76]

I'm working on ways to reduce the bulk data so it's easier to deal with.

The citation-map is about 1.2GB uncompressed.
You could add a view like the one below and shrink it down to less than 120MB without any data loss at all.

CREATE OR REPLACE VIEW reshaped_citation_map AS
SELECT
    cited_opinion_id,
    -- Aggregate citing_opinion_id and depth into a list of structs
    -- Order the list by depth using struct_pack
    LIST(struct_pack(citing_opinion_id := citing_opinion_id, depth := depth) ORDER BY depth ASC) AS citations
FROM
    "citation-map"
GROUP BY
    cited_opinion_id;

Then you export the view like this.

COPY (
  SELECT * FROM reshaped_citation_map ORDER BY cited_opinion_id DESC
) TO './parquets/citation-map-reshaped.parquet' (FORMAT parquet, COMPRESSION zstd, COMPRESSION_LEVEL 22);

The above is just using duckdb on the raw .csv file, but it can easily be adapted to Postgres. It takes about 10 minutes to run on my 2018 MacBook Pro with 32GB of RAM.
The end result is over a 10x reduction in disk space and makes the data much faster to work with.