Ismail Seyfi's Blog

Following snippet of PostgreSQL documentation,  https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW , explains the benefits of partitioning as fallows.  Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits: Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. Partitioning effectively substitutes for the upper tree levels of indexes, making it more likely that the heavily-used parts of the indexes fit in memory. When queries or updates access a large percentage of a single partition, performance can be improved by using a sequential scan of that partition instead of using an index, which would require random-access reads scattered across the whole table. Bulk loads and deletes can be accomplished by adding or removing partit...

I have been actively using PostgreSQL for the last 7 years. As with many databases engines, PostgreSQL provides us with many capabilities when it comes to helping developers identify slow performing queries. This article focuses on several useful (primarily administrative queries) for PostgreSQL which may be helpful in debugging you database performance issues. Queries Show Running Queries Often times it is useful to see which queries are running and how long they have been running from. The below is a sample query to find 'active' queries and information about them.  SELECT pid, client_addr, query_start, age(query_start, clock_timestamp()), usename, query, state, wait_event_type, -- Only available in >9.6 wait_event -- Only available in >9.6 FROM pg_stat_activity WHERE query != ' ' AND query NOT ILIKE '%pg_stat_activity%' AND state = 'active' ORDER BY query_start desc; Particularly useful attribute ...