will produce this is select distinct and union as opposed to union all and by the way that's a query tip for you if you're doing a union query and you don't have a good reason to deduplicate then use union all because it is much less expensive to execute the deduplication is expensive but most of the time you won't see unique because postgres will do something like a hash aggregate instead in order to implement this under the hood the a sort should be obvious right we're going to have a sort limit using limiter offset that's the node you'll see is the limit node other operations CTE scan that's with clauses so if you have a with clause and then you have another query portion that is querying that with clause you'll see a CTE scan node that's our reference to the with clause sub query scan is the same only for sub queries and this is usually in cases where the results of the sub query can't be folded into the main query we try to do that whenever possible because the thing that postgres can do the fastest in terms of corresponding rowsets is some kind of join so one of the things that the query rewriter tries to do all the time is come with a way of how can I turn this into a join so if you're seeing some of the sub queries scan it's because postgres couldn't figure out how to turn it into a join materialize that's actually our most expensive sort of sub query or with clause which says hey in order to actually execute this sub clause I had to actually turn it into a built constructed complete row set in memory which is expensive and that's what materialize means so the query that can be a reason why it's slow and then an append and that's for like union all and partitioning and other things where we're appending rowsets to each other vertically so ctescan example we're going to have all kinds of things ctescan and it's going to have this and it'll refer to here's the name of the with clause toe tab and then there'll be a filter outside the ctescan and in recent versions you'll get this tremendously useful rows removed by filter because that rows removed by filter is actually the majority of the rows that were returned you start saying hey is there some way I can push that criteria down inside the with clause so that I'm not doing all this expensive filtering so having done all of that analysis the real actually I can take like one minute of questions on the node types before we start looking at why is my query slow any questions yeah your statistics Postgres keeps statistics on I think I talk about this in why is my query slow actually so let's actually see let me talk about why is my query slow if not I will revisit it so why is my query slow so one of the things that you may have seen you've noticed the background of my title slides here well that background is this wonderfully useful tool maintained by Hubert out of Poland called explain.devces.com and what this tool allows it's an online website in PHP that lets you paste in a a query text and it will it will kind of parse it out and it will attempt to actually find certain things about your query there are common patterns for why it's slow like a huge difference in the estimated number of rows returned versus the actual number of rows returned it'll also tell you which steps took the most time right because one of the problems is remember I said those costs in the times cascade upwards well so it can take you a fair amount of math to figure out how long did this step actually take independent of the other steps and so this will do that math for you as you can see whoops inclusive versus inclusive times right so in this particular query hey this bitmap index scan is where I'm spending most of my time and explained at epsys.com has highlighted that for me so tremendously useful tool do keep in mind that it is somebody's public web thing so if you have data restrictions you work at a HIPAA compliant PCI compliant company etc. you might want to pull down his source code for this and run it internally instead the or he actually does have a button there to obfuscate all your column names I don't know I don't really do any of my sequels so maybe yep the um there are some other tools you can use the PG admin GUI web desktop client will do a visual diagram of the query execution I tend to find that it only works for very simple queries because the diagram gets unwieldily large very quickly there's a more sort of steroid version of this originally developed by redhat for redhat database and now maintained as part of the enterprise db developer studio um I haven't actually seen this because I've never used the edb developer studio but it's there um however for a lot of people their primary tool for helping interpret explain plans is the PostgreSQL channel on irc.freenode.net um the um where people come in and they say guys this query is slow and they post their explain plan and then they get please do an explain analyze and they post their explain analyze then people are like ah so particularly when you're learning how to interpret explain plans the irc channel is really invaluable so here's some examples of particular query problems that you might encounter and how you'd actually get that out of the explain plan um so here's one where we simply need an index that we don't have um because you can see here we're doing a full table scan and we're doing a full table scan on a pretty large table 200,000 rows probably full table scan is not the best strategy for this it's certainly not happening quickly um and we've got this filter called category equals 7 now there's actually a little bit more to this query I just sampled it out there were other columns being selected but the other columns were what you call selective there were columns for each each value had thousands of rows and postgres will only use an index if it can pull a very small portion of the table usually no more than a couple percent beyond that it's actually cheaper to do the sequential scan um so the answer for this particular query was to modify one of those indexes to include this category column and all of a sudden the query got orders of magnitude faster another one example is bad row estimate now I've actually flexed with the alignment here so that you can actually see this um I didn't actually pick it out um so index scan and this sort of thing and here's the cost here's the estimated number of rows returned from this here's the actual time and here's the actual number of rows returned well we're a couple orders of magnitude off now these are estimates um I generally say anywhere within a factor of 5 that is 5 times as many to 1 5th as many um well doesn't generally change the query plan sometimes it does when you're really close between two different cost estimates but it doesn't generally but things where you are 2 orders of magnitude off will change the query plan and this particular case you know if you're actually pulling 10 percent of the table then in index scan is not the way to go um the um it's going to be slower than doing a sequential scan so um and you can see where this estimate gets off um trying to remember if I talk about statistics later anyway we'll find out um now another one that you actually have here is I mentioned that loop thing right and this is where a lot of people miss the calculation which is we're doing a nested loop query up here and we're corresponding to 10,000 rows which means that um on this actual index scan that we're doing is we're actually looping over it um you know almost 11,000 times and that's what's actually consuming a lot of our time that's why actually the end time for this is what it is most of the time it's being spent in that loop and so you look for these things with very high numbers of loop values usually there's a better way to do that now this is a complicated one um but it's really annoying because there isn't a good easy way to fix it so one of the things we implement in Postgres is this thing called abort early plans and the way that an abort early plan works is hey, you've asked for 100 rows from this table and I don't have a good index to use or I don't have um uh you know or I'm joining against something else or whatever but the criteria you've specified are very broad and therefore I think I can scan 1% of the table and get your 100 rows and then I can quit the problem is if Postgres is looking about how common your criteria are or how well distributed they are like say that your criteria are common but all those rows are grouped somewhere down towards the end of the table then it can end up scanning the whole table so um where you can actually see this here um is you notice this really weird circumstance look the cost of this node is $1,000 and the cost of the node above it is only $2,600 wait I thought you said cost accumulate upwards so the cost of accumulating upwards why is this cost this low well the answer is Postgres is expecting to abort this join long before it finishes executing it but if it's wrong that can be a really slow query um and the reason why it's doing that here is if you look down a node and you say index scan backwards so what happens is you have an ascending index and then you ask for everything greater than x we'll do an index scan backwards um and um and we expect that the index scan backwards you see we've got this right here $4.2 million rows on the index scan backwards so Postgres is figuring that those index criteria apply to a lot of the table um but it's wrong they apply to a much smaller slice of the table and you scan a lot more of the table to find the 100 rows you were looking for um I did say I would mention statistics so the way that Postgres um the way that the query planner figures all this stuff out is that we have tables of statistics kept in the Postgres system catalog about what's in all of your tables and these statistics are asynchronously updated however they are samples number one they're based on sampling the table because we don't want to full table scan all tables every time we update the statistics they're prohibitively expensive so number one they're based on samples and number two um we're then applying algorithmic rules to those samples so we have a list of the most common values we have some histograms of ranges um we have some information on how unique values are in the column and that sort of thing but because all of those things are estimates they can all be off right so if you apply an estimate to an estimate you get an estimate that has an even higher range of error um than you did originally so one of the things that you can actually do is mess with some of the execution of things now the brute force way to do this is to actually force certain plans in Postgres by turning off certain types of node so if you say set enable nest loop equals off then you're preventing Postgres from executing a nest loop now that is fine that is great as a way to test different query plans like hey if this was a hash join would it have been faster well let's force that by disabling nest loops right and then you can see hey no a hash join was actually much much slower that's why the query planner predicted it was right so it's useful for testing the mistake that people make is that they actually change these things in postgresql.conf because here's the thing if you do that in postgresql.conf you're not disabling nest loops in that one slow query you're disabling nest loops for every query in the database which means a bunch of queries that used to be fast will all of a sudden become slow don't do it that's for troubleshooting queries only now one of the things you can do that's more useful is I said that the statistics were based on sampling you can increase both the default sample size and you can increase the sample size on specific columns the default sample size is like 100 samples which is very small if you're looking at a table that's got 50 million rows so you can increase it up to 10,000 samples anywhere over about 500 to 1,000 I really recommend doing it on a per column basis because if you're sampling say a large text column that gets really large and if you start bloating out the statistics table it itself gets slower at which point all of your query planning gets slower so you don't really want to do that do it on the columns that you frequently join on for example the other thing that you can do is that you can actually clobber the statistics in certain ways indistinct is the estimation of how unique a column is and because of there's no such thing as a perfect algorithm for estimating indistinct from a small sample so sometimes Postgres can play off based on how it took the sample and the thing is it's not that hard for you to get a better estimate of indistinct simply by doing a group by query one time the indistinct of tables that have been used for a while doesn't tend to change much over time and so you can overwrite it with a set statement and then you go ahead and run the explain again to see if it actually changed the query plan and in this case for our abort early plan it did change the query plan by changing some of the statistics and saying hey this abort early plan is bad because there's a lot more rows than you think by increasing the sample size we changed the histogram and Postgres suddenly realized that more rows would correspond to those criteria and therefore the abort early plan was a bad idea sorry less rows would and therefore the abort early plan was a bad idea so that's your sort of cycle so that's your basics of explain so you have about five minutes for questions so we have questions yes, yes you do if you're doing any of these statistics things, you have to run an analyze statement or it doesn't take effect until the next analyze so either you run an analyze statement or you wait for autoanalyze to get around to it but no, so the thing is like say you've got a B tree index right, that is a normal index on last name or a B tree index actually on a date added right and then you want to see everybody whose date added is after a certain date then it's going to do a reverse index scan because that's a descending search but the index scan is an ascending order I'm not entirely clear on why reverse index scans are slower than forward index scans it has something to do with the data structures that we use they are actually a little bit slower so for example if you are searching on greater than that date column all the time you probably want to remove that ascending index and replace it with a descending index a little bit faster more questions? the question was whether or not you could force it to use a particular index and the answer is no we haven't implemented that in Postgres for a variety of design reasons the you can manipulate a bunch of other things that will end up causing it to use that index and there are certain things that you can do with indexes like Postgres supports the concept of partial indexes where you do an index on where? yeah it's generally better in that case to actually look at why is it not using that index in the first place often if the index really would be the fastest way to do it and it's not using it there's a good reason why like actually your criteria don't match the index as well as you think for example one of the problems that you'll have in indexes on text columns is mismatch text encodings so you're using actually a different correlation in your client then the database then the column was populated with and therefore we can't use the index on the text column or you're using say a like or unanchored text search and you're using a like criteria and the text column wasn't built with there's this criteria called text pattern ops that you can apply to an index on a text column that makes it support like and otherwise it doesn't unless you're in C encoding there's a bunch of little things like that will actually prevent postcards from using a particular index even when you think the index is applicable and sometimes the index is actually slower in that case it would be nice to be able to force it to use the index just as an experiment but we don't currently have a way to do that so I did a tool a while ago and I'm trying to remember where it is on GitHub that actually looks through all of your tables and then based on an estimate of how many rows it has in your tables it takes all the columns that you've bothered to index deciding that those are your important columns because why would you index them otherwise and increases the statistics to a value based on a coefficient of that I did that quite a while ago though and I don't remember where it is ping me later on and I'll find it keep in mind that increasing the sample size in postcards is not always beneficial and it's not always size based that is in a lot of cases where you want bigger samples is not because the table is bigger but because it's more asymmetrically skewed and then like if your data is completely randomly distributed then you can go with the sample size of 10 right it's when it's not randomly distributed that you need larger sample sizes so in cases of highly skewed tables right for example if you have a column that represents activity status and then you're corresponding that against date well obviously the active rows are going to be most of the ones with the latest dates but postcards won't really understand won't know that instinctively and so that having higher sample sizes there's been various proposals to actually make the automated sampling correspond to a percentage of the table but none of those have been incorporated into mainstream postcards because that would actually make more sense from my perspective yeah one of the things to understand is until Postgres 9.6 we will not have any correlation stats between columns so Postgres assumes that values between two columns are entirely randomly matched which is a really problem when they are entirely non-randomly matched like say month and year right or zip code and state Postgres will estimate way low on the number of rows returned from that we are getting column correlation stats in 9.6 for some value of that and that situation will improve a lot but for right now if you're saying hey I put criteria on two independent columns and Postgres estimated really low orders of magnitude low it's usually because those two columns are actually highly correlated but Postgres has no way to estimate that and sometimes the answer can be making a multi-column index which Postgres will evaluate according to different stats and sometimes there isn't really good answer for that so I take one more question then we have to quit one more question okay awesome thank you very much go ahead and get started I'll be talking about all the big data science stuff that Postgres has features and capabilities that maybe some of you haven't heard about talk about developer features administration performance there's a community aspect to this as well I think and then finally I actually have a case study for one of our clients that kind of shows how you can put all these different features together so developers there's a number of things that Postgres has that you can use to make your job of developing an application or developing predictive analytics models easier the most obvious of which is the JSON data type this was added in Postgres version 9.2 and there's been features implemented around JSON in every release since then 9.5 gains the ability to actually go in and very easily modify parts of the JSON document without having to go through a bunch of extra work to split things out and bolt the document back together there's two different data types two different JSON data types that Postgres supports the plain JSON data type basically takes your JSON and it stores it as text keeps it in the raw form the only thing that it really does is it validates that it is valid JSON that's nice if you expect to be ingesting JSON and then not really doing very much with it it is faster to pull data into that data type because it doesn't have to parse it it just does a syntactical check the second type is JSON B or binary JSON that data type when you provided a JSON document it actually goes and it parses it out and it stores the JSON in a compact binary format so your original formatting and things of that nature are gone but the nice thing is that the access to that data is then much faster the other thing that you can do with JSON B is you can actually index intelligently on it there's two different indexing methods that are provided one only operates at the very top level of a JSON document but it knows about every single key the other one operates really at the path level so it knows what paths are in your JSON as I said there's a very rich set of very rich support for it as of 9.5 there's 12 operators and 23 built-in functions for it and importantly there's also very strong interoperability between the JSON data types and other data types so what I mean by that is you can do things take a Postgres row out of a table and say hey take this row of data and turn it into a JSON document a single function call is very easy and whatever your input data is you then get a valid JSON document out and this is just one example of the kind of interoperability that you have with JSON the next thing that Postgres features is really connect to everything that you could possibly want to connect to foreign data wrappers is the foundation for this those are added in 9.1 there are now just short of 100 foreign data wrappers in fact there are so many foreign data wrappers that there is a foreign data wrapper that reads the list of foreign data wrappers for you there are 15 foreign data wrappers targeting NoSQL technologies across 10 different NoSQL databases and there are 7 that are targeting big data systems like Hadoop and HDFS now if you need to talk to something that is not that doesn't have a foreign data wrapper provided for it first of all it is not that difficult to produce a new foreign data wrapper there are 2 projects one is called multi-corn the other is called I read it yesterday and I can't remember the name of it but basically what multi-corn does is you can write a brand new data wrapper in Python the other version of that is the same idea it makes it very easy to develop a foreign data wrapper in Rails or sorry in Ruby should all of those steps create your own foreign data wrapper the next capability that you have is support for multiple procedural languages and from the standpoint of interoperating with other data systems this really gives you the world because you can write code to run natively inside a Postgres database in Python in Ruby in Java so whatever data you need to get at there's a procedural language that will allow you to get at it assuming that there's not a foreign data wrapper that you can use already and again because Postgres is so extremely extensible if you have a language that's not supported like for example I look to see if the Go language had a PL language and I didn't see it so that might be an interesting project is to add support for Go in Postgres finally on the developer side Postgres data types are themselves extensible perhaps the simplest version of that is a domain which really is a data type that you put some additional checking on and also create a composite data type so for example Postgres does not have a data type for complex numbers like Python does but if you need to store complex numbers in Postgres you can easily create a complex data type that just has your real and your imaginary components as two fields to a single data type you operate on that as a single data type so from the standpoint of data it's one single number but that single number has two components you can also create brand new data types just last week I decided hey let's see what it would look like to take a NumPy NDE array, a Python data type and port that into Postgres and once I figured out really most of the difficulty here was figuring out some compilation issues between Postgres and Python in order to implement that data type it's like 10, 20 lines of C code and 10 lines of SQL code and here's a brand new data type now that you can make use of in addition to the type extensibility there was a feature added in I think it was 9.4 or 9.5 called transforms and these allow you to control how specific Postgres data types are relayed to the different procedural language functions so for example the NDE array data type I created a representation function, those of you that are familiar with Python will recognize the wrapper format and you know this is a function it says hey I return text I'm a Python language function the important thing is the transform for type NDE array that tells Postgres hey there's a special thing I want you to do for this data type so when you call this function in Postgres and you hand it an NDE array data type that gets treated in Python it's an NDE array data type so all the Python function has to do is say oh well here I'm just going to return the representation and that's what you get out so all these developer features ultimately what they mean is that if you're working with complex analytical tasks the last thing that you want to have to do is go learn a whole bunch of new tool chain go figure out a whole bunch of extra stuff you just want to be able to do your job and get it done and move on and these developer features make that very easy for you to be able to do that and any place where you see something missing and go oh man I wish Postgres did this the extensibility capabilities make it easy for you to add something that you don't have and by the way if anybody has any questions during the talk please just raise your hand I don't want anybody to wait till the end so next up is administration if you're going to maintain a big data environment administration becomes a really key facet to this and there's a number of things that Postgres has that honestly I think are actually superior to a lot of the big data options that are out there that are more traditionally used a big one of those is backup I think this is something that a lot of the big data tools they just go oh the data set so big I mean how can you back up 10 terabytes of data well data is your business most companies if they lose their entire data set just lock the doors because you're done so I think dealing with backups that needs to be a foreground thought that needs to be something that you think of upfront and Postgres has done that so there are two major ways to do backups in Postgres point in time recovery is really point in time recovery is an extension of the same kind of crash recovery system that Postgres uses to deal with a server going down so it uses the right ahead log and takes that right ahead log copies it to a separate place and that in conjunction with just a plain old file system level copy of your database that constitutes backup the really strong capability that point in time gives you is that when you need to do a recovery you can say okay recover to this exact transaction because if you look at traffic on the mailing list and when people are asking about backup questions I'd say probably half the time it's not because they lost their entire database it's not because a file system just went kaboom and everything's gone it's because somebody made a mistake and they said oh this table with test data drop table test data oh wait that wasn't test data that was production data and now you have this database that is all still there and it's up and running but now you can't get to this very important data so this point in time recovery capability makes it much easier to deal with those kind of situations because you can recover the database to the exact transaction before the mistake was made. The other backup option that you have is pgdump from a big data standpoint probably not what you're going to want to do it has gotten significantly faster it is now possible to run both a dump and a restore in parallel really the reason that I put it on the talk is depending on the criticality of your data the thing that I really like about pgdump is it's basically impossible to screw it up point in time recovery there's different ways to configure it there's different tools you can use to configure it it's very binary binary level you can't do things like take a point in time recovery on say an intel architecture and move it to a different architecture which isn't as big of an issue today but whereas pgdump it's a sequel level dump of the database so when you're thinking about disaster recovery I would encourage you to at least ask yourself the question hey should we be doing periodic pgdumps as a last ditch recovery safety net replication is replication with big data is interesting because a lot of times you're dealing with these very large data sets that you don't necessarily think oh I want multiple copies of it but then again a lot of times you're dealing with these data sets and tools that run in a sharded environment and as part of the sharding they keep more than one copy of the data so postgres you can do the same thing there's multiple different options that you can use to achieve that and that gives you a lot more flexibility probably the easiest thing to set up is just simple streaming replication so this builds on top of the same code base and the same concepts that are used for point in time backups but it extends it so that now it's a point in time backup that's happening continuously and at the same time that it's happening you have a server that's restoring that backup and that you can run queries against so you can stand up these replicas they're very easy to stand up you also have the capability for these to be truly synchronous replicas so what that means is if you have synchronous replication enabled and on your master you do a bunch of work in a transaction you go to say commit that commit is not going to return to you until the commit record is recorded by the replica as well so when that commit comes back to you you know that that data is stored not only on the master but also on your replica and in 9.5 you can actually have multiple synchronous replicas one of the other nice things about the synchronous replication is that you can control this on a transaction by transaction basis you can have a transaction in fact not only that you can have a transaction where you say look I just want the transaction to come back ASAP I don't care if the data is on disk so if you have tasks that need to deal with ingesting data at a very high rate this is something very powerful because you can do the ingestion get the data sent over to Postgres and get an immediate return from Postgres let your code keep going gain more throughput without having to resort to actual you know parallel processing or something of that nature or something asynchronous the other interesting thing that you can do with streaming replication is you can have the exact opposite of a synchronous replica you can have a replica where you say I want you to intentionally be four hours behind whatever is happening on the master this is very useful from a disaster recovery standpoint because again if we go back to the scenario of somebody goes and they drop a really important table and now you have to recover you don't have to worry about oh we have to go we have to get the backups we have to stand up a recovery server we have to start processing the backup no you have a server that's been doing all that now all you have to do is tell that server hey something bad just happened so I need you to recover to this point and go do that and as soon as it's done with that recovery it will come up and then you can pull the data off of there Postgres also has logical replication support with a PG logical extension this was just released not even a month ago I think it's very similar in nature to trigger based replication but the big difference is PG logical uses the logical replication framework that was first added in I believe it was 9.4 that is based on the transaction log so this is logical quote unquote statement level or trigger based replication that doesn't use triggers so it's extremely fast it is comparable in performance in fact to streaming replication the other thing so why would you want to use this one reason is streaming replication you have to replicate an entire Postgres instance all the databases all the data all the tables the whole line yard you have no choice no matter logical replication you can replicate only what you need so if you have certain tables that you know a subset of tables that are hit heavily you want to do some predictive modeling on them things of that nature you can take just those parts of your data break them out to a separate server and not worry about other parts of the database that aren't important and you need to support that workload the other thing that you can do with logical replication is this provides a mechanism for doing a a Postgres version upgrade where you can actually test out the new version of Postgres you can run queries against it if it looks good and you're happy with it you can switch your production environment over to it reverse the replication so you still have your old version of Postgres it's up and running it's got all the the current data if the new version doesn't work out in production there's a problem you can switch back the in place version upgrade that you that is more typically used PG upgrade that doesn't have that capability even if you use the the method of upgrading that copies all the data once you've stood up your production traffic well now your old version is out of date you don't have the same information from a multi master standpoint there is now binary data replication this builds on top of the logical replication framework it's very similar but instead of being truly acid as PG logical is this uses eventual consistency and it supports wide area replication there's a number of people that are running this where they have data setters spread across the planet and BDR is very happy dealing with the kind of latencies that you get on those kind of of internet links the one kind of downside to BDR is that right now for BDR to function there's a few things that it needs within Postgres itself that are not in Postgres yet so it is currently it currently requires a slightly modified version of Postgres so it's not 100% true community Postgres finally there are other replication schemes that are out there for Postgres Sloanie and Lone Deast are probably the two most popular there's a few other ones that are out there at this point in time I would really tend to recommend that you don't use these ones unless you have a very specific need PG logical or streaming replication will most likely solve all the needs that you have better than either one of these would so clearly when we're talking analytic workloads things of that nature performance becomes very very critical and this is something that the Postgres community really puts a lot of emphasis on there is a tremendous amount of work that happens to to constantly improve Postgres performance and I think that one of the really important things to understand here is that you know big data there's all this focus on ooh web scale we have to scale horizontally and for a large enough data set that's true but there are a lot of data sets that Postgres is very happy to run on a single vertical server and you don't need the extra overhead the extra expense of colo space for 10, 15, 20 extra servers running around when you can get a single large server that will do the same thing I've certainly run I personally run databases that peaked at over 6000 transactions a second and that was on a 4 terabyte OLTP database um running on I think it was like a $20,000 server and that was on IO hardware that had problems as well so despite some IO issues Postgres was able to scale very well to that and that was version 9.2 the version number here matters because as I said the community puts a very heavy emphasis on continually improving the performance capabilities of Postgres it wasn't that long ago where if you wanted to score if you wanted to scale Postgres past 4 cores it would ramp up to like 4 maybe 6 cores and then you had this downward angle well now it'll very happily do 80 cores um there are significant improvements that are made in every major release and typically the number that I tend to go with as a ballpark is every major version of Postgres improves performance by about 20% obviously that's very heavily dependent on your workload some people won't see it tremendous you know they may see 5 or 10% getting a problem area and they'll do a version upgrade and go oh holy cow it's twice as fast so why does this matter for big data well data keeps getting bigger it wasn't very long ago when a terabyte database I mean that was a big deal if you had a terabyte now a petabyte is a big deal you know so that's gone up by a factor of a thousand and what allows you to manage that kind of growth is your technology platform basically has to grow faster than your data is and Postgres performance improvements play a significant portion of that because if you're getting 20% year over year from Postgres that means you don't have to look at your own software stack and go man where are we going to get the next 20% from it's a very good comment because a lot of the NoSQL solutions are very very space inefficient and it's kind of easy to dismiss that with oh disk is cheap and yes disk is cheap but what's not cheap is the I.O Alvaro the creator of ToroDB gave a talk a couple hours ago in the other room and he's got numbers that showed comparing Mongo to Toro so it's sorry let me take a step back ToroDB is the MongoDB wire protocol implemented on top of a relational database specifically Postgres though theoretically you could run any relational database and the testing that they have done they see that storing the same data in Postgres versus storing it in Mongo it's anywhere from 68% of the size to 38% of the size so when you get down to 38% of the size that means that two thirds of the data that was in Mongo was wasted space basically and that makes a big deal from a performance standpoint so no that's a very good point it's certainly relevant so finally coming out in 9.6 after I don't know how many years of us wanting it Postgres is getting parallel query execution this is a tremendous amount of work that's been done by a handful of people right now it supports parallel scans I know that there's parallel join execution I don't know offhand if there's parallel index reads I don't know if anybody here does and it does support or they're working on support for parallel aggregate operation one of the things that people have a tendency to focus on with big data is this whole horizontal scaling and admittedly this is an area that Postgres is not as strong on but there actually are options here first of all is Postgres XL this is a fork of Postgres that allows you to run distributed transactions on multiple instances in a fully acid manner from a user interface standpoint it basically looks like oh it's just one Postgres instance it just happens to be spread across a number of different servers there are some other variations of this in fact Postgres XL grew out of something called Postgres XC so L comes after C you want the newest one there's also Postgres R there's also Green Plum which was very recently open sourced Postgres XL seems to kind of have the biggest weight behind it right now so that's why I mentioned it specifically the other option that you've got for sharding is a project called PG Shard and PG Shard I was going to put examples in and I completely spaced out and didn't do it but basically if you want to take a table with PG Shard and spread it across multiple servers you run one function to say hey this table I want you to shard it on this field and you then run a second command to say ok now that this is sharded go actually spread that data across multiple clusters that you have once you run those two commands you write queries against that table and they just get spread to all the different data nodes there's nothing else that you really have to worry about so very very easy to use very flexible the only downside is that with spreading the data out you don't necessarily have any more so if you want to do updates and in fact it does require that when you're doing updates you have to include the partition column in your where clause of the update so really updates are not going to be acid there are going to be spread across multiple nodes but as long as you are ok with that it's a really good option for getting horizontal scalability should you need it Postgres also has support for column stores for certain workloads certain data locality patterns a column oriented storage technique can potentially be a lot faster than doing the equivalent in a row based storage mechanism the C store foreign data wrapper is I would say the most up to date version of this it was created by Citus data it's basically one of the parts of their commercial technology and they've open sourced it for the community to use it is implemented as a foreign data wrapper so that's kind of the interface into it there is also an in memory column store there is a presentation on this at PG con last year and I did I did look when I looked it up there is current activity in github for this so there was there were commits back in I think October so there is activity on it but I haven't frankly seen much publicity on it so if you need a column store I kind of included it for sake of completeness if the C store FDW doesn't do what you need you might look at the in memory column store and there is also a foreign data wrapper to Monet which itself is a column store database finally on the performance side of things Postgres has support for GPU utilization for certain workloads this can provide just tremendous speed improvements because GPUs are all based on this idea of take a single or a handful of small operations and apply them to an enormous set of data as fast as you possibly can that's how graphics processing works and for certain activities in a database that actually lends itself very very well I would encourage you if you're interested in this go ahead take a look at the wiki page it's got some performance graphs on there and admittedly the performance graph is from an ideal best case scenario but the speed improvement is orders of magnitude like a factor of 100 or more so if you're pushing the envelope performance wise this can be a great thing and the nice thing is that it works natively once you load the extension it just goes and does its things so if it sees a query that it thinks would be a good candidate for running it's just going to go do it you don't have to sit there and manually hand code or anything like that finally another aspect of this that I think comes into play is there is really a very large very extensive community that has built itself around postgres and these are two examples that come to mind that I think are relevant to big data not anything comprehensive postgres is a restful API for postgres so if you want to be able to treat postgres kind of like is if it was a no-SQL database and provide a nice easy web interface to it postgres will do that for you also as I mentioned ToroDB is the Mongo wire protocol implemented on top of postgres so to kind of put all this stuff together I've talked about a whole bunch of different features just trying to kind of get you thinking about the different things that go into predictive analytics and data science and some of this big data stuff let's look at what happens when you actually put all this together so in a traditional analytics environment there's a number of different steps that get performed in order to go through the work of building a predictive model and trying to vet it out and trying to decide okay is this thing working is it not and really this is pretty painful so the old school traditional way to do this is first you would do a bulk export import of your production data into an analytic tool like SAS when you're dealing with a four terabyte database doing this becomes painful and then having to keep this thing up to date and having to do that every time that you need to spend a new version of the model it kind of stinks once you've got it in the tool now you can use the tool to start identifying correlations, patterns in your data things to be looking at and use that information to start creating your models and at this point you're basically creating your model by hand it's almost as if you're just writing the model as math formulas on a piece of paper once you've got an idea for okay these models here's what I think is going to work you'll do some amount of validation of the models based on the data that you've loaded into your analytics tool but ultimately you're probably going to have to take this model and run a regression back against your raw data set basically trying to simulate okay if this model was up in production when this event happened what would this model have said would it have made a better decision than the old model did and there's different ways that you can do that the way that I've seen it done is that you take the model and you write it in pure SQL and that can end up becoming kind of messy and difficult because even if the model itself isn't terribly sophisticated if you're dealing with a model that's looking at 50, 60, 70 different variables you've got a hand code well how do I pool each one of these variables out how do I present it and then you have to run the calculation as well and you're also worried about trying to do this in a way that's going to perform reasonably well so that you can run this test and it doesn't take three weeks to run so you go through some iterations of that and you use the information you gain to go tweak the model, change the parameters in it things of that nature finally you say okay I think we have a model I think we're good to go here Mr. Production Team Mr. Application Developer here's the model go code it and now you wait and six weeks goes by and eight weeks goes by and finally your model makes it out into production and now three to nine months later you can finally see whether all this work that you put into this model is actually producing good work if it's producing a significant improvement or not and I've talked to people that do a lot more of the consulting in the predictive analytics space and this is not uncommon this is a very prevalent thing that you see fundamentally because your data scientists and your analysts use a different set of tools and have a different set of needs and have a different way of thinking than your application developers do your application is frequently written in something like Rails well your modelers may be using Python they may be using R they may be using something else that you wouldn't really want to try and write your web application in this is the right tool for the analytics job this is the right tool for the application job and we have this big gap in between and that causes problems so when we started working with one of our clients they needed to be able to do this stuff and they've seen this kind of thing before so they were already very much in tune to this idea of look we need to make this this analytic workflow this has got to be made easier because it's just we can't be spending three, six, nine months on spinning models so if you take a step back and you think about creating a predictive model and especially executing the predictive model in a production environment there's kind of two things that go into this the first thing that you need is data and that sounds kind of obvious and silly but I think it's important to kind of segregate that out and separate it out because no matter what the data portion of executing your model it's a data problem whether your data is stored in a relational database whether it's stored in Mongo whether it's stored in React wherever it's stored the problem of how do I gather the data together that I need to make this prediction it's a data problem so the key facet there is solve your data problem using your data tool so in this case they are running on Postgres our methodology for pulling the data together is it's all SQL we have a view that goes and pulls the stuff together and then there's just a simple function that you call and say hey here's the person's identifier give me the data that I need to run this model and the function returns a Postgres complex data type that is every single piece of data that is needed by the model some of that is very simple scalar data some of it is arrays that that are actually arrays of complex data types themselves so there's an actual nesting that's happening here where you have this multi-layer document essentially that's being handed to the calculation function but in order to ensure robustness and to make sure that there's no surprises and that we know exactly what this data is and what format it's in we're not using something as malleable as say JSON it's done as an actual Postgres data type so there's no question about what is being handed back and forth so the data gathering is done in SQL the calculation function on the other hand and again we're keeping these two things separated very specifically so now the calculation you want to run the calculation in an R hey great fine you want to run it in Python okay not a problem you want to run it in Perl I mean yes we can do that it doesn't matter it'll run in whatever procedural language you want to as long as Postgres supports the procedural language you're good to go and the nice thing is is that these ideas have been wrapped up in a framework so there's a set of database functions and tables that are built around this notion of here's the data stuff here's the calculation stuff and this framework does things like when you go to set up the next version of a model you say okay here's version 3 of the fraud model here's the function that goes and gathers the data and that gets put into the framework and the source code of that function gets put into the framework here's the function that runs the calculation and the source code gets put into the framework so now we have this record that says oh here's the exact version and every time we do a run of the model we say well here's the model we're running we capture all the input data we store that as part of the execution this individual execution we then also hand this to the calculation function it does its thing it provides another Postgres compound data type we then store the compound data type of the results and now we have a record of absolutely everything that went into executing this model now for a lot of you out of curiosity how many of you are doing big data how many of you data science data okay good about quarter you know a lot of you if you're doing like weather modeling or something like that you're like what why why are you going to all this problem why are you going to all this trouble well Kirio's capital is a lending institution and there's a lot of regulations that are wrapped up around lending institutions so when they're at work one day and the auditor shows up at the front door they want to be able to answer auditors questions and part of this is the auditor can go in and pull individual loans or pull declined loans and say why did you decline this person alone did you decline it because they're a woman or did you decline it because their credit stinks the first one is bad the second one is okay so this framework was developed specifically specifically to to support those kind of ideas that now they can explicitly say here's everything that went into this model decision so those of you that are in a heavily regulated industry those of you that want that traceability you know that's that's something that might be very valuable others of you that don't care so much about the traceability a lot of that I think is still valuable you know this notion of separate the data gathering from the calculation but even more importantly take utilize Postgres as the thing that binds this stuff together and it makes it easy because now since all this stuff is done in Postgres we can do things like spin up a snapshot of production go mess around with the models whatever I don't care if you screw the whole thing up as long as you don't leak any sensitive data you can go test on it oh you think you got something up and workable you want to do a regression okay fine let's run the regression on a replica say or we could even potentially run the regression in production itself and just not you know not use it for decisioning and the nice thing is we would be able to prove oh nothing's decisioning on it because there's no records that indicate it I could easily put a trigger in place that says if you try and create a record deny it you know so there's all this flexibility that comes about because instead of this analytics team and the application development team I'm standing here in the middle as the data architect saying come on guys let's pull this in together here so finally you know one thing I do want to mention obviously I'm a big Postgres fan if you haven't picked up on that there is no such thing as a magic bullet there was a tremendous amount of hype around no sequel when the tools started coming out and a lot of people like oh this is going to solve everything and you look over the past year or so and all of a sudden now there's all these projects that are springing up around taking a no sequel technology and putting some form of sequel back on top of it because as it turns out sequel is a pretty good language for dealing with data maybe you can't do all of your big data analytics data science in Postgres that's fine there are certainly workloads where Postgres is not the right tool for the job it's not the right answer what I do think that Postgres has to offer is this incredible amount of connectivity and this incredible flexibility through its extensibility and the capabilities that it already has built in so that the parts that don't make sense to put into Postgres fine put it in the technology that makes sense but then use Postgres as kind of your hub use it as the thing that can pull all this stuff together and make it easier to do your job make it easier to work with and of course if you do have parts of your system where you really do care about immutable data quality and you need to make certain that hey when I say this thing is committed it's committed whether it's because of regulatory reasons whether it's because you're dealing with things like a shopping cart if somebody buys something from you I tend to think it's pretty important that when they click submit and you say commit that that record is stored when you show them the confirmation page it says yes your brand new widget 5000 it's on its way so questions yes so because because Postgres has this support for multiple different procedural languages you can run pretty much whatever you want to run in the database now I will say that obviously if you've got something it's going to just burn through 80 cores like there's no tomorrow maybe you don't want to run that on the database server itself but you can also because you've got this advanced language support maybe what you're doing in the database is you make a call into a procedural function that itself can go call out to a more distributed environment and then the data comes back in now on its surface that sounds kind of weird because why wouldn't you just have the application do it and the reason I would personally do it in the database is you're doing your data gathering you run the calculation you get the results back you probably need to store the results in the database too so really this is all still a data problem it's not until you've captured the data calculated and then stored the results that now you go back to the application and say hey here's the number that you were looking for Postgres does not and I don't think it should because it's got Python just as an example and I know that there's some nice neural net capabilities in Python and you can pull those classes in all you have to do is install the class library on the database server and you can run it right there native within Postgres again this is where you do run into the tradeoff of if it is something that is computationally intensive that you're going to be doing you may not want to run that directly on the database server the question then becomes okay what's the data component of that look like like how much data are you dealing with for a model run if you're doing something that's say finance based something that's not handling enormous amounts of data I think it's reasonable to say hey we just got a Postgres database we pull it together and then we fire it off to an external process and there is parallel capabilities within Postgres and cluster capabilities on the other hand if you're trying to do weather modeling and you're trying to pull in every temperature sensor on the planet for the past week okay Postgres maybe not the right answer so it depends on what you're trying to do really what I want to do with this talk is just get people kind of thinking because I think right now there's this mindset of well it's a relational database you can't big data relational that doesn't mix well it can mix yes I do not know off the top of my head I don't know honestly the thing is is that I think so when you talk about foreign data wrapper sophistication at the base level all that a foreign data wrapper is doing is just passing data back and forth it's doing protocol translation and it's basically turning something like a select statement into whatever you need over here where the sophistication comes in is when you're trying to do stuff like okay I'm going to join three tables locally and two tables over in this foreign data source and three tables over in this data source so what's the efficient way to plan that and push that all out that level of sophistication I suspect is not in the Hadoop foreign data wrapper because that level of sophistication is still being worked on in the Postgres foreign data wrapper to let you talk to another Postgres database so that I don't think is there but I suspect that for a lot of these tools it's probably not a really germane question because a lot of times they don't you know I don't think Hadoop even has the concept of a join maybe Hadoop does but I know a lot of the NoSQL tools what's a join you know you're pulling an individual document or an individual key so I think there was some questions over here no anyone awesome thanks guys


