title: how to structure large table and its transactions in database?
body: <p>I have two big tables for example:</p>
  
  <p><strong>'tbl_items'</strong> and <strong>'tbl_items_transactions'</strong></p>
  
  <p>First table keeping some items metadata which may have 20 (varchar) columns with millions rows... and second table keeping each transaction of first table. </p>
  
  <p>for example if a user insert new record to <code>tbl_items</code> then automatically a new record will be adding to <code>tbl_items_transactions</code> with same data plus <code>date</code>, <code>username</code> and <code>transaction type</code> to keep each row history.</p>
  
  <p>so in the above scenario two tables have same columns but <code>tbl_items_transactions</code> have <strong>3</strong> extra columns <code>date</code>, <code>username</code>, <code>transaction_type</code> to keep each <code>tbl_items</code> history</p>
  
  <p>now assume we have 1000 users that wants to Insert, Update, Delete <code>tbl_items</code> records with a web application. so these two tables scale very soon (maybe billion rows in <code>tbl_items_transactions</code>)</p>
  
  <p>I have tried MySQL, MariaDB, PostgreSQL... they are very good but when table scale and millions rows inserted they are slow when run some select queries on <code>tbl_items_transactions</code>... but sometimes PostgreSQL is faster than MySQL or MariaDB</p>
  
  <p>now I think I'm doing wrong things... If you was me... do you use MariaDB or PostgreSQL or somthing like that and structure your database like what I did? </p>