I’m dealing with a massive dataset and trying to choose the right database platform for handling 100 billion data points, each of 1KB. Specifically, I’m considering Timescale Cloud.

CREATE TABLE your_table_name (
    id SERIAL PRIMARY KEY,
    location VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    uniqueCode VARCHAR(255) NOT NULL UNIQUE
);

-- Index on uniqueCode
CREATE INDEX uniqueCode_idx ON your_table_name (uniqueCode);

Mostly my queries will be as follows

1. Fetching latest recorded location of all assets in a time window

   Plain text

   Copy to clipboard

   Open code in new window

   EnlighterJS 3 Syntax Highlighter

   <code> SELECT DISTINCT ON (uniqueCode)

   uniqueCode,

   location,

   name,

   timestamp

   FROM

   your_table_name

   WHERE

   timestamp >= NOW() - INTERVAL '24 hours'

   ORDER BY

   uniqueCode,

   timestamp DESC;

   </code>

   <code> SELECT DISTINCT ON (uniqueCode) uniqueCode, location, name, timestamp FROM your_table_name WHERE timestamp >= NOW() - INTERVAL '24 hours' ORDER BY uniqueCode, timestamp DESC; </code>

    SELECT DISTINCT ON (uniqueCode)
        uniqueCode,
        location,
        name,
        timestamp
    FROM 
        your_table_name
    WHERE
    timestamp >= NOW() - INTERVAL '24 hours'
    ORDER BY 
        uniqueCode, 
        timestamp DESC;
   

2. Fetching Trajectory of a particular asset

   Plain text

   Copy to clipboard

   Open code in new window

   EnlighterJS 3 Syntax Highlighter

   <code> SELECT

   location,

   name,

   timestamp

   FROM

   your_table_name

   WHERE

   uniqueCode = 'your_asset_uniqueCode'

   AND timestamp >= NOW() - INTERVAL '24 hours'

   ORDER BY

   timestamp ASC;

   </code>

   <code> SELECT location, name, timestamp FROM your_table_name WHERE uniqueCode = 'your_asset_uniqueCode' AND timestamp >= NOW() - INTERVAL '24 hours' ORDER BY timestamp ASC; </code>

    SELECT 
        location,
        name,
        timestamp
    FROM 
        your_table_name
    WHERE 
        uniqueCode = 'your_asset_uniqueCode'
        AND timestamp >= NOW() - INTERVAL '24 hours'
    ORDER BY 
        timestamp ASC;
   

Is it smart to store 100 billion data points in a single instance with Timescale Cloud? What are the performance, scalability, and cost-effectiveness considerations?