Making Postgres Central in Your Data Center
BRUCE MOMJIAN

This talk explores why Postgres is uniquely capable of functioning as a central databasein enterprises. Title concept from Josh Berkus
https://momjian.us/presentations

Creative Commons Attribution License

Last updated: August 2024

1 / 40Outline

1. Object-relational (extensibility)
2. NoSQL
3. Data analytics
4. Foreign data wrappers (database federation)
5. Central role2 / 401. Object-Relational (Extensibility)

Object-relational databases like Postgres support classes and inheritance, but mostimportantly, they define database functionality as objects that can be easily manipulated.

http://en.wikipedia.org/wiki/Object-relational_database3 / 40How Is this Accomplished?
pg_databasedatlastsysoid
pg_conversion

pg_aggregate
aggfnoid

tgfoidaggtransfn
aggfinalfn
aggtranstype

pg_proc
prolangpg_constraint

pg_am

contypid

amgettuple

conproc
pg_language
pg_castpg_rewrite
ev_class

castsource
casttarget
castfunc

pg_amproc
amopclaidamprocpg_trigger
tgrelid

prorettype
pg_opclass
opcdeftypeaminsert
ambeginscan
amrescanamendscan

pg_index
indexrelid
indrelid

pg_class
reltyperelam
relfilenodereltoastrelid
reltoastidxid

pg_typetyprelid

pg_operator
oprleftammarkpos

typelem

oprright

ambuildtypinput

oprresulttypoutput

oprcomambulkdelete
amcostestimate

typbasetype

oprnegate

amrestrpos

oprlsortop
oprrsortop
oprcodepg_inherits

pg_attribute

pg_attrdef

oprrestinhrelid
inhparent

attrelid
attnumadrelid

oprjoinadnum

pg_amopamopclaidamopopratttypid
pg_statistic
starelid
staattnum
pg_depend

pg_namespacestaop1

pg_authid

pg_description

pg_extension

http://www.postgresql.org/docs/current/catalogs.html
4 / 40

Example: ISBN Data TypeCREATE EXTENSION isn;\dT
List of data types
Schema | Name |Description--------+--------+-------------------------------------------------public | 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 (UPC)

http://www.postgresql.org/docs/current/isn.html
5 / 40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)6 / 40

The System Catalog Entry for INTEGERSELECT * FROM pg_type WHERE typname = ’int4’;-[ RECORD 1 ]--+--------typname| int4typnamespace| 11typowner| 10typlen| 4typbyval| ttyptype| b
typcategory| Ntypispreferred | ftypisdefined| ttypdelim| ,typrelid| 0typelem| 0typarray| 1007typinput| int4intypoutput| int4out
typreceive| int4recv
typsend| int4send
typmodin| typmodout| typanalyze| …

7 / 40

The System Catalog Entry for ISBNSELECT * FROM pg_type WHERE typname = ’isbn’;-[ RECORD 1 ]--+--------------typname| isbntypnamespace| 2200typowner| 10typlen| 8
typbyval| ttyptype| b
typcategory| Utypispreferred | ftypisdefined| ttypdelim| ,typrelid| 0typelem| 0typarray| 16405
typinput| isbn_in
typoutput| public.isn_out
typreceive| typsend| typmodin| typmodout| typanalyze| …

8 / 40Not Just Data Types, Languages

CREATE EXTENSION plpythonu;\dL
List of languages
Name| Owner | Trusted |Description-----------+----------+---------+-----------------------------------------plpgsql | postgres | t| PL/pgSQL procedural language
plpythonu | postgres | f| PL/PythonU untrusted procedural language

http://www.postgresql.org/docs/current/plpython.html
9 / 40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• PL/v8 (JavaScript)• SPI (C)http://www.postgresql.org/docs/current/external-pl.html
10 / 40

Specialized Indexing Methods• BRIN• BTree• Hash
• GIN (generalized inverted index)• GiST (generalized search tree)• SP-GiST (space-partitioned GiST)

http://www.postgresql.org/docs/current/indexam.html
https://momjian.us/main/presentations/extended.html#indexing
11 / 40

Index Types Are Defined in the System Catalogs Too

SELECT amname FROM pg_am ORDER BY 1;
amname-------brin
btree
hash
gin
gist
spgisthttp://www.postgresql.org/docs/current/catalog-pg-am.html
12 / 40

Operators Have Similar Flexibility

Operators are function calls with left and right arguments of specified types:\doS
Schema | Name | Left arg type | Right arg type | Result type | Description…
| integer| integer| integer| add
pg_catalog | +\dfS
Schema | Name | Result data type | Argument data types | Type…
| integer, integer| normalpg_catalog | int4pl | integer

13 / 40

Other Extensibility• Aggregates are defined in pg_aggregate, sum(int4)• Casts are defined in pg_cast, int4(float8)

14 / 40

Externally Developed Plug-Ins• PostGIS (Geographical Information System)• PL/v8 (server-side JavaScript)• pgvector• experimentation, e.g., full text search was originally externally developed15 / 40

Offshoots of Postgres• AlloyDB (GCP)• Aurora (AWS)• AsterDB• CockroachDB• Greenplum• Informix• Netezza• ParAccel, Redshift• Postgres XC• Truviso• Vertica• Yahoo! Everest• YugabyteDB
https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases
http://de.slideshare.net/pgconf/elephant-roads-a-tour-of-postgres-forks
16 / 40

Offshoots of Postgres

https://raw.github.com/daamien/artwork/master/inkscape/PostgreSQL_timeline/timeline_postgresql.png
https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases
17 / 40

Plug-In Is Not a Bad Word

Many databases treat extensions as special cases, with serious limitations. Postgres
built-ins use the same API as extensions, so extensions operate just like built-infunctionality.

18 / 40

