Transcription
Making Postgres Central in Your Data CenterBRUCE MOMJIANThis talk explores why Postgres is uniquely capable of functioningas a central database in enterprises. Title concept from Josh BerkusCreative Commons Attribution Licensehttp://momjian.us/presentationsLast updated: November, 20151 / 39
Outline1. Object-relational (extensibility)2. NoSQL3. Data analytics4. Foreign data wrappers (database federation)5. Central roleMaking Postgres Central in Your Data Center 2 / 39
1. Object-Relational (Extensibility)Object-relational databases like Postgres support support classesand inheritance, but most importantly, they define databasefunctionality as objects that can be easily lational databaseMaking Postgres Central in Your Data Center 3 / 39
How Is this Accomplished?pg databasedatlastsysoidpg conversionpg triggerpg mprocconprocpg amprocaggfinalfnpg languageaggtranstypepg castpg procpg constraintpg ampg rewritecastsourceprolangcontypidamgettupleev classcasttargetprorettypeaminsertcastfuncpg opclassambeginscanopcdeftypeamrescanamendscanpg indexpg classpg typepg oprnegateoprlsortopoprrsortopoprcodepg inheritspg attributepg attrdefoprrestpg ntattnumadnumamopopratttypidpg statisticstarelidstaattnumpg dependpg namespacestaoppg shadowpg grouppg static/catalogs.htmlMaking Postgres Central in Your Data Center 4 / 39
Example: ISBN Data TypeCREATE EXTENSION isn;\dTList of data typesSchema Name Description-------- -------- ublic ean13 International European Article Number (EAN13)public isbn International Standard Book Number (ISBN)public isbn13 International Standard Book Number 13 (ISBN13)public ismn International Standard Music Number (ISMN)public ismn13 International Standard Music Number 13 (ISMN13)public issn International Standard Serial Number (ISSN)public issn13 International Standard Serial Number 13 (ISSN13)public upc Universal Product Code /isn.htmlMaking Postgres Central in Your Data Center 5 / 39
ISBN Behaves Just Like Built-In Types\dTS pg catalog integer public isbn -2 billion to 2 billion integer, 4-byte storage International Standard Book Number (ISBN)Making Postgres Central in Your Data Center 6 / 39
The System Catalog Entry for INTEGERSELECT * FROM pg type WHERE typname ’int4’;-[ RECORD 1 ]-- --------typname int4typnamespace 11typowner 10typlen 4typbyval ttyptype btypcategory Ntypispreferred ftypisdefined ttypdelim ,typrelid 0typelem 0typarray 1007typinput int4intypoutput int4outtypreceive int4recvtypsend int4sendtypmodin typmodout typanalyze typalign itypstorage ptypnotnull fMaking Postgres Central in Your Data Center 7 / 39
The System Catalog Entry for ISBNSELECT * FROM pg type WHERE typname ’isbn’;-[ RECORD 1 ]-- --------------typname isbntypnamespace 2200typowner 10typlen 8typbyval ttyptype btypcategory Utypispreferred ftypisdefined ttypdelim ,typrelid 0typelem 0typarray 16405typinput isbn intypoutput public.isn outtypreceive typsend typmodin typmodout typanalyze typalign dtypstorage ptypnotnull fMaking Postgres Central in Your Data Center 8 / 39
Not Just Data Types, LanguagesCREATE EXTENSION plpythonu;\dLList of languagesName Owner Trusted Description----------- ---------- --------- -----------------------------------------plpgsql postgres t PL/pgSQL procedural languageplpythonu postgres f PL/PythonU untrusted procedural tic/plpython.htmlMaking Postgres Central in Your Data Center 9 / 39
Available Languages PL/Java PL/Perl PL/pgSQL (like PL/SQL) PL/PHP PL/Python PL/R (like SPSS) PL/Ruby PL/Scheme PL/sh PL/Tcl SPI xternal-pl.htmlMaking Postgres Central in Your Data Center 10 / 39
Specialized Indexing Methods BTree Hash GiST (generalized search tree) SP-GiST (space-partitioned GiST) GIN (generalized inverted c/indexam.htmlMaking Postgres Central in Your Data Center 11 / 39
Index Types Are Defined in the System Catalogs TooSELECT amname FROM pg mlMaking Postgres Central in Your Data Center 12 / 39
Operators Have Similar FlexibilityOperators are function calls with left and right arguments of specified types:\doSSchema Name Left arg type Right arg type Result type Descriptionpg catalog integer integer integer add\dfSSchema Name Result data type Argument data types Type pg catalog int4pl integer integer, integer normalMaking Postgres Central in Your Data Center 13 / 39
Other Extensibility Casts are defined in pg cast, int4(float8) Aggregates are defined in pg aggregate, sum(int4)Making Postgres Central in Your Data Center 14 / 39
Externally Developed Plug-Ins PostGIS (Geographical Information System) PL/v8 (server-side JavaScript) experimentation, e.g. full text search was originallyexternally developedMaking Postgres Central in Your Data Center 15 / 39
Offshoots of Postgres AsterDB Greenplum Informix Netezza ParAccel Postgres XC Redshift (Amazon) Truviso Vertica Yahoo! Everesthttps://wiki.postgresql.org/wiki/PostgreSQL derived roads-a-tour-of-postgres-forksMaking Postgres Central in Your Data Center 16 / 39
Offshoots of ter/inkscape/PostgreSQL timeline/timeline postgresql.pngMaking Postgres Central in Your Data Center 17 / 39
Plug-In Is Not a Bad WordMany databases treat extensions as special cases, with seriouslimitations. Postgres built-ins use the same API as extensions, so llextensions operate just like built-in functionality.Making Postgres Central in Your Data Center 18 / 39
Extensions and Built-In FacilitiesBehave the SameISNPostGISPostgres System TablesExtensionsPL/Rsum()int4btreePL/pgSQLMaking Postgres Central in Your Data Center 19 / 39
2. NoSQLSQLMaking Postgres Central in Your Data Center 20 / 39
NoSQL TypesThere is no single NoSQL technology. They all take differentapproaches and have different features and drawbacks: Key-value stores, e.g. Redis Document databases, e.g. MongoDB (JSON) Columnar stores: Cassandra Graph databases: Neo4jMaking Postgres Central in Your Data Center 21 / 39
Why NoSQL ExistsGenerally, NoSQL is optimized for: Fast simple queries Auto-sharding Flexible schemasMaking Postgres Central in Your Data Center 22 / 39
NoSQL Sacrifices A powerful query language A sophisticated query optimizer Data normalization Joins Referential integrity DurabilityMaking Postgres Central in Your Data Center 23 / 39
Are These Drawbacks Worth the Cost? Difficult Reporting Data must be brought to the client foranalysis, e.g. no aggregates or data analysis functions.Schema-less data requires complex client-side knowledge forprocessing Complex Application Design Without powerful querylanguage and query optimizer, the client software isresponsible for efficiently accessing data and for dataconsistency Durability Administrators are responsible for data retentionMaking Postgres Central in Your Data Center 24 / 39
When Should NoSQL Be Used? Massive write scaling is required, more than a single servercan provide Only simple data access pattern is required Additional resource allocation for development is acceptable Strong data retention or transactional guarantees are notrequired Unstructured duplicate data that greatly benefits fromcolumn compressionMaking Postgres Central in Your Data Center 25 / 39
When Should Relational Storage Be Used? Easy administration Variable workloads and reporting Simplified application development Strong data retentionMaking Postgres Central in Your Data Center 26 / 39
The Best of Both Worlds: PostgresPostgres has many NoSQL features without the drawbacks: Schema-less data types, with sophisticated indexing support Transactional schema changes with rapid additional andremoval of columns Durability by default, but controllable per-table orper-transactionMaking Postgres Central in Your Data Center 27 / 39
Schema-Less Data: JSONCREATE TABLE customer (id SERIAL, data JSON);INSERT INTO customer VALUES (DEFAULT, ’{"name" : "Bill", "age" : 21}’);SELECT data- ’name’ FROM customer WHERE data- ’age’ ’21’;?column?---------"Bill"Making Postgres Central in Your Data Center 28 / 39
Easy Relational Schema ChangesALTER TABLEBEGIN WORK;ALTER TABLEALTER TABLEALTER TABLECOMMIT;customer ADD COLUMN status CHAR(1);customer ADD COLUMN debt limit NUMERIC(10,2);customer ADD COLUMN creation date TIMESTAMP WITH TIME ZONE;customer RENAME TO cust;Making Postgres Central in Your Data Center 29 / 39
3. Data Analytics AggregatesOptimizerServer-side languages, e.g. PL/RWindow functionsBitmap heap scansTablespacesData partitioningMaterialized viewsCommon (recursive) table expressionsMin/max indexes (coming in 2015)GROUPING SETS, ROLLUP, CUBE (coming in 2015)Data warehouse-specific solutions are required for paralleloperations across sing with PostgreSQL.pdfMaking Postgres Central in Your Data Center 30 / 39
Read-Only Slaves for AnalyticsMaster Server/pg xlogData WarehouseNetwork/pg xlogMaking Postgres Central in Your Data Center 31 / 39
4. Foreign Data Wrappers (Database Federation)Foreign data wrappers (SQL MED) allow queries to read and writedata to foreign data sources. Foreign database support includes: CouchDB Informix MongoDB MySQL Neo4j Oracle Postgres RedisThe transfer of joins, aggregates, and sorts to foreign servers is not esql.org/wiki/Foreign data wrappersMaking Postgres Central in Your Data Center 32 / 39
Foreign Data Wrappers to Interfaces JDBC LDAP ODBCMaking Postgres Central in Your Data Center 33 / 39
Foreign Data Wrappers toNon-Traditional Data Sources Files HTTP AWS S3 TwitterMaking Postgres Central in Your Data Center 34 / 39
Foreign Data Wrapper ExampleCREATE SERVER postgres fdw testFOREIGN DATA WRAPPER postgres fdwOPTIONS (host ’localhost’, dbname ’fdw test’);CREATE USER MAPPING FOR PUBLICSERVER postgres fdw testOPTIONS (password ’’);CREATE FOREIGN TABLE other world (greeting TEXT)SERVER postgres fdw testOPTIONS (table name ’world’);\detList of foreign tablesSchema Table Server-------- ------------- ------------------public other world postgres fdw test(1 row)Foreign Postgres server name in red; foreign table name in blueMaking Postgres Central in Your Data Center 35 / 39
Read and Read/Write Data SourcesPostgresOracleora tabmon tabMongoDBtw tabTwitterMaking Postgres Central in Your Data Center 36 / 39
5. Postgres CentralityPostgres can rightly take a central place in the data center withits: Object-Relation flexibility and extensibility NoSQL-like workloads Powerful data analytics capabilities Access to foreign data sourcesNo other database has all of these key components.Making Postgres Central in Your Data Center 37 / 39
Postgres’s Central RoleISNOraclePostGISMongoDBExtensionsForeign DataWrappersPL/RTwitterPostgresJSONWindow FunctionsDataWarehouseNoSQLEasy DDLData ParitioningBitmap ScansShardingMaking Postgres Central in Your Data Center 38 / 39
ickr.com/photos/vpickering/3617513255Making Postgres Central in Your Data Center 39 / 39
Outline 1. Object-relational (extensibility) 2. NoSQL 3. Data analytics 4. Foreign data wrappers (database federation) 5. Central role Making Postgres Central in Your Data Center 2/39
