DEV Community: Aditya Agrawal

OAuth2.0 Auth Tokens: A Comprehensive Guide

Aditya Agrawal — Tue, 01 Apr 2025 05:08:00 +0000

OAuth 2.0 in Real Life: The Netflix Streaming Analogy

Imagine you're using Netflix on multiple devices. When you first log in on your smart TV, Netflix doesn't just give you unlimited access forever - that would be a security risk. Instead:

After entering your username/password, your TV receives a short-lived access token (valid for ~1 hour) that lets you stream content immediately.
Your TV also receives a refresh token that's securely stored and invisible to you. When your access token expires while binge-watching "Stranger Things," Netflix uses this refresh token behind the scenes to get a new access token without interrupting your viewing or asking for credentials again.
If someone somehow steals your refresh token from your TV and tries to use it on another device, Netflix's security systems detect this unusual pattern and invalidate all your tokens, forcing a new login.

This is exactly how modern web applications work with OAuth 2.0 - your browser or mobile app securely manages these tokens to provide seamless access while maintaining security, just like Netflix does across your devices.

Tokens are the backbone of secure API access in modern web and mobile applications. This article explores:

How OAuth 2.0 uses access and refresh tokens
Token anatomy and cryptographic implementation
Detailed OAuth 2.0 flows with sequence diagrams
Token lifecycle management
Advanced security mechanisms
Implementation patterns and anti-patterns

Understanding OAuth 2.0 Architecture

OAuth 2.0 is an authorization framework that enables third-party applications to obtain limited access to a user's resources without exposing their credentials. It is widely used for delegated authorization in modern web and mobile applications.

1. Roles in OAuth 2.0

OAuth 2.0 defines four primary roles:

Resource Owner: The user who owns the protected resources and grants permission for access.
Client: The application requesting access to the resource owner's protected resources.
Authorization Server: The server that authenticates the resource owner and issues access tokens.
Resource Server: The API or service that hosts the protected resources and validates access tokens.

2. OAuth 2.0 Grant Types

OAuth 2.0 supports multiple grant types, each suited for different use cases:

Authorization Code Grant: Used for server-to-server communication and web applications with a backend.
Implicit Grant (deprecated): Originally used for single-page applications (SPAs) but now replaced with Authorization Code + PKCE.
Client Credentials Grant: Used when the client itself (not a user) needs access to resources.
Password Grant (deprecated): Used when the user provides credentials directly to the client (not recommended due to security concerns).
Refresh Token Grant: Allows clients to obtain a new access token without requiring user authentication.

3. OAuth 2.0 Tokens

OAuth 2.0 uses different types of tokens:

Access Token: A short-lived token used to access protected resources.
Refresh Token: A long-lived token used to obtain new access tokens.
ID Token (used in OpenID Connect): A token containing user identity claims.

Token Anatomy: What's Inside?

Token Structure in OAuth/OIDC

Both access tokens and refresh tokens can be implemented as JWTs (JSON Web Tokens), though they serve different purposes and have different characteristics.

Access Token Structure

Access tokens are typically JWTs containing claims about the authenticated user and their permissions:

{
  "iss": "https://auth.example.com",        // Issuer
  "sub": "user123456",                      // Subject (user ID)
  "aud": "https://api.example.com",         // Audience (intended recipient)
  "iat": 1714768000,                        // Issued At timestamp
  "exp": 1714768800,                        // Expiry timestamp (13 minutes later)
  "jti": "abcd1234-ef56-gh78-ij90",         // JWT ID (unique identifier)
  "scope": "read:profile write:settings",   // Authorized scopes
  "role": "admin",                          // User role (for RBAC)
  "tenant_id": "org_987654321",             // Multi-tenancy identifier
  "azp": "client_app_123",                  // Authorized party (client ID)
  "auth_time": 1714767900                   // Time of original authentication
}

Refresh Token Structure

Refresh tokens can also be implemented as JWTs, though with a focus on token management rather than authorization data:

{
  "sub": "user123456",                      // Subject (user ID)
  "session_id": "abcd1234-ef56-gh78-ij90",  // Unique session identifier
  "iat": 1714768000,                        // Issued At timestamp
  "exp": 1715372800,                        // Expiry timestamp (7 days later)
  "jti": "1234567890",                      // JWT ID (unique identifier)
  "client_id": "client_app_123",            // Client application ID
  "scope": "read:profile write:settings",   // Authorized scopes
  "device_id": "device_abcdef123456",       // Device identifier
  "family_id": "session_family_7890",       // Token family for rotation
  "rotation_counter": 0                     // Number of times rotated
}

Regardless of format, refresh tokens are typically validated against server-side records for additional security, with each token mapped to a database entry containing complete session information.

OAuth 2.0 Flows and Token Usage In-Depth

OAuth 2.0 provides multiple grant types (flows) to suit different application types and security requirements. Each flow is designed for specific use cases and security considerations.

1. Authorization Code Flow

The standard flow for server-side web applications that can securely store client secrets.

2. Authorization Code Flow with PKCE

Enhanced security for public clients like mobile apps and SPAs that can't securely store secrets.

3. Client Credentials Flow

For machine-to-machine (M2M) authentication without user interaction.

4. Device Authorization Flow

For devices with limited input capabilities (IoT, Smart TVs, CLI apps).

5. Resource Owner Password Flow (Legacy)

Direct username/password authentication - recommended only for legacy system migration.

Flow Selection Guide

Flow Type	Best For	Security Level	User Interaction
Authorization Code	Server-side web apps	High	Required
Auth Code + PKCE	Mobile/SPA apps	High	Required
Client Credentials	Server-to-server	High	None
Device Authorization	IoT/CLI apps	High	Required (secondary device)
Resource Owner Password	Legacy systems	Medium	Direct credentials

Key Considerations:

Always prefer Authorization Code flow with PKCE for modern applications
Use Client Credentials only for trusted backend services
Avoid Resource Owner Password flow in new implementations
Consider Device flow for input-constrained scenarios

- Implement proper token storage based on client type

Token Lifecycle Management

Token Issuance Process

When an authorization server issues tokens, it follows these steps:

Validate the grant (code, credentials, etc.)
Authenticate the client and/or user
Determine appropriate scopes and permissions
Generate cryptographically secure tokens
Store token metadata for refresh and introspection
Sign the access token (if using JWTs)
Return tokens to the client

Access Token vs. Refresh Token Lifecycle

Token Versioning for Policy Updates

Advanced systems implement token versioning to ensure security policy updates are enforced:

# Pseudocode for token versioning
def create_refresh_token(user_id, client_id, scopes):
    # Get current security policy version
    current_policy_version = get_current_policy_version()

    refresh_token = {
        "user_id": user_id,
        "client_id": client_id,
        "scopes": scopes,
        "issued_at": current_timestamp(),
        "expires_at": current_timestamp() + REFRESH_TOKEN_TTL,
        "policy_version": current_policy_version,
        "jti": generate_uuid()
    }

    # Store in database
    store_refresh_token(refresh_token)

    # Return opaque token that references the stored data
    return encode_token(refresh_token["jti"])

def refresh_access_token(refresh_token_jti):
    # Retrieve stored token data
    stored_token = get_stored_token(refresh_token_jti)

    # Check if token policy is current
    current_policy_version = get_current_policy_version()
    if stored_token["policy_version"] < current_policy_version:
        # If major policy change, force re-authentication
        if is_major_policy_change(stored_token["policy_version"], current_policy_version):
            raise RequireReauthenticationException()
        # If minor policy change, update token
        else:
            update_token_policy_version(refresh_token_jti, current_policy_version)

    # Generate new access token
    return generate_access_token(stored_token["user_id"], stored_token["scopes"])

6. Advanced Token Security Mechanisms

Refresh Token Rotation

Token Binding

Token binding attaches tokens to specific devices or browser instances, preventing token theft across environments. This technique ensures that a token cannot be used outside its originally bound context, mitigating replay attacks.

Layered Token Architecture

Sophisticated systems implement a three-tiered token architecture to enhance security:

Access Token (AT): A short-lived token used for API requests, minimizing exposure in case of leaks.
Refresh Token (RT): A longer-lived token used to obtain new access tokens. Its rotation and binding add extra security.
Device-Bound Token (DBT): A token uniquely bound to a device, ensuring that even if an RT is compromised, it cannot be used from a different device.

OAuth 2.0 in Microservices Architecture

Token Propagation

In microservices environments, tokens need to flow between services securely:

JWT vs. Opaque Tokens: The Great Debate

Token Type	Advantages	Disadvantages
JWT	- Self-contained (no database lookup) - Stateless verification - Contains claims and metadata - Standard format with library support	- Cannot be revoked without extra mechanisms - Size limitations (URL safe) - Claims are visible (though encrypted) to clients
Opaque Tokens	- Can be instantly revoked - No size limitation for metadata - No sensitive data exposure	- Requires database lookup for validation - Reduced performance at scale

Hybrid Approach

Many systems use a hybrid approach:

