PostgreSQL SQL MED

1 .PostgreSQL SQL-MED Ibrar Ahmed Senior Software Engineer @ Percona PostgreSQL Consultant 

2 .What? • Is an relational database management system (RDBMS) Licence PostgreSQL: Released under the PostgreSQL License. (Similar to the BSD or MIT) • Is a Object-Relation Database Management System(ORDBMS) • One of the finest open source relation Why? database which has some object- oriented features. • PostgreSQL is free. There are many companies Support? providing professional support • PostgreSQL is Open Source. for PostgreSQL. • PostgreSQL Conform to the ANSI-SQL: 2008. • PostgreSQL is ACID (Atomicity, Consistency, Isolation and Durability) Complaint. Who? • Web technology • Financial • No-SQL Workload • Small & Large Scale Business … !2 

3 . Accessing Data From Multiple Sources SELECT data from multiple “Database Engines” and generate results? Is it possible within Database Engine Is it possible? 

4 .Application Architecture 1/2 MySQL Module libmysqlclient U S E PostgreSQ L libpq R Module A P MongoDB Module libmongo-c P L Join I JDBC C Module JDBC A T I File System Module JDBC O N … File System API 

5 . SQL-MED Management of External Data • SQL standard, It is defined by ISO/IEC 9075-9:2008 • SQL/MED provides extensions to SQL that define FDW ( Foreign Data Wrapper) • PostgreSQL start implementing in its core since PostgreSQL version 9.1 What do you mean by start implementing? • PostgreSQL community builds its own few postgresql_fdw. • Now there are many FDW implemented by other people. https://wiki.postgresql.org/wiki/Foreign_data_wrappers Do we really need to implement separate extension? 

6 .Application Architecture 2/2 mysql_fdw libmysqlclient U S E postgres_fdw R libpq A P mogo_fdw libmongoc P … L PostgreSQL Module libpq I Connection Pooling hdfs_fdw C JDBC A Aggregate T I Filter hdfs_fdw JDBC O N Join file_fdw File System API 

7 .PostgreSQL Foreign Data Wrapper • DML Support* • Query Splitting • Joins • Aggregate • Connection Pooling 

8 .PostgreSQL Foreign Data Wrapper Push Down • Quals pushdown • Joins Push Down • Aggregate Push Down 

9 . PostgreSQL Foreign Data Wrapper Executing Query > db.mongo_tbl.find() SELECT * FROM mysql_tbl; { "_id" : ObjectId("5b64d9628365b615c035bcba"), "id" : 1, "data" : "Item" } { "_id" : ObjectId("5b64d97c8365b615c035bcbb"), "id" : 2, "data" : "Item-2" } { "_id" : ObjectId("5b64d9818365b615c035bcbc"), "id" : 3, "data" : "Item-3" } • CREATE EXTENSION mysql_fdw; • CREATE EXTENSION mongo_fdw; • SELECT count(*) FROM mysql_tbl AS mysql, • CREATE SERVER mysql_svr • CREATE SERVER mongo_svr mongo_tbl AS mongo FOREIGN DATA WRAPPER mysql_fdw FOREIGN DATA WRAPPER mongo_fdw WHERE mongo.id = mysql.id; OPTIONS (host ‘127.0.0.1’, OPTIONS (host ‘127.0.0.1’, port '5432', port '5432', dbname 'db'); dbname 'db'); • CREATE USER MAPPING FOR postgres • CREATE USER MAPPING FOR postgres SERVER foreign_server SERVER mongo_svr OPTIONS (user ‘mysql_user', OPTIONS (user ‘mongo_user', password ‘mysql_pass'); password ‘mongo_pass'); • CREATE FOREIGN TABLE mysql_tbl ( • CREATE FOREIGN TABLE mongo_tbl ( id integer NOT NULL, id integer NOT NULL, data text data text ) ) 

10 .“It's not a faith in technology. It's faith in people. – Steve Jobs