Gimme some YeSQL ! - and a GIS -
CC Attribution 3.0 Germany:
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.
Picavet, Vincent
So long NoSQL, here is YeSQL !Not long ago, PostgreSQL was the the most advanced OpenSource relational database. With the latest version 9.4, it became an over-powerful mutant : the most advanced OpenSource Object-Oriented relational and/or non-relational, Spatial, SQL and/or NoSQL database. For the sake of simplicity, let us call it a YeSQL database.This presentation will introduce you to the feature galore of PostgreSQL, giving insights into the latest improvements from a user point of view. Of course some GIS inclination will drive this talk, and show you how you can take advantage of spatial extensions together with PostgreSQL core features.PostgreSQL 9.4 is an important milestone for various reason : a lot of new outstanding features, and core improvements which prefigure a whole world of new use cases. The main feature from a user perspective, giving PostgreSQL this YeSQL title, is probably the new JSONB storage. A fine marriage between the Hstore extension and JSON support, it literally transforms PostgreSQL into a document database (think MongoDB in PostgreSQL without data losses).We will therefore present great PostgreSQL 9.4 features, and some ways to use them with spatial data, leveraging the latest PostGIS and PointCloud extensions :* Exclusion constraints* KNN search* Lateral joins* Window functions* (writeable) (recursive) CTE* Automatic updateable views* Materialized views* JSON, more JSON, JSON indexing, JSON proceduresÉ* Foreign Data Wrappers* Logical decoding and future applications* More JSON ? GeoJSON ?The feature set available to PostgreSQL users is growing with every release, as are performances. And the spatial part of it is not lagging behind. It is a must-use platform for data management, data infrastructures. And a GIS.
 Hi everybody, I hope you had a good start for this video. And so I'm Vincent Picavit and I'm going to present you today some things about PostgreSQL in general. So PostgreSQL, what is this thing? Do you know what PostgreSQL is exactly? Who knows that? Yeah, some of you. Actually, PostgreSQL is a thing you have burned out with Pogs. Yes. So apparently you are all users of PostgreSQL. But to define what it is just quickly, it's RODBMS, which is a relational object database management system, which is kind of a mutating of the database software world. PostgreSQL is SQL compliant. It's transactional. It's ACID. So that's a basic constraint on databases. It's got MVCC, multi-version control system. It's extensible. And it's got special features because you've got PostGIS. Of course, you know that. So today I'm going to talk about PostgreSQL and new stuff. And some not that new stuff, but interesting stuff, which are specific to PostgreSQL and not specific to PostGIS. You all know a lot about PostGIS, I think. But there are a lot of things in PostgreSQL which may be useful for you, which are not exactly inside PostGIS. So PostgreSQL is currently at version 9.x. So that's the main version. Stable version is 9.3. And 9.4 is just around the corner. So we are currently in beta 2. And it won't be long before it is probably in October or something like that. So what's new in PostgreSQL 9.4? I'm going to show you some of the new stuff. But to sum up, there is a lot of good foundation work, like very basic work, very boring work that you want to use exactly like that until maybe a few years. But a lot of things needed to make new advances in the database world with PostgreSQL. So 9.4 is important in this regard. And there are lots of new features, various new features. And some of them are useful for GIS. So I'll show you something. So we are talking about SQL. So who of you don't know SQL at all? Oh, not even one? That's good. Perfect. So let's go for the feature get-all. So first one is lateral join. So lateral join is a new kind of specification for join. And it allows you to access to other tables in subqueries. Which is kind of not that useful apparently. But actually, for us, GIS, it fixes the distance operator constant issue, which means actually if you know this one, which is a key NN search, or nearest neighbor search, you have to use normally a constant to compare to the geometry you are looking around. And you cannot refer here to something which is outside of the query. But with this lateral keyword, you can do that now with POSGRACYQL 9.4. So you can have this kind of query, which is find me the two bars closest to each bus stop. Just in case one is closed, I want two. And so I got buses. I got points for buses. And in the subquery, I'm going to find the two closest bars in regard to the bus's geometry. So that's something which is interesting for GIS, for GIS, for XP. Because otherwise, you would have to do a lot of subqueries, and that's a pain to write. So that's pretty good. Another one is auto update views. So we got views. You all know what a view is. The thing is, with 9.3, we got an later. We got other updateable views. So when you create a view, you can actually update the view, insert things into the view, or delete the view as soon as it's a simple view. I mean, when it's a very complex view with joining a lot of tables, it's not possible to know, to determine which updates you really want to know. But if it's a simple view, you can do that without having any trigger, because otherwise, you would have to do that with triggers. So that's pretty interesting. And 9.4 adds a column level management, which means you can have a view with some columns that are updatable and some that are not. Think, like if I compute a buffer in a view, for example, I cannot update the buffer column because it's not in the parent table. But with 9.4, you could update the updatable column and not the buffer table. So that's pretty cool. And it allows us well to do fine tuning special right management, because you could create a view on a specific area. And then you could say, I grant some rights to this view for some users, but not to the parent table, so that you can restrict the rights to some areas. And the check options allows you to prevent the user to update the view so that new elements go out of the view. So this is an example of a view with such a constraint. So I say, I want to have always contained inside this polygon. And I want check options so that if I move the geometries, I cannot move them outside of this polygon. So that's what I can do now is updating the view and simplifying the geometry will be no problem. But if I try to translate the geometry a lot, it won't fit into the polygon defined on the view. And then post-graph SQL will say, no, you cannot do that and fail. So that's a cool thing to do as well with 9.4. And check options is something which is pretty useful as well. Talking about views, we've got materialized views as well now. So materialized views came from 9.3. And it actually computes a view instead of just creating a reference to a query. And it stars the results. So you can think about it as a cache view, for example. It allows indexing on the materialized view. So you can have some specific index on the materialized view, which you don't have on the parent table. So that's interesting. And by default, indexes and constraints from the parent tables are not copied. So if you want to have the same optimization you have in your parent table, you have to copy the indexes and constraints on the materialized view. 9.4 improves that to refresh concurrently, which means you can refresh your view while you're still accessing it. So you don't have a big lock on the view. So that's pretty cool. The use cases are materialized views of complex joins, of complex spatial operations, of frame data wrapper, everything which takes time to compute. You can use a materialized view to cache this data. So that's cool. That's an example of a materialized view. So I got, let's say, that's a pretty computer intensive view here, computing the buffer of some, a lot of points. I can use a materialized view and it's create materialized view simply. And then to refresh, I can say refresh my materialized view point buffer. And you can see that it's using a sequential scan on the materialized view. That's a cool thing, too. Ranges, sorry? Yeah, question? Is there a way to increment materialized views with the new system? Not as it is. I'll talk about incremental things a bit later. So ranges are new data types which works with integral, numeric dates. And it allows you to do ranges of elements. So you can have open ranges, closed ranges, infinite ranges. You can have indexes on ranges. And it's good for time data specifically because you all have some valid before and valid after columns in your databases. So you can use ranges. You will have only one column. And that's pretty convenient. Write table city. Who knows what the city is? Yeah, some of them. Some of you. So city is a common table expression. It's actually the with keyword. So that's another syntactic way of writing query, which is pretty convenient because you can do things like with my kind of temporary table, which is a query. I want them to do some operations. So you can write your queries as steps, actually. And in PostgreSQL latest version, you can do some modifying queries inside your common table expression, just like that. I got new table, bad data, which I create. And I want to delete things from my good GIS data, where it's not valid. And I call this the result of this delete, the temporary table deleted. And I want to insert all the results into my bad data table. So that's all one query. It's taking the data out, and at the same time, taking the data in another table. So it's a pretty good syntax for writing complex SQL queries. And with city, you can also do recursive city, which is something you can do network with it, so elephants. So recursive cities are in this SQL standard, so that's with recursive keyword. And that's the city syntax, which is pretty cool. You've got to be careful with cities to performances, because sometimes it prevents the optimizer to do some optimization. And it can be slower than a normal query with sub-query. Well, recursive cities allows us to do graph analysis. Because it's recursive, and graph algorithm, by nature, are recursive, you can implement them using SQL. That's an example of it. I don't want you to read everything, but to know how it looks, you've got three steps mainly. You've got the initialization phase, where you select some elements. Then you've got the recursive phase, where you will say, OK, I will reuse the result of the previous step to get new elements. So for example, on a network, I will take one road, and I will say, give me all the roads connected to my first road, and then you go recursively from one road to another to do some graph analysis. And at the end, you can use the results to display something. One new case, and it's exactly the SQL query which is here, is I got a river network, and I compute all upstream segments from one specific segment. And it's very fast. If you've got indexes, it's going to use indexes all along the way, and it's pretty cool, and it's pretty fast. It's actually, for some queries, faster than using pdrouting. It's a bit more complicated to write, but once you've written one recursive query, it's more or less always the same. Another feature, Unlock Table. So Unlock Table are tables with no security. So that's no wall, no maintenance, and it's really faster to write to. But it will not survive a crash. You have to say that again? So it's useful for temporary results, and for reproducible results. So if you do some pretty easy computation step with PodGIS, for example, you can use Unlock Table. It will be quite faster, but do not forget that if you server crash, data left. So that's pretty easy to use. It creates Unlock Table, and you just say that's a new table. With 9.5, which is next version, so next year, you will be able to say, alter table, set logged, or set unlocked. So you can switch from log to unlock, which is pretty convenient. When your temporary data switch to non-temporary data, you can set log, or you lose your data. So that's about, yeah, quick. To do that now, you need a basic creation table. Sorry? To do that now, you need a copy of it. Yeah. Logical decoding. So that was about the question about data deltas and all. So logical decoding is probably the biggest 9.4 feature. But you won't use it. Why is it? Why is that? Because the first step of many steps, it's kind of a database modification logger. Actually, it's some logger, some journalizing on the logical level, so on the query level. And that's the first step to logical replication, which is another step to multi-master replication, actually. So when you've got multiple server updated at the same time, and you want to synchronize them, so that's multi-master replication. And you want to do a replication at the logical level for that. So that's the first step. Multi-master replication won't be in PostgreSQL until next year, probably. But you can always already use logical decoding for a few things like logging. We're going to have versioning soon. We're going to have incremental updates as well at some points. And it looks like something like that. So you initialize the logical decoding so that PG create logical replication. You create a slot, so that's a place I want to listen to what happens in my database. And then I'm going to do some operations on my database. For example, delete from points where GID equals 1. And then I can query the logical decoding tool so that again gets the modification done. And this test decoding driver will send me some specific data saying, the point table has been modified and the GID1 has been deleted. So you can access to all the modifications in your database with SQL. That's pretty cool. If I update the point, for example, I change the geometry, I can access the logical decoder logging, and I can get everything which is modified. So I could recreate the queries which have been used to modify the data. I could re-apply them to another database. Yeah? AUDIENCE member 1, how does this differ from just doing it before trigger? What, before trigger? There's no trigger. So it's faster as well. It's very low level in PostgreSQL. And it's rock solid. And yeah, it's much faster. And it's easier to set up. And it's extendable as well. So this output is a test decoding driver. There are other drivers. You can get the data in JSON, for example. So you can get the modification written in JSON and use it for whatever you want to use it. We apply that to a MongoDB database or something. Yes, QA. So Yes, QA is not an existing word, but it's from a geometry fountain who spotted that. So it's just a reaction to the no SQL thing. So you know no SQL more or less. Some people say no SQL is the future. Some others say it's back then. So but which no SQL? What no SQL? There are a lot of different no SQL elements. And PostgreSQL supports now document oriented no SQL. So that's unstructured data, k-par values. And you can think of it as MongoDB on ACID. So you're getting high there. And I will present to you quickly the JSON and JSON B part. There are the XML part as well, which is pretty old. And the HSTOR will be presented in the next talk, I think. So quickly, because I really run out of time, JSON is a new document storage in 9.2 and later. You can store key value things. You've got indexing, but no gins and gist. You have to create expression indexes. It's a text storage. It's very fast for IEO. It's got a size of a rate compared to just plain text. But it's pretty efficient. It's not as efficient as HSTOR, but still. And there is no gins and gist index, or no specialized index for this type of data. And you've got a lot of operators and functions. So you can access elements. You can access element as text. As JSON object, you can get passes out of some JSON object. You can convert some rows to JSON. So you directly have some JSON data out of your database here with a geometry and coded as WKB. And you've got a ton of different features with JSON. I'm sorry. I'm quick, because I really run out of time. There are a lot of things to present. So JSONB is a big new thing. So second best feature in PostgreSQL 9.4 past logical decoding, that's for JSON binary. So JSON type, JSON native type in PostgreSQL, is written as text. This JSONB is a binary storage. So you've got like a tree inside the database with different JSON parts. So it's no K duplicates. There is no other preservation. It's got slower IEO. It's quite bigger than JSON in storage. But actually, it's better because you have HStor like operators. You have all the JSON operators as well. And you have a few more operators, which are the one here. And more importantly, you've got GIN indexing. So you can index your JSON. And it's really fast. So use it except for some specific use cases if you need some very good IEO. Or if you need some flat string and you can use HStor, you can use HStor as well. So GIN indexing on JSONB is there for this kind of operator, which is, is there a key present? Is there any of the elements present in my JSON? It's very fast. You can as well write expression indexes. And you get some variation on the indexes so that you can pretty tailored the optimization for lookup inside your database. So you can do this kind of thing. Do I have B2 inside my A1, B2 JSONB elements? And it's a true. Do I have any B elements in any BK in A1, B2? Yes, there is. I can do pass search as well. I can do lookup for a number of keys. That's very flexible, very, very convenient. So that's all unstructured data. And it's very fast. If you have JSON, you can have GeoGison with GIS folks. So post-GIS place too. You can have a result by SDS as GeoGison. And it will output you something like this. And you can do some queries mixing the JSON native types with post-GIS GeoGison. And you got this kind of well-formatted GeoGison documents outside of your database without having to write any PHP code, which is good. So JSONB, other queries, a lot of other queries. And some more notes to have a bit of time. There are new things as well on PostgreSQL. So for deploying, there is this apt.postgreSQL.org which got packages for all supported PostgreSQL version for all epitaph based distributions. So whatever is a version of PostgreSQL, whatever is a version of your distribution, you can install packages. That's maintained, trusted. Post-GIS is inside. So use it. That's very powerful. I got Docker, PGGs, images as well, image as well, with all the GIS features inside already. So PostgreSQL 9.4, PODGIS, 3D stuff, Pitcher routing, PON cloud. And it's a one-liner normally on Docker-enabled distribution. If it works, that is a little bug. But normally it should. So that's pretty convenient. Last note on what's coming next, so what we are currently working on, Oracle Spatial Foreign Data Wrapper. Access your Oracle Spatial data from inside your Post-GIS database. So that's pretty cool, that's a transparent access to Oracle Table. Currently there is no Oracle Spatial support or only Oracle Classic. You could work that on with WKT as an intermediary format from Oracle to Post-GIS, but it's a mess to set up. So we're working on native support. So from SDO directly to Post-GIS. That's for replication cross database queries. It's a work in progress, but progressing fast. So you can follow the proof request here. That's an example of using directly an Oracle database with Post-GIS queries. So it doesn't work yet, but it should work. Not from 9.4. It's a support project. So once it's out, you can use it with 9.4 if you want. But it's not out yet. Better spatial index is SPGist, which is a Google SumoRov.gov project. We'll see if the code is committable. Not sure yet. But there are some steps. So it's faster to read, faster to build. And there is this vodgasing. So we had MongoDB on ACID. So now we have PostgreSQL with vodgah. That's the Russian guys creating new kind of indexes. That's the derivation of GsonB indexes. And it's a NER tree based on Gist as an entry tree. Wow. What does that mean? That means it solves the spaghetti monster mess when you have all your data with the same extents. Your queries will run very slow with Post-GIS. The idea is making out of this polygon a lot of different small polygons so that we can still optimize queries based on indexes. So that's not yet. That's probably for 9.5. But it's coming at some point. And that's about all. That's a lot of things. I'm late. I'm sorry. But maybe you have time for one question? Yeah. Yeah. So do you have any questions? No question? What? Was too much? OK. So thank you. And have a good day. Thank you. Thank you.
 Hi everybody, I hope you had a good start for this video.  And so I'm Vincent Picavit and I'm going to present you today  some things about PostgreSQL in general.  So PostgreSQL, what is this thing?  Do you know what PostgreSQL is exactly?  Who knows that?  Yeah, some of you.  Actually, PostgreSQL is a thing you have burned out with  Pogs.  Yes.  So apparently you are all users of PostgreSQL.  But to define what it is just quickly, it's RODBMS, which is  a relational object database management system, which is  kind of a mutating of the database software world.  PostgreSQL is SQL compliant.  It's transactional.  It's ACID.  So that's a basic constraint on databases.  It's got MVCC, multi-version control system.  It's extensible.  And it's got special features because you've got PostGIS.  Of course, you know that.  So today I'm going to talk about PostgreSQL and new stuff.  And some not that new stuff, but interesting stuff, which  are specific to PostgreSQL and not specific to PostGIS.  You all know a lot about PostGIS, I think.  But there are a lot of things in PostgreSQL which may be  useful for you, which are not exactly inside PostGIS.  So PostgreSQL is currently at version 9.x.  So that's the main version.  Stable version is 9.3.  And 9.4 is just around the corner.  So we are currently in beta 2.  And it won't be long before it is probably in October or  something like that.  So what's new in PostgreSQL 9.4?  I'm going to show you some of the new stuff.  But to sum up, there is a lot of good foundation work, like  very basic work, very boring work that you want to use  exactly like that until maybe a few years.  But a lot of things needed to make new advances in the  database world with PostgreSQL.  So 9.4 is important in this regard.  And there are lots of new features, various new features.  And some of them are useful for GIS.  So I'll show you something.  So we are talking about SQL.  So who of you don't know SQL at all?  Oh, not even one?  That's good.  Perfect.  So let's go for the feature get-all.  So first one is lateral join.  So lateral join is a new kind of  specification for join.  And it allows you to access to other tables in subqueries.  Which is kind of not that useful apparently.  But actually, for us, GIS, it fixes the distance operator  constant issue, which means actually if you know this one,  which is a key NN search, or nearest neighbor search, you  have to use normally a constant to compare to the  geometry you are looking around.  And you cannot refer here to something which is outside of  the query.  But with this lateral keyword, you can do that now with  POSGRACYQL 9.4.  So you can have this kind of query, which is find me the  two bars closest to each bus stop.  Just in case one is closed, I want two.  And so I got buses.  I got points for buses.  And in the subquery, I'm going to find the two closest bars  in regard to the bus's geometry.  So that's something which is interesting for GIS, for  GIS, for XP.  Because otherwise, you would have to do a lot of  subqueries, and that's a pain to write.  So that's pretty good.  Another one is auto update views.  So we got views.  You all know what a view is.  The thing is, with 9.3, we got an later.  We got other updateable views.  So when you create a view, you can actually update the view,  insert things into the view, or delete the view as soon as  it's a simple view.  I mean, when it's a very complex view with joining a lot of  tables, it's not possible to know, to determine which  updates you really want to know.  But if it's a simple view, you can do that without having  any trigger, because otherwise, you would have to do that  with triggers.  So that's pretty interesting.  And 9.4 adds a column level management, which means you  can have a view with some columns that are  updatable and some that are not.  Think, like if I compute a buffer in a view, for example,  I cannot update the buffer column because it's not in  the parent table.  But with 9.4, you could update the updatable column and not  the buffer table.  So that's pretty cool.  And it allows us well to do fine tuning special right  management, because you could create a view on a  specific area.  And then you could say, I grant some rights to this view  for some users, but not to the parent table, so that you can  restrict the rights to some areas.  And the check options allows you to prevent the user to  update the view so that new elements go out of the view.  So this is an example of a view with such a constraint.  So I say, I want to have always contained inside this  polygon.  And I want check options so that if I move the geometries, I  cannot move them outside of this polygon.  So that's what I can do now is updating the view and  simplifying the geometry will be no problem.  But if I try to translate the geometry a lot, it won't fit  into the polygon defined on the view.  And then post-graph SQL will say, no, you  cannot do that and fail.  So that's a cool thing to do as well with 9.4.  And check options is something which is  pretty useful as well.  Talking about views, we've got materialized  views as well now.  So materialized views came from 9.3.  And it actually computes a view instead of just creating a  reference to a query.  And it stars the results.  So you can think about it as a cache view, for example.  It allows indexing on the materialized view.  So you can have some specific index on the materialized  view, which you don't have on the parent table.  So that's interesting.  And by default, indexes and constraints from the parent  tables are not copied.  So if you want to have the same optimization you have in  your parent table, you have to copy the indexes and  constraints on the materialized view.  9.4 improves that to refresh concurrently, which means you  can refresh your view while you're still accessing it.  So you don't have a big lock on the view.  So that's pretty cool.  The use cases are materialized views of complex  joins, of complex spatial operations, of  frame data wrapper, everything which takes time to  compute.  You can use a materialized view to cache this data.  So that's cool.  That's an example of a materialized view.  So I got, let's say, that's a pretty computer intensive  view here, computing the buffer of some, a lot of points.  I can use a materialized view and it's create  materialized view simply.  And then to refresh, I can say refresh my materialized view  point buffer.  And you can see that it's using a sequential scan on the  materialized view.  That's a cool thing, too.  Ranges, sorry?  Yeah, question?  Is there a way to increment materialized views with the  new system?  Not as it is.  I'll talk about incremental things a bit later.  So ranges are new data types which works with  integral, numeric dates.  And it allows you to do ranges of elements.  So you can have open ranges, closed ranges, infinite  ranges.  You can have indexes on ranges.  And it's good for time data specifically because you all  have some valid before and valid after columns in your  databases.  So you can use ranges.  You will have only one column.  And that's pretty convenient.  Write table city.  Who knows what the city is?  Yeah, some of them.  Some of you.  So city is a common table expression.  It's actually the with keyword.  So that's another syntactic way of writing query, which is  pretty convenient because you can do things like with my  kind of temporary table, which is a query.  I want them to do some operations.  So you can write your queries as steps, actually.  And in PostgreSQL latest version, you can do some  modifying queries inside your common table  expression, just like that.  I got new table, bad data, which I create.  And I want to delete things from my good GIS data, where  it's not valid.  And I call this the result of this delete, the temporary  table deleted.  And I want to insert all the results  into my bad data table.  So that's all one query.  It's taking the data out, and at the same time, taking the  data in another table.  So it's a pretty good syntax for writing complex SQL queries.  And with city, you can also do recursive city, which is  something you can do network with it, so elephants.  So recursive cities are in this SQL standard, so that's  with recursive keyword.  And that's the city syntax, which is pretty cool.  You've got to be careful with cities to performances,  because sometimes it prevents the optimizer to do some  optimization.  And it can be slower than a normal query with sub-query.  Well, recursive cities allows us to do graph analysis.  Because it's recursive, and graph algorithm, by nature,  are recursive, you can implement them using SQL.  That's an example of it.  I don't want you to read everything, but to know how  it looks, you've got three steps mainly.  You've got the initialization phase, where you  select some elements.  Then you've got the recursive phase, where you will say,  OK, I will reuse the result of the previous step to get new  elements.  So for example, on a network, I will take one road, and I  will say, give me all the roads connected to my first  road, and then you go recursively from one road to  another to do some graph analysis.  And at the end, you can use the results to display  something.  One new case, and it's exactly the SQL query which is here,  is I got a river network, and I compute all upstream  segments from one specific segment.  And it's very fast.  If you've got indexes, it's going to use indexes all  along the way, and it's pretty cool, and it's pretty fast.  It's actually, for some queries, faster  than using pdrouting.  It's a bit more complicated to write, but once you've written  one recursive query, it's more or less always the same.  Another feature, Unlock Table.  So Unlock Table are tables with no security.  So that's no wall, no maintenance, and it's really  faster to write to.  But it will not survive a crash.  You have to say that again?  So it's useful for temporary results, and for  reproducible results.  So if you do some pretty easy computation  step with PodGIS, for example, you can use Unlock Table.  It will be quite faster, but do not forget that if you  server crash, data left.  So that's pretty easy to use.  It creates Unlock Table, and you just say  that's a new table.  With 9.5, which is next version, so next year, you  will be able to say, alter table, set logged, or set  unlocked.  So you can switch from log to unlock, which is pretty  convenient.  When your temporary data switch to non-temporary data, you  can set log, or you lose your data.  So that's about, yeah, quick.  To do that now, you need a basic creation table.  Sorry?  To do that now, you need a copy of it.  Yeah.  Logical decoding.  So that was about the question about data  deltas and all.  So logical decoding is probably the biggest 9.4  feature.  But you won't use it.  Why is it?  Why is that?  Because the first step of many steps, it's kind of a  database modification logger.  Actually, it's some logger, some journalizing on the  logical level, so on the query level.  And that's the first step to logical replication, which is  another step to multi-master replication, actually.  So when you've got multiple server updated at the same  time, and you want to synchronize them, so that's  multi-master replication.  And you want to do a replication at the logical  level for that.  So that's the first step.  Multi-master replication won't be in PostgreSQL until  next year, probably.  But you can always already use logical decoding for a few  things like logging.  We're going to have versioning soon.  We're going to have incremental updates as well at some  points.  And it looks like something like that.  So you initialize the logical decoding so that PG create  logical replication.  You create a slot, so that's a place I want to listen to what  happens in my database.  And then I'm going to do some operations on my database.  For example, delete from points where GID equals 1.  And then I can query the logical decoding tool so that  again gets the modification done.  And this test decoding driver will send me some specific  data saying, the point table has been  modified and the GID1 has been deleted.  So you can access to all the modifications in your database  with SQL.  That's pretty cool.  If I update the point, for example, I change the  geometry, I can access the logical decoder logging, and I  can get everything which is modified.  So I could recreate the queries which have been used to  modify the data.  I could re-apply them to another database.  Yeah?  AUDIENCE member 1, how does this differ from just doing it  before trigger?  What, before trigger?  There's no trigger.  So it's faster as well.  It's very low level in PostgreSQL.  And it's rock solid.  And yeah, it's much faster.  And it's easier to set up.  And it's extendable as well.  So this output is a test decoding driver.  There are other drivers.  You can get the data in JSON, for example.  So you can get the modification written in JSON and use it  for whatever you want to use it.  We apply that to a MongoDB database or something.  Yes, QA.  So Yes, QA is not an existing word, but it's from a  geometry fountain who spotted that.  So it's just a reaction to the no SQL thing.  So you know no SQL more or less.  Some people say no SQL is the future.  Some others say it's back then.  So but which no SQL?  What no SQL?  There are a lot of different no SQL elements.  And PostgreSQL supports now document oriented no SQL.  So that's unstructured data, k-par values.  And you can think of it as MongoDB on ACID.  So you're getting high there.  And I will present to you quickly the JSON and JSON  B part.  There are the XML part as well, which is pretty old.  And the HSTOR will be presented in the next talk, I  think.  So quickly, because I really run out of time, JSON is a new  document storage in 9.2 and later.  You can store key value things.  You've got indexing, but no gins and gist.  You have to create expression indexes.  It's a text storage.  It's very fast for IEO.  It's got a size of a rate compared to just plain text.  But it's pretty efficient.  It's not as efficient as HSTOR, but still.  And there is no gins and gist index, or no specialized  index for this type of data.  And you've got a lot of operators and functions.  So you can access elements.  You can access element as text.  As JSON object, you can get passes out of some JSON  object.  You can convert some rows to JSON.  So you directly have some JSON data out of your database  here with a geometry and coded as WKB.  And you've got a ton of different features with JSON.  I'm sorry.  I'm quick, because I really run out of time.  There are a lot of things to present.  So JSONB is a big new thing.  So second best feature in PostgreSQL 9.4 past logical  decoding, that's for JSON binary.  So JSON type, JSON native type in PostgreSQL,  is written as text.  This JSONB is a binary storage.  So you've got like a tree inside the database with  different JSON parts.  So it's no K duplicates.  There is no other preservation.  It's got slower IEO.  It's quite bigger than JSON in storage.  But actually, it's better because you have HStor like  operators.  You have all the JSON operators as well.  And you have a few more operators, which are the one  here.  And more importantly, you've got GIN indexing.  So you can index your JSON.  And it's really fast.  So use it except for some specific use cases if you  need some very good IEO.  Or if you need some flat string and you can use HStor,  you can use HStor as well.  So GIN indexing on JSONB is there for this kind of  operator, which is, is there a key present?  Is there any of the elements present in my JSON?  It's very fast.  You can as well write expression indexes.  And you get some variation on the indexes so that you can  pretty tailored the optimization for lookup  inside your database.  So you can do this kind of thing.  Do I have B2 inside my A1, B2 JSONB elements?  And it's a true.  Do I have any B elements in any BK in A1, B2?  Yes, there is.  I can do pass search as well.  I can do lookup for a number of keys.  That's very flexible, very, very convenient.  So that's all unstructured data.  And it's very fast.  If you have JSON, you can have GeoGison with GIS folks.  So post-GIS place too.  You can have a result by SDS as GeoGison.  And it will output you something like this.  And you can do some queries mixing the JSON native types  with post-GIS GeoGison.  And you got this kind of well-formatted GeoGison  documents outside of your database  without having to write any PHP code, which is good.  So JSONB, other queries, a lot of other queries.  And some more notes to have a bit of time.  There are new things as well on PostgreSQL.  So for deploying, there is this apt.postgreSQL.org  which got packages for all supported  PostgreSQL version for all epitaph based distributions.  So whatever is a version of PostgreSQL,  whatever is a version of your distribution,  you can install packages.  That's maintained, trusted.  Post-GIS is inside.  So use it.  That's very powerful.  I got Docker, PGGs, images as well, image as well,  with all the GIS features inside already.  So PostgreSQL 9.4, PODGIS, 3D stuff,  Pitcher routing, PON cloud.  And it's a one-liner normally on Docker-enabled distribution.  If it works, that is a little bug.  But normally it should.  So that's pretty convenient.  Last note on what's coming next, so what we are currently  working on, Oracle Spatial Foreign Data Wrapper.  Access your Oracle Spatial data from inside your Post-GIS  database.  So that's pretty cool, that's a transparent access  to Oracle Table.  Currently there is no Oracle Spatial support  or only Oracle Classic.  You could work that on with WKT as an intermediary format  from Oracle to Post-GIS, but it's a mess to set up.  So we're working on native support.  So from SDO directly to Post-GIS.  That's for replication cross database queries.  It's a work in progress, but progressing fast.  So you can follow the proof request here.  That's an example of using directly an Oracle database  with Post-GIS queries.  So it doesn't work yet, but it should work.  Not from 9.4.  It's a support project.  So once it's out, you can use it with 9.4 if you want.  But it's not out yet.  Better spatial index is SPGist, which  is a Google SumoRov.gov project.  We'll see if the code is committable.  Not sure yet.  But there are some steps.  So it's faster to read, faster to build.  And there is this vodgasing.  So we had MongoDB on ACID.  So now we have PostgreSQL with vodgah.  That's the Russian guys creating new kind of indexes.  That's the derivation of GsonB indexes.  And it's a NER tree based on Gist as an entry tree.  Wow.  What does that mean?  That means it solves the spaghetti monster mess  when you have all your data with the same extents.  Your queries will run very slow with Post-GIS.  The idea is making out of this polygon  a lot of different small polygons  so that we can still optimize queries based on indexes.  So that's not yet.  That's probably for 9.5.  But it's coming at some point.  And that's about all.  That's a lot of things.  I'm late.  I'm sorry.  But maybe you have time for one question?  Yeah.  Yeah.  So do you have any questions?  No question?  What?  Was too much?  OK.  So thank you.  And have a good day.  Thank you.  Thank you.
