Ramanna Gunde

The tech talk delves into similarity search and it’s benefit by implementing it through pgvector, which is an open-source extension that enables efficient vector similarity searches within PostgreSQL databases. It explains the underlying principles of vector embeddings and how pgvector leverages indexing techniques to accelerate similarity queries.

Additionally, the talk presents practical examples and code snippets demonstrating how to set up and utilize pgvector for vector similarity search use cases. It covers topics like recommendation system, indexing, and querying for similar vectors based on a given input vector.

Today’s modern applications must be Always-on, Always-available and serve a global user base that demands fast response times. A fully Multi-master Distributed Database architecture is critical for organizations to meet these demands. In this talk, we will explore the core use cases that benefit from deploying PostgreSQL with asynchronous logical replication in a Multi-region or even Multi-cloud environment. Learn how issues such as slow response times, maintenance or provider downtimes, data storage requirements, and throughput can easily be addressed.

You’ll see how pgEdge is demonstrating the value of Open source, pure Postgres, multi-master solution to improve data latency and high availability as well as address data residency requirements.

Key takeaways:
By attending this session, you will gain valuable insights into the world of scaling PostgreSQL for high-traffic applications. Whether you are a developer, architect, or database administrator, this talk will equip you with the knowledge and best practices necessary to design and implement a scalable and performant Multi-master Distributed Database architecture.

The talk dives into diagnostic capabilities of PostgreSQL’s EXPLAIN ANALYZE command with emphasis on BUFFERS option. The topic although is interesting but can feel daunting to lots of people. This talk with help understand a bit more deeply in to that. We will cover how this tool can provide crucial insights into query execution plans and buffer usage, which are essential for optimizing database performance. We will explore the significance of buffer statistics, such as blocks hit and read, and discuss specific scenarios where these metrics can be misleading, such as in nested loops where shared hit counts may be double-counted. We will also have practical examples with the queries running and demonstrate what block hits vs block reads indicates. We will end with a look at how we did apply the diagnosis for running one of our GetPostsBatchForIndexing to make it faster.

PostgreSQL is first and foremost an OLTP DBMS. But it has a rich set of features which allows it to be used as an OLAP DBMS. It is highly compliant with SQL standard, providing analytical features like windowing. It supports many data types, including Geo-spatial datatypes and JSON, which are important for analytical workloads. It supports full-text search on document data types and support non-traditional indexes like GIN, BRIN along side partial and expression indexes. Using physical or logical replication one can create a low maintenance ELT system, to separate OLAP and OLTP workloads. It is extensible through Extensions, which extend its already rich ecosystem by adding complex data types, user defined functions etc. Parallel querying capability allows processing larger datasets in lesser time. Foreign data wrappers allow querying external data sources in todays hybrid data models. Partitioning along with FDW paves path for horizontal scaling.

Yet when it comes to processing TBs for data it falls short of the other DBMSes built specially for analytical workloads. However PostgreSQL ecosystem is stepping up to this challenge. Once of the current trends is to use a combination of cloud columnar storage format, columnar execution engine and FDW. Columnar execution engine and columnar storage make it possible to process large amounts of data efficiently. FDW provide a way to interface columnar engine from PostgreSQL. PostgreSQL itself provides rich SQL and handles authentication, governance and metadata. These systems are easier to maintain, do not involve data movement and are yet reasonably efficient.

The talk will explore PostgreSQL’s offerings in the analytical space.

As the demand for scalable and responsive database solutions grows, managing PostgreSQL connections effectively becomes paramount. In this talk, we delve into the intricacies of scaling PostgreSQL connections, addressing the pivotal issues of performance optimization and cost efficiency.

Our topic encompasses a comprehensive exploration of the following key topics:

Understanding the Cost of Connections:

We dissect the overhead associated with PostgreSQL connections, shedding light on the implications and performance bottlenecks incurred by inefficient connection management.

The Imperative Need for Connection Pooling:

Recognizing the limitations of direct client-to-server connections, we underscore the necessity of implementing a robust connection pooling mechanism to alleviate strain on database resources and enhance scalability.

Exploring various connection Pooling options:

we explore options for connection pooling and load balancing. By comparing various solutions, we equip participants with the knowledge to make informed decisions tailored to their specific requirements.

The talk firstly introduces all pertinent levels of database monitoring and then focuses on PostgreSQL and the means it provides. The meaning and importance of key metrics will be explained. As the Postgres community has already developed a lot of tools in that area, some popular common options will be highlighted together with the problems that different monitoring approaches have. To overcome some of these problems an Open Source tool from Cybertec, called pgwatch, is introduced and explained in detail to offer the simplest possibly entry into exhaustive Postgres monitoring. Also discussed will be advanced topics like anomaly detection and alerting, which can be easily implemented on top of the underlying data tier (TimescaleDB) with the help of the TICK stack or Grafana.