Among a lot of other changes, the year 2020 brings Online Conferences to us. In the Postgres community too we now record our talks at home and send a video file to be playedto a virtual audience, and sometimes shared later in a platform online. So this year I did participate in Postgres Vision 2020 where I did deliver a talk about The Art of PostgreSQL. This a talk all about the book that I have written and self-publish at The Art of PostgreSQL: learn how to turn thousands of lines of code into simple SQL queries.

Postgres has extensions, and that’s awesome! Of course as the author of CREATE EXTENSION I’m a little biased… just remember that the ability to extend Postgres is way more than just this command. The whole database system has been design from the ground up to allow for extensibility. Parts of the design is to be found in the way you can register new objects at runtime: functions of course, and also data types, operators, index support structures such as operator classes and families, even index access methods!

Today’s article shows a query that you can use to list those tables in your schemas that are using a data type which is provided by an extension.

PostgreSQL connection strings embedded in your application can take two different forms: the key-value notation or the postgresql:// URI scheme. When it comes to using psql though, another form of connection string is introduced, with command line options -h -p -U and environment variable support.

In this short article you will learn that you can use either of the three different forms in psql and thus easily copy & paste you application connection string right at the console to test it!

I did it again! Today I am releasing the new edition of my book, with a new title: “The Art of PostgreSQL”. I’m very happy (and quite excited) to declare my book as Generally Available!

The Art of PostgreSQL is the new edition of my previous release, Mastering PostgreSQL in Application Development. It contains mostly fixes to the old content, a new title, and a new book design (PDF and paperback). Content wise, The Art of PostgreSQL also comes with a new whole chapter about PostgreSQL Extensions.

The new chapter covers extensions such as hstore, pg_trgm, intarray, earthdistance, ip4r, and hll or HyperLogLog, one of the all times favorite extensions of Craig Kerstiens… who made himself available to answer my questions and share his view of PostgreSQL Extensions in an interview!

An SQL Injection is a security breach, one made famous by the Exploits of a Mom xkcd comic episode in which we read about little Bobby Tables:

PostgreSQL implements a protocol level facility to send the static SQL query text separately from its dynamic arguments. An SQL injection happens when the database server is mistakenly led to consider a dynamic argument of a query as part of the query text. Sending those parts as separate entities over the protocol means that SQL injection is no longer possible.

We have loaded Open Street Map points of interests in the article The Most Popular Pub Names — which compares PostgreSQL with MongoDB for simple geographical queries, and is part of our PostgreSQL Extensions article series. In today’s article, look at how to geolocalize an IP address and locate the nearest pub, all within a single SQL query!

For that, we are going to use the awesome ip4r extension from RhodiumToad.

PostgreSQL is a relational database management system. It’s even the world’s most advanced open source one of them. As such, as its core, Postgres solves concurrent access to a set of data and maintains consistency while allowing concurrent operations.

In the PostgreSQL Concurrency series of articles here we did see several aspects of how to handle concurrent use cases of your application design with PostgreSQL. The main thing to remember is that a Database Management System first task is to handle concurrency access to the data for you.

A previous article in the PostgreSQL Concurrency series covered how to manage concurrent retweets in an efficient way: in Computing and Caching, we learnt how to maintain a cache right in your PostgreSQL database, using MATERIALIZED VIEWS. We also had a look at how to take care of Batch Updates and Concurrency.

While in the first case we are providing a solution to a technical problem where we want to solve performance issues while keeping the same semantics, in the second case we are actually implementing a part of the application’s Business Logic as a scheduled job.

Today’s article shows a modern technique to handle the scheduling of those business oriented activities that are not tied to any user activity. When thinking about it this way, you certainly don’t want to implement the backbone of your business logic in a shell script that’s directly maintained in the production environment, do you?

This article fits in the PostgreSQL Concurrency series, where we installed a tweeter like application schema and had all the characters from Shakespeare’s A Midsummer Night’s Dream tweet their own lines in our database in PostgreSQL Concurrency: Data Modification Language.

A previous article in the series covered how to manage concurrent retweets in an efficient way: Computing and Caching, where we learn how to maintain a cache right in your PostgreSQL database, thanks for materialized views. We even went as far as maintaining an external cache in another application layer using PostgreSQL LISTEN and NOTIFY features and a Golang application.

Today’s article is going to address concurrency in the context of updating data in a batch. This activity is quite common, as soon as your system is connected to other systems either internally or with external providers. While it’s pretty easy to ingest new data, and easy enough to update data from an external source when nothing happens in your database, doing the operation safely with concurrent activity is more complex. Once more though, PostgreSQL comes with all the tooling you need to handle that situation.

This article fits in the PostgreSQL Concurrency series, where we installed a tweeter like application schema and had all the characters from Shakespeare’s A Midsummer Night’s Dream tweet their own lines in our database in PostgreSQL Concurrency: Data Modification Language.

A previous article in the series covered how to manage concurrent retweets in an efficient way: Computing and Caching, where we learn how to maintain a cache right in your PostgreSQL database, thanks for materialized views.

Today’s article shows how to maintain an external cache in another application layer. In this article we are going to maintain an in-memory cache in a Golang service, using PostgreSQL LISTEN and NOTIFY features.