JWTs for access tokens (short-lived)
Opaque tokens for refresh tokens (revocable)

JWT validation: Fast, and in Memory
DB Lookup: Slow, and IO operation

Final Takeaways

OAuth 2.0 securely grants API access without password sharing.
Access tokens are short-lived credentials for API access.
Refresh tokens enable seamless long-term sessions.
Use PKCE for all public clients to prevent interception attacks.
Implement token rotation to detect and prevent token leakage.
Consider token binding to specific devices or sessions.
Adopt a layered token architecture for complex systems.
Monitor token usage for anomalies and security threats.

By mastering OAuth 2.0's token mechanisms, you can build robust, scalable, and secure authentication systems for any application architecture.

JWT - Based Auth: A Deep Dive

Aditya Agrawal — Mon, 31 Mar 2025 05:00:00 +0000

Understanding JWT-Based Security

In modern web applications, JSON Web Tokens (JWTs) have become the cornerstone of authentication and authorization. They provide a stateless, secure way to handle user sessions and permissions across distributed systems. This means that the server does not need to store session information, which can improve scalability and performance.

What is a JWT?

A JWT is a compact, URL-safe means of representing claims between two parties. Think of it as a digital passport that contains three parts:

Header - Algorithm & token type
Payload - Claims and data
Signature - Verification code

Interactive JWT Explorer

Try editing the header and payload below to see how JWTs work in real-time:

JWT Internals: Breaking Down the Parts

1. Header

The header typically consists of two parts:

Token type (JWT)
Signing algorithm (e.g., HMAC SHA256 or RSA)

The header is a JSON object that is Base64Url encoded to form the first part of the JWT.

{
  "alg": "HS256",
  "typ": "JWT"
}

2. Payload

The payload contains the claims. Claims are statements about an entity (typically, the user) and additional data. There are three types of claims: registered, public, and private claims.

Registered claims: Predefined claims that are not mandatory but recommended, such as iss (issuer), exp (expiration time), sub (subject), and aud (audience).
Public claims: Custom claims created to share information, which should be defined in the IANA JSON Web Token Registry or be collision-resistant.
Private claims: Custom claims agreed upon by parties using the JWT.

{
  "sub": "1234567890",    // Subject (user ID)
  "name": "John Doe",     // Custom claim
  "role": "admin",        // Role for authorization
  "iat": 1516239022,     // Issued at timestamp
  "exp": 1516242622      // Expiration timestamp
}

3. Signature

The signature is created by:

Taking the encoded header
Taking the encoded payload
Adding a secret
Applying the algorithm specified in the header

The signature ensures that the token hasn't been altered. It is crucial for verifying the authenticity of the token.

HMACSHA256(
  base64UrlEncode(header) + "." +
  base64UrlEncode(payload),
  secret
)

JWT: Encoding vs. Encryption

JWTs use encoding, not encryption. This means the data in a JWT is visible to anyone with access to the token, as it is encoded using Base64Url. However, the integrity of the data is protected by a signature, ensuring the token hasn't been tampered with.

Encoding: Converts data into a different format using a public scheme (Base64Url for JWTs), making it URL-safe but not secure from viewing.
Encryption: Converts data into an unreadable format without a secret key, which JWTs do not use for payloads.

The main purpose of a JWT is to verify the identity of the issuer and ensure the token's integrity through a digital signature. Understanding this distinction helps developers decide what data to include in a JWT and how to handle it securely.

JWT in Authentication Flow

User Login
- The client sends a login request with credentials.
- The server verifies the credentials and, if valid, generates a JWT.
- The JWT is sent back to the client and stored, typically in a secure, HttpOnly cookie.

Subsequent Requests
- The client includes the JWT in the Authorization header of subsequent requests.
- The server verifies the JWT and checks permissions before granting access to protected resources.

JWT for Authorization

JWTs excel at authorization because they can carry role and permission information. This allows for fine-grained access control based on the user's role and permissions.

{
  "sub": "1234567890",
  "role": "admin",
  "permissions": ["read", "write", "delete"],
  "department": "engineering"
}

Security Best Practices

Token Expiration
- Use short-lived access tokens to minimize the risk of token theft.
- Implement refresh token rotation to maintain user sessions securely.
- Always include the exp claim to define the token's expiration time.
Payload Security
- Never store sensitive data in the payload, as it can be decoded by anyone with access to the token.
- Minimize payload size to reduce the risk of exposure and improve performance.
- Use standard claims when possible to ensure compatibility and security.
Signature Protection
- Use strong secrets and consider using asymmetric algorithms like RS256 for added security.
- Rotate secrets periodically to mitigate the risk of key compromise.

Common JWT Pitfalls

Client-side Storage
- Store JWTs in HttpOnly cookies to protect against XSS attacks.
- Avoid storing JWTs in localStorage, as it is accessible via JavaScript.
- Implement CSRF protection to prevent unauthorized requests.
Token Invalidation
- JWTs cannot be directly revoked, so use a token blacklist to manage invalid tokens.
- Keep expiration times short to limit the impact of a compromised token.
Algorithm Attacks
- Always validate the alg header to prevent algorithm substitution attacks.
- Do not accept the none algorithm, and use explicit algorithm checking to ensure security.

Self-contained JWT Example

Encoding Example

You can create and inspect JWT parts using basic bash commands. This example demonstrates how to manually create a JWT using a secret key.

# Create a header
HEADER='{"alg":"HS256","typ":"JWT"}'

# Base64URL encode the header
function base64UrlEncode() {
  local input="$1"
  echo -n "$input" | base64 | tr '+/' '-_' | tr -d '='
}

# Base64URL encode the header
HEADER_ENCODED=$(base64UrlEncode "$HEADER")
echo $HEADER_ENCODED

# Create a payload
PAYLOAD='{"sub":"1234567890","name":"John Doe","iat":1516239022}'

# Base64URL encode the payload
PAYLOAD_ENCODED=$(base64UrlEncode "$PAYLOAD")
echo $PAYLOAD_ENCODED

# Create signature (using OpenSSL with a secret key)
SECRET="your-256-bit-secret"
SIGNATURE=$(echo -n "${HEADER_ENCODED}.${PAYLOAD_ENCODED}" | \
  openssl dgst -binary -sha256 -hmac $SECRET | \
  base64 | tr '+/' '-_' | tr -d '=')

# Combine all parts to create the final JWT
JWT="${HEADER_ENCODED}.${PAYLOAD_ENCODED}.${SIGNATURE}"
echo $JWT

Final Thoughts

JWTs provide a powerful mechanism for handling both authentication and authorization in modern web applications. While they come with their own set of challenges, understanding their internals and following security best practices allows you to implement robust, stateless authentication systems.

Remember: Always validate tokens server-side, protect your secrets, and never trust client-side data without verification.

Authentication vs Authorization: Understanding the Difference

Aditya Agrawal — Sat, 29 Mar 2025 02:15:56 +0000

Securing the Digital World: Authentication vs Authorization

In today's digital landscape, security is more critical than ever. Two fundamental pillars support every secure system: Authentication ("Are you who you say you are?") and Authorization ("What are you allowed to do?"). While closely related, these concepts serve distinct purposes in protecting our digital assets.

Let's explore these concepts through a familiar experience we've all had — going through airport security.

The Airport Security Journey: A Perfect Security Model

In web security, Authentication and Authorization work together like airport security. Let's see how:

1. Security Checkpoint

Authentication: Show your ID/passport to prove who you are
Authorization: Your boarding pass determines where you can go

2. Airport Access

Authentication: Scan your boarding pass
Authorization:
- First class: Access to premium lounges, priority boarding
- Economy: Basic access to gate and standard services

This airport security model mirrors digital systems:

Airport Security	Digital Security
Passport/ID	Username/Password
Biometric Gates	Fingerprint/Face ID
Boarding Pass	Access Token
Class of Service	User Role/Permissions

Understanding Authentication

Authentication is the process of verifying who you are. Imagine arriving at the airport and presenting your passport to the security officer. This serves as proof of your identity. In the digital realm, authentication works similarly — the system checks your credentials to ensure you are who you claim to be.

How Authentication Works

Authentication typically involves one or more of the following methods:

Username and password — akin to showing your passport
Biometric verification — like facial recognition used at airport gates
Multi-Factor Authentication (MFA) — comparable to providing both your passport and an additional document
Single Sign-On (SSO) — similar to using a trusted airline loyalty card across partner airlines
OAuth and OpenID Connect — akin to having a trusted airline verify your identity with partner services
Magic Links — like receiving a boarding pass directly to your phone

Types and Methods of Authentication

Authentication methods are generally classified into three categories, each with practical examples:

