Downloaded 21 times
Uploaded byEDB
PostgreSQL as a Strategic Tool
The document discusses the strategic advantages of PostgreSQL and the role of EDB in enhancing its adoption and capabilities. It emphasizes best practices for leveraging PostgreSQL in enterprise applications, including collaboration, standardization, and automation. The document also presents EDB's contributions to PostgreSQL, its prominence in the open-source database market, and provides insights into effective deployment strategies.
PostgreSQL as a Strategic Tool
- 1. PostgreSQL as a StrategicTool Marc Linster, CTO September 23, 2020
- 2. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.2 • Slides and recording will be available in next 24 hours • Submit questions via Zoom Q&A – will be answering at end Welcome – Housekeeping Items
- 3. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.3 Agenda • Who is EDB • Practices to help drive PostgreSQL adoption • Understand the application landscape and the fit for PostgreSQL • Understand how to eat the elephant - one byte at a time • Foster collaboration to drive adoption • Use standardization to enable automation
- 4.
- 5. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.5 EDB supercharges PostgreSQL Privately Held Major PostgreSQL community leader Over 4000 customers The largest provider of open-source PostgreSQL based software, services and support Founded in 2004 Over 10 years of consecutive quarterly subscription growth 400+ employees 16 offices worldwide Recognised leader in Relational Database Management Systems (RDBMS) by both Gartner and Forrester
- 6. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.6 • Enterprise PostgreSQL innovations • 4,000+ global customers • Recognized by Gartner Magic Quadrant for 7 years in a row • PostgreSQL community leadership 2019 Challengers Leaders Niche Players Visionaries Abilitytoexecute Completeness of vision 1986 The Design of PostgreSQL 1996 Birth of PostgreSQL 2004 EDB is founded 2020 TodayMaterialized Views Parallel Query JIT Compilation Heap Only Tuples (HOT) Serializable Parallel Query We’re database fanatics who care deeply about PostgreSQL Expertise
- 7. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.7 EDB Postgres Advanced Server Feature Highlights EDB Feature Contributions to PostgreSQL Core PostgreSQL Feature Highlights v12 • Compound Triggers (OC) • Interval Partitioning (OC) • Aggregate Functions: Median & ListAgg (OC) • CAST(Multiset) (OC) • Six Additional Schema Views (OC) • Pluggable Table Storage Capability • WIP: Zheap Storage Engine Providing Better Control Over Bloat • ATTACH PARTITION • SERIALIZABLE for Parallel Query • SQL/JSON Path Expression Support • Optimized Common Table Expressions (CTE) • Improved B-Tree Indexing • Stored Generated Columns • Security Improvements (LDAP and GSSAPI) v11 • Data Redaction (OC) • Autonomous transactions (OC) • Performance Analytics • Procedure Overloading • Just-in-time (JIT) compilation using LLVM • Multiple Parallelism Improvements (e.g.Hash Joins) • Automatic Cache Pre-warm at Start-up • Partitioned Table Improvements • Performance Improvements for Very Large DBs • SQL Stored Procedures with BEGIN, COMMIT / ROLLBACK • Window Functions Support for SQL:2011 Standard v10 • Re-direct Audit Log Records to syslog • EDB Clone Schema • Automatic Cache Pre-warm at Start-up • Customizable WAL Segment Size • Parallel Query (index, bitmap scans & merge joins) • Durable Hash Indexes • Postgres Foreign Data Wrapper Push Down of Joins and Aggregates • Trigger Transition Tables • Logical Replication • Declarative Table Partitioning • Quorum Commit for Synchronous Replication • SCRAM-SHA-256 authentication v9.6 • Advanced Queuing (DBMS_AQ) (OC) • Nested Sub-Procedures (OC) • Partitioned Table Improvements • EDBLoader Enhancements (OC) • Parallel Hint Support (OC) • Parallel Sequential Scans, Joins, and Aggregates • Synchronous Replication Support for 2+ Standbys • Multiple Performance Improvements • Avoid Repetitive Autovacuum • Full-text Search for Phrases • Remote Joins, Sorts, Updates in postgres_fdw • Cube Extension kNN support • Index-only scans for partial indexes OC = Oracle Compatible and usable for non-Oracle applications Proven Leadership...Strongest Contributions to PostgreSQL
- 8. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.8 Core team Major contributors Contributors Connected…EDB is Community Connected Named EDB open source committers and contributors Akshay Joshi Amul Sul Ashesh Vashi Ashutosh Sharma Jeevan Chalke Dilip Kumar Jeevan Ladhe Mithun Cy Rushabh Lathia Amit Khandekar Amit Langote Devrim Gündüz Robert Haas Bruce Momjian Dave Page Designates PostgreSQL committers
- 9. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.9 Quick Deploy Solution Design Migration Assessment Migration Assistance PostgreSQL Strategy Security Assessment Enterprise Architecture EDB Postgres Advanced ServerPostgreSQL EDB Postgres Tools* PostgreSQL Optimization Enterprise Strategy Custom ServicesGetting Started Cloud DBA Service Remote DBA Service Technical Account Manager PostgreSQL Technical Support Software Support Design and Build Services EDB Software, Support, and Services * Postgres Enterprise Manager, Backup and Recovery Tool, Failover Manager, Replication Server, Containers, Kubernetes Operator, PostGIS, Pgpool, PgBouncer, Connectors, Foreign Data Wrappers, Migration Toolkit Performance Tuning Automation Services Monitoring Best Practices Backup Best Practices Implementation Embedded Expert Training
- 10. What is the challenge? •How to get the most mileage out of the best open source relational database • How do leaders do it? • Where do they start? • How do they drive the change?
- 11. Proven Practices ● Understand theapplication landscape and the fit for PostgreSQL ● Understand how to eat the elephant - one byte at a time ● Foster collaboration to drive adoption ● Use standardization to enable automation
- 12. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.12 Open Source wins in the RDBMS space By 2022, more than 70% of new in-house applications will be developed on an OSDBMS or OSDBMS-based dbPaaS, and 50% of existing proprietary RDBMS instances will have been converted or be in process of converting. By 2022, more than 60% of all global organizations will formalize their management of internal skills and standards related to OSDBMS to enable better use of open source in production. Gartner Group - State of the Open-Source DBMS Market, 2019
- 13. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.13 PostgreSQL won If you bet… you bet on PostgreSQL Most Loved DatabaseMost Commonly Used Database Postgres Popularity Source: Stack Overflow Developer Survey, 2019 Source: DB-Engines.com, 2020
- 14. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.14 Postgres is second most popular image in containers (after Nginx) Source: Datadog, Nov 2019, https://www.datadoghq.com/container-report/
- 15. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.15 100 TB+; read- only; relational +; multiple topics; rel. Integrity not relevant ; data scientist focus Hadoop, Cassandra Data volume and velocity Where PostgreSQL Shines Data Lake Data Warehouse Data Mart ODS System of Record System of Engagement Edge Computing Mobile Computing 50TB+; read only; relational; star/cube/mat. view; periodic refresh; multiple topics; analyst focused 10TB+; read only; relational; star/cube/mat. view; periodic refresh; single topic; citizen analyst focus 10TB+; read only; relational only; normalized data model; concurrent refresh; citizen analyst focused 1-20TB; read/write; 90% relational; ACID; ERP/CRM, Line of business focused < 2TB; read/write; relational + doc + GIS; ACID or BASE; micro services; website and social media focus < 2TB; read/write; relational + doc + GIS; ACID or BASE; micro services; M2M, IoT focus < 1GB; read/write; relational + doc + GIS; single user; micro services IoT, mobile app focus Redshift, Snowflake, Teradata, Exadata, BigQuery Exadata, Oracle DB, SQL Server, Postgres Oracle, SQL Server, Postgres Oracle, SQL Server, Postgres MongoDB, MySQL, Redis, Postgres, MySQL, Postgres SQLite, Realm Innovation; cost; compatibility; no vendor lock in; Multi-model; innovation; cost; Available Evrywhr Hardware acceleration; MPP; S3 integration Hardware failover Native write scalability No single user footprint; no replication from/to mobile Multi-model; innovation; cost; Available Evrywhr Analytics capab.; compatibility; heterogeneous rep. Analytics capab.; compatibility; ease of integration. Description Key players PostgreSQL Strengths PostgreSQL Limitations
- 16. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.16 Eat the elephant - one byte at a time Temptation: ● Go for major impact ● Address the worst legacy problem first Recipe for success: ● Tier your applications ● Develop reference architectures ● Understand your maturity ● Find the balance between feasible and meaningful
- 17. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.17 Sample Tiering Framework RPO: 24 hours RTO: 24 hours GRO: NA DB Failover time: NA An. maint. window: 24 hours SLA: 99.5% RPO: 1 min RTO: <1 min (Failover) 6 hours (PITR) GRO: NA DB Failover time: <1 min An. maint. window: 6 hours SLA: 99.99% RPO: No data loss RTO: <1 min (Failover) < 3 hours (PITR) GRO: 1 min DB Failover time: <1 min An. maint. window: 4 hours SLA: 99.999% RPO: No data loss RTO: <1 min (Failover) < 3 hours (PITR) GRO: 1 min DB Failover time: <1 min An. maint. window: 4 hours SLA: 99.999% RPO: No data loss RTO: <1 min (Failover) < 3 hours (PITR) GRO: 1 min DB Failover time: <1 min An. maint. window: 4 hours SLA: 99.999%Departmental Departmental Mission Critical Enterprise Mission Critical Enterprise Mission Critical High Volume Enterprise Mission Critical High Volume & High Scale < 1TB; < 500 TPS; < 100 users < 1TB; < 500 TPS; < 100 users < 5TB; < 5000 TPS; < 1000 users < 5TB; < 10,000 TPS; < 1000 users < 20TB; < 10,000 TPS; > 1000 users
- 18. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.18 Reference Architectures ● CPU, Memory, Storage ● Local, metro, geo-distributed ● Replicas for redundancy, read- scalability or both ● Streaming replication, logical replication or both? ● Connection pooling, query routing, or both? ● SLA, RPO, RTO, GRO
- 19. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.19 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.19 Emerging • Getting started with PostgreSQL • Often for a single project • For test/dev apps Strategic • PostgreSQL considered a “standard” • Many production apps incl. tier 1 • Concerns for scalability, HA, and automation Expanding • Increasingly for production apps • Tools and automation important • Worries about database security Your PostgreSQL journey PostgreSQL Maturity Where are you?
- 20. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.20 Collaboration and evangelizing Problem Evangelizing Enablement Successful open source adoption benefits from organizational change ● DBA teams and development teams are often at arms length ● “The infrastructure is ready, but nobody uses it” ● “We would build on Postgres, but the DBAs can’t handle it” ● Create a bottom-up innovation movement ● Informal discussions, lunch & learns, hackathons, meetups ● Create DevOps culture - break down the walls between developers and DBAs ● Make sure DBAs are ready to support production ● Consider Remote DBA Services ● Invest in automation to be responsive
- 21. Automation and standardization • Opensource == agility • Agility == speed and scale • There is no scalability without automation • Automation requires standardization
- 22. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.22 Postgres Deployment Automation Technology Decision Choices • Terraform • CloudFormation • Ansible • Chef • Puppet • Salt • Configuration Management Vs Provisioning • Procedural Vs Declarative • With local install or not • Mutable Infrastructure Vs Immutable Infrastructure • Agent Vs Agentless Factors to Consider
- 23. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.23 Deployment Tool Survey (June 2020)
- 24. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.24 EDB Postgres on Ansible Galaxy
- 25. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.25 Recommended Practices • Understand the application landscape and the fit for PostgreSQL • Understand how to eat the elephant - one byte at a time • Foster collaboration to drive adoption • Use standardization to enable automation
- 26. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.26 Resources Thank You Next Webinar: October 7 Overcoming write availability challenges of PostgreSQL The EDB Website Postgres A Strategic Weapon Reference Architectures Ansible Galaxy Postgres Roles