Optimizing Performance for Large PostgreSQL Database with Parent and Child Tables

# Description I am currently facing performance challenges in a PostgreSQL database scenario involving a parent table and 30 child tables. Notably, none of these tables are partitioned, and some of the child tables have substantial individual sizes, with a cumulative size of all tables reaching 5TB. Despite having indexes on both the parent and child tables, executing queries, such as the one below, takes an extended period, often several hours. # Question I am seeking guidance on optimizing performance in this context. Are there specific configurations, aside from indexes, that could significantly improve query speed for such a large and complex database structure? Moreover, I am curious if PostgreSQL might have inherent limitations in efficiently handling databases of this size and weight. If so, are there alternative strategies that could be considered for better performance? There has been speculation about PostgreSQL facing challenges with disk I/O, particularly when compared to other databases like Oracle or NoSQL. Is this speculation accurate? ## Infos ### Version Running on Google Cloud SQL PostgreSQL 13.12 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit ### Table

CREATE TABLE mytable (
    id_pos int8 NOT NULL PRIMARY KEY,
    date_insert DATE DEFAULT NOW()
);

CREATE TABLE mytable_child1 (
    id_pos int8 NOT NULL PRIMARY KEY,
    date_insert DATE DEFAULT NOW(),
    other_field varchar(10) NOT NULL
) INHERITS (mytable);

### Indexes On each tables (parent and child) I've this indexes

CREATE INDEX IF NOT EXISTS mytable_date_insert_idx ON mytable USING btree (date_insert);

CREATE INDEX IF NOT EXISTS mytable_child1_date_insert_idx ON mytable_child1 USING btree (date_insert);

### Query

SELECT * FROM mytable WHERE date_insert >= CURRENT_DATE - INTERVAL 1 MONTH;