Table of Contents

1. Introduction
2. Why SQL Performance Tuning Matters
3. What Is EXPLAIN PLAN?
4. Basic Usage of EXPLAIN PLAN (PostgreSQL, MySQL, Oracle)
5. Understanding Query Execution Stages
6. Interpreting Rows, Cost, Width, Time
7. Common Operators in Query Plans
8. Seq Scan vs Index Scan
9. Join Types and Their Cost Implications
10. Sorting, Filtering, Aggregation Overheads
11. Using ANALYZE for Actual Execution Times
12. Detecting Missing Indexes
13. Query Rewriting for Optimization
14. Index Selection and Composite Indexes
15. Table Statistics and Autovacuum (PostgreSQL)
16. Partitioning and Clustering for Performance
17. Caching and Materialized Views
18. Performance Pitfalls and Anti-Patterns
19. Real-World Tuning Example
20. Summary and What’s Next

1. Introduction

No matter how well-designed your database is, inefficient SQL queries can lead to performance bottlenecks. EXPLAIN PLAN is a diagnostic tool that helps you understand how a query is executed, and how to improve it.

2. Why SQL Performance Tuning Matters

• Faster query execution
• Reduced cloud/data warehouse costs
• Better user experience for dashboards
• Scalability for growing data volumes
• Helps meet SLAs in production systems

3. What Is EXPLAIN PLAN?

EXPLAIN (or EXPLAIN PLAN) is a SQL command that shows the query execution strategy chosen by the database optimizer.

It tells you:

• Which indexes (if any) are used
• How rows are scanned or filtered
• The expected cost and row count
• The execution order of joins and filters

4. Basic Usage of EXPLAIN PLAN

PostgreSQL:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

MySQL:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Oracle:

EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Use EXPLAIN ANALYZE (PostgreSQL) to run the query and show actual performance.

5. Understanding Query Execution Stages

Typical stages include:

• Table Scan (Seq Scan or Index Scan)
• Filter
• Sort
• Join (Nested Loop, Hash Join, Merge Join)
• Aggregate (GROUP BY, COUNT, SUM)
• Output rows

6. Interpreting Rows, Cost, Width, Time

Example (PostgreSQL):

Seq Scan on orders  (cost=0.00..431.00 rows=21000 width=64)

Use these estimates to evaluate efficiency and selectivity.

7. Common Operators in Query Plans

8. Seq Scan vs Index Scan

Seq Scan is used when:

• The table is small
• Index is not selective
• The cost of random access is too high

Index Scan is preferred when:

• Filtering on indexed columns
• Returning a few rows

EXPLAIN SELECT * FROM users WHERE id = 5;

Should show Index Scan using pk_users.

9. Join Types and Their Cost Implications

Joins can dominate query cost — reordering tables or adding indexes may help.

10. Sorting, Filtering, Aggregation Overheads

Sorting is expensive on large datasets:

ORDER BY created_at DESC

Adding an index on created_at DESC can eliminate the sort phase.

Aggregates like GROUP BY, HAVING may trigger hash or sort operations. Use indexes or pre-aggregated tables for heavy workloads.

11. Using ANALYZE for Actual Execution Times

EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 1000;

Returns actual:

• Row count
• Execution time per step
• Number of loops

Helps detect misestimates (e.g., 10k expected rows but 1 million actual).

12. Detecting Missing Indexes

Use EXPLAIN to find sequential scans on large tables:

EXPLAIN SELECT * FROM sales WHERE product_id = 100;

If Seq Scan appears, consider:

CREATE INDEX idx_sales_product_id ON sales(product_id);

13. Query Rewriting for Optimization

Instead of:

SELECT * FROM orders WHERE status != 'shipped';

Rewrite to:

SELECT * FROM orders WHERE status IN ('pending', 'cancelled');

Negations are hard to optimize — always prefer positive filtering.

14. Index Selection and Composite Indexes

Composite indexes help multi-column filters:

CREATE INDEX idx_customer_order_date ON orders(customer_id, order_date);

Useful for:

SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

Only the leftmost prefix is used unless all columns are included in filter.

15. Table Statistics and Autovacuum (PostgreSQL)

PostgreSQL’s query planner depends on table stats:

