PostgreSQL - Object Relational Database
Download as PPTX, PDF6 likes3,473 views
Mubashar Iqbal presented on PostgreSQL, an open-source object-relational database system. PostgreSQL prioritizes reliability, security, and standards compliance. It supports Linux, Unix, Windows and is programmed through interfaces like C/C++, Java, .NET, PHP and Python. Common uses include ERP, data warehousing, and network tools. Prominent users include Yahoo, Sony, Reddit, and Skype. Key features include ACID compliance, online backup, point-in-time recovery, and SSL encryption.
![Triggers On DML (Data Manipulation Language) SELECT, INSERT, UPDATE, DELETE CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname ( arguments )](https://image.slidesharecdn.com/jone8hqhqfqsjxvl2dn6-signature-121f724bc7d6e1fe3a8a7869d1412cbf7fd38d7b63fc582313edae159a03c7c4-poli-141216075858-conversion-gate02/85/PostgreSQL-Object-Relational-Database-21-320.jpg)
PostgreSQL - Object Relational Database
- 2. Presenter: Mubashar Iqbal Senior Software Engineer Object Relational Database System
- 4. Fast
- 5. Flexible
- 6. Powerful
- 7. Scalable
- 9. What’s in your mind ?
- 11. Introduction The world's most advanced open source object-relational database system. The open source Oracle. PostgreSQL has a large distributed developer and user community. Community-owned with many companies involved. Supported operating systems ● Linux ● Unix ● Mac OS X ● Solaris ● Windows Native programming interfaces for: C/C++, Java, .Net, Perl, Python, Ruby, PHP
- 12. Development Priorities ● Designed by/for Database Administrators ● Data integrity ● Security ● Reliability ● Standards ● DB Features ● Performance ● Ease-of-use ● Programmer Features
- 13. Most Common Uses ● ERP ● Data Warehouse ● Geographic ● OEM applications ● Network tools ● CRM
- 14. Prominent users ● Yahoo! for web user behavioral analysis, storing two petabytes and claimed to be the largest data warehouse using a heavily modified version of PostgreSQL ● Sony Online multiplayer online games. ● Reddit social news website. ● Skype VoIP application, central business databases. ● Sun xVM, Sun's virtualization and datacenter automation suite. ● MusicBrainz, open online music encyclopedia. ● MyYearbook social networking site. ● Instagram, a popular mobile photo sharing service ● Disqus, an online discussion and commenting service
- 15. Features ● PostgreSQL often described as an open-source version of Oracle. ● BSD/MIT type license ● Reliability is PostgreSQL's top priority. ● Well-engineered, capable of supporting high-transaction and mission-critical applications. ● Comprehensive documentation and manuals available for free online. ● Commercial support is available from independent vendors. ● PostgreSQL is fully ACID compliant. ● PostgreSQL is considered the solemn, full-featured, workhorse for transactional enterprise applications, with strong ACID compliance.
- 16. Features (contd..) ● PostgreSQL supports one storage engine. ● SSL encryption ● Online backup ● Point-in-time recovery: Restore to any time in the past. ● Regular expression
- 17. Tools ● Psql: Command line front-end ● pgAdmin: GUI front-end ● phpPgadmin: Web based front-end ● MS ODBC ● MS Office + Postgres ● NaviCat: $$ ● DeZign: $$ ● EMS SQL Manager for PostgreSQL: $$
- 18. Data Types ● Numeric Types ● Character Types ● Hierarchical Types ● Binary Data Types ● Geometric Types ● Network Address Types ● Text Search Types ● UUID Type ● XML Type ● JSON Type ● Arrays ● Composite Types
- 19. Indexes B-tree: B-trees can handle equality and range queries on data that can be sorted into some ordering (<, <=,=,>=,>) Hash: Hash indexes can only handle simple equality comparisons GIN: GIN indexes are inverted indexes which can handle values that contain more than one key, arrays for example, GIN operator classes for one-dimensional arrays (<@,@>,=,&&) GiST: Generalized Search Tree, it is a tree-structured access method and also known as two-dimensional geometric data types (<@,@>,=,&&,>>,<<,&<,>&,~=)
- 20. Functions A stored procedure and user-defined function is a set of SQL and procedural statements (declarations, assignments, loops, flow-of-control) that stored on the database server and can be invoked using the SQL interface. CREATE FUNCTION function_name(p1 type, p2 type) RETURNS type AS BEGIN -- logic END; LANGUAGE language_name;
- 21. Triggers On DML (Data Manipulation Language) SELECT, INSERT, UPDATE, DELETE CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname ( arguments )
- 22. Cursors ● Used instead of FOR. ● Avoid memory overrun. ● Large data set. DECLARE curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tenk1; OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey; FETCH curs2 INTO foo, bar, baz; CLOSE curs1;
- 23. View View consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query. Views are a great way to simplify your data model. CREATE VIEW table_information AS SELECT * FROM table WHERE id = 123; Now you can simply query your new table directly: SELECT * FROM table_information;
- 24. User-defined objects New types of almost all objects inside the database can be created, including: ● Casts ● Conversions ● Data types ● Domains ● Functions, including aggregate functions and window functions ● Indexes including custom indexes for custom types ● Operators (existing ones can be overloaded) ● Procedural languages
- 25. Replication Methods 1. Master/Slave ● Asynchronous ● Synchronous 2. Multi-Master ● Asynchronous ● Synchronous 3. Proxy 4. Standby system
- 26. Master/Slave Replication Asynchronous Synchronous High availability High availibility Read performance Better read performance Offline peers Worse write performance async M S M S sync
- 27. Multi-Master Replication Asynchronous Synchronous Read performance High availiability Faster access across WANs Read performance Manage offline peers Difficult to get good write performance M M M M async sync
- 29. Comparison of scaling behaviour
- 30. Hierarchical Database Data is organized into a tree like structure. Representing information using parent/child relationships. Each parent can have many children, but each child has only one parent also known as a 1- to-many relationship. Different ways store data like this are • Enumeration path (ltree) • Adjacency List • Nested Sets
- 31. LTree – Label Tree ● Ltree is a PostgreSQL module. ● It is implements a data type ltree for representing labels of data stored in a hierarchical tree-like structure. ● Labels must be less than 256 bytes long. ltree stores a label path. ● A label path is a sequence of zero or more labels separated by dots. ● ltree supports several types of indexes that can speed up the indicated operators. ● Ltree performance is much better when you need to do ad-hoc queries over the tree ● Faster than recursive function that constantly needs to recalculate the branching. ● Some other databases have similar types. SQL Server 2008 has a datatype called HierarchyID which serves the same purpose as ltree but with different syntax.
- 32. Example Technique Adjacency Ltree Query WITH RECURSIVE d AS ( SELECT id FROM sponsorship WHERE id = 799 UNION ALL SELECT s.id FROM d JOIN sponsorship s ON s.parent_fk = d.id ) SELECT * FROM d ORDER BY id LIMIT 100; WITH p AS ( SELECT path FROM sponsorship WHERE id=799 ) SELECT s.id FROM sponsorship s, p WHERE s.path <@ p.path ORDER BY s.id LIMIT 100; Total Runtime 1946.48 ms 28.00 ms
- 33. More Details 1. Value Expression http://www.postgresql.org/docs/9.2/static/sql-expressions.html 2. String Functions and Operators http://www.postgresql.org/docs/9.2/static/functions-string.html 3. Mathematical Functions and Operators http://www.postgresql.org/docs/9.2/static/functions-math.html 4. MySQL vs PostgreSQL http://get.enterprisedb.com/whitepapers/Postgres_Plus_8.4_vs_MySQL_5.5.pdf 5. Scaling Behaviour http://tweakers.net/reviews/657/1/database-test-dual-intel-xeon-5160-introduction.html