Skip to main content

PostgreSQL 9.1 Vs 9.2 vs 9.3 - Part2

In last post I explained PostgreSQL 9.1 Vs 9.2.   In this post I will explain 9.3.

PostgreSQL 9.1 Vs 9.3
I)     Stability: PostgreSQL 9.3 released on 9th Sept 2013.
II)    Performance Improvement:
  • Collect and use histograms of upper and lower bounds, as well as range lengths, for range types 
  • Improve optimizer's cost estimation for index access
  • Improve optimizer's hash table size estimate for doing DISTINCT via hash aggregation
  • Suppress no-op Result and Limit plan nodes 
  • Reduce optimizer overhead by not keeping plans on the basis of cheap start-up cost when the optimizer only cares about total cost overall
III)    Other Key New Features:
  • Streaming-Only Remastering:
    "Remastering" is the process whereby a replica in a set of replicas becomes the new master for all of the other replicas. For example:
    1. Master M1 is replicating to replicas R1, R2 and R3.
    2. Master M1 needs to be taken down for a hardware upgrade.
    3. The DBA promotes R1 to be the master.
    4. R2 and R3 are reconfigured & restarted, and now replicate from R1
In prior versions of PostgreSQL, remastering required using WAL file archiving. Cascading replicas could not switch masters using streaming alone; they would have to be re-cloned. That restriction has now been lifted, allowing remastering from just the stream. This makes it much easier to set up large replication clusters; administrators no longer have to set up an online WAL archive if they don't need one for disaster recovery.
  • Allows replicas to be promoted in less than a second, permitting 99.999% up time.
  • Add optional ability to checksum data pages and report corruption
 IV) Compatibility:
No negative/breaking Changes been done in on 9.3 over 9.2, as per 9.3 release notes.
V) Cost of Upgreadation: 
Conclusion:
After going over the release notes of protgreSQL 9.2 and 9.3, following are my recommendations:
1. 9.2 has significant performance improvement on optimizer side, with few MUST HAVE features.
2. pg_stat_activity and peripheral views has structural changes, so need to evaluate monitoring tools stability.
9.1 to 9.2 has significant performance changes and must be upgraded.
With 9.3 most of the changes are done on Admin utility and replication areas. Not significant changes on optimizer side.
Direct 9.1 to 9.3 migration could also be considered.
I have explain the process of upgrading from PostgreSQL9.1 to 9.3 at  Upgrade PostgreSQL 9.1 to 9.3 - Step by Step

Reference:

Comments

Post a Comment

Popular posts from this blog

Drop all Objects from Schema In Postgres

To Drop all objects from Postgres Schema there could be following two approaches: Drop Schema with cascade all and re-create it again.  In some cases where you dont want to/not allowed to drop and recreate schema, its easy to look for objects on current schema and drop them. following script would help to do so, Create function which would do the task and then drop that function too. --- CREATE OR REPLACE FUNCTION drop_DB_objects() RETURNS VOID AS $$ DECLARE  rd_object RECORD; v_idx_statement VARCHAR(500);   BEGIN ---1. Dropping all stored functions RAISE NOTICE '%', 'Dropping all stored functions...'; FOR rd_object IN ( SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) as functionDef     FROM pg_proc p     INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)    WHERE ns.nspname = current_schema      AND p.proname <...
2 comments
My Other Post >>

Distributed transaction in Oracle ( Over Oracle DBLink)

To fetch the data from one server to and other Oracle server over DBLink, I experienced the following facts of Oracle Distributed transactions: Security issue: -           We cannot create Public synonym for the remote object accessed over Private DBLink of other Database.   -           It’s allowed to create private synonym for remote object, but you cannot grant the access over this synonym to any other schema. If you try to provide the grants to other schema Oracle raises an error:              [ORA-02021: DDL operations are not allowed on a remote database] “In an all you can access remote objects over private DBLink in the same schema where DBLink is created”. Fetching the Ref Cursor at Remote site:                   Let’s say we have two site...
1 comment
My Other Post >>

Vacuum Analyze Full Schema in PostgreSQL Database

To analyze full database schema, you can use following shell script : -------------------------------------------------------------------------------------------------------------------------- #!/bin/sh rm -rf /tmp/vacuum_analyze_MyData.sql dbname="mydata" username="mydatadatamart" namespace="mydatadatamart" # Vacuum only those tables which are fragmented over 30% # Analyze tables only if they are not already # Process CMF and Groups Master table every time as they are master Tables=`psql $dbname $username << EOF SELECT 'VACUUM ANALYZE VERBOSE ' || tablename || ' ;' AS TableList   FROM ( SELECT *,       n_dead_tup > av_threshold AS "av_needed",       CASE WHEN reltuples > 0      THEN round(100.0 * n_dead_tup / (reltuples))       ELSE 0       END AS pct_dead FROM (SELECT C.relname AS TableName, pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, pg_s...
3 comments
My Other Post >>