Middle+

There is a table with millions of records, but queries are almost always for the last one or two weeks. What would you do for optimization?

sobes.tech AI

Answer from AI

To optimize queries to a table with millions of records, when queries are almost always for the last 1-2 weeks, you can:

Use table partitioning by date so that queries scan only relevant partitions.
Create an index on the date field to speed up filtering.
Cache results of frequent queries if data doesn't change often.
Consider using specialized storage for temporary data (e.g., time-series databases).

Example of partitioning in PostgreSQL:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP NOT NULL,
    data JSONB
) PARTITION BY RANGE (event_time);

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

This way, date-filtered queries will run faster as they only access the necessary partitions.

Previous Next