There is a Log DB. As I understand, there are two constraints which influence the answer to my question: space needed to store the database, number of reads-writes/sec needed.
There are 72 M records inserted per day, which is equivalent to 2000 records generated per second during 10 active hours. There is no limitation on number of client threads that need to do inserts. It can be hundreds of clients. We need to store 180 days of this information like a sliding window - 13 000 M records in total, 30 TB (2500 bytes per record). When a new day starts, data for the oldest day shall be removed;
In parallel some subsystem periodically queries this DB with 500 reads/sec, there is no limitation on number of threads for that, it can be tens of threads:
select * from T_LOG_RECORD where ((F_EXTERNAL_ID="92" AND F_SCOPE="CONT") OR F_GROUP_TAG="100000164") AND F_RECORD_TIME < "2016-12-12 23:59:59" AND "2016-11-12 00:00:00" < F_RECORD_TIME AND F_STATUS = 0
Here is the draft DDL (Oracle syntax) of the single table in this DB to understand the idea, indexes are skipped:
CREATE TABLE T_LOG_RECORD ( T_LOG_RECORD NUMBER(20) NOT NULL, F_NOTIFICATION_ID VARCHAR(45) NOT NULL, T_OPERATOR_ID NUMBER(20) NULL, T_LANGUAGE_ID NUMBER(2), F_RECORD_TIME TIMESTAMP NOT NULL, F_ADDRESS_TYPE NUMBER(2) NULL, F_ADDRESS_VALUE VARCHAR(45) NULL, F_EXTERNAL_ID VARCHAR(45) NULL, F_SCOPE VARCHAR(45) NULL, F_SUBJECT VARCHAR(250) NULL, F_TEMPLATE_ID NUMBER(20) NULL, F_MESSAGE_TEMPLATE CLOB NULL, F_MESSAGE CLOB NULL, F_NOTIFICATION_PARAMS CLOB NULL, F_TRIGGERED_TIME TIMESTAMP(0) NULL, F_EXPIRED_TIME TIMESTAMP(0) NULL, F_GROUP_TAG VARCHAR(20) NULL, F_STATUS NUMBER(2) NOT NULL, F_DISCARD_REASON NUMBER(2) NULL, F_PRIORITY NUMBER(2) NULL, F_ERROR_CODE VARCHAR(45) NULL, F_DELIVERY_TIME TIMESTAMP(8) NULL ) PARTITION BY RANGE (F_RECORD_TIME) INTERVAL (NUMTODSINTERVAL(1, 'DAY')) (PARTITION p1 VALUES LESS THAN (TO_DATE('08-01-2017', 'DD-MM-YYYY'))) TABLESPACE SOME_TABLESPACE ;
I assume that commodity server hardware will be used. We will implement sharding on application level, with active-standby replication with 1 redundant copy.
My current assumption is that PostgreSQL on a single host (8 cores, 64 GB RAM, 2 x 4 TB SSD without any RAID) will be able to handle 10 K inserts/sec/host. So we will need 8 hosts/VMs with such parameters. The calculation is the following: 30 TB * 2 (replication factor) / 8 TB (total disk size on host) = 8 hosts. So there will be 4 host-shards and 4 hosts-replicas. Inserts will be done on host-shards, selects will be done on all hosts (shards and replicas).
Is my calculation correct?