01_Vincent_Picavet_720p_0_551.png 01_Vincent_Picavet_720p_552_1715.png 01_Vincent_Picavet_720p_1716_2279.png 01_Vincent_Picavet_720p_2280_2603.png 01_Vincent_Picavet_720p_2604_3731.png 01_Vincent_Picavet_720p_3732_4211.png 01_Vincent_Picavet_720p_4212_6191.png 01_Vincent_Picavet_720p_6192_8135.png 01_Vincent_Picavet_720p_8136_11015.png 01_Vincent_Picavet_720p_11016_11783.png 01_Vincent_Picavet_720p_11784_12179.png 01_Vincent_Picavet_720p_12180_14879.png 01_Vincent_Picavet_720p_14880_15011.png 01_Vincent_Picavet_720p_15012_15911.png 01_Vincent_Picavet_720p_15912_16931.png 01_Vincent_Picavet_720p_16932_17579.png 01_Vincent_Picavet_720p_17580_18503.png 01_Vincent_Picavet_720p_18504_19163.png 01_Vincent_Picavet_720p_19164_19655.png 01_Vincent_Picavet_720p_19656_23711.png 01_Vincent_Picavet_720p_23712_23987.png 01_Vincent_Picavet_720p_23988_24983.png 01_Vincent_Picavet_720p_24984_26459.png 01_Vincent_Picavet_720p_26460_26579.png 01_Vincent_Picavet_720p_26580_27047.png 01_Vincent_Picavet_720p_27048_28307.png 01_Vincent_Picavet_720p_28308_30851.png 01_Vincent_Picavet_720p_30852_31391.png 01_Vincent_Picavet_720p_31392_32039.png 01_Vincent_Picavet_720p_32040_32255.png 01_Vincent_Picavet_720p_32256_33527.png 01_Vincent_Picavet_720p_33528_33803.png 01_Vincent_Picavet_720p_33804_34811.png 01_Vincent_Picavet_720p_34812_35063.png 01_Vincent_Picavet_720p_35064_35348.png