If your PostgreSQL query runs slowly, what should you do? How can you find out what has gone wrong, and how to optimize it? The “explain” command (and related ‘”explain analyze” command) tell you what the PostgreSQL query planner intends to do, and then (if you use the “analyze” option) executes the query, as well. The problem is that the output from “Explain” can often be cryptic to newcomers. In this talk, Josh Berkus introduces the “explain” command, and shows how database developers and administrators can use it to improve the speed of their queries.
The upcoming (9.5) version of PostgreSQL will include the long-awaited “UPSERT” functionality, which makes it possible to insert-or-update with a single query. How is this different from other functionality, such as MERGE? What are the use cases for this functionality? And what should we keep in mind when we use it? In this talk, Peter Geoghegan introduces UPSERT, compares it with other alternatives (including in MySQL), and demonstrates when and why we can look forward to seeing it in the upcoming version of PostgreSQL.
Everyone wants their database to run faster. Optimizing databases is a big and complex topic, but there are some simple rules of thumb that, if you follow them, can help your database (and the applications using the database) to run faster. In this talk, Josh Berkus describes a number of techniques you can use to improve your database performance — sometimes, in major ways, with a small amount of thought and work.
When we think of programming databases, we often think of doing so either from a standard programming language, such as Python or Java, or from SQL. In the case of PostgreSQL, we get extra flexibility, in that programming languages can be external (via a client library) or internal (via the PL/* languages). However, users of Unix have one additional way to work with PostgreSQL, namely shell scripting. The Unix shell is powerful and flexible, and can be used to communicate with our database in a number of ways — for maintenance, data loading, and even executing queries. In this talk, Joe Conway describes a number of ways in which Unix shell scripts can be used to communicate with PostgreSQL, and thus integrate the database into an even wider array of tasks and systems.
SQL vs. NoSQL has become a huge war, in some ways. Which is better? The answer is, of course, “it depends.” Each has its advantages and disadvantages. But PostgreSQL, which is becoming more of a platform than just a database, now offers us ways to combine SQL and NoSQL documents into the same database model. How does this work? When and why would we want to do this? In this talk, Jamey Hanson gives us a number of examples of how, when, and why combining SQL and NoSQL is both possible and preferable.
Every database needs to be backed up; the data is too important to lose, and there’s often no way to reproduce it. The standard backup program for PostgreSQL is pg_dump, but perhaps you want something with higher performance or greater flexibility. PgBackRest is one such tool that has is maintained and used by some members of the PostgreSQL community, which aims to solve many of the problems currently experienced when creating backups. In this talk, David Steele introduces PgBackRest, and describes its motivation, operation, and use cases.
PostgreSQL is a highly extensible database; it allows you to create new types, as well as functions to work with those types. PostGIS is an add-on for PostgreSQL that turns it into a spatial database — one that can keep track of where things are located, and perform queries based on locations. In this talk, Paul Ramsey describes the main features of PostGIS, and when (and how) you would want to take advantage of them.
Is it a good idea to put PostgreSQL in the cloud? If so, then which cloud (“Platform as a service” — “PaaS”) provider provides the best performance? And which options should you use, once you have settled on a vendor? These are questions that I’m increasingly asked by my clients, and I was thus particularly happy to have discovered this talk, in which Josh Berkus describes his investigation and comparison of various PostgreSQL cloud providers. If you’re wondering whether the cloud is an appropriate location for your database, but weren’t sure which provider might make the most sense, this talk should be quite interesting for you.
PostgreSQL is a great database — but every technology involves trade-offs and limitations. In this talk, Robert Haas lists many of issues that PostgreSQL faces, from the on-disk format to logical replication to connection pooling. What issues exist, what can be done to improve things, and what are the core developers doing to move in that direction? If you’re a user of PostgreSQL, or just curious to learn about the architecture of relational databases, this talk should be of great interest to you.
One of the things that my students and clients most ask for in PostgreSQL is multi-master replication. That is, they’re OK with the existing master-slave replication, in which one server handles both reads and writes, and the others (slaves) are read-only. In this talk, PostgreSQL core developer Andres Freund describes a number of different master-master solutions that have been proposed and implemented over the years, and describes a project on which he’s working that he hopes (and expects) will pave the way forward for multi-master replication in the coming years.