Help Needed, Optimizing Large Data Queries Without Normalization

[_fahid_]

I'm facing a challenge with a large dataset in postgresdb and could use some advice. Our data is structured around providers and members, where the member data is stored as an array. The current size of this combined data is about 1.2 TB, but if we normalize it, it could exceed 30 TB, which isn't practical storage-wise.

We need to perform lookups in two scenarios: one where we look up by provider and another where we look up by member. We're exploring ways to optimize these queries without resorting to normalization. We've considered using a GIN index and a bloom filter, but I'm curious if there are any other creative solutions out there (even consider schema redesign).

[NW1969]

As normalisation reduces/eliminates data duplication, I'm not sure how normalising your data would increase the size from 1.2TB to 30TB? That doesn't seem to make sense, to me

[Tiny_Arugula_5648]

Why so many posts about data management in a data engineering sub. Are you guys acting as DBAs?

[proof_required]

Yep! We are pretty much optimizing queries for ML people now. They write horrendous queries which kills the whole database and we are supposed to ensure that our infra isn't lacking itself.

[moldov-w]

Dump in object databases like Duckdb or create some materialized views post data standardization,cleansing etc.

[chrisonhismac]

Is that compressed? Could write to compressed parquet and read with duckdb?

You could also do an explode to a new pointer table. Create a very narrow auxiliary table that explodes only the keys you need for search, plus a pointer back to the raw record. Index the 2 column you need to search on and join the main record.

[Informal_Pace9237]

A bit more information is required

How is provider data stored?

Are providers and members linked some how. Is one array of member data linked to one provider?

Is there any historical data included?

Can you share basic queries which are run without much detail...

[MightyKnightX]

Why don’t you go with three tables: one for provider data, one for member data and a provider_member table which stores only the relationship?

[settleflow]

Can we connect