PostgreSQL

Slow things down to make them go faster

D.postgresql
Jimmy Angelakos
<p>It's easy to get misled into overconfidence based on the performance of powerful servers, given today's monster core counts and RAM sizes. However, the reality of high concurrency usage is often disappointing, with less throughput than one would expect. Because of its internals and its multi-process architecture, PostgreSQL is very particular about how it likes to deal with high concurrency and in some cases it can slow down to the point where it looks like it's not performing as it should. In this talk we'll take a look at potential pitfalls when you throw a lot of work at your database. Specifically, very high concurrency and resource contention can cause problems with lock waits in Postgres. Very high transaction rates can also cause problems of a different nature. Finally, we will be looking at ways to mitigate these by examining our queries and connection parameters, leveraging connection pooling and replication, or adapting the workload.</p>
Topics: 1. Understand what we mean by high concurrency. 2. Understand how high concurrency affects Postgres performance. 3. Understand how locks/latches affect Postgres performance. 4. Understand how high transaction rates can affect Postgres. 5. Mitigation strategies for high concurrency scenarios.

Additional information

Type devroom

More sessions

2/6/22
PostgreSQL
Francesco Tisiot
D.postgresql
<p>Optimization problems are everywhere, from deciding which clothes to pack in our luggage (aka the knapsack problem), to selecting the tasks that will be worked during a sprint. Trying to solve these type of problems by hand is a tedious task often resulting in sub-optimal decisions.</p> <p>In this talk, we'll understand how PostgreSQL recursive queries can help. Starting from the proper problem definition, we'll then explore how to build queries that call themselves recursively, what are the ...
2/6/22
PostgreSQL
Boriss Mejias
D.postgresql
<p>PostgreSQL provides great support for JSON objects and every developer working with this data type should learn how to take advantage of it. Instead of going through the documentation on how to use JSON in PostgreSQL, in this talk we will use a case study to learn by example. We will also complement the usage of JSON by introducing other data types such as ARRAY. We will also review some design decisions to turn JSON objects into columns and vice versa when it's convenient. This talk will ...
2/6/22
PostgreSQL
Ryan Booz
D.postgresql
<p>Exploring new features in PostgreSQL or reproducing an unusual query plan can be tricky without representative data to utilize. While there are a plethora of sources for sample data and tools to import it, you can end up spending too much time finding representative data to work with. In our day-to-day work at Timescale, we often need to quickly create lots of sample time-series data to demonstrate new features, run a benchmark, or help community members with examples as they learn.</p> ...
2/6/22
PostgreSQL
Bruce Momjian
D.postgresql
<p>Postgres has been a vibrant project for decades, and probably will be popular for decades to come. However, as with any complex endeavor, challenges are always lurking. This talk explores the many project, competitive, and technical challenges in the future that could derail its success. By exploring these challenges, we will be better able to avoid them.</p>
2/6/22
PostgreSQL
Juan Pan
D.postgresql
<p>As the most popular open source relational database in the world, PostgreSQL keeps attracting the significant attention it deserves. With the ever increasing data storage and query requirements, new challenges are brought forward for horizontal elastic expansion and security of the PostgreSQL database. How to provide existing PostgreSQL databases with incremental capabilities such as data sharding, data encryption and other functions is of great concern to many PostgreSQL users. This session ...
2/6/22
PostgreSQL
Attila Tóth
D.postgresql
<p>Since PostgreSQL 9.3 introduced materialized views, it’s been an important feature that many users leverage to power dashboards, pre-compute information, or execute common queries in a much faster manner.</p> <p>On the flip side, if your database often gets updated, keeping your materialized views up-to-date can be challenging. Especially when working with time-series data where timeliness is often an important requirement. In this talk, I will share some tactics to keep your materialized ...
2/6/22
PostgreSQL
Daniel Westermann
D.postgresql
<p>This talk is for those who are new to PostgreSQL or those who just started, or all the others that want to hear a personal story: When I started with PostgreSQL around 10 years ago, I came with an Oracle background. It took me quite some time to understand how the PostgreSQL project is organized, how the community is working and how to deal with issues I've faced when I needed support. This is not a technical talk at all, but it should save you quite some time in your journey with ...