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 choosi

ng 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 eve

n 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 in

to 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 pa

rent 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 th

ings 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] an

other 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 rep

lacement 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 d

atabase 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 ela

stic 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 wi

thin 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 functionalit

y 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 too

k 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 a

nd 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