Re: Thousands of partitions performance questions

On Mon, 29 Apr 2019 at 19:20, Shai Cantor <shaicantor(at)gmail(dot)com> wrote:
> Some notes I haven't shared or were not clear enough on the previous post.
>
> Data is inserted using the copy command only and in an offline manner. Meaning, no user action creates or updates the data. An offline job runs is. Number of inserts can reach up to 1500 a day.
> Queries are only on a single partition
>
> Can that ease the performance?

Yes, COPY will be more efficient. In PG11 it still means locking all
partitions but that becomes more worthwhile the more tuples that are
inserted at once. The same goes for INSERT with multiple rows in the
VALUES clause.

> Will querying directly the partition tables help?

Yes. If you're able to determine which partition to query from within
the application and write that in the query instead, then this will be
much less planner overhead for PG11. It'll be pretty much the same as
if you were querying a normal table.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services