ANALYZE orders;

Autovacuum keeps stats up-to-date, but manual updates can help during large ETL jobs.

16. Partitioning and Clustering for Performance

Partitioning:

CREATE TABLE sales_y2024 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

Improves filter performance on date ranges.

Clustering:

CLUSTER orders USING idx_orders_customer_id;

Physically organizes data by index — faster reads for ordered scans.

17. Caching and Materialized Views

Use materialized views to cache expensive queries:

CREATE MATERIALIZED VIEW top_products AS
SELECT product_id, SUM(sales) FROM sales GROUP BY product_id;

Refresh as needed:

REFRESH MATERIALIZED VIEW top_products;

18. Performance Pitfalls and Anti-Patterns

• SELECT * in large joins
• Functions in WHERE clause (DATE(created_at)) disable index
• Using DISTINCT instead of GROUP BY
• Sorting large unsorted tables
• Implicit type casts (e.g., WHERE id = '123' with id as INT)

19. Real-World Tuning Example

Before:

SELECT * FROM logs WHERE DATE(timestamp) = CURRENT_DATE;

Problem:

• Disables index on timestamp

Optimized:

SELECT * FROM logs
WHERE timestamp >= CURRENT_DATE AND timestamp < CURRENT_DATE + INTERVAL '1 day';

Now uses index — dramatically faster.

20. Summary and What’s Next

EXPLAIN PLAN is your window into SQL’s execution logic. By interpreting plans, rewriting queries, and applying indexing strategies, you can significantly improve query performance and reduce compute costs.

Table of Contents

1. Introduction
2. Importance of SQL in Financial Analysis
3. Core Financial Statements in Data Tables
4. Building a Profit & Loss (P&L) Report in SQL
5. Structuring Accounts: Revenue vs Expense Categories
6. Monthly and Quarterly P&L Trends
7. EBITDA and Operating Margin in SQL
8. Using Window Functions for Running Totals
9. Forecasting Future Revenue with SQL Techniques
10. Year-over-Year and Quarter-over-Quarter Comparisons
11. Common Financial Ratios and Metrics
12. Working with Budget vs Actuals
13. Rolling 12-Month Financials
14. Revenue Recognition Logic in SQL
15. Modeling Deferred Revenue and Accruals
16. Real-World Example: SaaS Financial Analytics
17. Cohort-Based Revenue Analysis
18. Integrating SQL Output into BI Dashboards
19. Best Practices for Financial Modeling in SQL
20. Summary and What’s Next

1. Introduction

Financial analytics translates raw transactional data into meaningful insights such as profitability, performance, and forecasted revenue. SQL provides the foundation for transforming structured financial data into clear, scalable reports like P&Ls, forecasts, and KPIs.

2. Importance of SQL in Financial Analysis

• Extract and aggregate general ledger or ERP data
• Prepare cash flow, P&L, and balance sheet views
• Enable dynamic filtering by region, department, or entity
• Build fiscal calendars and roll-forward reports
• Power finance dashboards in BI tools

3. Core Financial Statements in Data Tables

Typical financial table structures:

4. Building a Profit & Loss (P&L) Report in SQL

SELECT
  a.account_group,
  DATE_TRUNC('month', t.transaction_date) AS month,
  SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE -t.amount END) AS amount
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
WHERE a.account_group IN ('Revenue', 'COGS', 'Expenses')
GROUP BY a.account_group, month
ORDER BY month, a.account_group;

5. Structuring Accounts: Revenue vs Expense Categories

Use a consistent account hierarchy in your accounts table to simplify queries.

6. Monthly and Quarterly P&L Trends

SELECT
  DATE_TRUNC('quarter', transaction_date) AS quarter,
  account_group,
  SUM(amount) AS total
FROM p_l_data
GROUP BY quarter, account_group;

Enable rolling views with window functions.

7. EBITDA and Operating Margin in SQL

EBITDA = Earnings Before Interest, Taxes, Depreciation, and Amortization.

SELECT
  report_month,
  SUM(CASE WHEN group = 'Revenue' THEN amount ELSE 0 END) AS revenue,
  SUM(CASE WHEN group = 'COGS' THEN amount ELSE 0 END) AS cogs,
  SUM(CASE WHEN group IN ('Salaries', 'Rent') THEN amount ELSE 0 END) AS opex,
  SUM(...) AS EBITDA
