search

quantified-uncertainty / metaforecast

Fetch forecasts from prediction markets/forecasting platforms to make them searchable. Integrate these forecasts into other services.
https://metaforecast.org/
MIT License
57 stars 5 forks source link

Get rid of postgres schemas - ok? #25

Closed berekuk closed 2 years ago

berekuk commented 2 years ago

I was surprised that the current codebase uses PG schemas (latest and history) to separate table namespaces.

I admit I don't have much experience with postgres (most of my experience is with mysql), so it might be that, but also I couldn't understand why it's necessary.

I planned to just go with the flow and adopt this instead of complaining, but then I tried to write a PR which uses Prisma Migrate for database migrations (since managing it by hand with pgInitizalize* functions and YOLO flags is becoming unhandy)... and it turns out Prisma doesn't support tables in multiple schemas. Or at least doesn't mention it in the docs.

Which updated me in the direction of "maybe it's not the best practice for postgres either", so I googled around a bit and I think people mostly say that schemas are for multi-tenant cases or for multiple apps, not for a single app with namespacing needs (which are better managed by underscore prefixes).

So maybe we should just move everything to public?

NunoSempere commented 2 years ago

Yeah, changing this is fine. It just bought me some clarity to be able to differentiate history.something and latest.somethingelse

berekuk commented 2 years ago

Cool. Oh, btw, while we're at it, is it really necessary to split the history into multiple tables?

I'd expect db indices to be good enough to take care of any performance issues in case that's the reason for splitting.

berekuk commented 2 years ago

And for optimizing performance we have multiple other strategies, e.g. normalizing options column into proper DB columns. Also, I believe history tables don't get queried from anything performance-sensitive anyway, at least for now.

But, in general, querying one large table with an index is usually faster than querying 10 small ones.

NunoSempere commented 2 years ago

Feel free to use one big table instead. I originally used small ones so as to not exceed the mongodb document size limit, but this isn't a problem on postgres.