Many of us who work with relational databases don’t think too much about SQL. We use it, of course, but it’s easy to forget that SQL — like other languages — has grown, evolved, and improved over the years. There are many features included in modern SQLs, and PostgreSQL has included many of them in its recent releases. In this talk, Markus Winand describes many of these features — how to use them, and also when to use them.
In recent years, PostgreSQL has gained a number of facilities that were normally associated with NoSQL databases, such as a key-value store and JSON support. In the latest (9.4) release, the JSONB data type brought high performance and many new features. In this talk, Micah Yoder describes the JSONB data type, how it can be used from within SQL, how its performance compares with MongoDB, and how such features can be used from within Python.
What happens when, within a Ruby on Rails application, you use ActiveRecord to submit a query? In this talk, Pat Schaughnessy traces your query all the way through to the database (in this case, PostgreSQL), and demonstrates how much work Ruby and the database are doing. If you’ve ever wondered how much work happens when you invoke a single Ruby method, now you’ll have your answer.
PostgreSQL is obviously a relational database. (The “SQL” in its name sorta gives that away.) But PostgreSQL has, over the last few versions, added a growing number of features normally associated with NoSQL. In this talk, Vibhor Kumar describes some of the latest NoSQL features to be added and improved in PostgreSQL, , including the Hstore key-value storage and the JSONB storage type.
PostgreSQL has been around for 20 years, and continues to be a popular and powerful open-source project, with a growing number of companies adopting it for their database needs. The PostgreSQL developers typically release a new version toward the end of every calendar year; this year, we can expect version 9.5 at some point late in 2015. In this talk, Magnus Hagander describes the new features that PostgreSQL 9.5 will include. If you use PostgreSQL (or are wondering why so many people are now adopting it), this talk will shed light on the future directions of PostgreSQL, and will provide you with the insights you need to prepare for this new version.
Working with databases means, almost inevitably, trying to improve the speed of some of your queries. In the case of PostgreSQL, successful query tuning means understanding how the database works — and that includes understanding the query optimizer, the “brains” of PostgreSQL, which decides how to join your tables, what to read from disk, and which indexes to apply (among other things). In this Webinar, Bruce Momjian describes PostgreSQL’s query optimizer in great detail, providing many insights that come in handy when working to improve the speed of your queries.
It’s common for applications to wait for data to arrive in a database. What’s a good way to do that? One is to poll the database every so often, checking to see if our data has arrived. But there’s another way — maybe the database can tell us when something has happened, and we can be informed asynchronously. It turns out that PostgreSQL supports just this sort of notification, using the NOTIFY and LISTEN commands. Moreover, if you’re using Python, you can easily subscribe to PostgreSQL’s notification channels, and wait for PostgreSQL to inform your Python program that new data has come in. This talk, by Daniel Rocco, shows you how a combination of PostgreSQL and Python makes it quite easy to use these asynchronous notifications.
PostgreSQL offers a wide variety of data types, which enable to us to create precisely defined, constrained, and accurate models of our data. PostgreSQL 9.2, introduced several years ago, included a “range” type, which lets you describe … well, ranges of data. In this talk, Jonathan S. Katz describes this new type, and shows what you can do with it. You might think that the range type isn’t that useful, but he demonstrates a number of examples of where it can be quite useful.
MySQL and PostgreSQL are both well-known open-source relational databases. What happens if you want to move your application from MySQL to PostgreSQL? Data migrations are always difficult; if you have a great deal of data, or you have used many of MySQL’s built-in functionality, then you may encounter problems. In this talk, Dimitri Fontaine describes a real-life example of migrating a very large data set from MySQL to PostgreSQL. What was easy, and what was hard? What does he recommend to others interested in doing so?
PostgreSQL uses processes, rather than threads. In order for them to communicate, they use shared memory. In this talk, PostgreSQL contributor Bruce Momjian describes how PostgreSQL uses shared memory, and what data is contains and passes among the various processes. If you always wanted to know how PostgreSQL stores and retrieves information before it goes onto the disk, this talk is for you!