Scaling Connections, TimescaleDB, Time-series Gaps, Red & Golden Signals | Scaling Postgres 138

In this episode of Scaling Postgres, we discuss scaling connections, the release of TimescaleDB 2.0, how to find time-series gaps and monitoring RED & Golden signals.

Content Discussed

• Improving Postgres Connection Scalability: Snapshots
• TimescaleDB 2.0: A multi-node, petabyte-scale, completely free relational database for time-series
• Detecting gaps in time-series data in PostgreSQL
• PostgreSQL, RED, Golden Signals: getting started
• PostgreSQL clustering: vip-manager
• Migrating interactive analytics apps from Redshift to Postgres, ft. Hyperscale (Citus)
• Joins using LIKE or why PostgreSQL FTS is a powerful alternative
• PostgreSQL: Foreign keys and insertion order in SQL
• [YouTube] Postgres TV
• Getting Started With Postgres 13 on Ubuntu 20.04
• Using PostgreSQL and SQL to Randomly Sample Data
• Learn PostgreSQL - a new book
• Stefan Fercot

YouTube Video

Podcast Audio

Transcript

[00:00:00] In this episode of Scaling Postgres, we talk about scaling connections timescale,DB time series, gaps in red and gold signals. I'm creston, Jameson. And this is scaling postgresepisode 138.

[00:00:22] All right, I hope you, your friends, family and coworkers continue to do well. Our firstpiece of content is improving postgres connection scalability snapshots. This is fromCitusdata.com, and this is talking about postgres scaling. To handle more connections, normallyyou have to start using PG bouncer once you start getting up above, say, 200, 300, 400, 500connections. But this is looking to scale beyond that and not necessarily have to rely on aseparate pooler like PG bouncer or PG pool, because you get a number of benefits, such asbeing able to maintain sessions and to do prepared statements that you can't do when youtypically do transaction pooling in something like PG bouncer. And we had covered this in aprevious episode of Scaling Postgres, but this post actually describes the changes that havebeen made. And these improvements have been contributed to postgres 14, which is due to bereleased in the third quarter of 2021. So next year, next fall. Now, he kicks off mentioning theperformance improvements first, but the area that he decided to work on were snapshots. Sorelated to MVCC being able to maintain multiple versions of the database for different users toview, based upon what kind of transactions they are running, there's, the necessity to tracksnapshots of what transactions can see. And basically he went in and optimized that. So I'mbasically just focused on the graphs here. Here he goes from one to ten to 100,010 thousandconnections to the database. Now, of course, all the points are that he tested more than that, butjust the graph indicated here. And with the enhancement he made, you don't really see anythinguntil maybe the 90 connection mark. And up until that point, they are identical. The blueindicates prepatch, and the orange or yellow indicates post patch. And this is TPS transactionsper second for a PG bench workload. And like I said, up to 90 connections, there was no realdifference. But then at that point, you start seeing a little disparity until around 5600, and thenthe difference starts growing significantly. So much so that it's between two and three timesmore efficient, this patch, once you get out to 10,000 or more connections. So really a greatperformance improvements with high numbers of connections. And you can also see that theperformance doesn't drop off as much as you do prepatch. Then he looked at the state ofunused connections. So basically along the X axis, he's tracking the number of idle connectionsto around 10,000 connections. And you can see with one active connection and 10,000 inactiveconnections, essentially your TPS throughput gets cut in half. But after you apply this patch, itremains consistent up to that 10,000 connection mark. And then even with 48 activeconnections, the performance fares even better. You basically get no difference in the number ofinactive connections. So another huge performance improvement if you're wanting to use a lot ofconnections. So this is great news for Postgres 14. Now the rest of this post covers the changesthat were made to the snapshotting process and he mentions all the different commits and allthe different information. I'm not going to cover it all here, but he talks all about the differentbottlenecks he encountered and what areas he covered. So if you're really interested in thedetails, I highly encourage you to check out this blog post. And he even has the raw data ofeverything that was measured at the bottom here. So this is a great, great post. So definitely Isuggest checking out this post from Citusdata.com.

