0

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;
2

1 Answer 1

0

Are there specific configurations, aside from indexes, that could significantly improve query speed for such a large and complex database structure?

Sure, query tuning, and re-architecting as needed. Would need to see the query plan (via EXPLAIN ANALYZE) to offer specific advice there.

Moreover, I am curious if PostgreSQL might have inherent limitations in efficiently handling databases of this size and weight.

Nope. It operates measurably the same as any other modern database system.

If so, are there alternative strategies that could be considered for better performance?

See my answer to your first question. As far as re-architecting goes, it depends on what your use cases are and what you're doing with the data after you SELECT it. For example, if you're doing any kind of aggregation, there's design strategies such as pre-aggregation, and other features that can help with that.

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?

Absolutely not. Firstly, the difference between any NoSQL database system and a SQL (relational) database system is never one relating to performance. And as mentioned earlier, PostgreSQL (among most mainstream database systems) all perform measurably the same. It comes down to how you use them based on your use cases.

I'll end my answer with a question: How much data does your example query return for the provided interval of CURRENT_DATE - INTERVAL 1 MONTH?

3
  • Thanks for you answer. The query returns approximately 50GB of data, and analyzing its performance using EXPLAIN ANALYZE is challenging. Based on the log, the query takes approximately 2 days to retrieve the data. This query is utilized within a PSQL procedure. So use partitions on tables could not be a solution? Feb 2 at 22:19
  • @MarcoCesarato "So use partitions on tables could not be a solution?" - Nope, not at all. Partitioning isn't meant to improve performance (especially for DQL queries) rather it's meant to improve data management. Partitioning just breaks up the data linearly, indexing breaks it up logarithmically (partitioning in its own sense), making indexing exponentially more performant than Partitioning. "The query returns approximately 50GB of data" - Returning that much data at a time is always going to be resource intensive and bound by hardware bottlenecks not software, so query tuning can't...
    – J.D.
    Feb 2 at 23:48
  • ...usually help much with that, re-architecting can. But again, why do you need to pull 50 GB of data at a time?...what are you actually doing with that data next? How many rows is that 50 GB of data, and how many rows are in the table total?
    – J.D.
    Feb 2 at 23:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.