PostgreSQL

Web:http://www.postgresql.org/

PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance.

PostgreSQL provides few interesting features that make it a perfect choice for CDR-Stats:

Materialized views

We created 2 Materialized views to help on our reporting, here is the schema structure of those 2 views:

-- Materialized View
CREATE MATERIALIZED VIEW matv_voip_cdr_aggr_hour AS
    SELECT
        date_trunc('hour', starting_date) as starting_date,
        country_id,
        switch_id,
        cdr_source_type,
        hangup_cause_id,
        user_id,
        count(*) AS nbcalls,
        sum(duration) AS duration,
        sum(billsec) AS billsec,
        sum(buy_cost) AS buy_cost,
        sum(sell_cost) AS sell_cost
    FROM
        voip_cdr
    GROUP BY
        date_trunc('hour', starting_date), country_id, switch_id, cdr_source_type, hangup_cause_id, user_id;

-- Create index on Materialized view
CREATE UNIQUE INDEX matv_voip_cdr_aggr_hour_date
  ON matv_voip_cdr_aggr_hour (starting_date, country_id, switch_id, cdr_source_type, hangup_cause_id);

-- Materialized View
CREATE MATERIALIZED VIEW matv_voip_cdr_aggr_min AS
    SELECT
        date_trunc('minute', starting_date) as starting_date,
        country_id,
        switch_id,
        cdr_source_type,
        hangup_cause_id,
        user_id,
        count(*) AS nbcalls,
        sum(duration) AS duration,
        sum(billsec) AS billsec,
        sum(buy_cost) AS buy_cost,
        sum(sell_cost) AS sell_cost
    FROM
        voip_cdr
    GROUP BY
        date_trunc('minute', starting_date), country_id, switch_id, cdr_source_type, hangup_cause_id, user_id;

-- Create index on Materialized view
CREATE UNIQUE INDEX matv_voip_cdr_aggr_min_date
  ON matv_voip_cdr_aggr_min (starting_date, country_id, switch_id, cdr_source_type, hangup_cause_id);

You can drop those views with:

-- Drop Materialized View
DROP MATERIALIZED VIEW matv_voip_cdr_aggr_hour;

-- Drop Materialized View
DROP MATERIALIZED VIEW matv_voip_cdr_aggr_min;

You can refresh the view as follows, using “CONCURRENTLY” to ensure we do not lock the view:

# Refresh without lock
REFRESH MATERIALIZED VIEW CONCURRENTLY matv_voip_cdr_aggr_hour;

# Refresh without lock
REFRESH MATERIALIZED VIEW CONCURRENTLY matv_voip_cdr_aggr_min;

The update of the Materialized view is done periodically by a celery task using the above commands “REFRESH MATERIALIZED VIEW”.