CODEWITHSUNDEEP
mysqlsqloracle-databasecolumn-sizing
Dasun Pubudumal
Dasun Pubudumal

Reputation: 97

Dynamic column sizes in relational databases

Assume we have a system which includes a relational database (Say, MySQL or Oracle, for instance) which is populated by a scheduled job which consumes data from an external API. Also assume there are no guarantees for the size of data (e.g. size of a particular value of a json) coming from the API.

In this kind of a scenario, what is the best practice to determine a proper column size for a particular attribute populated using data coming from the API? Is it an apt way to dynamically (programmatically) adjust the column size based on incoming data, or do we assign a relatively larger size to accommodate incoming larger data?

Thank You.

Upvotes: 0

Views: 349

Answers (1)

MT0
MT0

Reputation: 167982

[A]ssume there are no guarantees for the size of data (e.g. size of a particular value of a json) coming from the API.

In this kind of a scenario, what is the best practice to determine a proper column size for a particular attribute populated using data coming from the API?

In Oracle, you can use a CLOB data type.

Say, [...] Oracle, for instance

In Oracle, a VARCHAR2 column is limited to 4000 bytes in the SQL scope. If you have no guarantees about the size of the data then use a CLOB. In Oracle 19c a CLOB datatype has a limit of 4GB * DB_BLOCK_SIZE initialization parameter (which give a total size of 8 TB to 128 TB). If you are exceeding that limit then you should seriously consider re-working how you are consuming the data.

Upvotes: 2

Related Questions