background

Select For Update, PGx Framework, Cool Additions, Full-Text Search | Scaling Postgres 186

Join Over 1,000 Engineers & Get New Episodes Weekly!

In this episode of Scaling Postgres, we discuss select for update, the pgx framework to generate extensions, cool additions to Postgres 14 and full-text search.

Content Discussed

YouTube Video

Podcast Audio

Transcript

All right. I hope you, your friends, family, and coworkers continue to do well. Our first piece of content is "SELECT FOR UPDATE and its behavior with foreign keys in PostgreSQL". This is from migops.com and they're talking about using SELECT FOR UPDATE. Ideally, to avoid a deadlock problem or basically, you want to serialize access to a particular row. So maybe you want to update this row by multiple workers where you don't want them to update over one another. So they have a scenario where you're using SELECT FOR UPDATE and then you do an update of the row and you do this within a transaction. Now the particular issue that they're discussing is when you have a child table and you're basically updating the parent, but you have a child table that has a form key constraint relative to the parent table.

So for example, this works fine and doesn't cause deadlocks between updating different rows of the parent table. But it can cause locking when you're trying to do something with the child table because it needs to validate this row. So they show an example here where you're doing a beginning transaction, put a SELECT FOR UPDATE, and do the update. Whereas when you try to do an insert into the child table, it just says waiting. Basically, it waits because this first transaction hasn't been committed yet. But there is a way around it. You can use a SELECT FOR UPDATE but do FOR NO KEY UPDATE.

What that basically means is it's not going to be updating the key that you're referencing. So it is possible to allow a select statement to be able to at least query this row. So this is the same process as before, starting a transaction, doing the SELECT FOR UPDATE but doing a FOR NO KEY UPDATE. Then you do the update, it is updated but it's not committed yet. In here, you can actually do another transaction, do the insert and it inserts without an issue at all because it is able to acquire a select lock on that row because you're not going to be updating the key value referenced here. So this is a great way to increase concurrency when you're using foreign key constraints and you are using SELECT FOR UPDATE. So if you want to learn more, I encourage you to check out this blog post.

The next piece of content- "PGDD EXTENSION MOVES TO PGX''. This is from rustprooflabs.com. Now they're talking about an extension that they developed called PgDD which is a data dictionary. It basically allows you to just do common selects to look at the structure of the tables rather than using psql and doing \d and \d+. You can just use SQL to query and find out the structure of the tables quite easily. But what most of this post discusses is that it's been recently rewritten from using, say, pure SQL or pgSQL into using a pgx framework which is written in Rust. So he went through the process of trying to convert this extension, PgDD, to just be a pure C extension, but he ran into some issues with it. So he actually tried this pgx framework that is reliant upon Rust and he found this to be a much better experience to develop an extension. So this is a separate library that you would have to depend upon, but he had a very good experience with it and he wanted to share that with the community. So if you're interested in that, you can check out this blog post.

The next piece of content- "Cool New Contributions to PostgreSQL 14". This is from enterprisedb.com, and like other blog posts, this is a highlight of what they find significant with the release of Postgres 14. They cover different areas such as security, manageability changes, scalability and performance, application performance, tuning and indexes. Complex query processing, improvements using extended statistics, different types of standardization, enhancements to logical replication, and even some benefits to sharding. So if you want to review another post about the improvements that have come to Postgres 14, you can definitely check out this one.

Next piece of content- "Probing Text Data Using PostgreSQL Full-Text Search''. This is from arctype.com, and they're talking about using Full-Text Search in Postgres. So it's just a post that runs through how you can set it up and start using it. Fundamentally, it's basically starting to use ts_vector fields to store your data and then ts_query fields to query against that data, and ideally using a gin index to be able to access those. They also have a brief discussion on dictionaries and ranking as well. The post actually doesn't talk about some of the ways that you can keep this ts_vector field up to date, like generated columns or maybe using triggers. But if you want to learn more, you can check out this blog post.

The next piece of content- "A Complete Guide to PostgreSQL Backup & Recovery". This is from enterprisedb.com, and they're talking about different ways that you can backup and recover Postgres. The built-in utilities are pg_dump to backup a single database or pg_dumpall. This is basically a logical way to backup the database. They also have pg_base_backup as the physical base tool, which basically backs up the individual files of the database. They also mentioned some external options such as just doing a physical backup of the directory using say, tar for example.

Or you could do a block-level backup of the volume where the data resides, or you can also use third-party tools such as Barman and pgBackRest. So it goes through talking about how to use pg_dump, pg_dumpall, and pg_base_backup, as well as doing a Point in Time Recovery. Then they also talk a little bit more about Barman because that is developed by EnterpriseDB and how you could use that to backup your system as well. So if you want to learn more, you can check out this blog post.

The next piece of content- "Transition Tables and Incremental View Maintenance". This is from yugonagata-pgsql.blogspot.com. They're talking about this new feature that is in development called Incremental View Maintenance, where basically you can create a materialized view without having to refresh it. It keeps itself up to date through, say, a trigger mechanism. This talks about the development of this feature, particularly the transition tables that they're working on. So if you want to learn more about that, you can check out this blog post.

The next piece of content- "How to Set Up Kerberos Authentication Using Active Directory with PostgreSQL Database". This is from enterprisedb.com and this post basically shows you how to set up Kerberos authentication with the Microsoft Active Directory in Postgres. So if you have a need to do that, you can check out this post.

Next piece of content- "OPENSTREETMAP SERVICE BY CYBERTEC". This is from cybertec-postgresql.com. They've launched a free service that provides you a way to download in terms of Postgres dump files. OpenStreetMap helps with data from different countries, and you can just check out gis.cybertec-postgresql.com to find out more about it and how to download and get started with those files.

The next piece of content- "PostgreSQL 14 on Kubernetes (with examples!)". This is from blog.crunchydata.com and they've updated their PGO, their Postgres Operator for Kubernetes to work with Postgres 14. This blog post explains how to get started with it. So if you're interested in that, you can check out this one.

The next piece of content, the PostgreSQL person of the week is Amit Kapila. So if you're interested in learning more about Amit and his contributions to Postgres, you can check out this blog post.

Lastly, we had another episode of The Rubber Duck Dev Show this week. This one was on "When Should You Comment Your Code". So feel free to check out this content if you are interested.

episode_image