SQLite: Protégé of PostgreSQL

Video in TIB AV-Portal: SQLite: Protégé of PostgreSQL

Formal Metadata

SQLite: Protégé of PostgreSQL
Title of Series
Number of Parts
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Release Date
Production Place
Ottawa, Canada

Content Metadata

Subject Area
The Keynote for PGCon 2014 by D. Richard Hipp SQLite has become the most widely deployed SQL database engine in the world, with over two billion installations providing SQL database services for cellphones, web-browsers, cameras, and countless other gadgets and applications. But SQLite is not in competition with PostgreSQL. Rather, PostgreSQL and SQLite complement each other, with each engine targeting a different class of problems. SQLite can be thought of as a derivative of PostgreSQL. SQLite was originally written from PostgreSQL 6.5 documentation, and the SQLite developers still use PostgreSQL as a reference platform to verify that SQLite is working correctly.
Revision control Digital photography Cuboid Database Smartphone Web browser Game theory Quicksort Website Fault-tolerant system Library (computing) Number
Scripting language Vacuum Standard deviation Software developer Projective plane Execution unit Electronic program guide Database Instance (computer science) Mortality rate Cartesian coordinate system Personal digital assistant Video game Software framework Quicksort Endliche Modelltheorie Website
Suite (music) Server (computing) Sequel Average Different (Kate Ryan album) Single-precision floating-point format Software testing Website Recursion Oracle Exception handling Server (computing) Forcing (mathematics) Expression Stress (mechanics) Database Instance (computer science) System call Residual (numerical analysis) Query language Logic Crash (computing) Order (biology) Video game Website Table (information) Oracle
Scale (map) Enterprise architecture Enterprise architecture Computer file File format Server (computing) Multiplication sign Prisoner's dilemma Computer file File format Client (computing) Database Open set Directory service Binary file Cartesian coordinate system Binary file Single-precision floating-point format Different (Kate Ryan album) Hierarchy MiniDisc Software testing MiniDisc Form (programming)
Group action Code State of matter Multiplication sign File format Water vapor Mereology Database transaction Computer programming Airfoil Formal language Subset Query language Medical imaging Mathematics Sign (mathematics) Different (Kate Ryan album) File system Physical system Programming language Workstation <Musikinstrument> File format Cross-platform Real number Software developer Fitness function Bit Instance (computer science) Formal language Entire function Process (computing) Order (biology) Quicksort Computer file Virtual machine Content (media) Thermodynamischer Zustand Term (mathematics) Integer Computing platform Computer-aided design Content (media) Mathematical analysis Database Druckstoß Cartesian coordinate system Word Film editing Integrated development environment Logic Query language Computing platform Conditional-access module Table (information)
Presentation of a group Plotter Multiplication sign 1 (number) Database Open set Food energy E-text Medical imaging Mathematics File system Cuboid Office suite Thumbnail Covering space Presentation of a group Touchscreen File format Real number Electronic mailing list Data storage device Complete metric space Measurement Flow separation Open set Entire function Process (computing) Crash (computing) Order (biology) MiniDisc Arithmetic progression Reading (process) Reduction of order Web page Backup Overhead (computing) Computer file Computer-generated imagery Data recovery Virtual machine Entire function Computer 2 (number) Crash (computing) Read-only memory Profil (magazine) Graph (mathematics) Slide rule Information Image resolution Database Cartesian coordinate system Subject indexing Software Query language File archiver
Asynchronous Transfer Mode Greatest element Presentation of a group Table (information) Computer file Multiplication sign Source code Random access Numbering scheme Content (media) Database transaction Product (business) Utility software File archiver Demo (music) File format Interior (topology) Database Basis <Mathematik> Database transaction Cartesian coordinate system Similarity (geometry) File archiver Key (cryptography) Table (information)
Group action Concurrency (computer science) User interface Length Orientation (vector space) Multiplication sign Decision theory System administrator File format Database Insertion loss Client (computing) Mereology Software bug Wiki Mathematics Different (Kate Ryan album) Computer configuration Single-precision floating-point format Repository (publishing) Data conversion Error message Data compression Physical system Proof theory Collaborationism Enterprise architecture Regulator gene File format Block (periodic table) Computer file Binary code Interior (topology) Data storage device Stress (mechanics) Database transaction Type theory Repository (publishing) Crash (computing) Order (biology) Right angle Quicksort Data type Data compression Point (geometry) Server (computing) Enterprise architecture Table (information) Computer file Mass Content (media) Code Computer Field (computer science) Power (physics) Revision control Crash (computing) String (computer science) Representation (politics) Integer MiniDisc Traffic reporting Form (programming) User interface Installation art Scale (map) Multiplication Scaling (geometry) Key (cryptography) Server (computing) Client (computing) Database Directory service Line (geometry) Binary file Cartesian coordinate system Configuration management Limit (category theory) System call Single-precision floating-point format Word Wiki Error message Query language Personal digital assistant Network topology File archiver Table (information)
Point (geometry) Table (information) Algorithm State of matter Multiplication sign Replication (computing) Disk read-and-write head Declarative programming Computer programming Subset Formal language Twitter Bit rate Different (Kate Ryan album) Flowchart Codec Representation (politics) Endliche Modelltheorie Domain name Programming language Theory of relativity Arm Key (cryptography) Data storage device Computer Database Cartesian coordinate system Computer programming Formal language Type theory Data model Process (computing) Query language Statement (computer science) Video game Codec Quicksort Representation (politics) Table (information) Row (database)
Algorithm Computer file Algorithm Code Electronic mailing list Code Computer programming Programmer (hardware) Goodness of fit Self-organization Right angle Data structure Right angle Data structure
Pairwise comparison Scaling (geometry) Algorithm Multiplication sign Consistency Computer program Data storage device Database Database Term (mathematics) Cartesian coordinate system Computer programming Formal language Term (mathematics) Query language Codec Software testing Key (cryptography) Object (grammar) Representation (politics) Metropolitan area network
Axiom of choice Consistency Multiplication sign ACID Database Water vapor Data storage device Shift operator Semantics (computer science) Power (physics) Finite set Googol Query language Traffic reporting Mathematical optimization Form (programming) Physical system Shift operator Scaling (geometry) Server (computing) Consistency Projective plane Data storage device Analytic set Database Database transaction Semantics (computer science) Googol Query language Figurate number Cycle (graph theory) Quicksort Mathematical optimization Physical system Oracle
Axiom of choice Point (geometry) Metropolitan area network Greatest element Arm Touchscreen Key (cryptography) Information Multiplication sign Source code Virtual machine Menu (computing) Web browser Line (geometry) Limit (category theory) Special unitary group Uniform resource locator Mathematics Query language Different (Kate Ryan album) Uniform resource name Quicksort
Point (geometry) Computer file Code Multiplication sign Virtual machine Limit (category theory) Order (biology) Mathematics Different (Kate Ryan album) Program slicing Summierbarkeit Physical system Data type Metropolitan area network Database Price index Line (geometry) Density of states Configuration management Orbit CAN bus Graphical user interface Word Event horizon Multi-agent system Estimation Repository (publishing) Query language Synchronization Website
Point (geometry) Axiom of choice Context awareness Enterprise architecture Code Multiplication sign Water vapor Open set Disk read-and-write head Mereology Special unitary group Number Mathematics Type theory Software repository Selectivity (electronic) Traffic reporting Computing platform Programming language Database Cartesian coordinate system Computer programming Formal language Equivalence relation Type theory Word Explosion Event horizon Query language Repository (publishing) Computing platform Figurate number Writing
Type theory Constraint (mathematics) Validity (statistics) Multiplication sign Line (geometry) Information security Data type Asynchronous Transfer Mode Wide area network
but the it's the on it's
embedded database engine it's found in a lot of like your web browser on Microsoft Excel quick look to the sky on library it also found a
lot of gadgets it's it's running your smartphone it's running on you're probably running you're you're you're set up box for your cable television on you quarter game consoles in this sort of thing it's a small embedded database and it looks like a post grace it's open source and so I don't really know how many versions are out there running but if you count up the number of smartphones and the number of copies of Firefox and so forth and so on we think that maybe there's
about 2 billion instances of SQL like running in particular moment and about 500 thousand different applications that code to use SQL like this huge user base I did not really set out to to do that it can work at and see many
as right often what does this have to do with on POS President opposed this Congressman modernist July conference well it turns out that as to like a sort of a spin off a PostScript I call it maybe a conceptual framework has joined us in any pose crisco unit but the way I came about was I was working on a project and we were using PostgreSQL are as a development database and on where we actually needed an embedded database and and I I use the PostgreSQL documentation as my guide for writing is to life and you can tell that because as far as I know there's a lot to database engines in the world that have vacuum command and then they are post-processed you life but it was you know but PostgreSQL we all as what really depends on POS crisper more than just that initial development we continued to really look up to PostgreSQL as our war on all we know is this this thing about SQL and they ask if you've had read the SQL standards and try to understand what they mean on you might as well be reading agency integrate script it's just incomprehensible into apparently here a PostgreSQL you have some really really bright people who can understand that stuff and understand and interpret it from your mortals and but nobody Mr. liking can do that too we we really looked to as to post PostgreSQL as our model for what to do correctly yeah well and good but so you know every and with best you want something will come of so what's the well had some obscure query and of say well as giving the wrong answer here and it'll be sort of an ambiguous cases by scratching their heads and with it will what what is the right thing to do actually and our standard thing to ask is What
would post residues are so we go out and they oppose stress is the same answers as to life the issue is settled just have to assume that but that's the right thing to do but you know we also look to pose this we're adding new features just this past January we can ask your light on we added to our common table expressions and so the 1st thing we did when we were tasked to do this by 1 of our customers was to go to post-presidency but they didn't know when beyond that we implement something suppose this doesn't do I mean we will we will allow an order by calls on the of a recursive table expression so that you can on the selected either on a depth 1st forces breadth-first search and the average we started using exactly that the posters avidness in the documentation we found wanting is exactly the same instance will be passed that we end up using but still this is this is post this is what we're going to
1st another reason we look to post press is our past experience about on others as 5 or 6 years ago we were doing a new test suite rescue I called logic testing you can read about it at that website at the top and the idea was that we have about 7 million different queries that we run through 5 different database engines as you like my skill oracle sequel server and post and in the course of doing as we learned a lot of things on we learned that the my syntax is not really compatible with anybody else and with that we also it's important that we managed to Segfault every single database engine except for PostgreSQL but there's always work always gives the correct answer now just yesterday someone was telling me well that's just because you were not running 9 . 3 by it it I don't allow a I don't know but I bet that we always end end end in really outside the posters community puts this really does have this reputation for being rock-solid and always giving you the right answer it's not just us it's everywhere so that's why we use POS crystals so
but there are some differences between police prison you light on were at the time of listening post this is really designed as your enterprise data deeper and you're all close as people so I'm assuming you understand what I mean by that the test you only really wants to be an application files on there and you might not really have a clear Constable to understand all the time talking that would lead to an
application file format you know whenever you got a new and we're cool lap are mainly new file open the file save whatever this stuff gets is written onto the disks in some form or another and this might be XML JASigning animal comma-separated values some kind of binary format typically those the common thing to do for for 1 applications is some it's not a single file that you create a directory hierarchy full of files and put all your stuff there and that's the file for that for the application and what we like to argue is that SQL like makes a much better file format so that when you do FileSaver followed instead of writing a bunch of files of writing pile files its opening in using this July database and there's a lot of
advantages to this out for 1 use to like you don't have any on passing code passing writing logic to debug user-friendly you you might write something it seems to work really good but then when you're you're your application filed exceeds 2 Jacobites and and and then also pointed longer fits in at sign 32 bit integer might suddenly fail we morning debugged all that's accordionist rely on you get a single-file document you preserve the document metaphor and your file you can easily attach it to an e-mail and send it to someone you get a high-level query language automatically this greatly simplifies the development of the content is accessible if you if you application file format and it's a pile of files and some of them are in strange formats on the data lives longer than code long after your application has ceased to exist somebody might still need to access that data and in a weird pilothouse files format there have trouble with that if it's in the SQL format the more likely will access that term as joins cross-platform or cross-language by that I mean you can take an airstrike database file move it off with a beginning in 32 bit machine move it over to little Indian 64 bit machine it works fine in place it there's no byte order dependencies in word size dependencies is complete across platforms it's it's a universal file and this is really cool when you're writing application restored images someplace you don't have to worry about by water and that sort of thing also language because a lot of of this this comes along a lot like research environment we have different teams they're doing different parts of some process in this team over here was write in Python discuss this group of years and see sharpens groups in C + + in that group there's and for trained for work and the but if if I asked you like your application file format that can all interoperate easily get atomic transactions incremental and continuous updating you don't have to write rewrite the entire file system change if you bytes it's easily extensible with you if you know people always of they develop an application file format and they don't play in well free extending it because it never you your 1st cut is never sufficient to make you got SQL database there you just add a new table or and columns to the existing tables and you can extend the foreign varies like its multiprocessor multi-thread state your afterward at 2 instances the application messing with the neighbors followed the same time and you can get improved performance so here's an
example of a major application users as to what is its file format of MicroStation is a CAD CAM system that's probably used to design say water works in the city that you live in this sort of thing a big multinational company and all of them into the got it this CAD system and whenever you design something with MicroStation and you to file save it right have an instrument database and then they have all these accessory programs that do things like water hammer in seismic analysis and all these other and analysis things for your design and they're written by different teams possibly in different programming languages because it's in a database of it's really easy for them to interoperate and also as you might has some the it's gonna archery query engine which they used to to quickly figure out some subset of the database to use for viewing like that
on another example of using this joint is an application file format is Adobe's on Photoshop Lightroom all which stores all this information as to why the Adobe team more of an early adopter destructive purpose and they were the 1st ones to discover that it's actually faster to store blobs in the database than it is to store them as separate files and file system up to us up to a particular size they they had the question of what should we store all thumbnails in the database we just put the file name in the database story thumbnail several it turns out that we did some measurements and turns out that for thumbnails of less than about 100 k it's faster to read and write them from the database and is to read and write them from the from the from the disk but we don't know why probably because the open close overhead but they were the 1st to discover this surprise me because when I was designed like I never intended it to store really be blobs like this and in related work that well and the blobs it had broken up into individual pages to fit into the database file and I didn't do anything to make an efficient there is just a linked list of pages instead of to access a and 1 kilobyte of thumbnail it's got to you know read this cover follow linked list of 100 1 K pages to to get all the data that's actually faster than opening the file and reading of of this so some other things should not
be used as fuel and I wanted to place and what it hears time that you know how things can and better so consider the open office documents of this presentation is done in the office and so this is the OpenOffice documents and open document is article presentation for example is really just a zip archive with an XML files and images and not to be feared open document on overnight existed long before it's due like to this is not you know deficiency that didn't think do this this is just an example in a way out anybody here use use actually some of a proprietary presentation software that what the I yes so these but I know of this great I love and use it for everything but it has some annoying features on 1 is that we open it up and get the slow progress plot that as been prosthesis reading porous entire final in order for displaying if it were data just query for the 1st screening and bring breed up right away on saving with open office has this annoying thing that you be hearing information in summaries sizable make a backup of itself and it freezes up the screen and the little progress bar goes across and they can take 10 seconds or more the presentation on Muslim machine and is what's up all doing this back up in the recent as back up because crashes a lot and room was only work but so he doesn't have to write the entire file in order to make a backup which appeared a database you only have to write those particular changes that you were doing on if you ever shut the hell OpenOffice LibreOffice new office by turning the computer off in the next and bring it up it was to get the recovery process to bring the dialog box and have to click on some things very only if you use SQI database the recoveries that but never need to file save there's you just it can save energy time and you can have undue that moves across sessions to you're working in critique undue unlimited that the why you've got it open the thing you shut down with the presentation of the machine with the database you could read of triggers so that you could undo back into your previous sessions and if could do a lot of things like a large searchable database of index files I am I like to read books online look in the performance always using the reader not but they're giving people what is another 1 of these profiles format the zip archive of XML files and I'm see if that was SQL you could just then you have all these you give open faster if you could have full text search 1 of the I Love and look but 1 annoying thing about is the search is useless it's essentially graph it takes forever but we can have a full text search with complete Google syntax on if if they had done it as the data now
in obviously what it hears I just as an example I wrote this little utility on to like archiver which is the idea of taking a pile of files and move into a database in this entire scheme is shown here we get the name of the file its access permissions last modification time size and then the data is a big and if you did very this according to which 1 and at the very URL here at the bottom that where you can go and download the source code if would look at just a demo it's not a real product but you could use something like this as a replacement for your zip-archives if you're doing a pile of files for me and and you gain transactional support random access and what you've done that then you could start adding other tables and gradually transitioned into using an SQL basis for your application files and I I did this season will probably a data and a lot bigger than a zip archive not
really this presentation is speeding con 2014 that piece in the OpenDocument Format unzipped it and then recompressed it with this as your archival tool and you can see it grew in size by half of a per cent so a database file can be is storage efficient as a
zip archive so what was present 1 1 last example bear with me a lot users get right or you know he's noticed that gets low configuration management tool and it uses a pile of file format about that get directory is a bunch of files in there and that's the its application form it was that word database instead an SQL database of some sort we have advanced queries that you could use for much richer richer user interface and that's examples of that later on it would proven its crashes right out committing something in and you lose power you get weird things in order file you can easily add tickets and our trouble tickets and both reports and wiki to you archive and simple if you had a database that to drive it here the concurrent access on multiple strips can be using the same repository at the same time of coding errors will be less likely to corrupt the repository because of what you could do is when you're making changes the repository you could you could do it all started transaction and just before you commit you go back through and make sure that everything is still accessible everything is still right of orientation you commit if anything is wrong then back in and back on the fly repository compression all right we only have to get packages of those to get past automatically every few commits or something but you could do that on the fly in the the but really you end up with a single file repository a document and you could just you can attach should give repository to e-mail and send it to a group of collaborators alas it in
new that so anyway there's a kind of this kind of the difference here between PostgreSQL insulitis has year 20 Enterprise Data depot where this joint is geared more toward the file format and all of these other real differences between the 2 systems flow out of that automatically so if we put stress is that the client-server because you need a server to manage your enterprise question so no you don't have to you are afraid you relevant and and don't get ahead of me you don't get ahead and now so that you are quite request you want us to be serverless it it is it it it's lying directly to the decision server to get involved because this is part of a small application of this crisis striving to scale up you want store ever more data or even more places in it where the disjoint was the scale there because it has to fit whatever smaller devices on and with PostgreSQL but the database files are in some secret directory your database administrator I where there somewhere my computer I'm not really sure where all with this you like the single this file is well make call everyone because that's what we're trying to preserve the document for adopted you you have the option to dump restored so if you want to change your file format you can do that and then when people of upgrade to the next version may be found a better way to do things that you could you could say well this we had to do the great oppose pristine invented odometer which is fine With this joint we've got billions and billions of database files in the wild and we have to be able to for that 1 that was there are some things that I would have done differently looking back at the bottom line but I can't fix it now because I get this massive legacy base that I have to continue to support and finally with pose great you will you have to have an installer because you get all sorts of binaries going all over the place where system was the single to link and your application so those are the differences and the key point of this is that as you is not a competitor with as joins competing with F opened OK it's a complementary to post proceeds to work in tandem with each other all the are other differences and this is worth going over all and post-Christian can create a table with an integer column and then you can insert into that column history that looks like an integer in it will automatically do the conversion from strange injured storage and ask you why does exactly the same thing in a can have a column that is text and you can do it in certain that that column that is an integer and PostgreSQL will convert that integer into its text representation and store the text and ask you what will do exactly the same thing but any of the bottom I've got a column which is of type integer and I'm storing a string in there that does not look like an integer and PostgreSQL will give you an error message if you try to do that SQI is happy to store the string see the thing you like is that the column types or suggestions not a requirement so our it tries to put things in the you specified but it doesn't sister point there's no data loss really trees is still there nothing is lost in conversion but it just doesn't system converting things so insecure your wife because the data type is just a suggestion it can be completely and then you can create tables with columns that have no type specified whatsoever and then it will just stay or whatever and then it had happened but there are some not pose Chris has this data type called any but it doesn't work on calls and I can I can speculate on why that is and and the probably technical reasons that the limit is put forward to you that that's actually a very useful thing to have in some cases the ability to store any data type in a particular call I've seen some of my clients are where they do schemas and they've got all they've got some table where the need to store 3 of what about the data and they'll have a separate column for each data type and then other cultures which did not as rosy as and so of the 3 of the 4 data in 1 we fill in the other 3 Knowles and just use 1 of the 4 3 you can do that it would be so much more convenient to be able to just have a column where you can store or whatever you want just say From this Christmas to the text type in her right for that congratulations I love that or not not every SQL database engine does that it was something that really bugs me is that on people were still using horror and putting a fixed from a text fields and I don't really this is you know we're planning of of a fixed length text the text field makes sense in 1975 but it word this is 2014 while we still doing that well as every not realize that there no other truly buffeted good pose that allows them to allows a text field to be primary key you can't do that in every other enterprise database engine on regulations but you know 1 thing that I do find lacking press is that you don't have a block type you have binary yes you could substitute body there but then you also don't you can't do the blob literals with the x followed by the string that contains facts which I might be mistaken because you know the standard account of written in Sanskrit but I think that the standard isn't this is not by it's eventually right so this is something I claim that this is a a rare deficiency impose grants and I just wanted to bring to your attention will of the people of how did blob literals and discuss what will you somewhere postprocessor Pacific stuff here OK I
accept differences between post-christmas job but the united by this SQL language and that's really the point of all this this is what this is what brings us all together SQL is the secret sauce that we really should pay attention to when when I hear people played of talking a database engines a lot of the talk is about to arm of the storage engine replication of and that sort of thing but always very important although memorize all the important but really it's it's the SQL is the spice that really makes this happen this is what really sets posters apart from a lot of other offerings out there is the SQL and then you need to think of SQL models the query language but a programming language each statement is a separate program when you prepare a query that's like compiling and and this is why is it so as chosen all of language and that it's declarative you're telling it what to do not have to do it and it takes new users a while to really get into that you can recognize a new at some of the new SQL is that they just do queries that return a single row based on the primary key and they don't want to this and then they process inside the application rather than doing a single query that does all the processing they really haven't done this whole declarative query type things into their heads the other really cool thing about SQL is the relational data model really works well for representing a lot of things in the application domain much much much better than a lot of other things that are in common use and representation is the the essence of computer programs that quote comes from Fred Brooks
Mythical Man-Month a very familiar with the mythical man-month who is not readily command you should go by that I read that tomorrow but in life rates as I've shown here flow charts in conceal your tables and I should continue to be mystified show me your tables and I usually won't need a flow chart milled be obvious but the trend within that it's the data that really matters but now this is all you can tell by the use of the work flow charts in this quote that this was written a long time ago when he was this flow chart were state of the art in programming technology OK so we come along way since the end but this this this timeless truth remains essentially the same here
is about Rob Pike saying essentially the same thing more recently this data dominated you've chosen right data structures and organize things well the algorithms will almost always be self evident data structures not algorithms are central to programming this is essentially saying the same thing is that if the issuer schema design that matters and
then here's all live letters for all saying you only get mailing list of all places that programmers worry about the code of good programmers worry about data structures and their relationships which is very true very ironic then that get should use a pile of files data structure but on in like
the players are you know these key-value eventually consistent data stores I really they they they they're not going to stand the test of time I will do not believe that I mean they you you know and and I get the idea of value individually consistent is that sometimes you just gotta have that for scale if the performance and there are few problems that really did man there but it's it's it doesn't have that time was about it that something like SQL does SQL is built to last is only around for a long time but if
I can call no SQL databases because I hate that term I prefer the term postmodern database because I live in rural post modernism in the absence of objective truth when you clearly I knew who had been when clearly none of these postmodern databases you don't get back a fact you get back an opinion In opinion application sometimes that might be OK but just that you don't wanna do this for absolutely everything you do you will a lot of times you really need to know facts on I like draw comparisons of
between 2 the art world I mean you know you have the form you to priceless works of art of the intervention Picasso here but 1 of them is closer to reality it is it realistic were the other 1 is abstract and it's not real accordingly or fashion but only for the iconic for reality when I'm doing work for if
a couple years ago I ran across of this talk by on counting toward his from Google you talking about this sort new SQL of thing that's internal to do local span of I don't know a lot about spam in fact I'm not sure anybody else go google knows much about span the published report a kind of sketchy but apparently spanner is a global scale transactional acid SQL database engine and he in his talk he said there's been a and there's been a big cultural shift in google the SQL-based analytics system Dremel made a lot of SQL combatant Google people realize that all the incredible power of being able to push the semantics of the query over to the storage engine and just let the storage and you figure out what to do and he also notes that no skill databases that only have weak consistency or enforcing a broadly applied preacher optimization on the entire system so that I want to focus on this this idea of pushing the semantics of the query into the storage engine and letting the storage engine figure out what to do that's kind of water because everyone of us has a finite number of brain cycles that were allowed to use it you can spend this brain cycles within messy immoral behavior and your queries warn you could spin them actually providing value to your customers you can have that choice and projections some time thinking about having your reasonable less time you spend thinking about queries the more time you we will
spend thinking about adding value here is an example are I use every browser that's made the but of Firefox's is probably my favorite it has a single also MBA we start typing things in up there on the URL bar and they give you a lot of choices of things that might not expect an arm of every time you press a key in the also ball and Firefox that query
run it pulls a lot of information from a lot of different sources you can see there's 6 different sort of sub-queries as a union always ordered by there's a limit that went off the bottom of the screen at the end you don't really have the machine agree that's not the point my point is this query is replacing probably several thousand lines of C + + much easier right much faster right much easier to debug much easier to tweak it you 1 improved performance art when a change in behavior slightly here is on
another example they notice here I'm using Chrome on I took the giver repository for PostgreSQL I did this Saturday so this is not up to date on it at the since last check something Savary originated check is something to the get repositories etc. OK there you go so so this is out of date but I have so I loaded into a different orbit configuration management system and I'm not trying to at and and I did that because there's other configuration management system all is back like SQL rather than as a pile of thoughts and not trying to I'm not trying to push you change that's that's not the point is the point is on 1 show you what you can do if you have an SQL backing rather than a pile files and I just asked it to show me all the check-ins propose press around the same time that I made the 1st check into SQL like which was on May 28 the 2 thousand and as you can see it appears that of Bruce was making some changes to document at time and with the question do this and you could do this and yet but you have to do with it would have to scan through Falcon's of thousands of chickens going back to trace the history defined point on words because this is an SQL database we have indices this was computed in 5 ms on a machine that is a 124 slices about 2 machine a 124 slice of a real machine and also hosting several all the all of major websites at the same time it took 5 ms and then what you have data n 0 here's the query
on the it's it's it is a joke lined with 2 sub-queries it's not all that complicated I did that appear for you not expect know what this means the point is that this is replacing hundreds of thousands of lines of code that we can get what all here's another
query this the same database where we're looking at the number of chickens by user and so end the weekend Thomas Bruce is a close 2nd and Peter might not know is a distant 3rd I was told some explained to me that are follow this is it realistic because this is just recording the idea of the person who did the commit and apparently a lot of a lot of you just patches in and brief will push them for them but it this is only in this hundreds and hundreds of reports like this that you might find useful could you do this and get motion again and I think get hold of something like this all but have but how would you do that in you and then when I ask that question probably a lot of heads and you're finding well I could start pulling data here and there no you're not getting it that's not the point you should have to think about how to get the data the when you do this the way you generate this report a select cannot stall common name from checking and thereby the by 2 water by 1 increasing in the let the database engine figure out what to do every now and then you have to do is enhanced if it affords proved critical query but you 97 per cent of the time which is going the right thing you have to worry about and more all that data by the way
is available online if you'd like to look at it from 5 it's is just a snapshot of the gear repositories not 8 so I will take it down after a few weeks so if you're watching this on you to a month later it might be the anymore but it's there out some final concluding thoughts on are you really need think about post breast for its SQL that's the really the secret sauce it's a programming language not a data-storage engine and that's the key thing is what makes pose risks of great is the SQL post this is also the best available reference platform for SQL out there right and let me encourage in the strongest possible words not to let that change we really like postprocessor referenced by people and then finally like you write your post applications and you're typing along writings and code and you start type F open or whatever the equivalent is your programming language of choice something quite SQ IB and their use with SQL like work better in this context and you can carry the SQL forward into that part of the application as well I I thank you very much for inviting me to this
talk that its conclusion line here and we have these 2 questions if there are any yes sir I will repeat the whole white which is when when we consider adding constraints in the daytime validation other common question can if you really want to force a particular commonest you like to be on of secure data type you can put a check constraint there and say you know check type of whatever on their own people requested maybe a different mode you could put in were really does enforce the types of but I become reluctant to put that in and I I I I didn't mention earlier people tall people compared to the same is that postbases strongly typed in joint weakly typed hate that terminology as long it's PostgreSQL is rigidly typed in its true light is flexibly time but you could say a PostgreSQL is Om Om our of legalistically type and as you like is tight with forgiving loving kindness and so on I mean it's not there yet but I think is here by the way any other comments or questions hisses thank you very much for paying attention


  653 ms - page object


AV-Portal 3.20.1 (bea96f1033d39fbe77f82542458e108105398441)