günümüzde postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit...
TRANSCRIPT
![Page 1: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/1.jpg)
GünümüzdePostgres
Modern, Ölçeklenebilir Uygulamalar
Utku Azman | Citus Data |PGDay Istanbul | @citusdata | citusdata.com
![Page 2: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/2.jpg)
Veritabanlarının basit olduğu zamanlar(2000’lere kadar)
2
(OLAP)Workloads
Proprietary
Open Source
OperationsAnalytics(OLTP)
RDBMS
![Page 3: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/3.jpg)
Data Growth >> Silicon Growth…
Data 2x every 15 mo
Moore’s Law 2x every 24 mo
Data with less structure1 2
LOG
Bugün yapısal veritabanlarını zorlayan ikigelişme…
3
![Page 4: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/4.jpg)
Postgres’in geçmişinden…Bu zorluklar yeni değil
4
• SQL or not? (1995)
• Post-Ingres• Started life as object store• Added SQL API in 1995
1
2• Scaling out to handle data growth (2005)
• For analytics only: MPPs
• So many forks! AsterData, Netezza, ParAccel (Redshift), Greenplum
![Page 5: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/5.jpg)
5
Bugün ise Postgres bu zorluklara karşı çokdaha güçlü - SQL Extension APIS (2011)
Planner
Executor
Custom scan
Commit / abort
Access methods
Foreign tables
Functions
...
...
...
.........
...
Extension (.so)PostgreSQL
CREATE EXTENSION ...
![Page 6: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/6.jpg)
RDBMS ile günümüz ihtiyaçlarınacevap verme
Yapısal ve yapısal olmayan veri
Ölçeklenme—işleme & performans
6
1
2
![Page 7: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/7.jpg)
7
Dosya sistemindenbaşlama
(Hadoop)
(-) Pay cost at query time
(-) Batch vs. real-time
(-) Indexes (Append only FS)
(+) Any data, any structure
(+) ’Infinitely’ scalable storage
(+) Write fast
![Page 8: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/8.jpg)
8
Tek bir erişim şeklinidestekleme
(-) No expressiveness for analytics
(-) No JOINS, data duplication
(-) Enforce structure at app layer
Semi-structured (JSON)
(+) Simple: Put & Get
(+) Scalable
![Page 9: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/9.jpg)
9 Umur Cubukcu | Strata Data Conference | March 2018 | The State of Postgres
Table "public.events"Column | Type | Sample Data
------------------------------------------------------------------user_id | bigint | 09288created_at | timestamp | 2018-03-08 00:57:12.6936+00payload | jsonb |
Veritabanını JSON data tipiyle genişletme
Yapısal ve Yapısal olmayan veri1
![Page 10: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/10.jpg)
B-tree indexes
GIN & GiST indexes
Secondary indexes
Full text search
Index-only scans
Fitting indexes into memory
+
Not to forget: Parallel queries, MVCC, and many more.
İndex ve diğer temel performans araçları
Ölçeklenme – İşleme ve performans2
Umur Cubukcu | Strata Data Conference | March 2018 | The State of Postgres10
![Page 11: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/11.jpg)
11
SELECT FROM events a JOIN users b
SELECT FROM (a JOIN b)
SELECT FROM (a JOIN b)
Data Node 1
events
Events_101Events_103
SELECT FROM (a JOIN b)
SELECT FROM (a JOIN b)
Data Node 2
Data Node N
.
.
.
.
.
.
Users_101Users_103
…
users
Ölçeklenme – İşleme ve performans2
Events_104Events_102 Users_102
Users_104
İşlem (ve join)leri birden fazla PostgreSQL instances’a gönderme
![Page 12: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/12.jpg)
12
Postgres’e yatay ölçeklenme için eklenti: Citus
![Page 13: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/13.jpg)
PostgreSQL: Dinamik ve global ekosistem
cituspgcryptopg_cron
pg_partmanpostgresql-HLLcstore_fdwunaccentcube
jdbc_fdwpg_trgmPostGIS
…
Sample PostgreSQL Extensions Integrationspg_buffercachepg_prewarmbtree_ginbtree_gist
postgis_topologypg_stat_statementspostgresql-unit
plpgsqlplv8
pg_telemetryforeign data wrappers
…
13
![Page 14: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/14.jpg)
PostgreSQL’in artan momentumu
PostgreSQL
MySQLMongoDB
SQL Server + Oracle
Source: % database job postings that mention each specific technology, across 20K+ job posts on Hacker News, https://news.ycombinator.com
Database adoption among developers1
14
![Page 15: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/15.jpg)
Source: Google Trends for the past 2 years
Winning Startups & Enterprises
0
10
20
30
40
50
60
70
80
90
100
PG Mongo Hadoop
PostgreSQL popularity = Hadoop + Mongo combined
…ve büyüyen kullanıcı kitlesi
15
![Page 16: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/16.jpg)
Postgres’in uzun soluklu yürüyüşü
16
Günümüz uygulamalarında Postgres stack’in neresinde?
![Page 17: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/17.jpg)
Modern iş yükleri de evrim geçiriyor…
17
(OLAP)Workloads
Proprietary
Open Source
OperationsAnalytics(OLTP)
RDBMSImprovement
workloadsApplication workloads
- Transactions- Short-requests- In-app analytics
![Page 18: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/18.jpg)
Modern veritabanlarının desteklediği 3 tip uygulama
18
Time to action
Dat
a vo
lum
e
Application data
Systems of record• Core workloads, transactions• Real-time data• Millisecond latencies
Systems of engagement• Drive engagement & revenue• Real-time data, multiple sources• Sub-second latencies
Systems of improvement• Identify business process improvements• Offline data, multiple sources• Sub-minute / hour latencies, data analysts
1
3
2
![Page 19: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/19.jpg)
Altyapınızda PostgreSQL’in yeri
19
PostgreSQL
Note: Standard PostgreSQL connectors for all tools (e.g. ODBC / JDBC, PostgreSQL language bindings) available for integrations.
Application
• Standalone database• Storage• Compute
Data
Spark
HDFS / S3
• Persistence layer for Spark
• Persistence layer for Kafka
Kafka
NoS
QL
• Adjacent to NoSQL
![Page 20: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/20.jpg)
Özetle: PostgreSQL
20
Eklentiler
Esneklik
Ekosistem
![Page 21: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/21.jpg)
Ölçeklenme – Video demo
![Page 22: Günümüzde Postgres · 2018. 9. 28. · postgis_topology pg_stat_statements postgresql-unit plpgsql plv8 pg_telemetry foreign data wrappers](https://reader035.vdocuments.us/reader035/viewer/2022071513/6133bafddfd10f4dd73b46ff/html5/thumbnails/22.jpg)
Teşekkürlercitusdata.com/jobs
@citusdata
github.com/citusdata/citus