PostgresWorld
2021 Postgres Conference Webinars
Online / United States
January 05 - December 31, 2021
Conference Highlights
All we need to work with SQL is just SQL
Traditionally, we use Languages and Frameworks interact with Relational Databases. While these are effective in many ways, One problem is that, each of these brings new learning curve. It will be nice if we use SQL itself as the core skill and build application with plain simple SQL skills. In this webinar, I will demo this approach with a case study that builds simple, high performing Java application using Postgres database.
PostgreSQL Forks and Knives
The number of PostgeSQL forks keeps growing. Some of them are really ancient, while others are relatively new. Redshift, Greenplum, Cockroach, Timescale, RDS, Vertica, you name it. All modern clouds have their own forks of traditional PostgreSQL. But are these databases our familiar PostgreSQL, or something absolutely different? DBeaver supports all the main PostgreSQL forks, and we can confidently say that it is really surprising to find unexpected differences or similarities. If...
Postgres for SQL Server Users
As Postgres gains popularity in the DBMS world, it has become a very attractive alternative for Microsoft SQL Server, allowing organizations that make the leap to take advantage of modern features while saving big money on software licensing. In this talk I will go over the top reasons to choose Postgres over SQL Server and outline a plan for performing such migrations. Key learnings: The main differences to consider when switching from SQL Server to Postgres Data type mappings...
Configuring PostgreSQL for Faster Analytic Query Performance
Are you using PostgreSQL for query-intensive applications like reporting and charting, analytics (OLAP/HTAP), or data warehousing? Join us on February 4 to learn straightforward ways to get the best possible query performance from PostgreSQL. Swarm64 Solution Architect, Sebastian Dressler, will cover proven PostgreSQL configuration tuning best practices that have helped PostgreSQL users significantly boost analytic query performance. Topics include: Configuring for parallel...
Blockchain as a Database
Throughout the history, exchange of trusted information among people played an essential role in every aspect of their lives: socially, economically and politically. Blockchain is an innovation in database technology for keeping temper proof (trusted) data in a permanent, immutable, decentralized, global, and trustless ledger. It combines distributed computing, databases, networks and cryptography and is relatively new, and it is rapidly evolving. It allows people, organizations and machines...
PostgreSQL Autovacuum Tuning Lessons From The Field
Do you support the PostgreSQL database behind a demanding application? Do your users have queries that mysteriously get progressively slower over time and then run normally again without explanation? Is AutoVacuum working on a particular table(s) almost all the time? This presentation offers guidance on identifying good practices around monitoring AutoVacuum, how to approach tuning, and how to avoid the most common pitfalls I see during tuning exercises as a consultant in the field. In...
Scaling Beyond Pg-Bouncer & Pg-Pool-II: Advanced Traffic Management
Database proxies intelligently manage Postgres connections to improve performance and scale. In this session, we will highlight deficiencies of Pg-bouncer and Pg-Pool-II that users should know, and propose solutions. Features to be discussed include: Connection pooling Read/write split with Strong Consistency Query caching Persisted connection, automated Postgres failover Active Directory / LDAP integration Includes live demo and Q&A
OpenStreetMap to PostGIS: Easier and Better!
This session will be a live demo to show how easy it is to import high quality OpenStreetMap data into PostGIS / Postgres. The long-used osm2pgsql tool has a new Flex output (Flex == "flexible") that has completely changed the game! This demo highlights how the osm2pgsql Flex output is being used by PgOSM-Flex (https://github.com/rustprooflabs/pgosm-flex) to provide a high quality "off-the-shelf" experience, and how it can be further customized to suit your geospatial data needs! The...
The Data Bottleneck - The Data Engineer And How We Are Trying To Fix This
Interview Query recently reported that the number of data engineering roles at companies continues to grow annually, where as surprisingly, the number of data scientists has shrank back in comparison. Data engineers spend much of their time building data pipelines and managing data warehouses, and there are lots of tools for data engineers to choose from when it comes to developing data pipelines. In this talk, we will discuss how in many companies currently, the data engineer is...
Building a Blockchain Inside PostgreSQL
Blockchains provide a number of useful features that a traditional SQL database fails to provide. These include immutable data, consensus and data replication. In this example a authenticated blockchain is implemented by using a number of features of PostgreSQL and extending PostgreSQL to allow for Peer To Peer (P2P) communication and block consensus. This also acts as a good example of how to use P2P and a consensus mechanism for reliable data distribution over faulty...
SQL for Time Series Using QuestDB
The nature of time series data means that 90% of the transactions occurring are new records being inserted, and queries are most likely to be recent time ranges. The Open Source QuestDB is optimized for this usage pattern. We have several features that provide high-performance inserts and reads over other PostgreSQL based Time Series solutions. This webinar covers how storage and ingestion work in QuestDB, how to use popular PostgreSQL clients with QuestDB, and why ANSI SQL is a perfect fit...
Better late than never - Stored Procedures support in PostgreSQL
Finally we have stored procedures in PostgreSQL 11, better late than never. PostgreSQL versions prior to 11 only supported functions, the support for stored procedures was added in PostgreSQL 11. It is added as a new schema object which is similar to functions and but without a return value and with some subtle differences and benefits. The main benefits of Stored procedure are the following : Transaction control for allowing commit and rollback inside procedures Making Oracle...
OtterTune: An Automatic Database Configuration Tuning Service
Database management systems (DBMS) expose dozens of configurable knobs that control their runtime behavior. Setting these knobs correctly for an application's workload can improve the performance and efficiency of the DBMS. But such tuning requires considerable efforts from experienced administrators, which is not scalable for large DBMS fleets. This problem has led to research on using machine learning (ML) to devise strategies to optimize DBMS knobs for any application automatically. The...
Faster PostgreSQL query performance on Amazon with PG Nitrous
Do you need faster Amazon RDS query performance? Need to scale end-user reporting and dashboarding in your RDS applications or add more complex analytics? Join us to learn about PG Nitrous, a new PostgreSQL cloud solution hosted on AWS EC2 by Swarm64. In this webinar we will demonstrate how to accelerate Amazon RDS PostgreSQL query performance by 30x by using PG Nitrous as a fast-query replica. Agenda: What's PG Nitrous? Using PG Nitrous as a fast-query replica for Amazon RDS...
Creating a Resilient PostgreSQL Cluster with Kubegres
Kubegres (https://www.kubegres.io/) is a Kubernetes operator allowing to deploy a cluster of PostgreSQL instances with data replication, failover and backup enabled out-of-the box. It brings simplicity when using PostgreSQL considering how complex managing stateful-set's life-cycle and data replication could be with Kubernetes. To manage replications, failover and backup, Kubegres is 100% relying on the PostgreSQL standard libraries bundled in the [PostgreSQL...
When it All Goes Wrong - Incident Response in Large Postgres Databases
When things go wrong with large, heavily loaded systems, it can take a significant amount of time to recover. For this reason, the same response patterns with other critical systems (nuclear power plants, jumbo jets) apply to disaster management here as well. This talk is a story of how we at Adjust managed one such disaster and what you can learn from it. The talk will also include what we learned from it as well. In this presentation, you will learn: Techniques and factors for...
Making Postgres Fly on Kubernetes
Today, running Postgres on Kubernetes is not out of the ordinary. But given all of Kubernetes capabilities, doing a 1:1 between how you run Postgres on Kubernetes and how you run it outside of it is a loss of opportunity. The Open-Source StackGres (https://stackgres.io) project has leveraged Kubernetes API to its fullest to make Postgres not run, but fly, on Kubernetes! Join this webinar with live demos to see how to: Collect Postgres and Patroni logs from all pods into a...
Building Open Source Teams
This presentation highlights the challenges of building and managing an open source team of volunteers. Topics include motivation, communication, and project management.
PostgreSQL HA with Patroni, etcd and HAProxy
This presentation will provide step by step details on how to deploy a highly available PostgreSQL cluster using Patroni, etcd and HAProxy. Patroni is a python based solution to provide HA and automatic failover in a PostgreSQL cluster. Patroni depends on etcd and HAProxy to provide all the features. This presentation will provide steps to deploy and test etcd and HAProxy independently. Once all the dependent modules are deployed and tested to work, the presentation will then provide steps...
The NoSQL Store Everyone Ignored
The talk is based on my original article that I wrote back in 2011 (https://maxpert.tumblr.com/post/14062057061/the-key-value-store-everyone-ignored-postgresql) and followed up with friend feed case study (https://maxpert.tumblr.com/post/32461917960/migrating-friendfeed-to-postgresql). We will do a walk through history of HSTORE, and how we can now use JSONB support in PostgreSQL, discuss what makes it enticing, and comparable to NoSQL stores like MongoDB or CouchDB. We will look at how...
Beyond Off-the-Shelf Consensus
There have never been more commercial tools available for building distributed data apps — from cloud hosting services, to cloud-native databases, to cloud-based analytics platforms. So why is it still so hard to make a successful app with a global user base? One of the toughest challenges cloud offerings take on is the problem of consensus, abstracting away most of the complexity. That's no small feat, given that this is a hard enough problem that people spend years getting a PhD just...
Will Postgres Live Forever?
This presentation explains how open source software can live for a very long time, and covers the differences between proprietary and open source software life cycles. It also covers the increased adoption of open source, and many of the ways that Postgres is innovating to continue to be relevant.
Using the PostgreSQL recursive CTE to compute Bacon numbers for actors listed in the IMDb
Students of Computer Science are often given this famous assignment: Compute the Bacon numbers for actors listed in the IMDb up to, for example, five hops The requirement is easy to understand and is readily communicated by the example "What is Al Pacino’s Bacon Number?" The answer is given by this path, where each step goes via a movie or TV show that starred some pair of actors: Kevin Bacon > Bill Murray > Billy Crystal > James Earl Jones > Al Pacino This shows that...
Episode 1: Introducing Developer Productivity Engineering
The emergence and adoption of DevOps practices at scale, following "Constraints"-based approaches such as Agile and Lean, has helped many businesses optimize their production strategy and keep pace with customer demand. Despite these advancements, hidden productivity bottlenecks still exist in our common build, test, and CI practices. Developers spend an inordinate amount of time waiting on feedback from local or remote build and test cycles. Tests can be non-deterministic or otherwise...
Long Queries and the Art of Full Scan
Some queries just can’t run in a fraction of a second, no matter how well written. This does not mean they can’t be optimized. Many practitioners hold that since analytical reports do not have strict response time requirements, it is not important how fast or slow they run. In extreme cases, report developers make no effort to make sure that reports are complete in a reasonable time, giving the excuse that the query only runs once a day or once a week or once a month. This is a dangerous...
What Next in Logical Replication
Logical replication has been there since 10.0 and with each release, it is getting better. This talk will start with the basic architecture of Logical replication in PostgreSQL and then cover the various ways in which it can be helpful to users. One of the shortcomings of logical replication as compare to physical replication is that currently, it allows the transaction to be replicated only once it is committed. This can create a large apply lag on the subscriber side for...
Reducing Costs and Improving Performance With Data Modeling in Postgres
During this talk we will explain how PostgreSQL organizes the data internally, how the Free Space Map (FSM) works and how we can reorganize the data model to take advantage of data alignment inside blocks reducing the size of the data in disk and consequently in memory, which can save money and improve performance.
Episode 2: Accelerating Build Feedback
Our first session focused on some of the theory behind the emerging practice of Developer Productivity Engineering (DPE) and some of the key metrics that should be observed and improved to ensure developer success. In this session, we will focus on the first major productivity bottleneck that DPE addresses -- the amount of time it takes for developers to get feedback about their build, and the way that feedback is delivered. Slow feedback is toxic to development because it interrupts the...
How to build a PostgreSQL-backed website quickly
We will use Ruby-on-Rails to build a PostgreSQL backed website. We'll use some of the excellent tutorials to build a small site with users and micro-content. We’ll talk about what to watch out for, which tasks to leave to the web framework, what to do on the database side. We'll show the various stages of development and we will leave the final product up on the web, for your use. To add a bit of spice, we will have the micro-content be links to various PostgreSQL resources.
Functions: Use and Misuse
Functions are among the most underused and misused of PostgreSQL objects. Since all modern programming languages include user-defined functions, people often assume that database functions are cut from the same cloth and if you know how to write functions and when to write functions in an application programming language, you can apply this knowledge to PostgreSQL. This could not be further from the truth. In this presentation, we will discuss how PostgreSQL functions are different from...
Timestamps, timezones, and interval arithmetic: what you need to know, and what you don’t need to know
Anecdotal reports indicate that PostgreSQL neophytes are daunted by the date and time data types, and by how operations that use values of these data types might be affected by the session's timezone setting. They find the topic to be mysterious and frightening. Even experienced developers struggle when they first embark on a critical project that relies on this functionality. I recently completed a careful and exhaustive study of the topic aiming for total understanding, complete in...
Episode 3: Accelerating Test Execution
Our first two sessions focused on Developer Productivity Engineering (DPE) as a practice and the specific tools and techniques used to accelerate the software build process. Acceleration of the build is only one facet of an overall DPE strategy, as significant opportunities for improvement still exist for businesses in the time spent waiting on test executions to complete. Most build systems still execute tests serially, and they don’t track metrics such as test flakiness, failures, and...
Power use of Indexes in PostgreSQL - A user perspective.
There have been many presentations about the Different Indexes in PostgreSQL ( B-Tree, HASH, GIN, GiST etc), especially from the architecture perspective. But these talks always lacked details from the user perspective on the selection of indexes. It is common to see that architects and developers fail to select the right types of index and the way it should be used. Just an overview of all types of indexes also won't help much in decision making. In this talk gives details...
Evolution of Partitioning Features in PostgreSQL - A super charged elephant
The Partitioning features in PostgreSQL is not something new. But it has matured over several years, release after release, especially the last 4 releases. The evolutionary nature (small changes in each version) is often overlooked by users. These small changes resulted in a build-up of a powerful Partitioning capability in PostgreSQL new versions. Now it is considered as capable of even replacing the worlds biggest database software even in Data Warehouses workloads. Since there...