[00:03:57] The next post is TimescaleDB 20, a multi node petabyte scale, completely freerelational database for time series. This is from Timescale.com. Now normally I don't coverproduct announcements, but this was a pretty significant announcement I wanted to draw yourattention to. So with this release, the fact that they're releasing it for free and they have cloudversions available, it's a very interesting usage model and just all the different features that allowas they state here, being able to scale out so you can scale out their distributed hypertables tomultiple nodes and the fact it's a time series, it's a very interesting product. At this point, I feel.Now this po --st goes into a lot of detail about what Timescale DB is. It's basically an extension of Postgres.They debunk some certain myths, so it does have a lot of marketing information. But overall,what the product offers I find very interesting. So for example, going from a single node to twonodes, four nodes, eight data nodes, you get almost linear scalability. They also get hugeperformance benefits by doing essentially column based storage of data within indexes alongwith compression that can get huge, they say up to 94% compression for certain data thatthey're working with. So it's just a very interesting blog post. And if you're looking for a scale outsolution for Postgres, to my knowledge there is Citus data, but now also TimescaleDB. So ifyou're interested in time series database or scale out solutions, apart from what you can do withthe community version of Postgres, maybe you want to check out this.

[00:05:34] The next post is detecting gaps in time series data in PostgreSQL. This is fromEndpoint.com. They're talking about they had a data collection scenario, but they had somegaps in that data collection and they wanted to detect where those gaps were. So this isbasically how they did it with SQL. And basically the main function that they leaned on was agenerate series. So basically to find missing rows that were date based, they used a generateseries command to do at one day intervals to produce data like you see here. Then they did aleft join and checked for nulls that exist in the Table of Interest. And they show this query righthere, and basically where that's null, they pull up. Okay, this is the day and the particular batchthat is missing. So then they can go and look and find the data. But then they also had a datetime field that they needed to check as well. And they expected something to be in that datetimefield around every ten minutes. So for this purpose they did a generate series, but they did it at aten minute interval. Now they did the same thing doing a join, but they actually did a lateral join.So for each row they did a specific query against the join Table of Interest. And that's whatlateral gives you. And using that, they were able to find the gaps. So this is a pretty interestingblog post using a technique to be able to find gaps in time series data. So if you're interested,you can check out this post.

[00:06:55] The next piece of content is PostgreSQL red gold signals. Getting started. So they'retalking about a monitoring solution using what's known as red or gold signals. And they havelinks to each of these methodologies, red being an acronym for rate errors and duration. So forexample, when doing monitoring, you want to monitor the rate. So say the numbers of requestsper second your services are serving. You want to track the errors. So the number of failedrequests per second and you want to track the duration, the time spent on servicing differentrequests. So they looked at this through PostgreSQL and how you can get this information fromthe system views. So the first is requests and what they advocate using is the PGSTATstatements view, and just doing a sum of all the calls from PG Stat statements. Now, keep inmind, PG Statstatements is a cumulative record of all the queries being done. So basically youneed to check the difference between the previous time it was run and the current time to get asense of how many calls were done within a particular time period. Next, they look at errors.Now, there's not a great way to do errors, but essentially they chose to do rollbacks. So theylooked in PGSTAT database to look at the transaction rollbacks. So I can imagine scenarioswhere rollbacks aren't always errors. For example, maybe you want to do it on purpose whenprocessing something, but this is what was available within the system view. And then theylooked at duration and that's basically looking at PG Stat statements. Again looking at a sum ofthe total time that it took to process different queries. Now, with the gold signals, they also coversaturation. So that's basically where request has to get queued. And you can imagine a scenariothat means say something is behind a lock. So if you wanted to just look at how many locks areoccurring that's one way. But what he likes looking at here is PG Stat Activity and tracking howmany queries are in an idle state or idle in a transaction, active or waiting. So if you're looking ata different way to monitor y --our postgres solution using red or gold signals, maybe check out this post from Dataeger.com.

[00:09:00] The next piece of content is PostgreSQL Clustering VIP Manager. This is fromCyberTech Postgresql.com and VIP Manager is a virtual IP manager and they're talking aboutusing it with, say, the Petrone stack. And that in that stack you have a application that speaks towhatever device is going to be routing the connection to the primary database. Now, in this case,this could be the VIP Manager and it's pointing to the virtual IP that's been assigned and then itconnects to whichever is the primary node that's responsible. The VIP Manager, at least whenworking with Petrone, is that it consults the Consensus Store, which could be say, Etsyd todetermine which the primary is, and it routes that virtual IP address to the correct physicaladdress of the current primary. So that's pretty much all the virtual IP manager does. And then itgoes into how you would do the configuration. It basically needs to know where this ConsensusStore is to be able to look at it, read it, find what is the entry for the primary database and whatvirtual IP to use to be able to do the redirection. So it's a very brief post about how you can setup the virtual IP manager specifically when using Petrone.