FROM p_l_data
GROUP BY report_month;

Operating Margin:

(EBITDA / Revenue) * 100

8. Using Window Functions for Running Totals

SELECT
  report_month,
  account_group,
  SUM(amount) OVER (PARTITION BY account_group ORDER BY report_month) AS cumulative_amount
FROM p_l_data;

Track cumulative revenue or cost trends.

9. Forecasting Future Revenue with SQL Techniques

Simple Linear Projection:

WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', transaction_date) AS month, SUM(amount) AS revenue
  FROM transactions
  WHERE account_group = 'Revenue'
  GROUP BY month
)
SELECT *,
       LAG(revenue, 1) OVER (ORDER BY month) AS last_month,
       revenue - LAG(revenue, 1) OVER (ORDER BY month) AS monthly_growth
FROM monthly_revenue;

Use that growth to extrapolate future months manually or in views.

10. Year-over-Year and Quarter-over-Quarter Comparisons

SELECT
  month,
  revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS revenue_last_year,
  (revenue - LAG(revenue, 12) OVER (ORDER BY month)) / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100 AS yoy_growth
FROM monthly_revenue;

11. Common Financial Ratios and Metrics

Combine fact tables with customer/order dimensions for full visibility.

12. Working with Budget vs Actuals

SELECT
  a.account_id,
  c.month,
  SUM(b.amount) AS budgeted,
  SUM(t.amount) AS actual,
  SUM(t.amount) - SUM(b.amount) AS variance
FROM budgets b
JOIN transactions t ON b.account_id = t.account_id AND b.month = DATE_TRUNC('month', t.transaction_date)
JOIN calendar c ON b.month = c.date
GROUP BY a.account_id, c.month;

13. Rolling 12-Month Financials

