Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Number of Measurement Records Exceed Integer Id Limit #198

Closed
WustlRob opened this issue Jul 5, 2018 · 8 comments

Comments

Projects
8 participants
@WustlRob
Copy link

commented Jul 5, 2018

We've run into an issue where we have more measurement records than an integer measurement_id allows in OMOP database (Over 3 billion where max 32 bit integer is 2,147,483,647). Our suggestion is to change the measurement_id field (and probably the id fields for other domains) to use a 64 bit integer. We are using PostgreSQL for our database.

@kembree1

This comment has been minimized.

Copy link

commented Jul 5, 2018

I'm surprised this hasn't been an issue before now.

@cgreich

This comment has been minimized.

Copy link
Contributor

commented Jul 6, 2018

No problem to use big_int. Won't make a difference in querying in 99% of the cases, particularly if the IDs are exclusively used for foreign keys.

@gowthamrao

This comment has been minimized.

Copy link
Contributor

commented Jul 6, 2018

I like the idea of converting all key fields to bigint. E.g. person_id, visit_occurrence_id etc. This will need changes to the omop ddl.

@clairblacketer

This comment has been minimized.

Copy link
Contributor

commented Jul 6, 2018

I think we have been using bigint for some of our keys, especially for the MarketScan data. It makes sense to add it in to the DDL; I'll just need to figure out how to translate that translates to bigquery and impala.

@clairblacketer clairblacketer self-assigned this Jul 6, 2018

@ericaVoss

This comment has been minimized.

Copy link

commented Jul 10, 2018

We see this mainly affecting the DDL, no need to update the WIKI.

@clairblacketer clairblacketer added this to To do in CDM v6.0 Jul 16, 2018

@GeraldPulver

This comment has been minimized.

Copy link

commented Jul 17, 2018

BIGINT sounds like a great idea. I use 32 Bytes for my keys on some projects, to accommodate 256b hash values. Yes, I realize that 32B would be overkill for a default allocation for keys. :)

@clairblacketer clairblacketer moved this from To do to Done in CDM v6.0 Aug 28, 2018

@vojtechhuser

This comment has been minimized.

Copy link
Collaborator

commented Sep 4, 2018

so this issue should be closed, right?

@WustlRob

This comment has been minimized.

Copy link
Author

commented Sep 4, 2018

I just checked the cdm_v6 branch and it looks good to close. Thanks everyone!

@WustlRob WustlRob closed this Sep 4, 2018

This was referenced Oct 11, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.