How to organize partitioning of the events table with 2 billion rows, growing by 10 million rows per day, with frequent range date queries?

For a table like events with 2 billion rows and a daily increase of 10 million rows, where range queries by date are common, partitioning by date is optimal. Usually, partitioning by days or months is used, depending on the data volume and query patterns.

Main recommendations:

• Partitioning by date — create partitions based on the event date (e.g., by day or month). This allows quick selection of relevant partitions during range queries.
• Automatic partition creation — set up a mechanism that automatically creates new partitions for new dates to avoid downtime.
• Archiving old data — for very old partitions, configure archiving or transfer to cold storage.
• Indexing — create indexes on columns frequently used in filters and joins, besides the date column.

Example for PostgreSQL with monthly partitioning:

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    event_date DATE NOT NULL,
    data JSONB
) PARTITION BY RANGE (event_date);

CREATE TABLE events_2024_06 PARTITION OF events
    FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');

-- Create new partitions monthly

This approach allows range queries to only access relevant partitions, significantly speeding up execution and reducing system load.