Knowledge-based: Something you know
- Passwords: A unique key that only you know.
- Security Questions: Personalized questions that are hard for others to guess.
- PIN Codes: Frequently used in ATMs and mobile devices.
Possession-based: Something you have
- Hardware Tokens: Physical devices generating time-sensitive codes.
- Mobile Devices: Receive codes or act as authenticators.
- Smart Cards: Often used in corporate environments.
- USB Security Keys: Physical keys providing added security.
Inherence-based: Something you are
- Fingerprints: Common in smartphones and laptops.
- Facial Recognition: Found in modern phones and security systems.
- Voice Recognition: Used in phone banking and smart devices.
- Retina/Iris Scans: High-security biometric checks.

Modern systems often combine these for enhanced security — known as Multi-Factor Authentication (MFA). For instance, logging into your bank app might require both a password and a fingerprint scan.

Understanding Authorization

Authorization determines what you can do once authenticated. If authentication is about proving your identity, authorization is about defining your privileges. Back to the airport analogy — your boarding pass determines which flight you can board, your seat, and whether you have access to exclusive lounges.

How Authorization Works

Even after proving who you are, the system still needs to verify what you're allowed to do. Authorization frameworks ensure users can only access resources they're permitted to interact with. Common models include:

Role-Based Access Control (RBAC): Users are assigned roles (e.g., Admin, Editor, Viewer) with predefined permissions.
Access Control Lists (ACL): Defines permissions for specific users or groups on particular resources.
Policy-Based Access Control (PBAC): Uses rules or policies to determine access.
Attribute-Based Access Control (ABAC): Grants access based on user attributes (e.g., department, location, device).
Permission-Based Access Control: Directly assigns specific permissions.

Real-World Authorization Scenarios

1. Corporate Environment

CEO: Full access to all company data.
Manager: Access to department-specific data.
HR Staff: Access to employee records.
Employee: Access to their personal workspace.

2. Social Media Platform

Admin: Modify site settings.
Moderator: Manage posts and users.
Premium User: Access exclusive features.
Regular User: Basic platform access.

3. Banking Application

Bank Teller: Process transactions.
Account Owner: View and modify accounts.
Joint Account Holder: Shared access to specific accounts.
Read-only Access: View statements only.

Key Differences Between Authentication and Authorization

Authentication	Authorization
Verifies identity	Grants access rights
Happens first	Happens after authentication
Visible to the user (e.g., login form)	Invisible to the user (background process)
Can be initiated by the user	Controlled by the system or administrator
Failure results in no access	Failure results in restricted access
Example: Password, fingerprint, SSO	Example: Admin privileges, file permissions

Session Management: Keeping Users Secure

Authentication and authorization may start the process, but session management ensures users remain securely logged in as they continue interacting with the system.

Key elements of secure session management include:

Session Tokens: Unique tokens (e.g., JWTs) generated at login to track the user.
Timeouts and Expiration: Sessions should expire after inactivity to prevent unauthorized access.
Token Rotation: Refreshing tokens periodically improves security.
Re-authentication: Prompting users to re-authenticate for sensitive actions, like changing account settings.

A detailed blog on session management is coming soon — stay tuned!

Role-Based Access Control (RBAC): A Closer Look

RBAC remains one of the most effective authorization strategies. Users are assigned roles, and each role has a predefined set of permissions.

For example:

Admin: Full permissions
Editor: Can create and edit content
Viewer: Read-only access

RBAC ensures users have just enough permissions to perform their tasks — no more, no less. We'll dive deeper into RBAC strategies in an upcoming blog.

Final Thoughts

Remember: Security is a journey, not a destination. Stay vigilant.

Understanding authentication and authorization is crucial for building secure, user-friendly systems. Authentication ensures users are who they say they are, while authorization ensures they can only do what they're allowed to. Together, they form the backbone of modern digital security.

By implementing best practices — like Multi-Factor Authentication, Role-Based Access Control, and secure session management — you create a safer, more resilient digital environment.

Navigating PostgreSQL - Query Optimizations

Aditya Agrawal — Sat, 15 Mar 2025 05:00:00 +0000

In this part, we'll learn how to think about query optimizations in PostgreSQL with practical examples, using all our previous knowledge of different scans, joins, and indexes.

Query Optimization

Query optimization is the process of improving the performance of a query. It involves analyzing and enhancing multiple aspects of how your queries interact with PostgreSQL. While there are many factors to consider, we can break down the optimization process into a few key areas:

Query Structure and Logic
- Writing efficient WHERE clauses
- Using appropriate JOINs
- Avoiding unnecessary subqueries
- Proper use of aggregations
Schema Design and Indexing
- Creating the right indexes for your queries
- Understanding when indexes help or hurt
- Maintaining optimal table statistics
- Smart choices in data types and constraints
Data Access Patterns
- Understanding how your application uses data
- Identifying frequently accessed columns
- Analyzing query patterns and frequency
- Considering caching strategies

The key to successful optimization is taking a methodical approach:

Measure current performance
Identify bottlenecks using EXPLAIN ANALYZE
Make targeted improvements
Validate the changes
Repeat if necessary

Let's explore these concepts with practical examples...

Example 1:

Get all the sales, employees and departments for the last month, where the sale amount is greater than 1090.

create view latest_extended_sales as
SELECT
    s.id as sale_id,
    s.amount sale_amount,
    s.sale_date as sale_date,
    e.id as employee_id,
    d.id as department_id,
    d.department_name as department_name
from
    sales s
    join employees e on s.employee_id=e.id
    join departments d on e.department_id=d.id
where
    sale_date > now()-interval'1 months'
    and s.amount > 1090
;

explain analyze 
select * from latest_extended_sales order by sale_id desc;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=268392.55..268402.13 rows=3831 width=538) (actual time=1064.998..1065.159 rows=3932 loops=1)
   Sort Key: s.id DESC
   Sort Method: quicksort  Memory: 404kB
   ->  Nested Loop  (cost=0.57..268164.54 rows=3831 width=538) (actual time=6.135..1063.872 rows=3932 loops=1)
         ->  Nested Loop  (cost=0.42..267542.42 rows=3831 width=22) (actual time=6.126..1060.664 rows=3932 loops=1)
               ->  Seq Scan on sales s  (cost=0.00..260474.20 rows=3831 width=18) (actual time=6.101..1050.484 rows=3932 loops=1)
                     Filter: ((amount > '1090'::numeric) AND (sale_date > (now() - '1 mon'::interval)))
                     Rows Removed by Filter: 9835028
               ->  Index Scan using employees_pkey on employees e  (cost=0.42..1.85 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3932)
                     Index Cond: (id = s.employee_id)
         ->  Index Scan using departments_pkey on departments d  (cost=0.14..0.16 rows=1 width=520) (actual time=0.001..0.001 rows=1 loops=3932)
               Index Cond: (id = e.department_id)
 Planning Time: 0.237 ms
 Execution Time: 1065.935 ms
(18 rows)

Let's analyze the query plan to understand where we can make optimizations.

Understanding the Query Plan

The query plan shows several key operations:

Sequential Scan on sales table:
- Scans over 9.8M rows to filter by sale_date and amount
- Takes 1050ms - the major bottleneck
- Removes 9.8M rows that don't match filters
Index Scans:
- Uses index on employees table to match employee_id
- Uses index on departments table to match department_id
- These operations are relatively fast
Overall Performance:
- Total execution time: 1065ms
- Most time spent on sequential scan (1050ms)
- Remaining operations only take ~15ms combined

Optimizing the Query

The main bottleneck is the sequential scan on the sales table that examines over 9.8M rows. The query plan shows this operation takes 1050ms out of the total 1065ms execution time. Since we're filtering on both sale_date and amount columns, and scanning the full table to apply these filters, we should consider creating appropriate indexes to avoid the costly sequential scan.

postgres=# create index on sales(amount);
CREATE INDEX
Time: 15829.995 ms (00:15.830)


QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=66342.37..66351.94 rows=3830 width=538) (actual time=340.297..340.749 rows=3932 loops=1)
   Sort Key: s.id DESC
   Sort Method: quicksort  Memory: 404kB
   ->  Nested Loop  (cost=1.01..66114.42 rows=3830 width=538) (actual time=0.204..338.719 rows=3932 loops=1)
         ->  Nested Loop  (cost=0.86..66017.29 rows=3830 width=22) (actual time=0.197..337.137 rows=3932 loops=1)
               ->  Index Scan using sales_amount_idx on sales s  (cost=0.43..58950.51 rows=3830 width=18) (actual time=0.189..311.728 rows=3932 loops=1)
                     Index Cond: (amount > '1090'::numeric)
                     Filter: (sale_date > (now() - '1 mon'::interval))
                     Rows Removed by Filter: 94309
               ->  Index Scan using employees_pkey on employees e  (cost=0.42..1.85 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3932)
                     Index Cond: (id = s.employee_id)
         ->  Memoize  (cost=0.15..0.17 rows=1 width=520) (actual time=0.000..0.000 rows=1 loops=3932)
               Cache Key: e.department_id
               Cache Mode: logical
               Hits: 3922  Misses: 10  Evictions: 0  Overflows: 0  Memory Usage: 2kB
               ->  Index Scan using departments_pkey on departments d  (cost=0.14..0.16 rows=1 width=520) (actual time=0.001..0.001 rows=1 loops=10)
                     Index Cond: (id = e.department_id)
 Planning Time: 0.369 ms
 Execution Time: 340.886 ms