SELECT
  month,
  SUM(amount) OVER (ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS rolling_12m
FROM monthly_revenue;

Useful for smoothing volatility and spotting trends.

14. Revenue Recognition Logic in SQL

For SaaS companies or subscription services:

-- Spread revenue over service period
SELECT
  customer_id,
  service_start,
  service_end,
  GENERATE_SERIES(service_start, service_end, INTERVAL '1 month') AS recognition_month,
  amount / EXTRACT(MONTH FROM age(service_end, service_start)) AS monthly_revenue
FROM subscriptions;

15. Modeling Deferred Revenue and Accruals

-- Deferred revenue = total invoice - revenue recognized so far
SELECT
  invoice_id,
  total_amount,
  SUM(recognized_amount) AS revenue_recognized,
  total_amount - SUM(recognized_amount) AS deferred_revenue
FROM revenue_schedule
GROUP BY invoice_id, total_amount;

Track liabilities accurately in SQL reporting views.

16. Real-World Example: SaaS Financial Analytics

Build dashboards using:

• Monthly Recurring Revenue (MRR)
• Churn Rate
• Net Revenue Retention (NRR)
• Customer Lifetime Value (LTV)
• ARPA (Average Revenue per Account)

Example:

SELECT DATE_TRUNC('month', invoice_date) AS mrr_month, SUM(invoice_amount) AS mrr
FROM subscriptions
GROUP BY mrr_month;

17. Cohort-Based Revenue Analysis

WITH cohorts AS (
  SELECT customer_id, MIN(subscription_date) AS cohort_date
  FROM subscriptions
  GROUP BY customer_id
),
activity AS (
  SELECT s.customer_id, DATE_TRUNC('month', payment_date) AS active_month, s.amount
  FROM subscriptions s
)
SELECT
  cohort_date,
  active_month,
  SUM(amount) AS revenue
FROM cohorts
JOIN activity USING(customer_id)
GROUP BY cohort_date, active_month;

Visualize revenue evolution per cohort.

18. Integrating SQL Output into BI Dashboards

• Build views or materialized tables for metrics
• Feed SQL outputs to Tableau, Power BI, or Looker
• Use parameterized queries for flexible analysis
• Automate scheduling via dbt or Airflow

19. Best Practices for Financial Modeling in SQL

• Normalize accounting data with clear account hierarchies
• Use fiscal_calendar dimensions for YTD/MTD logic
• Avoid mixing debits and credits in reporting tables
• Always reconcile totals (trial balance logic)
• Maintain audit trails (source file, ETL job, timestamp)

20. Summary and What’s Next

SQL is indispensable in financial analytics — from producing core reports like P&Ls to modeling deferred revenue and forecasting trends. With structured design and advanced SQL techniques, finance teams can build powerful, auditable, and automated pipelines.

Table of Contents

1. Introduction
2. What Is a Data Warehouse?
3. OLTP vs OLAP: The Need for Warehousing
4. Key Components of a Data Warehouse
5. What Is Dimensional Modeling?
6. Facts and Dimensions Explained
7. The Star Schema Architecture
8. Example of a Star Schema Design
9. The Snowflake Schema Architecture
10. Star vs Snowflake Schema Comparison
11. Denormalization and Performance Trade-offs
12. Creating Fact and Dimension Tables in SQL
13. Surrogate Keys and Their Role
14. Slowly Changing Dimensions (SCD Types)
15. Handling Time Dimensions
16. Using Views to Model Analytical Layers
17. Querying Star Schemas for BI and Reporting
18. Best Practices for Data Warehouse Schema Design
19. Real-World Warehouse Scenario: E-Commerce Analytics
20. Summary and What’s Next

1. Introduction

Data warehousing is the foundation of modern analytics, BI dashboards, and reporting systems. Understanding how to design and query data warehouse schemas—especially Star and Snowflake schemas—is key to building efficient, scalable analytics systems with SQL.

2. What Is a Data Warehouse?

A data warehouse is a centralized repository designed for analytical processing (OLAP) rather than transactional operations. It integrates data from multiple sources, cleans and structures it, and makes it query-ready for decision-making.

3. OLTP vs OLAP: The Need for Warehousing

4. Key Components of a Data Warehouse

• Fact tables: Store metrics (e.g., sales, orders)
• Dimension tables: Store attributes (e.g., customer info, product details)
• ETL/ELT pipelines: Load and transform data
• Schema design: Star or Snowflake
• Views and aggregates: Support analysis and reporting

5. What Is Dimensional Modeling?

Dimensional modeling is a design technique for warehouses where data is organized around:

• Facts (measurable events)
• Dimensions (descriptive attributes)

The goal is to create schemas that are intuitive and fast to query.

6. Facts and Dimensions Explained

Facts link to dimensions using foreign keys.

7. The Star Schema Architecture

In a star schema, a central fact table connects directly to surrounding denormalized dimension tables.

                +-------------+
                | dim_product |
                +-------------+
                       |
+-------------+   +-------------+   +-------------+
| dim_customer|---|  fact_sales |---| dim_date    |
+-------------+   +-------------+   +-------------+
                       |
                +-------------+
                | dim_region  |
                +-------------+

8. Example of a Star Schema Design

Fact Table:

CREATE TABLE fact_sales (
  sale_id SERIAL PRIMARY KEY,
  product_id INT,
  customer_id INT,
  date_id INT,
  region_id INT,
  quantity INT,
  total_amount NUMERIC
);

Dimension Table:

CREATE TABLE dim_product (
  product_id INT PRIMARY KEY,
  product_name TEXT,
  category TEXT
);

9. The Snowflake Schema Architecture

In a snowflake schema, dimensions are further normalized into sub-dimensions:

                 +-------------+
                 | dim_category|
                 +-------------+
                       |
                +-------------+
                | dim_product |
                +-------------+
                       |
                +-------------+
                | fact_sales  |
                +-------------+

Product links to category via a foreign key instead of storing the category name directly.

10. Star vs Snowflake Schema Comparison

11. Denormalization and Performance Trade-offs

Denormalizing dimensions (as in star schema) can:

• Reduce join complexity
• Speed up reads
• Slightly increase storage

Snowflake reduces redundancy but adds complexity and join cost.

12. Creating Fact and Dimension Tables in SQL

Example: Creating both dimensions and fact

CREATE TABLE dim_customer (
  customer_id INT PRIMARY KEY,
  name TEXT,
  email TEXT,
  signup_date DATE
);

CREATE TABLE fact_orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  date_id INT,
  total_amount NUMERIC,
  FOREIGN KEY (customer_id) REFERENCES dim_customer(customer_id)
);

