I have a system in development but are already using in beta, this system has a specific focus:
'control 100% of shares, share per share, without losing any history how it was and how it is now '
With this I ended up falling into a problem called:
Giant database in short time of use.
This does not disrupt the operational user's performance, since the system has the mirror with the current state and works on it, but it weighs on the performance of admin users to check anything they want.
Data example:
ID | COD | DATETIME |ACT |USER_RELACIONATED | OBJECT_RELACIONATED | SECTOR | SPEC
13869 |59d2677049ed6 | 2017-10-02 13:21:04 |NULL|TO|12094190345951120286cf02.53535322 | 12094190345951120286cf02.53535322 | 59ca9dbd11ee4 | contabil_control_obligation | deal_responsible
The use of the beta is about a month, and I already have 13896 records only in the history of the sector of the control of accounting obligations (I currently have 9 sectors), total of + - 90 thousand records.
The bank is divided into two partitions the mirror, which is what is displayed the user, and somatic, that where stored action-by-action situations, I took all the precautions I knew to format correctly, set the types of data, set indices, but the problem is in the size of that database.
I've never worked with a bank that would weigh so much, the doubt I'm having is this:
1 - What are the most applied concepts in large databases?
2 - What are the lightest data formats that can be used for fields?
3 - Triggers and direct relationships will disrupt my performance when I'm using the table without the relations and without activating the trigger?
PS: It's going to be really big, forecasting almost 1M of annual records only in history, and only with what is currently functional in the beta, it's to be anyway, the focus is this, but I want to improve consumption / occupy this in some way.
PS2: Switching DB is an option.