Postgre SQL as a Document Storage for NET

Postgre. SQL as a Document Storage for. NET applications Konstantin Proskurdin Adver-Online k. proskurdin@gmail. com

Sponsors

#sqlsatdnipro

Bio § § § Web Developer 10 years of experience in IT 9 years of DB experience 8 years of web experience MSPD

Agenda § § Structured/Semi Structured data Document-oriented databases Postgre. SQL Marten

Structured Data § § § Data model explicitly determines the structure of data Schema is fixed Easy to visualize A lot of Math Ready-made query language, SQL

Semi-structured Data § § § Self-describing structure The schema can easily be changed Helps to not worry about object-relational impedance mismatch Support for nested or hierarchical data Support for lists of objects Can represent the information of some data sources that cannot be constrained by schema § Flexible format for data exchange between different types of databases

Semi-structured Data Usage § § Document storage Data exchange (Json, XML) Shared data storage …

Document Storage § § § Mongo. DB Caché Document. DB Raven. DB Postgre. SQL

Postgre. SQL § § Hstore JSON HStore 2 JSONB

JSON vs JSONB § § § JSON: textual storage “as is” JSONB: no whitespaces JSONB: no duplicate keys, last key wins JSONB: keys are sorted JSONB: indexes could be used

JSON(B) Operators § § § -> Get JSON object field by key ->> Get JSON object field as text #> Get JSON object at specified path #>> Get JSON object at specified path as text @> Does the left JSON value contain the right JSON

Sample object

JSONB Demo

JSONB Indexes § Expression Indexes § GIN Indexes

JSONB Indexes Demo

Marten https: //jasperfx. github. io/marten/

Demo

Summary § § Structured/Semi Structured data Document-oriented databases Postgre. SQL Marten

That’s all