how to calculate the storage volume of a database

Asked

Viewed 1,961 times

0

The thing is, I’m having concerns about a database being used in a project. The project consists of the development of a software to unite the respective data to two solar spectrum sensors, and then the storage of them in a database together with other information from a "master" table. The concept is that for each row in the master table there will be thousands of rows in the spectrum table.

SUMMARY

We have the following diagram: diagrama de entidades

The problem I’m facing is that the master table rows will be added 24/7 once a minute, and the esp table rows will be inserted more than a thousand at a time, but only 5 times a day, with an interval of one hour.

PROBLEM

It may have been a bit confusing, but the major concern is not understanding this diagram.

What I want to do is calculate the average volume of data that will enter the bank per year/month/week. there is some formula tool or way to calculate how much space I will need to store this data?

thank you in advance!

database modeling

  • This changes from manufacturer to manufacturer. Specify a tag or in the text which is the DBMS you are using. Also specify the indexes because they also take up space.

  • But the manufacturer factor will not be taken into account much, because after reading the sensor I am formatting the data in python. After all I want to estimate the storage for all fields to be double, so I would have a break and then calculate the correct difference. I don’t understand what you mean by index, sorry for the lack of experience with database

  • The index part is because each index occupies space in the database and increases as the number of rows in the table increases. Knowing the manufacturer is also important, for example, the query of the answer that was posted works on Oracle but not on MS SQL Server.

  • thought the manufacturer mentioned would be the sensor, so said it would make no difference. In case I’m using postgresql.

1 answer

1


First you need to identify the size in bytes of each row. With this you will be able to identify, given the number of rows in the table, the size of the table.

In Oracle I use q query below to get the total table size and dividing by the number of rows I get the value per row and then follow with the estimate:

SELECT
   owner, 
   table_name, 
   TRUNC(sum(bytes)/1024/1024) Meg,
    ROUND(sum(bytes)/1024/1024/1024, 2) GB,
   ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE UPPER( owner) in UPPER('MY_OWNER')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
  • 1

    Great, that’s exactly what I wanted to know, so there’s a way, I didn’t know you could check the size of the lines like this. My bank has not yet been populated, but then I will popular it a little and I will perform some calculations before implementing fully. maybe I have differences of functions and querys by using postgres but I think I’ll cnseguir. Thank you very much

Browser other questions tagged database modeling

You are not signed in. Login or sign up in order to post.