Wait... PostgreSQL can do WHAT?

ever since completing my research for this  episode in fact I have only one question is it   even a database anymore stay with me and I'll  show you how posr s can replace all your back   and stack components after more than 35 years of  active development and a new release coming every   year posg scale today is the most popular and  according to its website most advanced open-source   relational database if we look into the latest  stch overlow developer survey it's also the most   loved database among professional developers in  the list of companies using posel in production   there are many household names every major cloud  provider has a managed poser scel database service   but unlike all the competitors in the pretty  crowded database Niche there is no single   company governing development of POS SQL there is  no King like lenus stals Matts or gidan rossom to   approve technical decisions the official road map  page even claims we enjoy allowing developers to   explore the topics of their choosing sounds like  a product owner's nightmare scrum yes scrum scrum   so what posq really is and how come it is so  successful database project with an elephant   masket was not always considered cool just  look at these stack Overflow developer service   for the past several years back in late '90s and  2000s every web developer knew what lamp acronym   stood for Linux Apache MySQL PHP MySQL an open-  Source database from Sweden became a go-to storage   solution for booming internet businesses it was  free it was easy to set up and work with and it   was fast Facebook used MySQL Twitter used MySQL  YouTube used MySQL poq project at the time was   focused on painstaking implementation of more  features better compliance with SQL standards   query optimizations indexing improvements crash  recovery the list goes on it was clear that its   contributors followed a very different philosophy  instead of speed and E of use the key selling   points of mysq they wanted to build the most  feature Rich and standards compliant database   in the world so how feature reach is feature Rich  poql the official website calls pogl an object   relational database what does it even mean the  idea is to bridge the gap between object oriented   concepts with object hierarchies and complex data  structures on the one side and relational database   design where you have only tables rows and columns  on the other so how does this Fusion of Two Worlds   work in poql first of all you are not limited by  native data types although even here you can feel   what feature reach means in addition to Common  numeric Boolean and character types poql supports   right out of the box several ways to store dat  time a bunch of network address data types that   allow for quick submitting calculations and for  example geometry types like polygon or Circle   to name just a few of supported native types  but because as I said above posq is an object   relational data base you can as easily Define  your own types and use them as if they were built   into the system let's say I work on roleplaying  game and I want to store game characters in the database let me Define the character type this is how I can create a table and use my  new custom data type there here's the game [Music] character this is  how I can query my character stable okay let's go back to objectoriented  Concepts you've seen complex data structures   type casting and the dot notation to access object  properties another Cornerstone objectoriented   concept that is implemented in posg scale is  inheritance table inheritance for my roleplay   game example let's say I want now to have a  table for NPCs NPCs are special characters   that always have a quest to offer to a player  I trust you have a good reason for interrupting me which means I can inherit my new NPC table  from the already existing characters table time   to populate it with a couple of [Music] NPCs pay  attention Fields character id and character are   not defined in the NPC's stable they are inherited  from the parent table here are my two NPCs in the   NPCs table now the interesting part all three  game characters two NPC players and one player   character are now stored in the P table let's  not forget that objectoriented programming is   code and data bundled together when it comes to  running code in the database posar scel has to   offer more than any of its competitors just  look at the table of supported programming   languages for stored procedures before I can  go straight to the code I need to enable the   language Handler and for that I need a package  manager yes there is a package manager for poql [Music] extensions [Music] [Music] [Music] is [Music] this is the code of the start  procedure I've inserted some test data for it [Music] already let's [Music] see with  objectoriented features of porra ql and   the power of stored procedures your database is  not at dump storage anymore and you can actually   set your backend application free from data logic  as this gentleman shows in his blog post things   like user management which is data logic can be  fully self-contained in a database with help of Po   scale extensions you can hash passwords generate  Json web tokens QR codes anonymize personal data   render HTML from templates and much much more  there are three different actively developed HTTP   clients that work entirely within database context  the more I learn about poql the more I come to a   conclusion that it has extension points everywhere  this extensibility is the feature of features   that makes po so Universal that it can in fact  replace your whole back end so now let me show   you how one database system one software product  can replace a whole technological zoo of typical   backend project shall we start with sketching it's  fairly easy to build a key value store on top of   pql I would like to have expiring values like in  redis so let me install a PG Chrome extension for that [Music] [Music] is now to the [Music] [Music] database [Music] 0:10:29.200,1193:02:47.295 [Music] another popular red feature pops up can be  replaced by built-in notify listen commands [Music] [Music] as you may expect from a relational  database notifications can be wrapped   in transactions and of course you  can create a trigger which will   send a notification whenever say a new  game character is added to the character   table since we started with messages how  about replacing your message que with posq PGM Q operates similarly to AWS managed  message Q in service sqs it ensures   exactly once delivery by temporarily  hiding recently accessed [Music] messages Engineers from prequel a company that facilitates  cross database synchronization have replaced a   popular message broker software rabbit mq  that served their back and job que with   just a POS skel table row level read WR  locks and ured that each job was picked   by a single worker the goal was to make  their infrastructure simpler and easier   to reason about we'll come back to this  idea later in this episode next candidate   for replacement in my list is mongodb to  begin with poel supports Json data type [Music] natively the query syntax may look  different from JavaScript TK [ __ ] syntax   but hey all the cool nosql databases out there  after being around for at least some time are   all adapted in some kind of an SQL like language  look at for example influx DB couch base so moving   to the real standards compliant SQL is a good  thing there is something about this technology   that makes it so resilient and future proof that  even no SQL vendors understand that they should   at least mimic the structured query language  if you decide to move your Json documents to   poql like for example Guardian did you will need  more disk space because there will be no document   Keys compression like in [ __ ] but then again  you will get out of the box integration with   everything else Sports BQ has to offer and of  course less infrastructure to maintain what about   other types of nosql data databases you can turn  your post SQL into a graph database with Apache   AG project and use both SQL and a graph language  open Cipher to model store and analyze connected   data for example you can map relationships in  social networks or track connections within   power grids things like that for time series data  there is time scale DB an extension designed to   efficiently handle timestamp data so that you can  say monitor iot device metrics or maybe analyze   user activity logs for web services time scale  DB Works nicely with grafana by the way if you   have a bunch of text Data though often times you  need to search through it not with like statement   in SQL which is pattern matching but in a way  a search engine does it with stop words where   stemming and ranking of search results that is the  search should find results with similar words and   sort them accordingly in a typical backend setup  this functionality is covered by elastic search   elastic search would continuously get the data  to index from somewhere else maybe from a poql   database and provide a standalone search service  but if you choose to integrate posg sql's full   text search capabilities you can achieve similar  functionality directly within your database given   that you wouldn't have to synchronize data  for search you will get an always upto-date   index and as with all the examples above your  infrastructure will be simpler and therefore   easier to maintain of course you should mind  the constraints but for many maybe even most   projects out there running an elastic search  cluster for full text search is an Overkill   when it comes to text of course you cannot get  around llms large language models imagine you   can query an open source llm model imagine a  chatbot running entirely on the database side   here you go you need a machine with a GPU and a  pgml extension okay so you ditch a message queue   and a whole bunch of nosql databases replace them  with SP SQL you can now search through your data   in near real time and run machine learning models  right within the database now what's left of your   backend stack the API itself post gr is the web  server that turns your database into a rest API   in a typical web framework to add a new entity you  would have to prepare database migrations Define   a data model in some kind of an orm and then  configure API endpoints in many cases the code   will just wrap around simple crud operations to  create update delete a record in a database with   pogress instead that you just need to create  a database table that's it pogress will take   care of authentication and authorization using  post gq's own Ro based access controls with ro   level granularity to go beyond simple crud as  you may have guessed it you got to use stored   procedures within stored procedures you can  of course leverage object relational nature   of fgra SC and all the extensions I've mentioned  before pops up messaging message cues key value   object graph and time series data stores  as well as full text search and machine   learning functionality and yes you can unit test  St procedures just like you would do with your web [Music] framework [Music] If instead of rest you'd rather prefer graphql  you covered as well PG graphql extension creates   a transparent mapping between your data in  poq tables and graphql entities so you can   run graphql queries using a single fun function  call if you couple PG graphql with postr you can   run graphql queries over HTTP by now you probably  have no doubt that posg is in fact a platform omn   project took this idea to extremes going as  far as managing containers storing files in   S3 and even providing payment support all within  poql regardless if you want to go all in with omn   or just make your backend infrastructure a bit  simpler to maintain and reason about pogress qu   is on your side it's not only as feature Rich as  you can imagine but also Enterprise grade reliable   think about it maybe you don't need another shiny  document database or a search cluster instead   adapting boring technology and embracing radical  Simplicity might yield long-term benefits for your   project besides that improving your ES skills  seems to be a very future proof investment and   while you're getting used to the idea that posq  is not a database I'll play Doom right in my SQL client

Channel: The Art Of The Terminal

Views: 185,440

Keywords:

Id: VEWXmdjzIpQ

Length: 20min 33sec (1233 seconds)

Published: Wed Mar 13 2024