Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about JSON B types, Earth distance, dates, times, intervals and authentication. I'm Kristen Jameson, and this is scaling postgres episode 123.
[00:00:22] All right, I hope you, your family and coworkers is continue to do well. Our first piece of content is JSON B, a container of types. This is from Momgn US and it's talking about using JSON B columns. Now, I found this interesting because he's talking about how types are used relative to postgres types and JSON types. And in this example here, he just puts straight values into a JSON B column. So he puts text string, an integer, a boolean, and there's actually a function called JSON B type above that returns what the type is of that JSON string. Now, I should mention there is a thunderstorm going on in case you hear any thunder. So you can use this function to determine what the actual type is that is stored within that JSON B field. Now, of course, the actual type according to Postgres, is still JSON B for all of these different pieces of information that are being stored. Now, you can convert each of these values to text using this operator here, or you could also cast it. And what's interesting is that even though you can cast it, also when you're storing data, you need to define it appropriately. So, for example, when you're inserting values, when you try to insert a text in a numeric combination, it actually gives you an error. You actually need to store it as text because that's not a numeric. For example, that's actually a series of text string, whereas this can be interpreted as a numeric type. So he corrected by changing it to actually an exponential. But you could, if you want to actually store this value, convert it to text by using double quotes. And then below here, you can see how he's casting it to a numeric using the double colon. Now, he's just been using values, but he says you can also apply the same concept to documents as well. So full documents, you can do the same thing and get the type JSON B type of to tell you whether it's a string or a number. So I found this pretty interesting because there's times I've had to store an unknown set of data and typically I've just used text strings to do it and had to do the type conversion at the application layer. But having this ability using the JSON B typo function, I might explore different alternatives when I need to implement a new solution to this. So if you're interested in this content, definitely check out this blog post.
[00:02:47] The next piece of content is Recreating YikYak with postgres. This is from Adamfallon.com. Basically, he's talking about calculating distances using postgres. And YikYak was a social network that would show people posting information within a certain distance around you. So he wanted to implement this using postgres so he set up Postgres, set up a database table for Postgres. And in it he used floats to store a latitude and a longitude. Now, he does say you can do it as a point, so that's another data type he could have used. But he did it using two floats. He inserted two posts into the table, storing different latitudes and longitudes. And then from a third location, he posted another one. Now, to do this, he installed two extensions. So basically, here's post one. Here's post two. And he's saying, let's imagine that we are at this location here. Now, to calculate this, he installed a cube extension and an Earth distance extension. So this is separate from PostGIS. You can do the same sort of technique using PostGIS. And I've actually seen references that that is potentially more accurate, some of the functions they have. But this is a quick and easy way to do it, using these two extensions. And to find the nearby posts, you use this query right here. So he's selecting from Posts, he's putting in latitude and longitude of the we are here area. And for a certain distance in meters around this point, it uses a function called ll Two Earth. So latitude, longitude to Earth coordinates and then uses an Earth box as kind of the bounding area and then uses the Contains operator to look through the Posts latitude and longitude, again converting it using this ll Two Earth function. And with that, he gets the two posts that we were expecting. So it's a very quick and easy way to see locations that are close by to a certain area. And then as a bonus in the conclusion, he says, of course you can add a Gist index here with a function against the table to get much better performance. So if you're interested in doing calculations involving Earth distance, definitely check out this blog post.
[00:04:58] The next piece of content is representing dates, times and intervals in PostgreSQL. This is from PG IO. Now, in the first section here, it goes over a I would say pretty opinionated perspective of what types you should be using and how you should be using them, although they don't give a lot of justification to why, but just basically say do this, don't do this, et cetera. Now, the main types they advocate you using are dates. So dates not containing a time, timestamp z. So the timestamp, that includes a time zone. So that's pretty much going to be most of your time tracking data type and then an interval. So a duration, for example. And they give an example here, one month, three days, for example.
[00:05:45] So these are generally the data types that you're going to want to use, working with dates and times. Now, actually, depending upon your application framework, I've also seen the case of using the timestamp without the time zone, although a lot of Postgres users advocate not using that. Some application frameworks have time zone capabilities built in and therefore the database just stores everything in UTC time and the conversion happens in the application. But of course it's up to you to determine how best you would like to do that implementation. And then it goes through and shows you different ways you can use these different data types. So for example, you can convert what is now to a date and add a certain number of days, or determine how many days from now. A certain day is how many days till Christmas, the ten longest courses in a table. And then they talk about the timestamp type and how that can be used. And also specifically talking about the differences between a transaction timestamp statement timestamp and the clock timestamp. So the transaction one indicates the timestamp at the start of the transaction. Times statement timestamp is the timestamp at the start of the statement that you're in. And then Clock timestamp gives you the actual system clock. And lastly it covers interval types and how you can work with those. Then they follow up looking on how you can extract different date time components from timestamps, as well as doing time zone conversions for working with dates and times. So if you're interested in a post to learn more about dates, times and intervals, definitely check out this post from PG IO.
[00:07:21] The next piece of content is how secure is your postgres? Part Two authentication. This is from the enterprisedb.com blog. In this section they're talking about authentication and primarily that's done through the Pghba.com file. So it has different entries within that file that when a client connection is attempted. This file is reviewed and the first matching entry is what's used for authentication purposes. So for example, this is using a local connection, connecting to a particular database for a particular user. And they're going to use Scram authentication. In this next example, they're using a host based access to connect via a particular IP address to a specific database for a specific user from a specific client IP. And they're going to be using MD Five authentication. Now of course, what you are generally going to use postgres is Host SSL, so requiring SSL connections to the database server or the host GSS ENC, which is a GSS API encrypted connection. So generally those are the two you're going to want to use. You can of course also use local connections if you're connecting to the database for administrative tasks. And then the next section here, they cover the different authentication types. So Trust is basically there's no protections at all if you're using Host and the IP matches, you can get in, there's no password or anything required.
[00:08:47] They talk about peer and identification. Peer is local connections that matches up users. So if the user in the database matches the user name, the system it grants you access. Ident is the same concept on a network level. So that is definitely not as secure, so you want to probably pretty much avoid that. These are the password authentication methods MD Five and Scrum. And as right now, everyone's advocating moving towards scram that is supported in the most recent versions of Postgres. You could also choose to do an external authentication system using Kerberos or LDAP, as well as use certificates. And it describes how you can set up certificates to be able to authenticate connections between clients and servers. And lastly, they follow up talking about the authentication timeout you may want to set in your postgresql.com file, as well as a contrib module you could add off delay. That minimizes how quickly someone can try password attempts against your PostgreSQL database server. So if you're interested in learning more about authentication and PostgreSQL, definitely check out this post from EnterpriseDB.
[00:09:55] The next piece of content is how to scram in postgres with PG Bouncer. And this is from Crunchydata.com, and it explains mostly how scram works, but it actually doesn't show you how to set it up. So like it doesn't give the commands and the steps to run through, but explains how it works, how PG Bouncer kind of handles it, and the advantages of using scrum over previous authentication methods. So if you want to learn more about how scram works with PG Bouncer, definitely check out this post.
[00:10:26] The next piece of content are what are PostgreSQL templates? And this is from Superbase IO, and they're talking about postgres database templates. Now by default, when you create a cluster, you're going to see a template zero database and a template one database. So whenever you create a new database, it uses the contents of template one to make a copy of that to create your new database. So you can make changes to template one. And then every database you create from that point forward will have any changes you've made to say, objects you've added to that database. Now template zero is basically a backup in case some big problem happens to template one. So basically never make changes to this, but you can use it to create a new template one if you need to. And actually they advocate not making changes to template one, but what they advocate doing is creating a custom template database. And what you would do is create a new database, set it up the way you want with all the objects added or changes you want to be able to make it a template. And then you execute this command alter database with the new template name with is template True. Then at that point when you create a new database to choose your new template, you say create database with the new name template and then your template name and it will create that new database using your custom template. So if you want to learn more about templates in PostgreSQL, definitely check out this blog post.
[00:11:54] The next piece of content is PG watch two version 1.8.0 released. This is from Cybertechn Postgresql.com, and this is a monitoring tool for Postgres, and it has a new release. The highlights for it are support for PG Pool Two, PostgreSQL 13 and Timescale DB Metric Storage. So if you're interested in PG Watch Two or looking for a monitoring solution, definitely check out this post from Cybertechnposgreskool.com.
[00:12:23] The next piece of content is Barman cloud part Two cloud Backup Now, we covered the first part of this post in the previous episode of Scaling Postgres, talking about the Barman wall archive, and it was basically a way to send the wall archive to a cloud destination provider such as Amazon's S Three. Well, this shows you how to actually do the cloud backup part. So if you're interested in that and use Barman, check out this post from Secondquadrant.com.
[00:12:51] And the last piece of content is the PostgreSQL Person of the Week is Stacey Hassler. So if you're interested in learning more about Stacey and her contributions to PostgreSQL, definitely check out this blog post that does it. For this episode of Scaling Postgres, you can get links to all the content mentioned in the show notes be sure to head over to Scalingposgres.com, where you can sign up to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes. Thanks.