The member from Essex, it's now time for a member's statement. The member from Frontenac, Constance. Thank you, Speaker. World COPD Day. In 2021, this government introduced for the first time and passed an Act to proclaim the day, November 16th, COPD Awareness Day. Chronic obstructive pulmonary disease is a serious and progressive disease that causes lung damage and blocks the airways. It is the fourth leading cause of death in Canada and a leading cause of illness. The Ministry of Health has taken significant action on COPD, care and prevention, aligned with health quality of Ontario's advice, including increasing access to smoking cessation programs for patients in both the hospital and primary care settings, including patients with COPD, increasing access to influenza and pneumonia vaccines among COPD patients, investing in early detection and treatment to slow the progression of this extremely serious lung disease. One of our most promising investments has been in the best care and primary care program, recognized by clinicians and patients as a highly effective, made in Ontario team-based, patient-centered care model. Best care has successfully reduced emergency room visits by 63% and hospitalizations up to 60% among COPD patients. This program has improved mental health among chronic disease patients who are receiving whole-of-person care for the first time. As demonstrated in peer-reviewed studies, best care and primary care has saved our health system millions of dollars in cost, alleviated pressures on capacity, and significantly improved the quality of care and life for Ontario's living with COPD. On this World COPD Day, I want to recognize the work of healthcare professionals from physicians to nurses to respiratory therapists who support people living with COPD every day and encourage Ontarians to remember that your lungs are for life. We'll go with the member from London North Centre. Speaker, last night London inaugurated our new City Council. I want to take this time to thank all of them for stepping up to serve the City of London and wish them all well in leading our great city to meet its potential. As deeply concerning, Speaker, the Councillors are being sworn in on the same week this government is going out of their way to strip them of their powers with Bill 23. This new legislation takes power away from cities right when they need it the most. It costs municipalities hundreds of thousands in lost revenue and gives the Minister sweeping powers they've never had over bylaws and local planning. It's also a direct attack on conservation authorities. Ontario is losing almost 320 acres of prime farmland per day, but this government wants to make the incredibly rich, even richer. This bill hurts the environment, renters and weakens democracy. It jeopardizes affordable housing stock and it's a gravy train giveaway to politically connected developers. I worry that this government's party with the public purse is completely out of control. Speaker, our City Councillors were elected by the people of London to lead our community. This government was not. Ontario cannot go backwards on the environment, democracy and protections for people who need it the most. I hope that this government will listen and learn what municipalities require and collaborate rather than dictate. Thank you. The member from Aurora New Market. Madam Speaker, I would like to inform the Chamber today of a tremendous community organization in my writing of New Market Aurora called In From The Cold, whose mission is to meet the needs of people who are homeless or at risk of becoming homeless. They provide emergency shelter in harsh winter conditions and offer a drop-by program, which is open seven days a week where community members are served meals, have access to shower and laundry facilities, as well as to computers and the internet. I am very pleased to announce that this year, our government has supported In From The Cold with two resilient communities grants. These total, a total of $277,400 through the Ontario Trillium Foundation. These grants help In From The Cold with staffing costs, including the hiring of a systems navigator. The grant also helped with the costs associated with building a new website to improve its digital presence, as well as to implement a health and wellness strategy and organizational risk management plan. When turning the facility, I had the privilege to meet their systems navigator and volunteers while witnessing firsthand the many vital services they provide. I would like to thank Anne Watson, the executive director, and all the volunteers at In From The Cold for the great work they do in our community. Thank you. Thank you. Thank you. Thank you. The member from Kewetsuno again. Amigwe Sb. Amigwe Sb. Good morning. Winter is almost upon us. This morning, I'd like to raise awareness of the impact of house fires and risks in First Nations. In 2019, in Kichindamaeksep, Ninoak, also known as KAI, a mother and four children passed away from a house fire. Unfortunately, I have stood in this house here and asked for moments of silence, multiple times for people in Kiewetnung who have died in house fires.  Despite First Nations having a 10 times higher mortality rate compared to non-First Nations communities in Ontario, First Nations children ages zero to nine have the highest fire-related mortality rate, which is 86 times, speaker, 86 times greater than non-First Nations children in Ontario. House fires and hazards are at an increased risk in all First Nations where access to fire departments and emergency medivacs require additional time for support. Or they may not even have access to this. Who do not have access year-round roads? And it's a challenge for First Nations, 24 First Nations in Kiewetnung. These concerns are directly linked to safety, well-being in lives of children. As the seasons change, I ask the members to consider the experiences of families in the North as they try to stay warm while living in these conditions. Miigwe, speaker. Member statements, the member for Carlton. Hey, Mr. Speaker. Mr. Speaker, I rise today to speak about one of Manitick's most charming and whimsical shops, the Gingerbread Man. Many from the historic village of Manitick and those that have traveled from far and wide to see the one-of-a-kind shop will remember the gingerbread aroma that filled the shop as soon as you stepped foot through the door. The incredibly detailed gingerbread showpieces, the multitude of gingerbread cookies for every occasion, the famous Buttertarts, the delicious Cawartha ice cream and so much more. Richard Palferman started the Gingerbread Man in 1998 in Toronto and eventually in 2001, he moved his home and business to Manitick. The shop has grown with the village of Manitick and had the pleasure of watching neighborhood families grow and have their gingerbread houses become part of Christmas traditions. The shop is also where Richard met his wife, Kairi. Kairi helped the business grow with her fantastic shortbread, banana bread and intricate decorating skills. Unfortunately, Mr. Speaker, on November 4th, Richard and Kairi were devastated by a massive fire that took away their home and business. The 150-year-old building, which is one of the first houses ever built in Manitick, will have to be torn down. The weeks before Christmas are the most busiest time of year for them. The loss of business means loss of revenue to pay their bills and support the community and the loss of their home means that they're left a hole in their hearts. Mr. Speaker, this is a huge loss for the village of Manitick. I send out my condolences to Richard and Kairi. And if anyone wishes to support them, please check out their website online. Thank you very much. Thank you. Thank you. Any more statements? The member for Janiskaming Parker. Mr. Speaker, I would like to once again make this house aware of a situation in my riding. The importation, storage and spreading of raw human sewage imported from Quebec stored on a former dairy farm lagoon. The neighbors raised concern when this was approved by the current minister of environment. I contacted the minister, especially because it was a former dairy lagoon and there was a well situated in the area. The ministry replied that it was a green site, no former, there was no former infrastructure and there was no well. There is a well, it was former infrastructure. When I made this again aware to the minister, no reply. A few weeks ago, I wrote the minister a letter saying, well, since the ministry didn't even identify there was a well, how can the neighbors be assured that you've identified any of the other infrastructure around the former dairy site? I got a nice letter back on Friday saying everything is fine. On Saturday, hopefully by coincidence, the lagoon was pumped. On Saturday night, after dark, excavators went in and excavated at night till 4.30 in the morning. I called the MOE, no report, but the local MOE inspector did send an email to one of the neighbors that this was MOE sanctioned and they were digging out the former foundations of the barn that didn't exist. We want all the documentation. We no longer have faith in the ministry. We need all the documentation. Thank you, Speaker. Member Statements, the member for Kitchener Conestoga. Thank you very much, Mr. Speaker. And normally when we get up and do Member Statements, we talk a little bit about something that's happening in our riding. But gosh darn it, Speaker, I am so excited. I just can't hide it. The World Cup is coming on November 20th on Sunday. And Speaker, I am a huge football or soccer fan. And believe it or not, for everybody here in the house, I actually have two children. One of them's middle name is Chelsea after Chelsea Football Club. And one of them's middle name is Robin, R-O-B-B-E-N after the Dutch footballer, Adrien Robin. So I'm a huge fan. I'm very excited. Really looking forward to seeing for the first time since 1986, Canada playing in the World Cup. Now I will say, Speaker, I'm a little partial to the German team. And we normally fly the German flag outside our house actually during the World Cup. But this year it's going to be a little bit different. We'll make sure that the Canadian flag is on top. But I encourage everybody if you have an opportunity, it's a little bit different with it being Qatar this session, I guess you could say. And the games are going to be on different times. It's going to be a little weird. But they start on the 20th. If you have an opportunity, please tune in. Support Canada. I know we have some athletes that are here from Team Ontario today as well. And so welcome to the Ontario legislature. And maybe one day they'll have an opportunity to play in the World Cup as well. Thank you. Member Stavins, the member for Guelph. Thank you, Speaker. I rise today to pay tribute to the thousands of acres of protected land that will be lost to the government's plan to pave over the Greenbelt. And to give voice to the millions of people in this province who love the Greenbelt, they want the Premier to keep his promise. Over and over again, inside and outside this house, the Premier explicitly promised to protect the Greenbelt from development. It's because there's a reason these lands are protected. It's the land that protects us from expensive floods. Cleans our drinking water. It's home to so many places that people love to spend time with their family. Home to the farmland that feeds us and supports our economy. And all this is under threat. So a handful of land speculators can turn millions into billions. And the rest of us will pay the price and longer commutes, higher flood costs, increased property taxes, and reduced food security. Speaker, there's 88,000 acres of land already approved for development within existing urban boundaries where we can build affordable homes and communities people want to live. We simply cannot afford to continue to lose 319 acres of farmland each and every day. So on behalf of my constituents and millions of Ontarians, we say, Premier, keep your promise. Keep your hands off the Greenbelt. Thank you. Member's statements. Member for Essex. Mr. Speaker, all of us remember that under Liberal government, 300,000 jobs fled the province of Ontario. But, Mr. Speaker, I have good news. Because those jobs are coming back. They're coming back to my riding of Essex, and they're coming back to the town of Kingsville. And I had the pleasure of going to Kingsville with the Minister of Economic Development and Trade. And he brought a provincial investment of $1 million to the town of Kingsville. And that $1 million is going to be matched by $11.5 million by business investors for a total of $12.5 million of manufacturing investment in the town of Kingsville. Now, that's a town of 21,000 people. So $12.5 million is a big deal. It's going to create 29 jobs, good jobs, skilled jobs, jobs you can start a family with, jobs you can build a house with. Mr. Speaker, I want to thank the Minister of Economic Development and Trade. And I want to thank the leadership of the Premier. I want to congratulate Idlecore Industries of Kingsville and MC3 Manufacturing of Kingsville for creating good, long-lasting jobs for the people of the county of Essex. Thank you. Thank you. Member statements. The member for Windsor to come see. Hey, hey, hey. Thank you, Speaker. I'd like to recognize a tremendous and well-loved institution in my riding Windsor to come see, the Greater Windsor Concert Band. This past weekend, the band celebrated their silver jubilee and true to form, they celebrated in style with a free community concert at the Serbian Center of Windsor. Led by their enigmatic director, Rick Moore, the band brings back to our community the magic of wind band and concert band music into our community. From their humble beginnings in 1997, comprised of Rick and 12 musicians that he was able to recruit and host it at St. Thomas of Villanova High School, the band delivered their first of many Christmas shows that year and have kept an incredible holiday spirit alive in our community ever since. The COVID-19 pandemic didn't keep the band down. Their YouTube and TikTok channels are going strong, delivering incredible music when it was needed the most to keep our spirits lifted. And for those among us who just can't get enough of all I want for Christmas is you, rest assured that the Greater Windsor Concert Band has an incredible rendition of this beloved holiday classic. Thank you to President Diane Hernandez, Vice President Lori Coulter, Secretary Annette LaRose, Treasurer Matthew Grayson, and Directors Karen Barnes, Amanda Hansen, Angela Manser and Laura Zartlinga for sharing your love of music with all of us in Windsor to come. Thank you very much. That concludes our member statements for this morning.


