Database / Open Source Database / PostgreSQL
PostgreSQL
Why PostgreSQL
PostgreSQL is “The world’s most advanced Open Source Database Management System” developed by a worldwide team of Community. PostgreSQL is free of cost and it is not controlled by any private entity. It’s possible for anyone to use and modify the software. Anybody can download the Open Source Software and use it without any license cost and if needed the source code can be changed as per the business needs.
The development community members are spread across the world and all world class powerful mind’s continuous customer centric efforts lead PostgreSQL to compete with other proprietary database software vendors in the market resulting great increase in PostgreSQL deployments worldwide because of its advanced features keeping with its pride “The world’s most advanced open source database management system”.
Features in PostgreSQL 14
- Major improvements to partitioning
- Improvements to parallelism
- Just-In-Time (JIT) compilation introduced
- Acid compliant
- Window functions fully support SQL:2011 standard
- Channel binding for scram authentication
- Covering indexes for b-trees (include)
- Advance vacuum & maintenance features
- Reindexing concurrently
Features in PostgreSQL 15
- Logical replication.
- Declarative partitioning.
- Usable hash indexes.
- Cross-column optimizer statistics.
- Parallel snapshots on replicas.
- Better tuning of parallel processing worker behaviour.
- Contri extension for checking validity of B-Tree Indexes.
- Temporary replication slots for safer replica building(per session).
- Significant general performance improvements.
- Stronger password authentication based on scram-sha-256
- Improved monitoring and control.
- Support for primary key, foreign key, indexes, and triggers on partitioned tables.
New Useful Extensions
- Pg_visibility
- Pgaudit
- Pgbadger-9.0
- Open postgresql monitoring(opm)
- Pg_partman & more
Development Life Cycle
- 24 releases since 1995
- Faster release cycles in the last 5 years
- Software release life cycle – 1+ alpha release, 1+ beta release, release candidate
- Stable version release happens in 3rd quarter
- Stakeholders: sponsors, professionals, researchers
- Maximum maintenance PostgreSQL release life cycle in community – 5 years
Open Source Community
- Sense of community
- Developers, users
- Volunteers and professionals
- Great members support from all over the world
License
- Liberal open source license
- Applies to every part (module, product, option) of postgresql
- Permission to copy, modify, distribute and documents without fee.
- Completely 100% open source
- No monopoly
- No fear of acquisitions
- No vender locking
Quality
- Written in c (multi-platform)
- Portable
- Solid foundations
- ANSI/ISO standards
- Acid Properties
- SQL complaint
- Community development
- MVCC
- Documentations and pdf’s are available.
Business Continuity
- Reliability and Data Integrity
- Scalable
- Durability
- Security with advanced options
- Data safety – Backup/Recovery
- Recovery point objective & recovery time objective
- Database replication
- High availability
- Disaster site builds
Security and Data Protection
- Secure tcp/ip connections using ssl
- Cryptographic functions
- Group and user permissions
- Multi-layer security model
- No monopoly
- Column and row level security
- Security-enhanced Linux
External Tools and Libraries
- Gis: postgis, pgrouting
- Admin: pgadmin
- Continuity & scalability: pl/proxy, pgpool, pgbouncer, pgbadger
- Third party tools
- Open source tools
- Commercial tools
Performance and Scalability
- Faster data execution
- Response time & throughput
- I/O access
- Indexes
- Partitioning
- Tablespaces
- Warm/hot standby
- Data/query
- Replication
- Disaster site
- onnection pooling
- Load balancing
Support & Services
- Many entities exists in the market to provide support and services for postgresql
- No fear/worries about support/services
- Onsite and remote dba services available
- Migration experts available to save huge costs.