(19 rows)

1065ms -> 340ms
Improvement: 3.1x

Analysis:

Created an index on the amount column of the sales table
The query planner now uses this index to filter rows where amount > 1090
Index scan on sales table takes 311ms vs 1050ms before - a significant improvement
However, the sale_date filter is still applied after the index scan, removing 94,309 additional rows
Total execution time reduced from 1065ms to 340ms (3.1x faster)

This shows the index helps but isn't optimal since we're still doing post-index filtering on sale_date. Let's create a composite index on both columns to improve this further.

Optimizing the Query Further

postgres=# drop index on sales_amount_idx;
postgres=# create index on sales(amount, sale_date);
CREATE INDEX


QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=12543.60..12553.18 rows=3831 width=538) (actual time=26.234..26.545 rows=3932 loops=1)
   Sort Key: s.id DESC
   Sort Method: quicksort  Memory: 404kB
   ->  Nested Loop  (cost=1.02..12315.59 rows=3831 width=538) (actual time=0.064..24.952 rows=3932 loops=1)
         ->  Nested Loop  (cost=0.86..12218.43 rows=3831 width=22) (actual time=0.058..23.751 rows=3932 loops=1)
               ->  Index Scan using sales_amount_sale_date_idx on sales s  (cost=0.44..5150.22 rows=3831 width=18) (actual time=0.051..12.323 rows=3932 loops=1)
                     Index Cond: ((amount > '1090'::numeric) AND (sale_date > (now() - '1 mon'::interval)))
               ->  Index Scan using employees_pkey on employees e  (cost=0.42..1.85 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=3932)
                     Index Cond: (id = s.employee_id)
         ->  Memoize  (cost=0.15..0.17 rows=1 width=520) (actual time=0.000..0.000 rows=1 loops=3932)
               Cache Key: e.department_id
               Cache Mode: logical
               Hits: 3922  Misses: 10  Evictions: 0  Overflows: 0  Memory Usage: 2kB
               ->  Index Scan using departments_pkey on departments d  (cost=0.14..0.16 rows=1 width=520) (actual time=0.001..0.001 rows=1 loops=10)
                     Index Cond: (id = e.department_id)
 Planning Time: 0.379 ms
 Execution Time: 26.683 ms
(17 rows)

1065ms -> 26ms
Improvement: 40x

Analysis:

Created a composite index on amount and sale_date columns of the sales table
The query planner now uses this index to efficiently filter both conditions:
- amount > 1090
- sale_date > now() - 1 month
Index scan on sales table takes only 12ms vs 1050ms before
Additional index scans on employees and departments tables are very fast (<1ms) due to primary key indexes
Total execution time reduced dramatically from 1065ms to 26ms
Overall improvement: 40x faster query performance

Example 2:

Get the employee with the highest salary for each department.

create index on employees(department_id, salary);


explain analyze
with cte as (
    select department_id, max(salary) m_salary
    from employees
    group by department_id
), cte2 as (
    select 
        d.department_name, 
        e.id, 
        e.salary
    from
        employees e
        join cte on cte.department_id=e.department_id and e.salary=cte.m_salary
        join departments d on d.id=cte.department_id
) select * from cte2 order by department_name
;

Sort  (cost=23358.94..23358.94 rows=1 width=528) (actual time=243.129..243.131 rows=10 loops=1)
   Sort Key: d.department_name
   Sort Method: quicksort  Memory: 25kB
   ->  Nested Loop  (cost=23334.57..23358.93 rows=1 width=528) (actual time=243.061..243.119 rows=10 loops=1)
         Join Filter: (employees.department_id = d.id)
         ->  Nested Loop  (cost=23334.42..23358.75 rows=1 width=20) (actual time=243.055..243.105 rows=10 loops=1)
               ->  HashAggregate  (cost=23334.00..23334.10 rows=10 width=36) (actual time=243.036..243.039 rows=10 loops=1)
                     Group Key: employees.department_id
                     Batches: 1  Memory Usage: 24kB
                     ->  Seq Scan on employees  (cost=0.00..18334.00 rows=1000000 width=12) (actual time=0.006..51.934 rows=1000000 loops=1)
               ->  Index Scan using employees_department_id_salary_idx on employees e  (cost=0.42..2.44 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=10)
                     Index Cond: ((department_id = employees.department_id) AND (salary = (max(employees.salary))))
         ->  Index Scan using departments_pkey on departments d  (cost=0.14..0.16 rows=1 width=520) (actual time=0.001..0.001 rows=1 loops=10)
               Index Cond: (id = e.department_id)
 Planning Time: 0.236 ms
 Execution Time: 243.167 ms
(16 rows)

Let's break down what this query does:

First, find the highest salary in each department
Then, get the employee details who has that maximum salary

Understanding the Query Plan

Let's analyze the execution plan:

The query starts with a sequential scan on the employees table (which has over 10M rows)
- This scans the ENTIRE table to find max salaries per department
- Very expensive operation taking most of the 243ms execution time
For each department's max salary:
- Uses index scan on employees to find matching employee
- Joins with departments table for department names
Performance issues:
- Total execution time: 243ms
- Sequential scan is extremely inefficient
- Not utilizing indexes effectively

While this may not seem slow, but in the database world this is a disaster.

This query performance is problematic for a production database:

Sequential scanning millions of rows is resource intensive
Response time is too high for interactive applications
Could cause bottlenecks under concurrent load

The key question is: Can we restructure this query to:

Avoid the full table scan?
Make better use of our indexes?
Process only the rows we actually need?

Optimizing the Query

explain analyze
select 
    d.department_name, 
    e.id, 
    e.salary
from
    departments d
    join lateral (
        select 
            id, salary
        from
            employees
        where
            department_id=d.id
        order by
            salary desc
        limit 1
    ) e on true
order by department_name
;


QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=87.44..87.79 rows=140 width=528) (actual time=0.085..0.086 rows=10 loops=1)
   Sort Key: d.department_name
   Sort Method: quicksort  Memory: 25kB
   ->  Nested Loop  (cost=0.42..82.45 rows=140 width=528) (actual time=0.016..0.076 rows=10 loops=1)
         ->  Seq Scan on departments d  (cost=0.00..11.40 rows=140 width=520) (actual time=0.003..0.004 rows=10 loops=1)
         ->  Limit  (cost=0.42..0.49 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=10)
               ->  Index Scan Backward using employees_department_id_salary_idx on employees  (cost=0.42..6251.68 rows=100000 width=12) (actual time=0.006..0.006 rows=1 loops=10)
                     Index Cond: (department_id = d.id)
 Planning Time: 0.101 ms
 Execution Time: 0.101 ms
(10 rows)

243.167ms -> 0.101ms
Improvement: 2400x

Let's analyze the query plan:

Departments Table Scan
- A sequential scan is performed on the departments table
- This is acceptable since the table only has 10 rows
- Cost is minimal at 0.003-0.004ms
Employees Table Index Scan
- For each department, an index scan is done on the employees table
- The scan is backward (descending) to get highest salary first
- The LIMIT 1 ensures we only get the top earner
- Very efficient: 0.006-0.007ms per department
Overall Performance
- Total execution time: 0.101ms
- Compared to original 243.167ms
- Represents a 2400x improvement
- Shows the power of proper indexing and query structure

Key Takeaways

Query Optimization Process
- Start with EXPLAIN ANALYZE to understand the execution plan
- Identify the most expensive operations
- Look for opportunities to use indexes effectively
- Restructure query to minimize resource usage
Important Query Plan Concepts
- Sequential Scan: Full table scan
- Index Scan: Using an index to access data
- Nested Loop: Row-by-row join operation
- Hash Join: Build hash table for joining
- Merge Join: Pre-sorted merge operation
- LIMIT: Restricting number of rows returned
Success Metrics
- Reduced execution time
- Better resource utilization
- Proper use of available indexes
- Scalable query structure

References

Navigating PostgreSQL - Join Strategies

Aditya Agrawal — Fri, 14 Mar 2025 05:00:00 +0000

Nested Loops

Nested loops join is one of the simplest join strategies that PostgreSQL uses. It works by taking each row from one table (outer table) and finding matching rows in another table (inner table) by scanning or using an index.

How Nested Loops Join Works

When PostgreSQL performs a nested loops join:

It first gets a row from the outer table (usually the table with fewer matching rows after filters)
For each outer row, it scans the inner table (either sequentially or using an index) to find matching rows
When matches are found, it combines the rows according to the join condition
This process repeats for every row from the outer table

Nested loops are most efficient when:

The outer table has very few rows after filtering
The inner table has an index on the join columns
The join needs to return only a small number of rows

think of this like a double for loop.

for (order in orders) {
    for (product in products) {
        if (order.product_id == product.id) {
            // join the order and product
        }
    }
}

