Download as PDF, PPTX
Uploaded bybotsplash.com
Migrating to postgresql
This document summarizes a presentation about migrating to PostgreSQL. It discusses PostgreSQL's history and features, including its open source nature, performance, extensibility, and support for JSON, XML, and other data types. It also covers installation, common SQL features, indexing, concurrency control using MVCC, and best practices for optimization. The presentation aims to explain why developers may want to use PostgreSQL as an alternative or complement to other databases.
Migrating to postgresql
- 1. Migrating to Postgresql ForCharlotte SQL Saturday on 12/07/2019 By Ramu Pulipati
- 2. Thank You toour Sponsors
- 3. About Me Cofounder /CTO at Botsplash Software-as-a-service and hosted messaging platform to engage businesses and customers. botsplash.com
- 4. What is Postgresql? •Free & Open Source RDMS • Alternative to Microsoft SQL Server, MySQL, Oracle, Mongo DB or any RDBMS and NoSQL databases • Battle tested for over 30 years with continuous improvements to support modern applications
- 5.
- 6. Why Postgresql? • BSDLicense • Free for Commercial use • No vendor Lock-in • Predictable releases & fixes • Active Community • Great Performance • Extensibility • Hosted options • Professional Services Source: postgresql.org
- 7. Differentiators • Strong SQLTypes • Rich JSON Support / NoSQL • Native Table Partitioning • Full Text Search • Pubsub • GIS / Spatial Functionality • Scalability & Multi-tenancy • Extensibility 80% Commercial Databases Postgresql
- 8. Trends Source: Stackoverflow from IgalSapir slides
- 9. Compare with others •Replaces • Mongo Db • SQL Server • MySQL • Oracle • Complements • Redis • Elasticsearch • Clickhouse Source: NoSQL Performance Benchmark 02/27/2018
- 10. History & What’snew Version Major Feature V12 10/03/2019 JSONB Query & Partition Improvements, Performance V11 10/18/2018 Query Parallelism Improvements Just-in-time compilation V10 10/05/2017 Declarative Table Partitions Logical Replication Improved Monitoring and Control V9.6 09/29/2016 Parallel Query Foreign Data Wrappers Replication V9.5 07/01/2016 JSONB Modifying Operators Row level security UPSERT statement V9.4 12/18/2014 Introduce JSONB Materialized View Improvements Source: Postgresql Releases
- 11. Getting Started • Downloadand Install • Postgres 11+ supports Windows 2012 R2 deployment. • Cloud hosting options • Client • pgAdmin • psql command line
- 12. SQL Support • CaseSensitive Schema Names • Case Sensitive Data Storage • Type casting & Operators • Date Time formatting • Common Table Expressions • Array Data Types • Upserts • Stored Procedures • Multiple languages support
- 13. Common Data Types DataType Commonly Used Names Number Smallint, integer, Bigint, decimal, Money Number (auto) Serial, BigSerial Enum Enum data types stored Character Char, nchar, Text Binary Bytea Date Time Timestamp with timezone, Timestamp without Timezone, interval Collections Array, JSON, JSONB Misc Boolean, UUID, Geo Types, Network Address Mac Address, Custom Types Full Text Search TsVector, TsQuery See details from tutorials point
- 14. Special Data Types •Collections • Arrays • Range Types • Searchable • Indexable • Aggregate functions Credit: Igal Sapir slides
- 15. JSON Datatypes • Equivalentto embedded MongoDB Credit: postgresql documentation Notable Functions Description ::JSONB Casting || or JSONB_SET or JSONB_BUILD_OBJECT Build or set JSONB values JSONB_AGG & JSONB_BUILD_ARRAY Aggregate functions JSONB_PATH_*, JSONB_EACH, JSONB_EXTRACT Path & Extract Functions JSONB DEMO
- 16. Advanced Data Types •Pivot Tables crosstab function • Materialized Views • HStore • EXPLAIN ANALYZE • Pg_stat • XML Columns (not much used)
- 17. Concurrency - MVCC •Postgres uses “Multi-version concurrency control”. • Isolation at “read-committed” level • Row level locks are explicit • Same technique used in Oracle, CouchDB, etc
- 18. Indexes • Index Support •Single Column / Exact Matches • Multi Column • Unique • Partial Indexes • Expression Indexes • Implicit Indexes (PK, UK) Credit: Igal Sapir slides
- 19. Disadvantages / Limits •Database only solution • Few available tools compared to commercial • Limited Talent Pool Source: https://www.youtube.com/watch?v=6p2TNPabt6M Source: Postgres limits from sreenstepslive.com
- 20. Best Practices • PerformanceOptimization • Do not read from Database at all • Use indexes efficiently • Use pg_stats collector to monitor usage and performance • Vacuum regularly to clean up storage • Bench mark hardware and optimize pg parameters • Always use SSL for connectivity in untrusted networks
- 21. Reasons to FillOut Session Evaluations Helps Speakers Helps Organizers Helps You!
- 22. References and Followup • Postgresql Documentatoin • Igal Sapir Postgresql Presentation • Introduction to Postgresql slideshare • The Internals of Postgresql • Postgresql excercises • MVCC: postgres internals or interdb Questions? Contact me at ramu@botsplash.com