13. Surrogate Keys and Their Role

Use surrogate keys (auto-incremented IDs) for dimensions rather than natural keys like email or name:

• Ensure uniqueness
• Enable SCDs (Slowly Changing Dimensions)
• Improve indexing and joins

14. Slowly Changing Dimensions (SCD Types)

Example for SCD Type 2:

INSERT INTO dim_customer_history (customer_id, name, valid_from, valid_to)
VALUES (1, 'Alice', CURRENT_DATE, NULL);

15. Handling Time Dimensions

Time is one of the most common dimensions in warehousing:

CREATE TABLE dim_date (
  date_id INT PRIMARY KEY,
  full_date DATE,
  year INT,
  quarter INT,
  month INT,
  week INT,
  day_of_week TEXT
);

Pre-generate a date dimension to allow fast filtering and grouping.

16. Using Views to Model Analytical Layers

Use SQL views to build abstractions:

CREATE VIEW sales_summary AS
SELECT
  d.year,
  c.name AS customer_name,
  p.category,
  SUM(f.total_amount) AS total_spent
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY d.year, c.name, p.category;

17. Querying Star Schemas for BI and Reporting

SELECT
  c.region,
  p.category,
  d.month,
  SUM(f.total_amount) AS monthly_sales
FROM fact_sales f
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_product p ON f.product_id = p.product_id
JOIN dim_date d ON f.date_id = d.date_id
GROUP BY c.region, p.category, d.month
ORDER BY d.month;

18. Best Practices for Data Warehouse Schema Design

• Use surrogate keys for dimension relationships
• Prefer star schema for simplicity and speed
• Design a calendar dimension
• Avoid circular joins or many-to-many between dimensions
• Pre-aggregate for performance (e.g., monthly rollups)
• Document all fact and dimension tables

19. Real-World Warehouse Scenario: E-Commerce Analytics

Use Case: Track sales, customers, product performance.

20. Summary and What’s Next

Designing proper Star and Snowflake schemas is critical for building high-performance, scalable data warehouses. Understanding how to model facts, dimensions, and time can greatly impact the effectiveness of your reporting and BI efforts.

Table of Contents

1. Introduction
2. Why JSON Support Matters in Modern SQL
3. PostgreSQL JSON vs JSONB
4. Creating Tables with JSON Fields
5. Inserting JSON Data (from APIs or files)
6. Accessing JSON Properties in PostgreSQL
7. Filtering Based on JSON Content
8. Updating and Modifying JSON Fields
9. Using JSON Functions: jsonb_set, jsonb_extract_path, jsonb_array_elements
10. Flattening JSON Arrays
11. Indexing JSONB for Performance
12. Parsing API Responses in PostgreSQL
13. Building a Table to Store API Logs
14. Practical Example: GitHub API Data in JSON
15. Joining JSON with Relational Tables
16. Aggregating Inside JSON Arrays
17. Validating and Cleaning JSON Payloads
18. Common Pitfalls and Optimization Tips
19. JSON Utility Functions and Extensions
20. Summary and What’s Next

1. Introduction

Modern applications often rely on API responses and semi-structured data, especially in JSON format. PostgreSQL stands out among SQL databases for its robust native JSON support, making it ideal for ingesting and analyzing data directly from APIs.

2. Why JSON Support Matters in Modern SQL

• APIs return nested JSON by default
• Avoid premature normalization
• Store entire documents or responses in a single field
• Perform flexible querying without rigid schema changes

3. PostgreSQL JSON vs JSONB

Recommendation: Use JSONB for analytics and indexing.

4. Creating Tables with JSON Fields

CREATE TABLE api_logs (
  id SERIAL PRIMARY KEY,
  source TEXT,
  payload JSONB,
  fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This allows you to store entire API payloads for later parsing and analysis.

5. Inserting JSON Data (from APIs or files)

INSERT INTO api_logs (source, payload)
VALUES (
  'github',
  '{
    "login": "prafull",
    "id": 12345,
    "repos": [
      {"name": "syskool", "stars": 150},
      {"name": "xeblabs", "stars": 80}
    ]
  }'
);