For example, in this query plan:

postgres=# explain select * from orders o join products p on p.id=o.product_id where o.id<107219;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Nested Loop  (cost=0.70..14.81 rows=11 width=50)
   ->  Index Scan using orders_pkey on orders o  (cost=0.42..2.62 rows=11 width=27)
         Index Cond: (id < 107219)
   ->  Index Scan using products_pkey on products p  (cost=0.27..1.11 rows=1 width=23)
         Index Cond: (id = o.product_id)
(5 rows)

Note: order is the outer table and product is the inner table.

Understanding the Query Plan

The index scan on orders returns 11 rows.
For each of these rows, the query performs an index scan on products.
The index scan on products returns 1 row for each of the 11 orders.

Hash Join / Hash

Hash join is another join strategy that PostgreSQL uses. It works by creating a hash table from one of the tables and then using that hash table to find matching rows in another table.

How Hash Join Works

When PostgreSQL performs a hash join:

It creates a hash table from one of the tables (usually the smaller one)
It then uses that hash table to find matching rows in another table
When matches are found, it combines the rows according to the join condition

Hash join is most efficient when:

The join needs to return only a small number of rows
The inner table has an index on the join columns

think of this like a hash map.

hash_map = {}
for (product in products) {
    hash_map[product.id] = product
}

for (order in orders) {
    if (hash_map[order.product_id]) {
        // join the order and product
    }
}

postgres=# explain select * from orders o join products p on p.id=o.product_id where o.id<117219;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Hash Join  (cost=15.68..339.95 rows=10536 width=50)
   Hash Cond: (o.product_id = p.id)
   ->  Index Scan using orders_pkey on orders o  (cost=0.42..296.81 rows=10536 width=27)
         Index Cond: (id < 117219)
   ->  Hash  (cost=9.00..9.00 rows=500 width=23)
         ->  Seq Scan on products p  (cost=0.00..9.00 rows=500 width=23)
(6 rows)

Note: orders is the outer table and products is the inner table.

Understanding the Query Plan

The index scan on orders returns 10,536 rows.
The hash join creates a hash table from the products table (smaller table).
For each row in the orders table, the query uses the hash table to find matching rows in the products table.
The hash join returns 10,536 rows.

Why Did PostgreSQL Choose a Hash Join Instead of a Nested Loop?

Key Differences from Previous Query (id < 107219):

The previous query had only 11 rows from orders, so a Nested Loop Join was efficient.
This query has 10,536 rows, so a Nested Loop would require 10,536 index lookups in products, which would be slow.
Instead, PostgreSQL builds a hash table once (cheap) and does fast lookups (O(1)) instead of repeated index scans (O(log N)).

Merge Join

Merge join is another join strategy that PostgreSQL uses. It works by sorting the two tables and then merging them together.

How Merge Join Works

When PostgreSQL performs a merge join:

It sorts the two tables by the join columns
It then merges the two tables together
When matches are found, it combines the rows according to the join condition

Merge join is most efficient when:

Both tables are already sorted (or can be sorted efficiently).
There's no suitable index for a Nested Loop.
The tables are large, making Hash Join expensive.

think of this like a merge sort.

merge_sort(left, right) {
    if (left.length == 0) return right;
    if (right.length == 0) return left;
}

explain
SELECT * 
FROM employees e
JOIN salary s
  ON e.id = s.employee_id
WHERE e.age < 40;

QUERY PLAN 
--------------------------------------------------------------
Merge Join  (cost=198.11..268.19 rows=10 width=488)
   Merge Cond: (e.id = s.employee_id)
   ->  Index Scan using employees_id on employees e  (cost=0.29..656.28 rows=101 width=244)
         Filter: (age < 40)
   ->  Sort  (cost=197.83..200.33 rows=1000 width=244)
         Sort Key: s.employee_id
         ->  Seq Scan on salary s (cost=0.00..148.00 rows=1000 width=244)

Understanding the Query Plan

The index scan on employees returns 101 rows.
The sort operation sorts the salary table by the join column (employee_id).
The merge join then merges the two sorted tables together.
The merge join returns 10 rows.

Why Did PostgreSQL Choose a Merge Join Instead of Nested Loop or Hash Join?

Key Differences from Previous Queries:

The employees table is filtered (age < 40), reducing rows to 101.
The salary table is not indexed on employee_id, so a Hash Join would require creating a large hash table.
The tables can be efficiently sorted and merged, making a Merge Join the best option.

Summary

Join Type	When It's Used	How It Works
Nested Loop Join	Best for small outer tables (<1000 rows) or when inner table has indexes on join columns. Ideal when outer table is heavily filtered.	Iterates over each row of the outer table and checks for matches in the inner table using either sequential scan or index scan. Works like a double for-loop.
Hash Join	Optimal for medium to large tables with equality joins. Used when no useful indexes exist and memory is sufficient.	Builds an in-memory hash table from the smaller table, then probes it using rows from larger table. Hash table enables O(1) lookups.
Merge Join	Best when data is already sorted (e.g. clustered index) or when sort cost is acceptable. Good for large tables that exceed memory.	First sorts both inputs on join key if needed, then walks through them in parallel, matching rows. Similar to merge phase in merge sort.

Join Type	Time Complexity	Space Complexity
Nested Loop Join	O(n * m) without index, O(n * log m) with index where n=outer rows, m=inner rows	O(1) - Minimal memory usage
Hash Join	O(n + m) for building and probing	O(n) where n = size of smaller table
Merge Join	O(n log n + m log m) if sorting needed, O(n + m) if pre-sorted	O(1) if pre-sorted, O(n + m) if sorting needed

References

Next Steps

Apply all our learnings to a real query and optimize it.

Navigating PostgreSQL - Index, Index Only and Bitmap Index Scans Explained

Aditya Agrawal — Thu, 13 Mar 2025 05:00:00 +0000

Index Scan

The index scan is one of the most fundamental access methods in PostgreSQL. Unlike a sequential scan that reads the entire table, an index scan leverages a pre-built index structure to efficiently locate specific rows.

How Index Scan Works

When PostgreSQL performs an index scan:

It traverses the index structure (typically a B-tree) to find entries matching the query condition
For each matching index entry, it retrieves the corresponding row's physical location (TID - Tuple Identifier)
It then visits the table to fetch the complete row data using these locations

Index scans are particularly efficient when:

The query is highly selective (returning a small percentage of rows)
The table is large
The index covers the columns used in the WHERE clause
The data needs to be returned in the index's sort order

postgres=# explain select * from orders where id='45555';
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using orders_pkey on orders  (cost=0.42..2.44 rows=1 width=27)
   Index Cond: (id = 45555)
(2 rows)

Note: Index scans can be less efficient than sequential scans when retrieving a large portion of the table, as each row access requires a random I/O operation to fetch the table data.

Index Only Scan (since PostgreSQL 9.2)

The Index Only Scan is a more specialized and optimized version of the index scan that can significantly improve query performance. Unlike a regular index scan that must access both the index and the table, an Index Only Scan retrieves all required data directly from the index itself, eliminating the need to visit the table.

How Index Only Scan Works

When PostgreSQL performs an Index Only Scan:

It checks if all columns needed by the query are stored in the index
It traverses the index structure to find matching entries
It retrieves the required data directly from the index without accessing the table

This access method is particularly efficient when:

The query only needs columns that are included in the index (either as key columns or included columns)
The table is large, making table access expensive
The visibility map indicates that all tuples are visible to all transactions (reducing the need for heap fetches)

postgres=# explain select id from orders where id='45555';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Only Scan using orders_pkey on orders  (cost=0.42..1.44 rows=1 width=4)
   Index Cond: (id = 45555)
(2 rows)

Note: the select id above is the only column needed by the query, and it is included in the index.

Visibility Map Considerations (Advanced)

PostgreSQL maintains a visibility map to track which pages contain only tuples visible to all transactions. For an Index Only Scan to be truly index only, it needs to verify tuple visibility:

If the visibility map shows all tuples on a page are visible, PostgreSQL can skip the heap check
Otherwise, it must visit the heap to check visibility, reducing the efficiency advantage

Bitmap Index Scan / Bitmap Heap Scan / Recheck Cond

A Bitmap Index Scan is a sophisticated access method that PostgreSQL uses when an Index Scan would be inefficient but a Sequential Scan would be overkill. Here's how it works:

How Bitmap Scans Work

Gather Phase: The bitmap scan fetches all matching tuple-pointers (row locations) from the index in one operation
Sort Phase: It organizes these pointers into a memory-efficient bitmap data structure, sorted by physical location on disk
Access Phase: It then visits the table pages in physical order, minimizing random I/O operations

This approach combines the selectivity of indexes with the efficiency of sequential disk access patterns.

When Bitmap Scans Are Used

PostgreSQL typically chooses a bitmap scan when:

The query is expected to return a moderate number of rows (too many for an efficient index scan)
The rows are scattered throughout the table (making random access inefficient)
Multiple indexes can be combined (using bitmap AND/OR operations)

