 Okay, thank you guys and you know sorry for you know cutting short the lunch time I think for me and I hope you guys had a good lunch so today I'm going to talk about one of the practices that I've been doing for last couple of years kind of like mixing matching our traditional database practices with our you know the most common trend that we have is non-structured data so hopefully it will be short session around 20 to 25 minutes so let's get started with it. So I'm Mizant I'm from Bangladesh I'm currently working as a telenoid health as head of engineering so mainly I am a software architect with around 16 years of PHP experience and I do some writing a little bit and my latest book was in was on PHP 7 data structure and algorithms and since we are just had lunch so I decided like why not show some picture of food so we can relate this right the spaghetti so when you are trying to get spaghetti with spoon what happens you are get having hard time to actually place it in a spoon so the problem is that you need a fork to actually you know scroll around and have the spaghetti but again once you are done with almost with the spaghetti you don't want to leave the sauce in the plate so you need a spoon to actually eat that so there's a common problem like which spoon should I use should I go for fork or should I actually go for spoon and we end up actually using both the same thing actually happens with our database choice actually so we are in a kind of like in a dilemma whether we should be going our traditional database like my SQL postgres SQL or should we actually go with our purpose built DBMS like MongoDB, CouchDB so it's like kind of like choosing which one is a you know best for us and at the end what we do we actually end up using both for relational data our love and you know kind of like long relationship with the RDBMS we can't actually leave it out and again with the modern trend we actually try to use the you know MongoDB or similar database for unstructured data so in a simple project we actually end up using both so that's actually makes our life more difficult unless we have something like this it's called spark so that's actually combination of spoon and fork which is used for spaghetti eating or other purposes maybe so if we can actually have this in our database that should have been great and that's actually what we are going to talk about yeah it's already there and whether we like it or not we have to thank Jason for that so we have this whole new not actually whole new it's actually been there for a long time especially used in JavaScript but standardized to actually use in everywhere at this moment and we are actually kind of like obsessed with Jason and we are using Jason almost everywhere and especially with the API and Jason they are actually kind of like synonymous right now so if you are using API we are always expecting that the data should be written as actually Jason so Jason actually gives us lots of flexibility when we are actually representing data in different formats and since we are actually working with Jason especially with you know other database like MongoDB and CouchDB what actually make us still use the rdbms so in unstructured data the problem is that the relationship is not there we miss the asset properties so not you know lots of transactions things that actually not that important over there but in rdbms we actually make sure that the data are highly structured we have the asset transactions we have the relationship and we are very much kind of like glued to our normalization process and we end up using lots of joints you know and also sometime for performance we do something called anti pattern denormalization we create flat tables and use those so in such way we are actually kind of like missing up our development so why not we actually move to the new era of rdbms with the support of Jason so lots of the database engines are actually right now started supporting Jason and one of my favorite database engine you can say that PostgreSQL so they have very good support of the Jason and Jason B and it's not a native Jason so a little bit different from what we actually see in MongoDB but it solves the purpose and in some cases it's better actually so Jason B is they call it Jason better or binary Jason whatever you name it in PostgreSQL they actually prefer it to call it Jason the better version and they are actually kind of like tackles the indexing problem that we previously had and this particular data type in data our database actually helps us to actually mix both our structural and Jason data same time and it's pretty easy to use if you are trying to implement your you know a Jason B structure in your current database design just create a column for with Jason B data type that's it and if you look at the example of Trello Trello have some card structure where we actually move the cards and each card has some descriptions, some to dos and lots of things now traditionally designing such you know solution might be you know very complicated because you have to end up with lots of tables one table for cards one table for say tags one table for you know to dos and at the end of the day you have to use join to find those data together so we are actually creating a simple cards table to show like how it can be actually mix and match with our traditional practices and with the Jason one so basically you can see some queries there so we are inserting some data Jason data in our Jason tab in a column we have name we have tags we have finished so this is actually another array so we are actually using multiple values within the Jason object and then again number four especially we have tags expert number three we have tags and ingredients which actually is not present in any of the other Jason object that we have so when we are actually structure storing this this actually helps us to actually store different sets of you know data formats in the same column and now when you are querying data so the big question is like yeah we can store it fine now how to actually query the data is it faster because we know when we are having the traditional database with you know structural data we can index those data we can get data faster we can query we can we do filtering and everything so what about the Jason part the good part is that yes we can query the data is pretty simple and we'll see like how what are these operators actually means so we are actually asking that within the data column in Jason I need a key called name and this value is actually returned as text and we are actually seeing that and similarly we can actually filter these results like where we want to see the curse which are actually finished so we can query those data and we can actually search filter those data as well and also we can check for example we see in some of the data models we had ingredients some didn't so if we want to know whether the column actually exist or not we can actually query it as well and also if some of the Jason object has nested Jason object we want to get those data we can use the built-in functions within the database to actually know those things so this is actually pretty simple it's already built in features of the database engine and also the indexing part we can actually index whole Jason or a part of it within our database engine so that it can work faster and I have worked with millions of rows of you know objects without indexing is still a lot faster you know so if you want to add indexing that's fine and what PostgreSQL does is that it supports indexing through gene which is a generalized inverted indexing and it actually is quite faster and there are some operators so which actually gives you as an object or text whether you want you can query using path and everything you can use Boolean operators to see whether that you know particular keys there or not in if the if a certain path exist or not everything now these are pretty simple and they have lots of built-in function the question is like if I can have the support of the same you know unstructured data within our rdbms what happens to MongoDB like is it really good compared to MongoDB so just to give an example that though the test is actually quite old so that's 2014 so what they did is that with 50 million records of complex document data they put it in both PostgreSQL and in MongoDB so result was that PostgreSQL was twice as fast at regarding data ingestion two and half time fast at data selection and three times fast at data insertion and while doing this PostgreSQL actually consume 25% less space considering the MongoDB one so MongoDB actually came up with the better version of their software in the next release so now it's almost per but in most cases if you go for you know performance analysis you will see in some part the jsnb operations of PostgreSQL is much faster compared to MongoDB and that's one of the reason actually when we started our microservice platform in Teleno Health we actually chose the PostgreSQL instead of MongoDB so what does it mean then you know we are not going to use MongoDB so that's actually not the case you know so MongoDB has also some better choices that for example if you are performing lots of dynamic queries MongoDB comes with automatic sharding features so if you want to scale Mongo is much better you know options in case of PostgreSQL or any traditional rdbms the performance tuning has to be done you know manually or you have to use third-party solutions to actually make it efficient but in Mongo that comes automatically and again like my suggestion for you guys should be like I am very much big fan of actually using both so I am using PostgreSQL in case basis where I need to actually kind of like denormalize my structure I use JSON over there for example if I have a post say post table and I have a tax table in traditional rdbms I will end up with three tables so I will have post I have tax and I have many to many relationship called post underscore tax that's the general practice that you have been doing but if I am actually kind of like trying to omit the whole post underscore tag and separate table what should I do I can't create a JSON column within the post where I am actually saving all the post related tax as an object as a simple JSON object that gives little bit flexibility when you are developing and also arises few questions like what happens to the normalization concept what if the tax are null so there can be so many issues but you are the person to actually going to choose what is best for your project so the suggestion is that if you are using static JSON data type postgres might be a good choice my SQL is coming up with the JSON data structure is almost like you know they're catching up with postgres in that sense and if you want to mix both unstructured and structured data solution is like yeah you can go for postgres or even my SQL they can be a good choice but if you are very much you know like heavily dependent on unstructured data preferably post MongoDB or similar database will be good because one of the features that in MongoDB you can actually change individual key directly whereas in postgres or my SQL what will happen is that you have to copy the whole object then change it then store it so the operation of change is actually more costlier in your traditional DBMS but if you are actually having a static JSON type data then maybe postgres will be much better for dynamic one MongoDB is much preferred so that's it I have very small presentation as I was asked to actually keep it within you know 15 to 20 minutes I actually try to keep it within 15 minutes unless you have any questions thank you guys