Use your backend or ETL pipeline to automate this step.

6. Accessing JSON Properties in PostgreSQL

SELECT
  payload->>'login' AS username,
  (payload->>'id')::INT AS user_id
FROM api_logs
WHERE source = 'github';

• -> returns JSON object
• ->> returns text (castable to scalar types)

7. Filtering Based on JSON Content

SELECT *
FROM api_logs
WHERE payload->>'login' = 'prafull';

With nested data:

WHERE payload->'repos' @> '[{"name": "syskool"}]'

8. Updating and Modifying JSON Fields

UPDATE api_logs
SET payload = jsonb_set(payload, '{login}', '"kumar_prafull"')
WHERE id = 1;

• First param: JSON field
• Second: path as array
• Third: new value (must be JSON-encoded)

9. Using JSON Functions

• jsonb_set: Update a specific key
• jsonb_extract_path: Navigate nested levels
• jsonb_array_elements: Unpack arrays into rows
• jsonb_build_object: Create JSON in SQL
• jsonb_object_keys: List keys in an object

Example:

SELECT key, value
FROM api_logs,
     jsonb_each_text(payload)
WHERE id = 1;

10. Flattening JSON Arrays

SELECT
  payload->>'login' AS user,
  repo_elem->>'name' AS repo_name,
  (repo_elem->>'stars')::INT AS stars
FROM api_logs,
     jsonb_array_elements(payload->'repos') AS repo_elem
WHERE source = 'github';

Each array element becomes its own row — useful for reporting.

11. Indexing JSONB for Performance

GIN Index:

CREATE INDEX idx_jsonb_payload ON api_logs USING GIN (payload jsonb_path_ops);

Expression Index:

CREATE INDEX idx_username ON api_logs ((payload->>'login'));

Improves filter and search performance.

12. Parsing API Responses in PostgreSQL

If API is fetched in ETL, store raw JSON, then parse downstream:

-- Extract nested country code
SELECT payload->'address'->>'country_code' AS country
FROM api_logs;

Use jsonb_populate_recordset() for bulk parsing if schema is known.

13. Building a Table to Store API Logs

CREATE TABLE external_api_responses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  endpoint TEXT,
  response JSONB,
  status_code INT,
  requested_at TIMESTAMP DEFAULT NOW()
);

Track performance, failures, and response sizes.

14. Practical Example: GitHub API Data in JSON

-- Assume payload from GitHub API
SELECT
  payload->>'login' AS username,
  jsonb_array_length(payload->'repos') AS repo_count
FROM api_logs
WHERE source = 'github';

15. Joining JSON with Relational Tables

-- users table has user_id
-- api_logs.payload contains matching GitHub user_id

SELECT u.name, a.payload->>'login' AS github_login
FROM users u
JOIN api_logs a ON u.external_id = (a.payload->>'id')::INT;

16. Aggregating Inside JSON Arrays

SELECT
  payload->>'login' AS user,
  SUM((repo_elem->>'stars')::INT) AS total_stars
FROM api_logs,
     jsonb_array_elements(payload->'repos') AS repo_elem
GROUP BY user;

Aggregate metrics from nested structures.

17. Validating and Cleaning JSON Payloads

Check for structure:

SELECT * FROM api_logs
WHERE jsonb_typeof(payload->'repos') != 'array';

Avoid invalid assumptions — always validate before parsing.

18. Common Pitfalls and Optimization Tips

• Use jsonb, not json, for faster querying
• Always wrap JSON string literals in double quotes
• Avoid storing massive payloads with no access plans
• Index only fields used in filtering
• Be careful with null vs missing key comparisons

19. JSON Utility Functions and Extensions

• jsonb_pretty(payload) – formatted view
• jsonb_strip_nulls(payload) – clean nulls
• jsonb_build_object() – construct JSON in SQL
• Extensions: pg_partman, pg_cron, plv8 (JS for PostgreSQL)

20. Summary and What’s Next

PostgreSQL provides powerful JSON capabilities that make it an excellent tool for storing and analyzing API data and other semi-structured documents. From storing entire payloads to filtering and joining on nested attributes, you can use pure SQL to explore flexible data structures.