Celeen and Micah are both experienced developers, but had never managed large databases. Then they joined Braintree’s Database team, which is responsible for the maintenance, scaling, and application interactions of twenty high availability Postgres clusters storing a hundred terabytes that drive the processing of more than 6 billion payments a year.
In this talk, we discuss learning about the inner workings of Postgres, how Braintree works with Postgres’ strengths, design and community to help overcome stresses at scale, and the perspective gained moving from an application development team to a database team.
Coming to PostgreSQL after years of Oracle performance tuning, I realize that I approach PostgreSQL with tools and methods than are different from what a ‘native’ postgres DBA will start with. What made my job easier on Oracle is also available, and free, for PostgreSQL:
– PGIO is more appropriate than pgbench when analyzing the platform performance
– pgSentinel, with its ASH sampling approach, is more powerful than statistics and ratios
– measuring work done, from linux system calls, rather than response time, to ensure predictable and scalable performance
Let’s make them better known with some demos.
PostgreSQL’s WAL is one of the core points of PostgreSQL, and used in many areas: Backup, replication, etc.
In this talk, I will mention about everything about WAL that a PostgreSQL DBA must know:
– What is WAL?
– What does it include?
– How to read it?
– What about wal_level ?
– Replication and WAL
– Backup and WAL
– PITR and WAL
– Other topics
Window functions are a very useful tool; since their introduction 10 years ago, they have been gradually adopted for various use cases where a simple aggregation is not flexible enough: incremental totals, moving averages, etc.
The latest options introduced in PostgreSQL 11 complete the implementation according to the SQL:2011 standard, adding capabilities such as range frames, GROUPS mode and exclusions.
In this talk we will review what users can do with window functions nowadays, and illustrate each specific feature with an example which is examined in detail.
Really often, what we, developers, do something that would drive DBAs crazy: we trust our ORM to handle the creation of indexes. That’s so easy ! Why not use it right?
So what’s the problem then ? Well most ORMs only use BTree indexes. Often, it’s what we need. But why cut ourselves from all the other index types ?
This talk covers PostgreSQL indexes types (B-Tree, GIN, GiST, SP-GiST, BRIN and Hash). We will take a look into how each type is implemented in Postgres source code and why it makes it more fit to certain data types. Through the very real example of an application to organise crocodiles dentist’s appointments, examples of use-cases for each index type will be explained to understand how to choose the right type.
At the end of the talk, you should be familiar with the internal data structure of indexes, which could help you choose the best index for you data type and query operators !
Postgres has the unique ability to act as a powerful data aggregator in many data centers. This talk shows how Postgres’s extensibility, access to foreign data sources, and ability handle NoSQL-like and data warehousing workloads gives it unmatched capabilities to function in this role.
Slides at https://momjian.us/main/writings/pgsql/central.pdf
Registrations for Postgres London 2021 are open. Register now to reserve your spot.
WHERE: Virtual Event
WHEN: Wednesday | May 12, 2021
PostgresLondon 2021 Call for Papers is closed.