[00:10:11] The next piece of content is migrating interactive analytics apps from Redshift toPostgres featuring Hyperscale Citis. This is from Citusdata.com. Now, a lot of this post includeskind of marketing information and highlighting Citis as an alternative solution to redshift, which ofcourse it is. But the certain thing that I looked at that I found interesting was where with redshift,because it's column based, you can't really add indexes as easily with Postgres Standard Rowstore and that by adding indexes for this specific use case they were able to get a two xperformance game versus redshift because you could add these indexes. Next, because it'susing a more recent version of Postgres sized data, that is, compared to Redshift, you're able touse JSON B to store semi structured data. Previously, the customer had to store it in large textfields in redshift, whereas they could use the JSON b to store it. And that enabled them to storedata in a much more compressed format and use gen indexes to efficiently query it. And theymentioned here the JSON data type in Postgres gave six to seven times compression comparedto how they had to store the data in redshift. So this was a pretty interesting post of some of theadvantages of using Postgres. Now, first they tried doing it on a single node. It didn't quite suitthem so they just went to a dual node that you can of course do when you're using Citus. So ifyou're interested in that, you can check out this post from Citusdata.com.

[00:11:43] The next piece of content is Joins using like or Why. PostgreSQL FTS is a powerfulalternative, and by FTS they mean full text search. So they had a customer they were workingwith and they were having slow performance when using like operator with Joins. Now, they triedat first to use the Btree gen index, which enables you to use indexings with Btree and gencombined, but they still didn't get great performance. As a matter of fact, they were getting aquery returned in 536 seconds, so pretty slow. But they found that they were able to use full textsearch instead of using the like queries. So with a full text search they added a generated appcolumn using TS vector to record what they needed to do. The full text search on added a genindex, and then once they rewrote the data to be able to query it efficiently, they actually gotdown to 1.5 seconds to run the query. So here's the case where using a simple like or I like to beable to search ended up being not as efficient as using a full text search with a gen index. So ifyou're interested in learning more, you can check out this post from Postgrespro Co, il.

[00:12:59] The next piece of content is PostgreSQL foreign Keys and Insertion order in SQL.Now they're talking about the case where you have foreign keys set up. So in this example, theyhave a currency, a location, product product description, and a product stock when you're goingto be inserting values. When you have foreign keys set up, between each of them there areessentially dependencies. So for example, a currency must exist before you insert a new productbecause it references that. And if you have a large hierarchy of de --pendencies set up, it can be difficult to determine what you need to insert first if you're new toa particular database. Well, here they're presenting a recursive query that actually reads throughthe system tables and determines what needs to be inserted first. So when running it against thesample that they had here, they show level one data needs to be inserted first. So you first needcurrencies and locations, then you can insert your products, then you can insert your productdescriptions and your stock. So it's a pretty interesting query to be able to find out thisessentially dependency information for your foreign keys. So if you're interested in learning moreabout that, you can check it out at CyberTech postgresql.com.

[00:14:10] The next piece of content is actually a YouTube channel that I found in my feed andbasically it's called Postgres TV. Now, they have had some older content that's been posted, butmore recently, for the last four weeks or so, they've been publishing episodes on about a weeklybasis. So if you're interested in more postgres content, you can check out these live shows thatare posted to Postgres TV.

[00:14:35] The next piece of content is getting started with Postgres 13 on Ubuntu 24. So thisgoes through the entire process of setting up Postgres, configuring it for external access andhow to do some basic backups. So basically goes over the installation process, getting thePostgres repository set up to be able to install the most recent version, which is 13 talks aboutsetting up the database cluster. How would you go about starting stopping it, where you wouldfind the log files and how you do the configuration for accessing it across the network, creatingusers, setting up a database as well as doing some basic backup and restore commands. So ifyou're interested in learning more about that, you can check this post out on PG IO.

[00:15:22] The next piece of content is using PostgreSQL and SQL to randomly sample data.This is from Crunchydata.com, and it's the next post in the series talking about doing scientificanalysis with data from the California Wildfires. And in this post they're talking about needing totake a sample of non Fire data and they wanted it to be the same amount of information as theFire data to be able to start to develop their models because they wanted to do a logisticalregression for it. So this goes through the process of taking a sampling of the data using theTable sample keyword to get the data together so that they can set up a set of data for bothtraining their model and also verification of it. So if you're interested in that, you can check outthis post from Crunchydata.com.

[00:16:08] The next piece of content is a post about a new book that's been released calledLearn PostgreSQL. So if you're interested in more learning material such as a book, they havethe outline here and a link to the book.

[00:16:20] And the last piece of content is the PostgreSQL. Person of the week is Stefan Forkat.So if you're interested in learning more about Stefan and his contributions to PostgreSQL,definitely check out this blog post that does it. For this episode of Scaling Postgres, you can getlinks to all the content mentioned in the show notes. Be sure to head over toScalingpostgres.com, where you can sign up to receive weekly notifications of each episode, oryou can subscribe via YouTube or itunes. Thanks. --