Download as PDF, PPTX
Uploaded byPostgresOpen
Gurjeet Singh - How Postgres is Different From (Better Tha) Your RDBMS @ Postgres Open
Postgres is an open source relational database management system that provides developers and managers with several advantages over commercial RDBMSs. For developers, Postgres is highly reliable, secure, and extensible due to its liberal license, wide range of built-in features, and support for custom data types and functions. For managers, Postgres offers zero acquisition costs due to its free license, lower maintenance costs compared to commercial databases, and support from many consulting vendors.
Gurjeet Singh - How Postgres is Different From (Better Tha) Your RDBMS @ Postgres Open
- 2. POSTGRES IS DIFFERENTFROM (BETTER THAN) YOUR RDBMS
- 3. Gurjeet Singh gurjeet.singh.im EDB -EnterpriseDB.com
- 4. FROM THE PERSPECTIVEOF A Developer Manager
- 5.
- 6. DEVELOPER : LICENSE BSD-likeLicense Liberal than/Very different from GPL. PostgreSQL License gives you Freedom to read, patch/improve the code. An opportunity to understand the database internals Install, use/develop and deploy full feature-set without a license Upgrade hardware without paying for license cost Unlike commercial RDBMSs where licenses come in the way of development, QA, and hardware upgrades.
- 7. DEVELOPER : RELIABILITY ACIDcompliant Constraints (Primary Key, Foreign Key, CHECK) Data-page checksums to detect corruption
- 8. DEVELOPER : SECURITY Per-objectGRANT/REVOKE permissions Per-column GRANT/REVOKE permissions SSL connections LDAP and RADIUS authentication
- 9. DEVELOPER : FEATURES Postgresis a Platform Vast feature set ... and ever-expanding Postgres Feature Matrix http://www.postgresql.org/about/featurematrix/
- 10. DEVELOPER : FEATURES CommonTable Expressions (CTEs) Similar to Macro in programming languages Supports recursive evaluation (alternative to Oracle's CONNECT BY)
- 11. DEVELOPER : FEATURES: DATA TYPES Many advanced data types come builtin Full List of Builtin Data Types http://www.postgresql.org/docs/9.3/static/datatype.html
- 12. DEVELOPER : FEATURES: DATA TYPES Does not have/need NVARCHAR data type The CHAR/VARCHAR/TEXT types are capable of storing Unicode data
- 13. DEVELOPER : FEATURES: DATA TYPES Boolean (a first-class data type) Bit (and Bit strings) Money Bytea (binary data) Interval (difference between TIMESTAMPs) Enumerated Types Geometry Types (Point, Line Segment, Polygon, ...) Network Address (inet, cidr, macaddr)
- 14. DEVELOPER : FEATURES: DATA TYPES Range Types (tsrange, daterange, int4range, ...) Composite types Builtin multi-dimensional arrays Automatically defines array type of builtin and user-defined data types
- 15. DEVELOPER : FEATURES: DATA TYPES hstore JSON XML
- 16. DEVELOPER : FEATURES StreamingReplication Hot Standby Per-transaction synchronous replication True serializable transactions LISTEN/NOTIFY Triggers Exclusion constraints Window Functions Function overloading Operator overloading Full-text search Large Objects up to 4TB Materialized views
- 17. DEVELOPER : FEATURES Createprocedures in various programming languages PL/pgsql PL/perl PL/tcl PL/java PL/v8 - JavaScript
- 18. DEVELOPER : FEATURE/PERFORMANCE TOAST TheOversized Attribute Storage Technique Automatic out-of-line storage and automatic compression
- 19. DEVELOPER : PERFORMANCE Cost-basedoptimizer Index-only scans Synchronized seq-scans Table partitioning Unlogged tables
- 20. DEVELOPER : PERFORMANCE Partialindexes Many types of indexes BTree Hash Gin Gist KNN SP-Gist
- 21. DEVELOPER : PERFORMANCE Typesof join-strategies Nested-loop joins Hash joins Sort-Merge joins Bitmap-index scan; Bitmap-And/Or joins (in-memory) Can use multiple indexes of a table in the same scan Semi joins Anti joins
- 22. DEVELOPER : PERFORMANCE Fasterthan NoSQL (at NoSQL use case) See slides 32 onwards of Chrisophe Pettus' PostgreSQL as a Schemaless Database http://thebuild.com/presentations/pg-as-nosql-pgday- fosdem-2013.pdf
- 23. DEVELOPER : DIAGNOSTICS Per-functionstatistics Performance Views pg_stat_activity pg_locks pg_stat_* pg_stat_all_tables pg_stat_all_indexes ...
- 24. DEVELOPER : DIAGNOSTICS LoggingOptions log_min_duration_statement log_temp_files DTrace/SystemTap support Scripts to record and diagnose performance issues
- 25. DEVELOPER : EXTENSIBILITY HighlyExtensible Create Your Own Data Types Operators Index Types create extension Check out Foreign Data Wrapper Foreign Tables PGXN.org
- 26. DEVELOPER : EXTENSIBILITY Examples PostGIS PostGISadds support for geographic objects to the PostgreSQL object-relational database. PostgreSQL-HLL A PostgreSQL extension adding HyperLogLog data structures as a native data type
- 27. MANAGER'S PERSPECTIVE Acquisition Cost(License) Future Proof Vendors/Providers (Companies who can help) Maintenance Cost (Support) Buy-in From Upper Management
- 28. MANAGER : ACQUISITIONCOST Free
- 29. MANAGER : ACQUISITIONCOST FREE Save Money $$$ Higher Profit Margins A Leg Up On The Competition
- 30. MANAGER : ACQUISITIONCOST BSD-like License Liberal than/Very different from GPL. Install, use/develop and deploy full feature-set without a license Upgrade hardware without paying for license cost Unlike commercial RDBMSs where licenses come in the way of development, QA, and hardware upgrades.
- 31. MANAGER : FUTUREPROOF Community-driven No single commercial entity controls it (unlike MySQL) Will be always free (cost and project management)
- 32. MANAGER : FUTUREPROOF Ever-increasing resource-pool; developers and DBAs Compare growth in job requirements
- 33. MANAGER : VENDORS/PROVIDERS Manytop-quality vendors EDB (EnterpriseDB) PG Experts 2nd Quadrant OmniTI Command Prompt Many smaller consulting teams/individuals
- 34. MANAGER : MAINTENANCECOST 24/7 Support available Very affordable support contracts For e.g. EDB charges per-socket (unlike Oracle, that charges per CPU)
- 35. MANAGER : MAINTENANCECOST Consulting Remote-DBA Database Health Checks Training For Developers For DBAs
- 36. MANAGER : UPPERMANAGEMENT BUY-IN Show them the last few slides :)
- 37.