PostgreSQL

A Deep Dive into PostgreSQL Indexing

H.2214
Ibrar Ahmed
Indexes are a basic feature of relational databases, and PostgreSQL offers a rich collection of options to developers and designers. To take advantage of these fully, users need to understand the basic concept of indexes, to be able to compare the different index types and how they apply to different application scenarios. Only then can you make an informed decision about your database index strategy and design. One thing is for sure: not all indexes are appropriate for all circumstances, and using a ‘wrong’ index can have the opposite effect that you intend and problems might only surface once in production. Armed with more advanced knowledge, you can avoid this worst-case scenario! We’ll take a look at how to use pgstatstatment to find opportunities for adding indexes to your database. We’ll take a look at when to add an index, and when adding an index is unlikely to result in a good solution. So should you add an index to every column? Come and discover why this strategy is rarely recommended as we take a deep dive into PostgreSQL indexing.

Additional information

Type devroom

More sessions

2/2/20
PostgreSQL
Claire Giordano
H.2214
Postgres is growing like gangbusters: in popularity, in adoption, and in the size of the ecosystem. And over 400 developers contribute code to Postgres today: their expertise, design chops, and skill are big factors in the increasing popularity of Postgres. But what if you’re not a developer? Are there things you can do to help grow the usage and popularity of Postgres? And are these non-code ways to contribute to Postgres important? Valued? Will they make a real difference? If you love ...
2/2/20
PostgreSQL
Stephen Frost
H.2214
Where, oh where, is all that time going? What in the world is that database thing doing?! This talk will help you understand what's happening (and why) and how to analyze poor query performance. We'll also go over steps and strategies to take to improve them and get the performance (and scalability!) you need. It all starts with figuring out what queries are slow, we'll do that by going into the various PostgreSQL configuration options for logging queries and a few helpful modules for getting ...
2/2/20
PostgreSQL
Alexander Kukushkin
H.2214
Many DBAs avoid any kind of cloud offering and prefer to run their databases on dedicated hardware. At the same time companies demand to run Postgres at scale, efficiently, automated and well integrated into the infrastructure landscape. The arrival of Kubernetes provided good building blocks and an API to interact with and with it solve many problems at the infrastructure level. The database team at Zalando started running highly-available PostgreSQL clusters on Kubernetes more than two years ...
2/2/20
PostgreSQL
Ilya Kosmodemiansky
H.2214
Even an experienced PostgreSQL DBA can not always say that upgrading between major versions of Postgres is an easy task, especially if there are some special requirements, such as downtime limitations or if something goes wrong. For less experienced DBAs anything more complex than dump/restore can be frustrating. In this talk I will describe why we need a special procedure to upgrade between major versions, how that can be achieved and what sort of problems can occur. I will review all possible ...
2/2/20
PostgreSQL
Jimmy Angelakos
H.2214
How to navigate the rich but confusing field of (Full) Text Search in PostgreSQL. A short introduction will explain the concepts involved, followed by a discussion of functions, operators, indexes and collation support in Postgres in relevance to searching for text. Examples of usage will be provided, along with some stats demonstrating the differences.
2/2/20
PostgreSQL
Federico Campoli
H.2214
Reading the manual before starting a new work is always a good practice. However some situations like pressure for delivery or lack of attention may lead to wrong assumptions that cause unpredictable results or even disasters. The talk, in a semi serious way, will walk the audience through some of corner cases caused by the lack of the good practice of RTFM.