Advantages of Bitmap Scans

Reduced I/O: By sorting tuple accesses by physical location, it minimizes disk seek operations
Index Combining: Can use multiple indexes simultaneously for complex conditions (e.g., WHERE x > 10 AND y < 20)
Memory Efficiency: The bitmap structure uses minimal memory even for large result sets
Batched Processing: Processes rows in batches, improving CPU cache utilization

postgres=# explain SELECT * FROM sampletable WHERE x < 423;
                                    QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on sampletable (cost=9313.62..68396.35 rows=402218 width=11)
       Recheck Cond: (x < '423'::numeric)
       -> Bitmap Index Scan on idx_x (cost=0.00..9213.07 rows=402218 width=0)
          Index Cond: (x < '423'::numeric)
(4 rows)

Explain Analyze

The Explain Analyze command provides a detailed breakdown of how PostgreSQL executes a query, including actual execution time and resource usage.

How Explain Analyze Works

The Explain Analyze command is used to get both the query plan and the actual execution time and resource usage.

Execution Plan: It generates an execution plan similar to EXPLAIN
Actual Execution: It actually executes the query and provides the actual execution time and resource usage.

!Caution: It actually executes the query.

postgres=# explain analyze select * from orders o, products p  where o.amount > 50000 and p.id=o.product_id;

                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=15.25..20507.79 rows=156593 width=50) (actual time=0.091..154.404 rows=157456 loops=1)
   Hash Cond: (o.product_id = p.id)
   ->  Seq Scan on orders o  (cost=0.00..20078.00 rows=156593 width=27) (actual time=0.006..128.617 rows=157456 loops=1)
         Filter: (amount > '50000'::numeric)
         Rows Removed by Filter: 842544
   ->  Hash  (cost=9.00..9.00 rows=500 width=23) (actual time=0.080..0.081 rows=500 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 36kB
         ->  Seq Scan on products p  (cost=0.00..9.00 rows=500 width=23) (actual time=0.004..0.039 rows=500 loops=1)

 Planning Time: 0.159 ms
 Execution Time: 158.574 ms

Let's break down how to read and understand a query plan!

Reading Query Plans

When analyzing a query plan, focus on these key elements:

Execution Time: The total time taken to complete the query
Node Times: The actual processing time for each operation node
Operation Types: The specific operations (like Seq Scan, Hash Join) being performed
Node Hierarchy: How operations are nested and depend on each other

Query Plan Structure

Query plans in PostgreSQL follow a hierarchical structure:

Each indented arrow (->) represents a node in the execution tree
Child nodes are processed before their parent nodes
The execution flows from the innermost (most indented) nodes outward
Each node represents a specific operation PostgreSQL performs

Example 1

explain analyze select o.*, p.* from orders o, products p  where o.amount > 50000 and p.id=o.product_id;

                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=15.25..20507.79 rows=156593 width=50) (actual time=0.094..154.278 rows=157456 loops=1)
   Hash Cond: (o.product_id = p.id)
   ->  Seq Scan on orders o  (cost=0.00..20078.00 rows=156593 width=27) (actual time=0.007..128.467 rows=157456 loops=1)
         Filter: (amount > '50000'::numeric)
         Rows Removed by Filter: 842544
   ->  Hash  (cost=9.00..9.00 rows=500 width=23) (actual time=0.082..0.084 rows=500 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 36kB
         ->  Seq Scan on products p  (cost=0.00..9.00 rows=500 width=23) (actual time=0.004..0.041 rows=500 loops=1)
 Planning Time: 0.203 ms
 Execution Time: 158.484 ms
(10 rows)

Step-by-Step Breakdown of the Query Execution Plan

1. Sequential Scan (Seq Scan) on orders o

The database performs a sequential scan on the orders table
It applies the filter amount > 50000, meaning it only keeps rows where the amount column exceeds 50,000
Rows Processed:
- Total rows in orders: 1,000,000
- Rows matching the filter (amount > 50000): 157,456
- Rows removed by the filter: 842,544 (remaining rows that didn't satisfy the condition)
This operation took 128.460 ms

2. Sequential Scan (Seq Scan) on products p

The database scans the entire products table (500 rows)
Since no filters are applied, all rows are read
The database creates a hash table in memory using the id column as the key
This operation took 0.037 ms, and the hash table used 36 kB of memory

3. Hash Join (Hash Join) Between orders o and products p

The database performs a hash join to match the product_id in orders with the id in products:
- The hash table (from products) allows quick lookups
- The database iterates through the filtered orders rows and checks for matches in the hash table
- The join condition is: o.product_id = p.id
Estimated output: 156,593 rows
Actual output: 157,456 rows
This operation took 154.278 ms

Final Output

The database returns 157,456 rows, each containing all columns from both orders and products
Total Execution Time: 158.484 ms

Example 2

Note: Only the where clause is different.

explain analyze select o.*, p.* from orders o, products p  where o.amount > 99000 and p.id=o.product_id;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.27..20274.89 rows=648 width=50) (actual time=0.062..119.781 rows=98 loops=1)
   ->  Seq Scan on orders o  (cost=0.00..20078.00 rows=648 width=27) (actual time=0.057..119.554 rows=98 loops=1)
         Filter: (amount > '99000'::numeric)
         Rows Removed by Filter: 999902
   ->  Index Scan using products_pkey on products p  (cost=0.27..0.30 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=98)
         Index Cond: (id = o.product_id)
 Planning Time: 0.162 ms
 Execution Time: 119.804 ms
(8 rows)

Step-by-Step Breakdown of the Query Execution Plan

1. Sequential Scan (Seq Scan) on orders o

The database scans the entire orders table.
It applies the filter amount > 99000, meaning it only keeps rows where amount is greater than 99,000.
Rows Processed:
- Total rows in orders: 1,000,000
- Rows matching the filter (amount > 99000): 98
- Rows removed by the filter: 999,902
- Execution Time: 119.554 ms

2. Index Scan (Index Scan using products_pkey) on products p

The database does NOT scan the entire products table.
Instead, it uses the primary key index (products_pkey) to look up id = o.product_id.
Why is an index scan used?
- The products table has an index on id
- Since we are retrieving a single row for each lookup.
- And the fact that the number of lookups is only 98, so index scan would be more efficient than a sequential scan.
- Execution Time: 0.002 ms per lookup.
- Total index scans performed: 98 (once for each matching row from orders).

3. Nested Loop Join (Nested Loop)

Since only 98 rows passed the filter from orders, the database chooses a Nested Loop Join instead of a Hash Join.
How it works:
- The system takes one row from orders.
- It performs an index scan on products to find the matching row.
- It repeats this process 98 times.
Total Execution Time: 119.781 ms.

Takeaways

The query plan is a tree structure that shows the operations that PostgreSQL will perform to execute the query.
Index scans are faster than sequential scans when retrieving a small percentage of rows.
Index only scans are even faster than index scans when the query only needs columns that are included in the index.
Bitmap scans are used when the query is expected to return a moderate number of rows.
The Explain Analyze command provides a detailed breakdown of how PostgreSQL executes a query, including actual execution time and resource usage.

References

Next Steps

Learn about the different types of join strategies in PostgreSQL.
Applying our learnings to optimize a real query.

Navigating PostgreSQL - The explain command and sequential scans

Aditya Agrawal — Wed, 12 Mar 2025 05:00:00 +0000

The Power of EXPLAIN in PostgreSQL

When optimizing database performance, it's essential to understand how your database executes queries. PostgreSQL provides the powerful EXPLAIN command that reveals the execution plan for your queries, helping you identify potential bottlenecks and optimization opportunities.

What is EXPLAIN?

The EXPLAIN command shows how PostgreSQL will execute your query. It reveals:

How tables will be scanned (sequential scan, index scan, etc.)
What join methods will be used for multiple tables
Cost estimates and other performance metrics

Here's how to use it:

EXPLAIN SELECT * FROM users;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on users  (cost=0.00..258.00 rows=5000 width=244)
                           ^      ^           ^         ^
                           |      |           |         |
                        startup total       output   average
                        cost    cost        rows     row size

Understanding the EXPLAIN Output

Let's break down each component:

Startup cost (0.00): Estimated computational units needed before the first row can be returned
Total cost (258.00): Estimated computational units for the entire query execution
Rows (5000): Estimated number of rows the operation will return
Width (244): Estimated average size of each row in bytes

PostgreSQL's Cost Model Explained

PostgreSQL uses a cost-based optimizer that evaluates different execution plans and selects the one with the lowest estimated cost.

The Unit of Cost

The baseline unit for cost is "the effort to fetch a single page sequentially." This provides a reference point for all other operations:

-- Key cost parameters that influence planning decisions
seq_page_cost = 1.0                   -- Cost of sequential page fetch (baseline)
random_page_cost = 4.0                -- Cost of random page fetch  
cpu_tuple_cost = 0.01                 -- Cost of processing a single row
cpu_index_tuple_cost = 0.005          -- Cost of processing an index entry
cpu_operator_cost = 0.0025            -- Cost of processing an operator

-- You can adjust these parameters for your environment
SET random_page_cost = 2.0;           -- Common setting for SSD storage
SHOW random_page_cost;                -- Verify the change

Understanding Page Costs: Sequential vs Random Access

Think of PostgreSQL's cost model like a librarian deciding the fastest way to find books:

Sequential Page Cost (seq_page_cost = 1.0)

Imagine walking along a bookshelf, checking each book in order. This is a sequential scan - it's predictable and consistent. PostgreSQL assigns this a baseline cost of 1.0 unit.

Random Page Cost (random_page_cost = 4.0)

Now imagine having to jump around the library, consulting the catalog and fetching books from different sections. This random access takes more effort! By default, PostgreSQL considers random access to be 4 times more expensive than sequential access.

Note: Cost values are unitless and relative, with 1.0 representing the baseline cost of a sequential page read.

Why This Matters

These cost settings determine when PostgreSQL chooses indexes:

With HDDs (traditional hard drives): Random access is genuinely slower (heads must physically move)
With SSDs: Random access is almost as fast as sequential access

SSD has no moving parts, so random access is almost as fast as sequential access.

For modern SSDs, many DBAs adjust the random_page_cost much lower (often to 1.1-2.0), which encourages PostgreSQL to use indexes more frequently, potentially improving performance.

How PostgreSQL Makes Planning Decisions

The query planner evaluates multiple potential execution plans by:

Estimating the number of rows that would match the query conditions
Calculating the cost of different access methods (sequential scans, index scans, etc.)
Considering table statistics and data distribution
Selecting the plan with the lowest total estimated cost

Sequential Scan: The Fundamental Table Access Method

A sequential scan reads the entire table from start to finish, examining every row.

EXPLAIN SELECT * FROM users;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on users  (cost=0.00..258.00 rows=5000 width=244)

When Sequential Scan is Used

PostgreSQL will choose a sequential scan when:

Small Tables: When a table is small enough that reading the entire table is faster than using an index
Large Result Sets: When a query is expected to return a significant portion of the table (typically >5-10%)
No Suitable Indexes: When no indexes exist that could help with the query
Full Table Operations: For operations that need to process every row (like COUNT(*) without conditions)

Advantages of Sequential Scan

Simplicity: No index lookup overhead
Predictable I/O Pattern: Reads data in physical order, which is efficient for disk I/O
Memory Efficiency: Can benefit from OS-level read-ahead caching
No Index Maintenance: Doesn't require indexes to be maintained

Disadvantages of Sequential Scan

Inefficient for Large Tables: Reading millions of rows when you only need a few is wasteful
Slower for Selective Queries: When you need only a small subset of data, indexes usually perform better
Resource Intensive: Consumes more I/O, CPU, and memory when scanning large tables

Optimizing Sequential Scans

Increase effective_cache_size: Helps PostgreSQL estimate available system memory for caching
Adjust work_mem: Provides more memory for sorting operations during scans
Consider table partitioning: Reduces the amount of data scanned for queries that can target specific partitions
Use parallel sequential scans: Set max_parallel_workers_per_gather to utilize multiple CPU cores

Summary

The EXPLAIN command is your window into PostgreSQL's decision-making process. By understanding how the query planner evaluates costs and chooses execution plans, you can make informed decisions about:

When to create indexes
How to structure your queries
How to configure PostgreSQL for your hardware
When sequential scans are appropriate and when they should be avoided

Next Steps:

Learn more about how data is stored on disk
Explore index scans and when they're preferred over sequential scans
Discover other types of scans (bitmap scans, index-only scans)
Master techniques to optimize your queries for better performance

References

Originally published at https://www.adiagr.com

Navigating PostgreSQL - Pages and Blocks

Aditya Agrawal — Tue, 11 Mar 2025 05:00:00 +0000

In PostgreSQL, data is stored in pages. A page is the smallest unit of data that can be read from or written to disk. Understanding how PostgreSQL organizes data at the storage level helps in optimizing database performance and understanding the internals of the database system.

What Are Pages and Blocks?

In PostgreSQL, the terms "page" and "block" are often used interchangeably. A page is a fixed-length block of data, typically 8KB (8192 bytes) in size, though this can be modified when compiling the server.

When PostgreSQL reads or writes data to disk, it does so in these page-sized chunks. This is the fundamental storage and I/O unit in PostgreSQL.

postgres=# SELECT current_setting('block_size');
 current_setting
-----------------
 8192
(1 row)

Page Structure

Each page in PostgreSQL follows a specific layout with several components:

Anatomy of a PostgreSQL page

PageHeader (24 bytes) - Contains metadata about the page
ItemIdData - Array of item identifiers pointing to the actual items
Free space - Unallocated space where new data can be inserted
Items - The actual data items (rows/tuples)
Special space - Used by index access methods to store specialized data

Let's look at these components in more detail:

Don't be intimidated by these technical details! Understanding PostgreSQL's storage layout provides context for query execution and performance. This knowledge forms the foundation for deeper exploration of PostgreSQL's internal workings in future articles.

Page Header (PageHeaderData)

The page header occupies the first 24 bytes of each page and contains crucial information:

Field	Size	Description
`pd_lsn`	8 bytes	LSN (Log Sequence Number) for WAL (Write-Ahead Logging)
`pd_checksum`	2 bytes	Page checksum for data integrity
`pd_flags`	2 bytes	Flag bits
`pd_lower`	2 bytes	Offset to the start of free space
`pd_upper`	2 bytes	Offset to the end of free space
`pd_special`	2 bytes	Offset to the start of special space
`pd_pagesize_version`	2 bytes	Page size and version information
`pd_prune_xid`	4 bytes	Oldest unpruned XMAX on the page

create extension pageinspect;

-- Get the page header of the first page of the table: test
postgres=# SELECT * FROM page_header(get_raw_page(‘test’,0));
   lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid 
-----------|----------|-------|-------|-------|---------|----------|---------|----------
0/16FC9DB0 |        0 |     4 |   928 |   960 |    8192 |     8192 |       4 |         0
(1 row)

Item Identifiers (ItemIdData)

Following the page header are item identifiers, each requiring 4 bytes. These act as pointers to the actual items stored in the page. Each identifier contains:

A byte-offset to the start of an item
The length of the item in bytes
Attribute bits affecting its interpretation

When a new row is inserted, a new item identifier is allocated from the beginning of the unallocated space, and pd_lower is increased accordingly.

Items (Rows/Tuples)

The actual data items (rows or tuples) are stored in the space allocated backward from the end of unallocated space. Each row has a structure called HeapTupleHeaderData followed by the actual column values.

The row header contains information like:

Transaction IDs (t_xmin, t_xmax)
Command IDs (t_cid)
Item pointer (t_ctid)
Information masks and flags
Offset to user data

CTID: The Physical Location Identifier

In PostgreSQL, each row has a unique physical identifier called the CTID (Current Tuple Identifier), which represents its location as a pair of values: (page_number, tuple_index).

Page Types

PostgreSQL supports several types of pages, each with a specific purpose:

Table Pages: Store table rows
Index Pages: Store index entries
Bitmap Pages: Store bitmap index entries
Heap Pages: Store heap tuples
Special Pages: Store special data (e.g., TOAST data)

Takeaways

Pages are the smallest unit of data that can be read from or written to disk in PostgreSQL.
Each page has a header that contains metadata about the page.
Pages are used to store data for tables, indexes, and other database objects.
All data read and written to disk is done in pages.

References

Originally published at https://www.adiagr.com

Navigating PostgreSQL - Views

Aditya Agrawal — Mon, 10 Mar 2025 05:00:00 +0000

Views are virtual tables based on the result set of a SQL statement. Think of them as stored queries that can be treated as if they were tables. When you access a view, PostgreSQL runs the underlying query and presents the results.

Key Features

Abstraction: Views hide query complexity from end users
Security: Control access to sensitive data by exposing only specific columns/rows
Data Independence: Changes to underlying tables don't affect applications using views
Query Reusability: Complex queries can be saved and reused easily

Types of Views

1. Standard Views

Virtual tables that run their query each time they're accessed
Always show current data
No additional storage required
Ideal for frequently changing data

2. Materialized Views

Store the result set physically
Must be refreshed to see updated data
Excellent for complex queries with infrequently changing data
Improve query performance for expensive computations

Example

CREATE VIEW product_level_sales_of_top_regions AS
WITH regional_sales AS (
    SELECT
        region,
        SUM(amount) AS total_sales
    FROM
        orders
    GROUP BY
        region
),
top_regions AS (
    SELECT
        region,
        total_sales
    FROM
        regional_sales
    WHERE
        total_sales > 10000
    ORDER BY
        total_sales desc
    LIMIT
        10
), prodcut_wise_data as (
    SELECT
        o.region,
        o.product_id,
        SUM(o.qty) AS product_units,
        SUM(o.amount) AS product_sales
    FROM
        orders o
    JOIN
        top_regions tr on tr.region=o.region
    GROUP BY
        o.region,
        o.product_id
)
select
    *
from
    prodcut_wise_data
;

SELECT * FROM product_level_sales_of_top_regions;

Notice how the view:

Simplifies complex queries by presenting them as tables
Provides a way to reuse complex queries
Allows for data masking and security by exposing only specific columns/rows

Best Practices

1. Naming Conventions

Use clear, descriptive names
Consider prefixing views (e.g., v_active_customers or suffixing with _view)
Document the view's purpose

2. Performance Considerations

Use materialized views for compute-intensive queries
Index materialized views when appropriate
Be cautious with view chaining (views referencing other views)

3. Security

Grant minimum necessary permissions
Use views to implement row-level security
Consider using views for data masking

References

Next Steps

In the next parts, we will explore the different types of scans and join strategies in PostgreSQL.

Originally published at https://www.adiagr.com

Navigating PostgreSQL - CTEs

Aditya Agrawal — Fri, 07 Mar 2025 05:00:00 +0000

Introduction

PostgreSQL is a powerful, open-source object-relational database system with over 35 years of active development. It has earned its reputation through:

Reliability: Rock-solid data integrity
Feature Set: Advanced SQL capabilities
Performance: Optimized query execution
Extensibility: Custom functions and data types

Why PostgreSQL?

Let's look at the numbers that make PostgreSQL a compelling choice:

Metric	Value	Context
Global Deployments	1.7M+	Enterprise-scale adoption
Developer Preference	45.55%	Most preferred RDBMS
Market Share	17.4%	Among relational databases
User Satisfaction	80.6%	Reported happiness rate
Language Support	50+	Programming languages
Community Size	6,800+	GitHub forks
Production Usage	5M+	Active websites

De Facto goto relational database for most people for most use cases

Prerequisites

What you need to know before diving in

To get the most out of this tutorial, you should have:

Basic understanding of SQL tables and columns
Familiarity with SQL query syntax
Access to a PostgreSQL database (v13 or later recommended)

WITH Queries (Common Table Expressions)

CTEs are like temporary views that exist only for the duration of your query. They help you:

Transform complex queries into named, manageable steps
Create reusable result sets within a single query
Significantly improve query readability
Make complex logic easier to understand

Example: Sales Analysis

Let's analyze product sales in top-performing regions. We'll compare traditional vs. CTE approaches:

-- Traditional Approach
SELECT
    o.region,
    o.product_id,
    SUM(o.qty) AS product_units,
    SUM(o.amount) AS product_sales
FROM
    orders o
    JOIN (
        SELECT
            region
        FROM (
            SELECT
                region,
                SUM(amount) AS total_sales
            FROM
                orders
            GROUP BY
                region

            ) AS regional_sales
        WHERE
            total_sales &gt; 10000
        ORDER BY
            total_sales DESC
        LIMIT
            10
    ) AS top_regions ON top_regions.region = o.region
GROUP BY
    o.region,
    o.product_id;

-- Modern CTE Approach
WITH
    regional_sales AS (
        SELECT
            region,
            SUM(amount) AS total_sales
        FROM
            orders
        GROUP BY
            region
    ),
    top_regions AS (
        SELECT
            region,
            total_sales
        FROM
            regional_sales
        WHERE
            total_sales &gt; 10000
        ORDER BY
            total_sales DESC
        LIMIT
            10
    ),
    product_wise_data AS (
        SELECT
            o.region,
            o.product_id,
            SUM(o.qty) AS product_units,
            SUM(o.amount) AS product_sales
        FROM
            orders o
            JOIN top_regions tr ON tr.region = o.region
        GROUP BY
            o.region,
            o.product_id
    )
SELECT
    *
FROM
    product_wise_data;

Key Benefits

Modularity: Break complex queries into manageable pieces
Reusability: Reference results multiple times
Readability: Self-documenting query structure
Maintainability: Easier debugging and testing

Why CTEs Win

✨ Clear Structure: Each step has a meaningful name
🔍 Debuggable: Test intermediate results easily
📝 Self-Documenting: Logic flow is immediately apparent
🔄 Reusable: Reference results multiple times

References

Next Steps

In the next parts, we will explore Views and Materialized Views in PostgreSQL.

Originally published at https://www.adiagr.com

A better way to search arrays in Postgres

Aditya Agrawal — Thu, 06 Mar 2025 09:32:44 +0000

When using arrays in Postgres often time you would have a need to filter based on an element in the array. This can be done using a simple gin index on the column.

Example

Assume resources Expense, Tag, and Org.

5M Expenses
10 Orgs
2K Tags

create table expenses(
   id serial primary key,
   org_id integer not null,
   tag_ids integer[] not null
);

insert into expenses 
   select 
       id, 
       1+id%9 org_id, 
       gi.user_ids
   from 
      generate_series(1, 5000000) id
   join lateral (
      select 
          i%5000000 pid, 
          array_agg(round(random() * 2000)) user_ids
      from generate_series(1, 5.5*5000000) i
      group by i%5000000
   ) gi on pid=id
;

Roughly every expense can have 5 tags. We need to be able to list all expenses in an org with a certain tag.

The org_id filter in most cases comes from access layers where a user with a token can access the data of one org only for which the session is active.

select 
    *
from
    expenses ex
where
    ex.org_id=1 
    and ex.tag_ids @> '{7}'
;

Note: @> is 'contains' operator
Postgresql: functions-array

No Indexes:

Execution Time: 1682.684 ms

Index on org_id:

create index on expenses(org_id);
Execution Time: 228.352 ms

Improvement of 7.3x

Can we do better? Enter Gin Index on tag_ids:

create index on expenses(org_id);
create index on expenses using gin (tag_ids);
Execution Time: 43.473 ms

Improvement of another 5.2x

Can we do even better? Enter Partial Index:

create index on expenses using gin (org_id, tag_ids);
ERROR:  data type integer has no default operator class for access method "gin"

Enter btree_gin extension:

create extension btree_gin;
create index on expenses using gin (org_id, tag_ids);

Execution Time: 7.293 ms

Improvement of another 5.9x

Summary:

Time: 1682.68 -> 228.35 -> 43.47 -> 7.29ms

Improvements:

total: 230x
from just org_id index: 31x

Postgres provides you with an extension out of the box called 'btree_gin'.

btree_gin provides sample GIN operator classes that implement B-tree equivalent behavior for simple non-composite data types.

References:

Originally published at https://www.adiagr.com

DEV Community: Aditya Agrawal

OAuth2.0 Auth Tokens: A Comprehensive Guide

OAuth 2.0 in Real Life: The Netflix Streaming Analogy

Understanding OAuth 2.0 Architecture

1. Roles in OAuth 2.0

2. OAuth 2.0 Grant Types

3. OAuth 2.0 Tokens

Token Anatomy: What's Inside?

Token Structure in OAuth/OIDC

Access Token Structure

Refresh Token Structure

OAuth 2.0 Flows and Token Usage In-Depth

1. Authorization Code Flow

2. Authorization Code Flow with PKCE

3. Client Credentials Flow

4. Device Authorization Flow

5. Resource Owner Password Flow (Legacy)

Flow Selection Guide

- Implement proper token storage based on client type

Token Lifecycle Management

Token Issuance Process

Access Token vs. Refresh Token Lifecycle

Token Versioning for Policy Updates

6. Advanced Token Security Mechanisms

Refresh Token Rotation

Token Binding

Layered Token Architecture

OAuth 2.0 in Microservices Architecture

Token Propagation

JWT vs. Opaque Tokens: The Great Debate

Hybrid Approach

Final Takeaways

JWT - Based Auth: A Deep Dive

Understanding JWT-Based Security

What is a JWT?

Interactive JWT Explorer

JWT Internals: Breaking Down the Parts

1. Header

2. Payload

3. Signature

JWT: Encoding vs. Encryption

JWT in Authentication Flow

JWT for Authorization

Security Best Practices

Common JWT Pitfalls

Self-contained JWT Example

Encoding Example

Further Reading

Final Thoughts

Authentication vs Authorization: Understanding the Difference

Securing the Digital World: Authentication vs Authorization

The Airport Security Journey: A Perfect Security Model

1. Security Checkpoint

2. Airport Access

Understanding Authentication

How Authentication Works

Types and Methods of Authentication

Understanding Authorization

How Authorization Works

Real-World Authorization Scenarios

1. Corporate Environment

2. Social Media Platform

3. Banking Application

Key Differences Between Authentication and Authorization

Session Management: Keeping Users Secure

Role-Based Access Control (RBAC): A Closer Look

Further Reading

Final Thoughts

Navigating PostgreSQL - Query Optimizations

Query Optimization

Example 1:

Understanding the Query Plan

Optimizing the Query

Optimizing the Query Further

Example 2:

Understanding the Query Plan

Optimizing the Query

Key Takeaways

References

Navigating PostgreSQL - Join Strategies