- Slides: 21
Postgre. SQL - a discussion for SQL Server Professionals VS.
Postgre. SQL: Some Interesting Facts Ø Year 1989: Postgre. SQL & SQL Server were both initially released in this year! Ø Postgre. SQL didn’t support SQL until 1994 Ø The language used to query data from it was called QUEL Ø Postgre. SQL is fastest growing database in popularity for last 3 years Ø It is indeed the world's most advanced open source database!
Postgre. SQL: SQL Server Equivalents Ø Pages are the basic units for storage – sized 8 K Ø Transaction log files and virtual log files – WAL (Write Ahead Logs) Ø Single instance has multiple databases Ø Multiple instances can exist co-exists, each having multiple databases Ø Databases can have multiple filegroups, called tablespaces Ø Exception: the way they work!
Postgre. SQL: SQL Server Equivalents Ø Transactional concurrency – using 1. 2. 3. 4. 5. MVCC (Multiversion Concurrency Control) Ø An update causes adding newer version and marking old version obsolete Ø Overhead, requiring regulated sweep to delete the old data Ø Avoids low level locks and blocking issues Ø Updates are costlier – imagine an update to an SQL index Insert “Bob” Insert “Marcy” Update “Bob” to “Tom” Insert “Nick” Delete “Marcy” xmin xmax empid ename 1 3 1 Bob 2 5 2 Marcy 3 1 Tom 4 3 Nick xmin: TID that inserts the tuple xmax: TID that removes tuple
Postgre. SQL: SQL Server Equivalents Ø Replication – how is it defined differently? Ø “Build option” is like log shipping – read and replay logs Ø Streaming Replication – close to SQL Server Replication; readonly server fetches transaction logs and replays them Ø Original method - Slony (master to multiple slaves) Ø Bucardo – an asynchronous replication variant, allowing for both multi-master and multi-slave operations Ø Synchronous multi-master, statement based replication middleware and other commercial options
Postgre. SQL: SQL Server Equivalents Ø High Availability options include multiple replication options, log shipping, failover to standby servers, record-based log shipping, clustering and other tools Ø TSQL – a structured and stricter procedural language, pl/pgsql, pl/perl, pl/python, pl/tcl and other addon options for PSQL
Postgre. SQL: Let’s Compare Ø Partitioning methods: partition functions and schema | partitioning by range, list and hash Ø Replication methods: based on Editions | built-in replication Ø In-memory capabilities: Memory Optimized DB | none Ø Procedural options: Stored Procedures | User Defined Functions Ø Reporting & BI: SSRS, Power. BI | 3 rd party tools https: //www. postgresql. org/download/products/5 -reporting-tools/
Postgre. SQL: Why Choose It? Ø Advanced RDBMS features making it an enterprise DBMS: views, functions (procedures), indexes, and triggers in addition to the primary key, foreign key and atomicity features Ø Numerous extension options: new features are distributed freely as it is open-source Ø Interoperability: Linux (all recent distributions), Windows (Win 2000 SP 4 and later), Free. BSD, Open. BSD, Net. BSD, Mac OS X, AIX, HP/UX, IRIX, Solaris, Tru 64 Unix, and Unix. Ware
Postgre. SQL: Why Choose It? Ø CPU architectures: x 86, x 86_64, IA 64, Power. PC/64, S/390, Sparc 64, Alpha, ARM, MIPS/EL, M 68 K, and PA-RISC Ø Security: native SSL support for connections to encrypt client/server communications Ø Security: Row Level Security Ø Security: SE-Postgre. SQL with additional access controls based on SELinux security policies Ø Postgre. SQL has a very strong and active community.
Postgre. SQL: Why Choose It? https: //db-engines. com/en/ranking Ø DBMS ranking: https: //db-engines. com/en/ranking
Postgre. SQL: Why Choose It? Ø How's Postgres fairing: https: //db-engines. com/en/ranking_trend
Postgre. SQL: Should I Migrate? Or, Not? Ø TCO: Total Cost of Ownership is often the key! Ø MSSQL has modest cost of ownership if the database is of significant size, or is used by a significant number of clients Ø MSSQL offers robust management systems and fine support Ø Companies get access to enterprise support and paid knowledge systems Ø The cost of the licensing is not that big of a feature gap; enterprise plugins come at extra bucks
Postgre. SQL: Should I Migrate? Or, Not? Ø ETL solution: SQL Server Integration Solutions Ø Reporting engine: SQL Server Reporting Services and Power. BI Ø Analysis engine: SQL Server Analysis Services Ø Google it! - more solutions than PG Ø Human resources – much more SQL Server resources Ø Easy toolset coverage – e. g. Excel integration Ø A solution based on Linux, Python and Postgres will generally cost more to build and market
Postgre. SQL: Should I Migrate? Or, Not? Ø Comparatively, similar features and professional support costs much lesser for Postgres Ø SQL Server gets preference by users - because they have been using it, and it works! Ø Most enterprise level Postgres based companies still prefer SQL Server or other DBs for critical financial applications Ø BSD-style GPL license may require proprietary modifications to be open-sourced
Postgre. SQL: Should I Migrate? Or, Not? Ø Seemless Active Directory integration provides a definite edge to SQL Server Ø Porting legacy code could increase costs exponentially; new code preferred Ø Postgres performance largely depends on underlying kernel calls and OS scheduler
Postgre. SQL: Where To Start? Ø. http: //www. postgresql. org/ Ø Subscribe to mailing lists, multiple categories: Admins, Users, Developers, Regional, Associations, UGs, Projects, others Ø Useful lists: pgsql-admin, pgsql-advocacy, pgsql-announce, pgsql-bugs, pgsql-docs, pgsql-general, pgsql-odbc. https: //lists. postgresql. org/ Ø Wiki, tips and tricks, techniques and methods: https: //wiki. postgresql. org/wiki/Main_Page
Postgre. SQL: Where To Start? Ø Standard list of tools: https: //wiki. postgresql. org/wiki/Community_Guide _to_Postgre. SQL_GUI_Tools Ø SSMS equivalents: pg. Admin, Adminer, SQL Workbench, DBeaver, Navicat, Omni. DB Ø Admin Tools: https: //severalnines. com/blog/topgui-tools-postgresql https: //www. postgresql. org/download/products/1 administrationdevelopment-tools/
Postgre. SQL: Where To Start? https: //postgres-slack. herokuapp. com/ Ø Slack: https: //postgres-slack. herokuapp. com/ Ø IRC: #postgresql on irc. freenode. net, #pg. Admin, #Post. GIS https: //www. postgresql. org/community/contributors/ Ø Contributors: https: //www. postgresql. org/community/contributors/ https: //planet. postgresql. org/ Ø Latest news: https: //planet. postgresql. org/ Ø Upcoming events: https: //www. postgresql. org/about/events/ Ø PGCon 2019, Postgre. SQL Conference for Users and Developers Ottawa, Canada, 28 -31 May 2019
Postgre. SQL: Where To Start? https: //www. postgresql. org/community/contributors/ Ø Contributors: https: //www. postgresql. org/community/contributors/ Ø Wizards: Dave Page, Tom Lane, Magnus Hagander, Peter Eisentraut, Bruce Momjian bricklen. github. io Ø Local Expert: Bricklen Anderson (bricklen. github. io) Ø Local Enthusiasts: DBA Team @ Plenty. Of. Fish Ø User Groups: TPUG, Montreal, Waterloo, SEAPUG… Vancouver? Ø Companies: EDB, Citus. Data, 2 nd Quadrant, Command Prompt
Still have questions? Ashish Sharma ashish. [email protected] com @DBAshish http: //ca. linkedin. com/in/ashishdbexpert