Episode Transcript
[00:00:00] Speaker A: In this episode of Scaling Postgres we talk about select for Update, PGx Framework, cool additions, and full text search. I'm Kristen Jameson, and this is scaling postgres episode 186.
Alright, 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 Updates. 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. Do a select for Update, do the update. Whereas when you try to do an insert into the child table, it just says Waiting, waiting. Basically it waits because this first transaction hasn't committed yet. But there is a way around it. You can use a select for Update but do for no key update. And what that basically means 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 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, PGD extension moves to PGx. This is from Rustprooflabs.com. Now they're talking about an extension that they develop 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. Plus you can just use SQL to query and find out what the structure of the tables are 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 goes 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.
[00:03:48] Speaker B: Can check out this blog post.
[00:03:50] Speaker A: 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. And they cover different areas such as Security manageability changes, scalability and Performance application, performance tuning and indexes, complex query processing.
[00:04:14] Speaker B: Improvements using extended statistics, different types of.
[00:04:17] Speaker A: 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 Archetype.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 gen index to be able to access those. They do 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.
[00:05:12] Speaker B: You can check out this blog post.
[00:05:16] Speaker A: The Next Piece of Content a Complete Guide to PostgreSQL Backup and 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 pgdump to backup a single database or pgdump. All this is basically a logical way to backup the database. And they also have PG based backup as the physical base tool, basically backs up the individual files of the database. They also mentioned some external options such as just doing a physical backup the directory just 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 the third party tools such as Barman and PG Backrest. So it goes through talking about how to use PG dump, PG dump, all PG based backup, as well as doing a point in Time Recovery. And then they also talk a little bit more about Barman because that is developed by Enterprise DB and how you could use that to backup your system as well. So if you want to learn more.
[00:06:16] Speaker B: You can check out this blog post.
[00:06:18] Speaker A: The Next piece of Content transition Tables and Incremental View Maintenance and this is from Yugonagata 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. And this talks about the development of this feature, particularly the transition tables that they're working on. So if you want to learn more.
[00:06:47] Speaker B: About that, you can check out this blog post.
[00:06:49] Speaker A: The Next piece of Content how to set up Kerberos authentication using Active Directory with PostgreSQL database and 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.
[00:07:06] Speaker B: Do that, you can check out this post.
[00:07:08] Speaker A: Next piece of content. OpenStreetMap service by CyberTech. This is from CyberTech Postgresql.com and they've launched a free service that provides you a way to download in terms of postgres dump files, OpenStreetMap data from different countries, and you can just check out GIS CyberTech. 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 Crunchydata.com and they've updated their PGO, their postgres operator for Kubernetes to work with postgres 14. And this blog post explains how to get started with it. So if you're interested in that, you.
[00:07:51] Speaker B: Can check out this one.
[00:07:54] Speaker A: The next piece of content, the PostgreSQL person of the week is amit. Capilla? So if you're interested in learning more about Amit and its contributions to postgres.
[00:08:02] Speaker B: You can check out this blog post.
[00:08:05] Speaker A: And 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.
That does it. For this episode of Scaling Postgres, you can get get links to all the content mentioned in the show notes. Be sure to head over to Scalingpostgres.com where you can sign up to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes. Thanks.