Extensions and Built-In Facilities Behave the SameISN
PostGIS

Postgres System Tables

Extensions
PL/Rsum()

int4btree
PL/pgSQL

19 / 40

Why Extensibility Matters Today
IoTAI

documents

mobileGPS /apps

GIS

socialweb browser

media
datarelational

warehouse

Red indicates traditional relational workloads.
20 / 40

2. NoSQL

SQL
21 / 40NoSQL Types

There is no single NoSQL technology. They all take different approaches and havedifferent features and drawbacks:• Key-value stores, e.g., Redis• Document databases, e.g., MongoDB (JSON)• Columnar stores: Cassandra• Graph databases: Neo4j

22 / 40

Why NoSQL Exists

Generally, NoSQL is optimized for:• Auto-sharding• Fast simple queries• Flexible schemas

23 / 40NoSQL Sacrifices• A powerful query language• A sophisticated query optimizer• Data normalization• Joins• Referential integrity• Durability

24 / 40

Are These Drawbacks Worth the Cost?

• Difficult Reporting Data must be brought to the client for analysis, e.g., noaggregates or data analysis functions. Schema-less data requires complex client-side
knowledge for processing• Complex Application Design Without powerful query language and query
optimizer, the client software is responsible for efficiently accessing data and fordata consistency• Durability Administrators are responsible for data retention

25 / 40

When Should NoSQL Be Used?

• Massive write scaling is required, more than a single server can provide• Only simple data access pattern is required• Additional resource allocation for development is acceptable• Strong data retention or transactional guarantees are not required• Unstructured duplicate data that greatly benefits from column compression

26 / 40

When Should Relational Storage Be Used?

• Easy administration• Variable workloads and reporting• Simplified application development• Strong data retention

27 / 40

The Best of Both Worlds: Postgres

Postgres has many NoSQL features without the drawbacks:• Schema-less data types, with sophisticated indexing support• Transactional schema changes with rapid additional and removal of columns• Durability by default, but controllable per-table or per-transaction

28 / 40

Schema-Less Data: JSONBCREATE TABLE customer (id SERIAL, data JSONB);
INSERT INTO customer VALUES (DEFAULT, ’{"name" : "Bill", "age" : 21}’);
SELECT data->’name’ FROM customer WHERE data->‌>’age’ = ’21’;?column?
---------"Bill"

29 / 40

Easy Relational Schema Changes

BEGIN WORK;ALTER TABLE customer ADD COLUMN debt_limit NUMERIC(10,2);
ALTER TABLE customer ADD COLUMN creation_date TIMESTAMP WITH TIME ZONE;ALTER TABLE customer RENAME TO cust;
COMMIT;

30 / 40

3. Data Analytics• SQL• aggregates, GROUPING SETS, ROLLUP, CUBE
• window functions• common table expressions (CTE)• server-side languages, e.g., PL/R

• Performance• optimizer, https://danolivo.substack.com/p/does-postgresql-respond-to-the-challenge• bitmap heap scans• BRIN and bloom indexes• materialized views• just-in-time compilation (JIT)• Large data sets
• data partitioning• tablespaces• parallelism• sharding (in progress)
http://www.slideshare.net/PGExperts/really-big-elephants-postgresql-dw-15833438
http://wiki.postgresql.org/images/3/38/PGDay2009-EN-Datawarehousing_with_PostgreSQL.pdf
31 / 40

Read-Only Replicas for Analytics

Primarypg_wal/Data Warehouse

Network

pg_wal/Tables from multiple clusters can be collected and synchronized on one cluster using
logical replication, and a single table can be broadcast to multiple clusters too.
32 / 40

4. Foreign Data Wrappers (Database Federation)
Foreign data wrappers (SQL MED) allow queries to read and write data to foreign data
sources. Foreign database support includes:• CouchDB• Informix• MongoDB• MySQL• Neo4j• Oracle• Postgres• RedisMany of these, e.g., the Postgres foreign data wrapper, can push down WHERE clauses,
joins, sorts, and aggregates.
http://www.postgresql.org/docs/current/ddl-foreign-data.html
http://wiki.postgresql.org/wiki/Foreign_data_wrappers
33 / 40

Foreign Data Wrappers to Interfaces• JDBC• ODBC• LDAP

34 / 40

Foreign Data Wrappers to Non-Traditional Data Sources• Files• HTTP• AWS S3• Twitter35 / 40

Foreign Data Wrapper Example
CREATE EXTENSION postgres_fdw;
CREATE SERVER postgres_fdw_test
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host ’localhost’, dbname ’fdw_test’);
CREATE USER MAPPING FOR PUBLICSERVER postgres_fdw_test
OPTIONS (password ’’);
CREATE FOREIGN TABLE other_world (greeting TEXT)SERVER postgres_fdw_test
OPTIONS (table_name ’world’);\det
List of foreign tables
Schema | Table | Server--------+-------------+------------------public | other_world | postgres_fdw_test
Foreign Postgres server name in red; foreign table name in blue

36 / 40

Read and Read/Write Data Sources

Postgres

Oracleora_tabmon_tabMongoDB
tw_tabTwitter
37 / 40

5. Postgres Centrality

Postgres can rightly take a central place in the data center with its:
• Object-relation flexibility and extensibility• NoSQL-like workloads• Powerful data analytics capabilities• Access to foreign data sources
No other database has all of these key components.

38 / 40

Postgres’s Central RoleISN

PostGISJSONNoSQL

Extensions

PL/R

Easy DDL

ShardingPostgres

Window Functions

OracleMongoDB

Twitter

Foreign Data
Wrappers

Data
Warehouse

Data Paritioning

Bitmap Scans

39 / 40

Conclusion

https://momjian.us/presentations

https://www.flickr.com/photos/kenny_barker/
40 / 40