This study found that certain aspects of the built environment can influence the amount of leisure time physical activity, ltpa, adolescents participate in. Specifically, security and aesthetics were found to be correlated with higher levels of ltpa. Additionally, accessibility and pedestrian safety were also found to be related to higher levels of ltpa. This article was authored by Hawu LV and Rui Wang.


So, over the next two lectures we will be talking about selected reaction monitoring also called multiple reaction monitoring. So, the topics that would be broadly covered would involve what are targeted proteomics approaches and why are they important. The basics of SRM or MRM the various steps that are involved in designing a successful targeted proteomics experiment and the analysis of SRM data using Skyline. The analysis of the generated dataset would be covered in the next lecture and this lecture would broadly cover the first three points. So, let us now dive deep into what are targeted approaches and why do they play such an important role in the field of proteomics. So far you have learnt about the shotgun proteomics approaches wherein the abundance of the peptides that are detected at the MS-1 level determines which peptides get fragmented at the MS-2 level. Upon fragmentation at the MS-2 level the identity of the peptide can be known subsequently this these are mapped to the proteins they come from. So, it can be argued that the shotgun proteomic approaches largely rely on the abundance of the peptides. So, one inherent problem of the shotgun approaches has been that the peptides which are of low abundance usually go undetected hence the identity of all the proteins may or may not be possible using shotgun approaches. So, just to give an idea this is how the shotgun approach looks like you have different targets one of the targets which bears the red dots is the actual target which you wish to hit. But you may or may not always succeed in hitting this target and more often than not you are likely to hit all the other targets along with the target of interest. This pretty much sums up how a shotgun approach works. On the contrary the targeted approach relies very heavily on the information of the protein and its peptides and a few other parameters about which we would discuss in the slides to come. It is to be noted that the targeted approaches are very specific and hence have become the choice of interest for most proteomics labs around the world. The targeted approaches are largely hypothesis driven so one can just look at those proteins of interest on which the hypothesis has been built upon. However, both the shotgun approaches and the targeted approaches are equally important and have played an important role in the development of proteomics. So, while we do not advocate any one particular approach over the other it is to be noted that the shotgun approaches have long served the proteomics field in the discovery phase whereas the targeted approaches are now increasingly replacing the conventional validation approaches which involve the use of antibodies like ELISA or western blotting. So, this is again the overview of the shotgun proteomics workflow. I am sure that by now you are very well acquainted with the steps involved in a shotgun experiment, but I would quickly walk through the major steps that are involved. So, we start with say the protein lysate on which the experiment is to be performed and then using the protease we digest these proteins to give rise to peptides. The digested peptides are then separated on column and using electrospray ionization the peptides get charged. So, by now you must be aware that only charged species get detected inside a mass spectrometer. So, ESI plays a very crucial role in mass spectrometry since it imparts charges on the peptides enabling them to fly inside a mass spectrometer. The next step is the identification of the peptides and database searching. This is the conventional shotgun proteomics workflow. So, the targeted proteomics workflow follow pretty much the same steps except for the fact that the mass spectrometer is instructed to monitor specific peptides and their resulting fragments. The slide that you see currently differs from the previous slide in the fact that the instrument here has been much better instructed to selectively monitor only a few peptides. So, before we look at the principle of selected reaction monitoring and how it works I would want to spend some time on the different targeted approaches that are known to us. So, as you can see in the slide there are three main branches for the targeted approaches wherein the first is selected reaction monitoring, the second is parallel reaction monitoring and the third is SWAT MS. While the SWAT MS is beyond the scope of this lecture, this topic has been extensively covered by Mr. David Campbell from Institute for Systems Biology. The selected reaction monitoring approach is also called multiple reaction monitoring. This technique involves the use of a triple quadrupole mass spectrometer. The first quadrupole and the third quadrupole of the instrument act as mass filters allowing us to monitor a peptide and its corresponding fragment alone. The parallel reaction monitoring approach is an advancement over selected reaction monitoring. Contrary to the use of a triple quadrupole mass spectrometer as seen in selected reaction monitoring, the parallel reaction monitoring approach involves the use of mass spectrometers with very high resolution. So, these mass spectrometers usually contain orbitrap or the time of flight mass analyzers. So, it can be argued that parallel reaction monitoring approaches will offer much higher resolution as compared to selected reaction monitoring. So, the parallel reaction monitoring approaches are also called MRMHR or SRMHR with HR standing for high resolution. So, we now look at the crucial steps that are involved in designing a targeted proteomics experiment. So, I would like to spend some time on this slide because it is very crucial to make a note of all those parameters that one needs to optimize for the success of a targeted proteomics experiment. Since the targeted experiments are usually hypothesis driven or the researcher has an idea as to the proteins that he or she wishes to monitor, the target proteins the information on the target proteins plays a very important role. The sample processing involved in a targeted proteomics experiment is no different from that seen in a conventional shotgun experiment. The proteins are first digested using a protease and this would result in the formation of peptides. The digested product would now contain peptides corresponding to the proteins of interest. A few of these peptides may or may not be proteotypic or quantotypic. So, quantotypic peptides are the ones which are relatively stable over a period of time and can be used for quantification. So, one can narrow down on these peptides through repeated experiments and through repeated optimization experiments and these peptides can then be taken forward at a later stage when the goal is to quantify or to find out the amount of the proteins of interest. So, once the peptides are formed they are separated over a liquid chromatography setup and one of the more crucial parameters for the success of a targeted proteomics experiment is to have an idea of the retention time of the peptides. By now you are aware that peptides differ in their behavior based on their amino acid composition and their hydrophobicity. So, it is very much likely that the peptides elute at different times from a column. So, one needs to have an idea of the LC gradient that is to be used for efficient elution of the peptides of interest. The next step is the parameter called collision energy. Collision energy is that energy which is needed to break the bond between two amino acids. So, on applying correct collision energy the peptides give rise to different fragments. So, one needs to have a very clear idea of the collision energy that is to be used to fragment the peptide of interest thereby giving rise to the different fragments which will then be monitored in the mass spectrometer. So, as you can see from the slide that one needs to have an idea of the precursor mass to charge ratio and the fragment mass to charge ratio. The precursor mass to charge ratio corresponds to the mass to charge value of the peptide of interest. Subsequently the fragment mass to charge corresponds to the mass to charge of the fragment that results from fragmentation of the precursor peptide. So, having an idea of each of these parameters would largely determine the success of an experiment and I urge you to look at different tutorials and lectures that are already available which cover these topics in much more detail. We now see the principle of selected reaction monitoring. So, as you can see from the slide selected reaction monitoring uses a triple quadrupole mass spectrometer. And in this current slide you can see that there are three quadruples.  Quadrupole 2 on the other hand is a collision cell where the fragmentation of the peptides takes place. So, the quadrupole 1 here has been assigned a particular mass to charge ratio. This would mean that only the species which fall in this mass to charge range would be allowed inside the mass spectrometer. Ideally you would want the mass spectrometer to be as much selective as possible. So, the success of the experiment would largely depend on each of the parameters that were discussed in the slide before. Once the peptides have entered into the collision cell, they will get fragmented based on the collision energy value that has been already provided to the instrument. This fragmentation would randomly result in the formation of different fragments and it is at this stage that the third quadrupole acts as a selective mass filter again wherein it would let through only a few fragments to pass through. So, it is to be noted that the method file that is provided to the instrument for acquisition contains all the relevant information and that is how the instrument is able to monitor the peptides and the fragments of interest. So, to sum up this slide both the quadruples of the instrument act as a mass filters thereby filtering out all the other unwanted peptides and the fragments and an important term that needs to be discussed here is the term transition. So, you would come across this term very frequently when you read papers on selective reaction monitoring and a transition is basically a quadrupole 1 to quadrupole 3 pair. So, the value of quadrupole 1 and quadrupole 3 for a peptide and its corresponding fragment is called a transition. So, let me just quickly walk you through an example to help you understand this concept better. In this slide you can see that there are 3 peptide sequences and the table contains the information of the peptide that is the peptide sequence. It also contains the quadrupole 1 mass to charge and the quadrupole 3 mass to charge. It also contains information of the fragment which results after cleavage of the peptide bond, the collision energy information and another column called the dwell time about which we will talk in a few minutes from now. So, as you can see each of these 3 peptides has different values fed in the table. So, let us consider the first 4 rows of the column wherein we are talking about peptide 1. So, as you can see the quadrupole 1 mass to charge for this peptide is the same in all the 4 rows. However, what is different is the quadrupole 3 mass to charge which means that while the quadrupole 1 is selectively allowing only the one peptide of interest. So, the quadrupole 3 has a job to let 4 different fragments arising from peptide 1 to pass through. The collision energy of this peptide is the same and the same would apply for all the other peptide sequences in the table. Subsequently, the table also contains information of two other peptides wherein the quadrupole 1 and quadrupole 3 charges have been mentioned and their respective collision energies. Now, the time that the instrument takes to monitor a transition in a particular cycle is called dwell time. So, as you can see each of these fragments has been acquired over a period of 100 milliseconds. So, the instrument does the job of acquiring individual fragments for peptide 1 then there is a brief pause time after which it starts collecting the information for peptide 2 again a pause time and then it would collect the information of peptide 3. So, the sum total of all these dwell times that you see in the table would give rise to a cycle time. The cycle time is that time which the instrument needs to collect at least one data point for every transition that is a part of the transition list. So, it is safe to assume that the data points would be picked largely depending on the chromatographic behavior of the peptide of interest and the end result would be a chromatogram as you see on the slide. So, this chromatogram is formed by joining all the data points that the instrument was able to collect over the retention time of the peak. With this we conclude the basic principle and the steps that are required to design a selected reaction monitoring experiment. In the next slide we will look at a data that was generated in the lab and we would use the popular software Skyline to analyze a data set. Thank you.