Overview #

Extracting Processor Features #

SELECT * FROM aws_rds_instances;

Finding Hourly Costs #

WITH vcpu_calculation AS (
    SELECT
        arn,
        region,
        CAST(pf->>'Value' AS INTEGER) AS core_count,
        CAST(tf->>'Value' AS INTEGER) AS threads_per_core,
        CAST(pf->>'Value' AS INTEGER) * CAST(tf->>'Value' AS INTEGER) AS vcpu
    FROM
        aws_rds_instances,
        jsonb_array_elements(processor_features->'ProcessorFeatures') AS pf,
        jsonb_array_elements(processor_features->'ProcessorFeatures') AS tf
    WHERE
        processor_features IS NOT NULL
        AND pf->>'Name' = 'coreCount'
        AND tf->>'Name' = 'threadsPerCore'
        AND arn = 'arn:aws:rds:us-west-2:123456789012:db:example-db-instance'  -- Note: Change the value of `arn:aws:rds:us-west-2:123456789012:db:example-db-instance' to the ARN of the RDS instance you want to check.
),
rds_pricing_info AS (
    SELECT
        p.sku,
        p.attributes->>'instanceType' AS instance_type,
        p.attributes->>'vcpu' AS vcpu,
        p.attributes->>'regionCode' AS region,
        p.attributes->>'memory' AS memory,
        p.attributes->>'storage' AS storage,
        p.attributes->>'networkPerformance' AS network_performance,
        p.attributes->>'operation' AS operation,
        p.attributes->>'usagetype' AS usagetype,
        p.attributes->>'engineCode' AS engine_code,
        p.attributes->>'databaseEngine' AS database_engine,
        p.attributes->>'deploymentOption' AS deployment_option,
        p.attributes->>'normalizationSizeFactor' AS normalization_size_factor
    FROM
        awspricing_service_products AS p
    JOIN
        vcpu_calculation AS v ON v.vcpu = CAST(p.attributes->>'vcpu' AS FLOAT)
        AND v.region = p.attributes->>'regionCode'
    WHERE
        p.attributes->>'servicecode' = 'AmazonRDS'
),
pricing_details AS (
    SELECT
        p.sku,
        p.instance_type,
        p.vcpu,
        p.region,
        p.memory,
        p.storage,
        p.network_performance,
        p.operation,
        p.usagetype,
        p.engine_code,
        p.database_engine,
        p.deployment_option,
        p.normalization_size_factor,
        st.type,
        st.effective_date,
        jsonb_array_elements(st.price_dimensions) AS price_dimension
    FROM
        awspricing_service_terms AS st
    JOIN
        rds_pricing_info AS p ON p.sku = st.sku
)
SELECT
    pd.sku,
    pd.instance_type,
    pd.vcpu,
    pd.region,
    pd.memory,
    pd.storage,
    pd.network_performance,
    pd.operation,
    pd.usagetype,
    pd.engine_code,
    pd.database_engine,
    pd.deployment_option,
    pd.normalization_size_factor,
    pd.price_dimension->'pricePerUnit'->>'USD' AS price_per_unit_usd,
    pd.price_dimension->>'description' AS price_description,
    pd.price_dimension->>'unit' AS unit
FROM
    pricing_details AS pd
WHERE
    pd.price_dimension->>'unit' = 'Hrs'
    AND pd.type = 'OnDemand'
ORDER BY
    pd.instance_type,
    pd.price_dimension->'pricePerUnit'->>'USD';

1. Calculate vCPU for a specific RDS instance:

   • The vcpu_calculation CTE (Common Table Expression) extracts the coreCount and threadsPerCore values from the processor_features jsonb array and calculates the vCPU for the specific RDS instance identified by the provided ARN.
   • It uses the jsonb_array_elements function to flatten the processor_features array twice, once for coreCount and once for threadsPerCore.
   • The WHERE clause filters to ensure that processor_features is not null and matches the specific ARN of the RDS instance.
2. Join RDS vCPU data with AWS pricing information:

   • The rds_pricing_info CTE joins the vcpu_calculation CTE with the awspricing_service_products table based on the vCPU count and region.
   • It filters for RDS service codes to ensure only relevant data is retrieved.
   • The CTE selects various attributes related to the instance type, region, and vCPU.
3. Expand price dimensions:

   • The pricing_details CTE joins the rds_pricing_info CTE with the awspricing_service_terms table based on the SKU.
   • It flattens the price_dimensions array from the awspricing_service_terms table, allowing us to work with individual price dimensions.
4. Final Selection and Filtering:

   • The main SELECT statement retrieves detailed pricing information by joining the pricing_details CTE with the awspricing_service_products table.
   • The WHERE clause filters for pricing units in hours and ensures that the pricing type is OnDemand.
   • The results are ordered by instance type and price per unit.

Detailed Explanation of Specific Clauses #

• RDS Service Codes: The servicecode for Amazon RDS is used to identify the specific service (in this case, Amazon RDS) within the AWS pricing data. This ensures that only pricing information related to RDS instances is retrieved.
• Pricing Type is OnDemand: On-Demand pricing refers to a pay-as-you-go model where you are charged for compute capacity by the hour or second with no long-term commitments or upfront payments. This ensures that the query retrieves only On-Demand pricing information, which is relevant for users who want to know the cost of running an instance without any reserved or spot pricing considerations.
• Pricing Unit is in Hours: This ensures that the retrieved pricing information is based on an hourly rate, which is useful for calculating the cost of running an RDS instance on a per-hour basis.

Example Query Results #

• sku: The Stock Keeping Unit (SKU) identifying the specific product.
• instance_type: The type of the instance (e.g., db.c6gd.2xlarge, db.m2.4xlarge).
• vcpu: The number of virtual CPUs.
• region: The AWS region where the instance is located.
• memory: The amount of memory allocated to the instance.
• storage: The storage configuration of the instance.
• network_performance: The network performance level of the instance.
• operation: The specific operation or lifecycle stage (e.g., CreateDBInstance).
• usagetype: The usage type, which often includes the billing category.
• engine_code: The code representing the database engine.
• database_engine: The type of database engine running on the instance (e.g., PostgreSQL, MySQL).
• deployment_option: The deployment configuration (e.g., Single-AZ, Multi-AZ).
• normalization_size_factor: A factor used for normalizing instance sizes.
• price_per_unit_usd: The hourly cost of running the instance.
• price_description: A detailed description of the pricing.
• unit: The unit of time for the pricing, typically hours (Hrs).

How to Use This Information #

• Cost Optimization: Compare the hourly costs of different instance types with the same vCPU to find more cost-effective options.
• Performance Analysis: Evaluate other features such as memory, storage, and network performance to ensure that you choose instances that meet your performance requirements.
• Deployment Decisions: Consider the deployment options (e.g., Single-AZ vs. Multi-AZ) and how they affect both cost and availability.

Summary #