Severalnines’ Post

Postgres 17 has rich set of query performance improvements. I tried to capture some of the important ones in the blog. To know more, click here:

PostgreSQL 17 has improved the B-Tree Index Scan for IN(list) WHERE clauses. Here is a demo of it on PG17, and how YugabyteDB optimizes the same for IN() and Nested Loop. https://lnkd.in/dtSyvYJk

PostgreSQL 17 - A Major Step Forward in Performance, Logical Replication and More Posted by Ahsan Hadi, pgEdge * Improved Query Performance with Materialized CTEs * Extracting column statistics from CTE references; Postgres 17 improves materialized CTE’s * Propagating pathkeys from a CTE to an Outer Query * Fast B-Tree index scans for Scalar Array * Retention of logical replication slots and subscriptions during upgrade https://lnkd.in/dtGv5N_X

How to Get the Most out of Postgres Memory Settings https://lnkd.in/eJCHmyRs #postgres

In PostgreSQL, you may need to know the current state of a running query. One of the solution is pg_query_state module. To enable this extension you have to patch the stable version of PostgreSQL, recompile it and deploy new binaries. If you are interested, you can check it; https://lnkd.in/dKWp_shG

The Challenges of Streamed I/O in PostgreSQL 17 While Streamed I/O promises significant performance improvements, it also introduces some challenges that the PostgreSQL developers have had to address: 1. Determining the Optimal Read Size: One of the key challenges is deciding how many pages to read in a single operation. Reading too few pages might not fully leverage the benefits of Combined I/O, while reading too many could lead to wasted I/O and memory if the extra pages aren’t needed. The PostgreSQL team has implemented heuristics to make this decision, likely considering factors such as: The type of operation being performed (e.g., sequential scan vs. index scan) Historical access patterns Available memory in the shared buffer pool 2. Handling Non-Sequential Reads: While Streamed I/O is particularly beneficial for sequential reads, PostgreSQL also needs to handle scenarios where multiple non-sequential pages need to be read. The preadv system call allows for this by accepting an array of buffer pointers and their corresponding file offsets. 3. Balancing with Existing Optimizations: PostgreSQL needs to balance Streamed I/O with existing optimizations like the operating system’s read-ahead functionality. The goal is to complement, rather than conflict with, these existing optimizations. 4. Memory Management: Reading multiple pages at once requires careful management of the shared buffer pool to ensure that the pre-fetched pages don’t prematurely evict other important pages from memory. #PostgreSQL #Database #softwareengineering #dbms

“I find this particular release really compelling, compared to the last several." — EDB SVP of Database Servers and Tools Tom Kincaid The release of PostgreSQL17 brings with it a number of exciting advancements to improve ease of use for developers and database users. EDB's Tom Kincaid sits down with Joab Jackson from The New Stack to share his favorite features.. Read the article for Tom's insights on: 🔸 The addition of Incremental Backup 🔸  Updates to Logical Replication 🔸 Improved JSON support ➡️ https://bit.ly/3zBAGLC #Postgres #PostgreSQL #justsolveitwithPostgres #EDBPostgresAI #data #AI #database #DBA #opensource #PG17 #Postgres17 #PostgreSQL17 #JSON

Soft deletes with Ecto and PostgreSQL #Ecto #ElixirProgramming #SoftDeletes #DatabaseManagement #DataIntegrity #PhoenixFramework #ElixirEcosystem #DatabaseBestPractices #SoftwareDevelopment #DataModeling https://lnkd.in/gmW9ZzVM

Speed Up Compound Full-Text Searches in PostgreSQL by 300x Querying your database across associations can become a bottleneck in your Rails application. Julian Rubisch explains why and suggests an efficient solution. https://lnkd.in/eSv7TTHn

Postgres replication in docker compose. You don't have to repeat the same code in docker-compose.yml: version: '3.8' x-postgres-common: &postgres-common image: postgres:14-alpine user: postgres restart: always healthcheck: test: 'pg_isready -U user --dbname=postgres' interval: 10s timeout: 5s retries: 5 services: postgres_primary: <<: *postgres-common ....