How to keep up database performance of tables with multi-billion rows

Question

I work as a product manager (with a solid Sql background) in a tech company which develops patient-data-management systems especially for ICU and anesthesia. To date, we have a customer base of mainly small to medium sized hospitals with let's say maximum of 20 beds which produce data every minute, but there are opportunities now to sell to large / university hospitals with around 200 critical care beds connected to multiple medical devices (monitors, respirators, dialysis, syringe pumps) and we're planning to come up with a new product generation.

Problem: We get a lot of customer calls regarding poor performance and it won't get better with the upcoming ones. Systems slow down over time.

For the large customers I calculate with 200 beds and an average of 30 rows per minute per bed. Assuming a 75% occupancy rate it adds up to ~ 2.4 Billion rows a year. Spread across ~3000 patient cases a year.

To date, we store every input, regardless of its origin (device or user) and data type (numeric, text, references, date and date ranges) in a single table that is full of indexes. Our developers somehow love that.

My plan is to seperate the data to different tables according to the data type, as my impression is that indexing will be more targeted and I have also verified it with competitor databases, which do it exactly like this.

Application queries are pretty basic and will mainly look like this

SELECT parameter_id, date_time, value
FROM data_numeric 
WHERE visit_id = :visit_id 
AND parameter_id = :parameter_id -- e.g. the heart rate
AND date_time >= :from_date_time
AND date_time < :to_date_time

No bulk inserts or updates exceeding 50 rows at a time.

Any ideas or experiences how we should start? Would index organized tables help in this case or other concepts like partitioning?

Appreciate your help!

Answer 1

You should consider sticking with your one large table and index architecture. That solution may be faster than splitting up the tables and indexes. Perhaps you have some tricky implementation problem that is preventing your indexes from scaling?

One index to rule them all (in theory)

B-tree index performance grows logarithmically. If the main row only has one data point, reading from a single large index will take O(LOG(N)) disk reads. Now let's say you split that large index up into 10 smaller indexes. Reading from a smaller index will cost O(LOG(N/10)), which will only save you a single read.

Now imagine the same scenario, but there are two data points to read. With a single index, reading two data points will likely still cost O(LOG(N)) disk reads. This is because the two values will be stored right next to each other, probably in the same database block. Compare that to reading two data points from two smaller indexes, which will cost 2 * O(LOG(N/10)), which is almost twice as much.

More realistic example

If the above run time analysis seems too abstract, below is a more realistic example that demonstrates how a large table and index strategy can outperform a multiple small tables and indexes strategy.

The below code creates 240M rows of numeric data, and 2.4M rows of string data. (My system couldn't handle a full 2.4B rows, so I scaled down to 1/10th.) As you would expect, the 240M row index on numeric values is about 100 times larger than the 2.4M row index on string values. But reading a single row from each index has very similar performance: 4 disk reads for numeric versus 3 for string.

If you're looking for all EAV data for a single visit or bed, you'll spend more time reading the indexes for 1% of your data than you will spend reading the indexes for 99% of your data. If that's the case, you might as well put everything in one table and one index.

There are many cases where a large index outperforms multiple small indexes. Maybe this won't apply to you - maybe your index access is not what I assumed. But it's worth testing it out at least. And understanding how logarithmic performance should happen may help you find a problem with your existing indexes.

Code for 240M rows of DATA_NUMERIC. BLEVEL+1 = 4 reads.

--Create table and sequence for sample data.
create table data_numeric
(
    data_numeric_id number primary key,
    visit_id        number,
    parameter_id    number,
    date_time       date,
    value           number
) nologging;
create sequence data_numeric_id_seq;

--Load 240M rows into table.
begin
    --for i in 1 .. 2400 loop
        insert /*+ append */ into data_numeric
        select
            data_numeric_id_seq.nextval,
            mod(level, 1000)         visit_id,     --1K visits for every 100K data points.
            mod(level, 100)          parameter_id, --100 different parameters.
            sysdate + level/86400    date_time,    --Each row has a different second.
            dbms_random.value * 1000 value         --Random numeric values.
        from dual connect by level <= 100000;
        
        commit;
    end loop;
end;
/

--Create index.
create index data_numeric_idx on data_numeric(visit_id, date_time) nologging parallel 8;
alter index data_numeric_idx noparallel;

--Number of reads to get one index block: 4
select blevel+1 from dba_indexes where index_name = 'DATA_NUMERIC_IDX';
--Table: 12771 MB
select bytes/1024/1024 mb from dba_segments where segment_name = 'DATA_NUMERIC';
--Index:  6112 MB
select bytes/1024/1024 mb from dba_segments where segment_name = 'DATA_NUMERIC_IDX';

Code for 2.4M rows of STRING_NUMERIC. BLEVEL+1 = 3 reads.

--Create table and sequence for sample data.
create table data_string
(
    data_string_id  number primary key,
    visit_id        number,
    parameter_id    number,
    date_time       date,
    value           varchar2(100)
) nologging;
create sequence data_string_id_seq;

--Load 2.4 rows into table.
begin
    for i in 1 .. 24 loop
        insert /*+ append */ into data_string
        select
            data_string_id_seq.nextval,
            mod(level, 1000)                  visit_id,     --1K visits for every 100K data points.
            mod(level, 100)                   parameter_id, --100 different parameters.
            sysdate + level/86400             date_time,    --Each row has a different second.
            dbms_random.string('a', len => 10) value         --Random strings values.
        from dual connect by level <= 100000;
        
        commit;
    end loop;
end;
/

--Create index.
create index data_string_idx on data_string(visit_id, date_time) nologging parallel 8;
alter index data_string_idx noparallel;

--Number of reads to get one index block: 3
select blevel+1 from dba_indexes where index_name = 'DATA_STRING_IDX';
--Table: 96 MB
select bytes/1024/1024 mb from dba_segments where segment_name = 'DATA_STRING';
--Index:  61 MB
select bytes/1024/1024 mb from dba_segments where segment_name = 'DATA_STRING_IDX';