Migrating from SQL Server to PostgreSQL: A Comprehensive Guide

Introduction

The world of databases is vast, and choosing the right one for your needs can be a complex decision. If you’re considering a move from Microsoft SQL Server to PostgreSQL (often referred to as Postgres), this guide will provide you with the insights and steps needed for a successful migration. We’ll explore the reasons behind this shift, the key differences between the two databases, and a step-by-step approach to ensure a smooth transition.

Why Choose PostgreSQL?

PostgreSQL has gained immense popularity due to its open-source nature, advanced features, and robust performance. Let’s delve into some of the compelling reasons why organizations are making the switch:

• Cost Efficiency: PostgreSQL eliminates licensing fees associated with proprietary databases, significantly reducing operational costs.
• Vendor Independence with Optional Support: Being open-source, PostgreSQL frees you from vendor lock-in, giving you greater control and flexibility over your database infrastructure. Additionally, organizations can benefit from the managed services and expert support provided by vendors like Tanzu PostgreSQL, ensuring robust performance, high availability, and seamless integration within enterprise environments while still leveraging the advantages of an open-source solution.
• Performance and Scalability: PostgreSQL excels in handling large datasets and complex queries, making it ideal for data-intensive applications.
• High Availability and Disaster Recovery: Features like replication, failover, and point-in-time recovery ensure minimal downtime and data protection.
• Security and Compliance: PostgreSQL offers robust security features, including data encryption and access controls, to help you meet regulatory requirements.
• Community and Support: A vibrant community and extensive ecosystem provide readily available resources and support.

Having explored the numerous benefits of PostgreSQL, it becomes evident why a growing number of organizations are choosing to make the transition. The next logical step is to plan your migration carefully to ensure a smooth and successful transition. In the following sections, we will outline the essential steps and considerations for migrating from SQL Server to PostgreSQL.

Planning Your Migration

A successful migration hinges on meticulous planning. Here’s a breakdown of the essential steps:

1. Define Clear Goals: Outline your objectives for the migration, whether it’s improved performance, cost savings, or enhanced features.
2. Understand Your Current Database: Evaluate your existing SQL Server setup, including schema, data volume, and any custom configurations.
3. Identify Challenges: Anticipate potential issues like compatibility problems or data loss risks.
4. Choose a Migration Strategy: Select the most suitable approach based on your goals and database complexity. Options include schema conversion, data migration (dump and restore, logical replication, or physical replication), and hybrid methods.
5. Resource Planning: Allocate the necessary hardware, personnel, and training to support the migration.
6. Contingency Planning: Prepare a rollback plan in case of unforeseen issues.
7. Communication Plan: Keep stakeholders informed about the migration progress and potential impacts.

The Importance of Understanding Application-Database Interaction

The way an application interacts with its database is a critical factor in determining the complexity and approach of a database migration. As the ultimate consumer of the database, your application’s interaction with the database is the most critical factor in determining the complexity and approach of a database migration. Applications heavily reliant on database-specific logic, such as stored procedures and triggers, present a greater challenge when migrating to a new database system. Let’s delve deeper into why this is the case and what it means for your migration planning.

Database-Centric vs. Application-Centric Logic

• Database-Centric Logic: When an application pushes a significant portion of its business logic into the database, it often leverages features like stored procedures, functions, triggers, and views. These objects encapsulate complex operations, data validation, and even parts of the application’s workflow. The advantage of this approach is centralized logic and potentially improved performance. However, the downside is increased coupling between the application and the database. Migrating to a new database system necessitates translating or rewriting these database objects, which can be time-consuming and error-prone.

• Application-Centric Logic: In contrast, applications with most of their logic residing within the application code itself tend to use the database primarily for data storage and retrieval. While this might lead to slightly more data transfer between the application and database, it offers greater flexibility when migrating databases. The core application logic remains unaffected, and the migration primarily involves adapting data access layers and queries to the new database’s syntax and features.

Assessing Your Application

Before embarking on a migration, it’s crucial to analyze your application’s interaction with the database. Here are some key questions to consider:

• How extensively are stored procedures, functions, and triggers used? If they’re pervasive and complex, anticipate a more involved migration process ?
• Is the application’s logic tightly intertwined with the database schema? If so, schema changes in the new database might require significant application code modifications ?
• Are there any vendor-specific features or extensions being utilized? These will likely need to be replaced or reimplemented in the target database ?
• How much data needs to be migrated? The volume of data impacts the migration strategy and duration ?

Migration Strategies Based on Application-Database Interaction

Having assessed how your application interacts with the database, it is crucial to determine whether it is tightly coupled to the database logic or primarily operates independently of it. This understanding will guide the development of tailored migration strategies, helping to define the necessary steps, estimate the effort required, and create a comprehensive plan for a successful migration.

• Database-Centric Logic:

  • Thorough Assessment: Conduct a detailed inventory of all database objects and their dependencies.
  • Code Conversion or Rewriting: Plan for significant effort in translating or rewriting stored procedures, functions, and triggers to the target database’s syntax.
  • Testing and Validation: Rigorous testing is essential to ensure the migrated logic functions correctly in the new environment.

• Application-Centric Logic:

  • Schema and Data Migration: Focus on accurately converting the database schema and transferring the data.
  • Query Adaptation: Adjust application queries and data access code to the new database’s syntax.
  • Performance Tuning: Optimize queries and database configuration for the new system.

Understanding how your application utilizes the database is paramount for a successful migration. By carefully analyzing the extent of database-centric logic and planning your migration strategy accordingly, you can minimize risks, reduce downtime, and ensure a smooth transition to your new PostgreSQL database. Remember, a well-prepared migration sets the stage for reaping the full benefits of PostgreSQL’s power, flexibility, and cost-effectiveness.

Now lets work through the general steps to plan and execute a database migration from SQL Server to Postgresql.

Step 1: Planning and Assessment

Inventory and Analysis

Inventory Existing Applications and Databases

• List All Databases: Identify all the databases that will be migrated from MS SQL Server to PostgreSQL.
• Identify Dependencies: Note down all dependencies, including linked servers, cross-database queries, and external systems.
• Review Application Interfaces: Identify how each application interfaces with the database (e.g., ORM, direct SQL queries, stored procedures).

Example:

Database Inventory:
1. CustomerDB
   - Tables: Customers, Orders, Payments
   - Dependencies: External CRM System, Linked Server to InventoryDB
2. InventoryDB
   - Tables: Products, StockLevels, Warehouses
   - Dependencies: Internal Reporting System

Evaluate Compatibility

Data Types

• Identify Unsupported Data Types: MS SQL Server and PostgreSQL have different sets of data types. Identify and map unsupported data types. I have created a comprehensive table for common data types and their mappings between sql server and postgresql.

Common Mappings

Certain data types in SQL Server have straightforward equivalents in PostgreSQL, making the migration process more predictable. Here are some notable mappings:

• DATETIME -> TIMESTAMP: In PostgreSQL, TIMESTAMP is used to represent both date and time, similar to DATETIME in SQL Server. If time zone support is needed, TIMESTAMPTZ should be used.
• MONEY -> NUMERIC(19,4): PostgreSQL does not have a MONEY data type, so NUMERIC with appropriate precision and scale is used to store monetary values.
• UNIQUEIDENTIFIER -> UUID: PostgreSQL provides native support for universally unique identifiers (UUIDs) through the UUID data type, which directly maps to SQL Server’s UNIQUEIDENTIFIER.

Understanding these common mappings helps in planning and executing the schema conversion effectively. It ensures that the data types used in your SQL Server database are accurately represented in PostgreSQL, maintaining data integrity and application compatibility.

Functions and Expressions

The migration process involves more than just moving tables and data; it also requires careful attention to the functions and expressions used within your SQL Server environment. Ensuring that functions and expressions are correctly migrated is essential for maintaining the logic and functionality of your database. SQL Server and PostgreSQL have different sets of built-in functions, stored procedures, and triggers, and these need to be carefully translated to ensure the application continues to work as expected after migration.

• SQL Functions

SQL Server and PostgreSQL, while both adhering to SQL standards, have their own sets of built-in functions. Review SQL Server functions (e.g., GETDATE(), ISNULL(), NEWID()) and find PostgreSQL equivalents (NOW(), COALESCE(), GEN_RANDOM_UUID()). It’s crucial to systematically review all the SQL functions used in your SQL Server code and identify their PostgreSQL equivalents. This might involve consulting documentation, online resources, or even experimenting within a PostgreSQL environment.

Beyond direct replacements, some functions might require adjustments in syntax or arguments. For instance, the DATEPART() function in SQL Server becomes DATE_PART() in PostgreSQL, and it might be more stringent about the format of date/time components. For instance PostgreSQL prefers single quotes around date part specifiers (e.g., date_part(‘year’, mydatefield)).

-- SQL Server
SELECT GETDATE(), ISNULL(column, 'default'), NEWID();

-- PostgreSQL
SELECT NOW(), COALESCE(column, 'default'), GEN_RANDOM_UUID();

• Stored Procedures and Triggers

Stored procedures and triggers encapsulate business logic within the database. You’ll likely need to rewrite stored procedures, paying attention to parameter handling, result set retrieval, and any T-SQL-specific constructs. SQL Server uses T-SQL (Transact-SQL) for stored procedures and triggers, while PostgreSQL uses PL/pgSQL (Procedural Language/PostgreSQL). The syntax and capabilities of these languages differ significantly, so stored procedures and triggers need to be carefully analyzed and converted.

T-SQL and PL/pgSQL have different syntax and control structures. For example, error handling, loops, and conditional statements need to be rewritten according to PL/pgSQL syntax. Some features available in T-SQL might not have direct equivalents in PL/pgSQL, requiring alternative implementations. Stored procedures and triggers might need to be optimized for PostgreSQL’s execution engine to ensure they perform efficiently.

-- SQL Server Stored Procedure
CREATE PROCEDURE GetCustomerOrders @CustomerID INT
AS
BEGIN
    SELECT * FROM Orders WHERE CustomerID = @CustomerID;
END;

-- PostgreSQL Function
CREATE OR REPLACE FUNCTION GetCustomerOrders(CustomerID INT)
RETURNS TABLE(OrderID INT, OrderDate TIMESTAMP, Amount DECIMAL) AS $$
BEGIN
    RETURN QUERY SELECT OrderID, OrderDate, Amount FROM Orders WHERE CustomerID = GetCustomerOrders.CustomerID;
END; $$ LANGUAGE plpgsql;

Triggers automate actions based on data modifications. Migrating triggers involves understanding the differences in trigger syntax and event handling between the two databases.

-- SQL Server Trigger
CREATE TRIGGER trgAfterInsert ON Orders
FOR INSERT
AS
BEGIN
    PRINT 'New order inserted';
END;

-- PostgreSQL Trigger and Function
CREATE OR REPLACE FUNCTION trg_after_insert()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'New order inserted';
    RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_after_insert
AFTER INSERT ON Orders
FOR EACH ROW EXECUTE FUNCTION trg_after_insert();

Indexes and Constraints

Ensuring that indexes and constraints are correctly migrated is crucial for maintaining data integrity, enforcing business rules, and optimizing database performance. Here’s why this is important:

• Primary and Foreign Keys:

  • Compatibility: Primary and foreign keys are fundamental to maintaining relational integrity between tables. They enforce the relationships defined in your database schema, ensuring that each record in a child table corresponds to a valid record in a parent table. Ensuring compatibility during migration prevents data integrity issues, such as orphaned records, which can lead to inconsistent and unreliable data.
  • Referential Integrity: Migrating primary and foreign keys correctly ensures that referential integrity constraints are preserved. This prevents operations that would violate the logical relationships between tables, such as deleting a parent record that still has associated child records.

• Unique Constraints and Indexes:

  • Data Integrity: Unique constraints ensure that the values in a column or a group of columns are unique across the table. This is essential for maintaining the accuracy and consistency of your data. For instance, a unique constraint on an email column in a users table prevents duplicate email addresses, which could otherwise cause issues in user identification and communication.
  • Performance Optimization: Indexes play a critical role in optimizing query performance. They allow the database to quickly locate and retrieve the necessary data without scanning the entire table. Properly converting and optimizing indexes can significantly improve the performance of read operations, such as SELECT queries, by reducing the time and resources required to access data.
  • Efficient Data Access: Unique indexes not only enforce data integrity but also enhance data retrieval speeds. When a unique index is used, the database engine can quickly ascertain the existence of a record without a full table scan, making operations like lookups and joins more efficient.

Example:

• In SQL Server:

  CREATE TABLE Orders (
      OrderID int PRIMARY KEY,
      CustomerID int,
      OrderDate datetime,
      CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
          REFERENCES Customers(CustomerID)
  );
  
  CREATE UNIQUE INDEX IX_Customers_Email ON Customers(Email);
  

• In PostgreSQL:

  CREATE TABLE Orders (
    OrderID integer PRIMARY KEY,
    CustomerID integer,
    OrderDate timestamp,
    CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
        REFERENCES Customers(CustomerID)
  );
  
  CREATE UNIQUE INDEX IX_Customers_Email ON Customers(Email);
  

Technical Steps:

• Identify Primary and Foreign Keys:
  • Use database tools or scripts to extract definitions of primary and foreign keys from SQL Server.
• Convert Keys and Constraints:
  • Ensure primary keys and foreign keys are defined correctly in PostgreSQL schema scripts.
• Define Unique Constraints and Indexes:
  • Map and create unique constraints and indexes in PostgreSQL to maintain data integrity and query performance.
• Validate Integrity and Performance:
  • Test the migrated schema to ensure that all constraints and indexes are functioning correctly and optimize performance as needed.

By correctly migrating indexes and constraints, you ensure that the structural and functional integrity of your database is maintained, leading to reliable data management and optimal performance in PostgreSQL.

SQL Syntax Differences

• Query Syntax: Modify SQL queries to be compatible with PostgreSQL. For example, replace TOP with LIMIT.

Example:

-- SQL Server
SELECT TOP 10 * FROM Customers;

-- PostgreSQL
SELECT * FROM Customers LIMIT 10;

Assess Data Size and Complexity

Data Size

Understanding the size of your databases and individual tables is a crucial step in the assessment phase. This information helps in planning the migration strategy, estimating the time and resources required, and identifying potential challenges related to data volume.

• Database Size: Calculate the total size of each database to be migrated. Knowing the total database size is essential for selecting the appropriate migration tools and methods. Large databases may require special considerations, such as phased migrations or the use of high-performance data transfer tools.

  -- SQL Server query to get database size
  USE CustomerDB;
  EXEC sp_spaceused;
  

• Table Size: Identify the size of individual tables. Understanding table sizes helps in pinpointing which parts of your database might be more challenging to migrate and may need special handling or optimization.

  -- SQL Server query to get table size
  SELECT 
      t.NAME AS TableName,
      s.Name AS SchemaName,
      p.rows AS RowCounts,
      SUM(a.total_pages) * 8 AS TotalSpaceKB, 
      SUM(a.used_pages) * 8 AS UsedSpaceKB, 
      (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
  FROM 
      sys.tables t
  INNER JOIN      
      sys.indexes i ON t.OBJECT_ID = i.object_id
  INNER JOIN 
      sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  INNER JOIN 
      sys.allocation_units a ON p.partition_id = a.container_id
  LEFT OUTER JOIN 
      sys.schemas s ON t.schema_id = s.schema_id
  WHERE 
      t.NAME NOT LIKE 'dt%' 
      AND t.is_ms_shipped = 0
      AND i.OBJECT_ID > 255 
  GROUP BY 
      t.Name, s.Name, p.Rows
  ORDER BY 
      TotalSpaceKB DESC;
  

Assessing the data size and complexity is vital because:

• Performance Planning: Large databases and tables may require more time and resources to migrate. Understanding their size helps in planning for downtime and optimizing performance during the migration.
• Resource Allocation: Knowing the volume of data helps in estimating the required storage, computing resources, and bandwidth.
• Risk Management: Identifying large or complex tables early in the process allows for risk mitigation strategies to be developed, such as breaking the migration into smaller, more manageable phases.
• Tool Selection: Different migration tools have varying capabilities and performance characteristics. Understanding the data size ensures you select tools that can handle the volume efficiently.

Data Complexity

Understanding the complexity of your data is equally crucial as knowing its size. Complex relationships, joins, and queries can significantly impact the migration process and the performance of the target system. Complexity of data generally manifests itself in the form of complex relationships and joins. This complexity also results in complex queries which need to be understood and documented appropriately.

Relationships and Joins

Review complex relationships and joins between tables. These are fundamental to your database’s integrity and performance. In SQL Server, relationships are often enforced through foreign keys and complex joins in queries.

Why This Matters:

• Integrity and Consistency: Ensuring that relationships are correctly migrated is critical to maintaining data integrity and consistency. Broken relationships can lead to data anomalies and corruption.
• Performance Impact: Complex joins can significantly impact query performance, both in the source and target databases. Understanding these joins helps in optimizing them post-migration.
• Schema Design: Properly migrating relationships requires careful schema design and planning in PostgreSQL to replicate the behavior and constraints present in SQL Server.

Complex Queries

Identify complex queries and views that might need special attention during migration. These often include nested subqueries, aggregations, and functions that might behave differently in PostgreSQL.

Why This Matters:

• Query Performance: Complex queries can behave differently in PostgreSQL due to differences in query planners and execution engines. Identifying these queries allows for performance tuning and optimization.
• Functionality Differences: SQL Server and PostgreSQL have different sets of built-in functions and capabilities. Complex queries might rely on SQL Server-specific functions that need to be re-implemented or adjusted for PostgreSQL.
• Testing and Validation: Migrating complex queries requires rigorous testing to ensure that the migrated queries return the same results and perform efficiently. This step is essential for maintaining application functionality and performance.

Example:

Complex Queries Inventory:
1. MonthlySalesReport
   - Joins: Orders, Payments, Customers
   - Aggregations: SUM, COUNT, AVG
2. CustomerActivityReport
   - Joins: Customers, Orders, Inventory
   - Subqueries: Nested subqueries for recent purchases

Technical Steps:

• Extract and Document Relationships:
  • Use database schema diagrams or SQL scripts to document existing relationships and joins.
• Analyze and Optimize Queries:
  • Review and optimize complex queries and views, considering PostgreSQL’s capabilities and performance characteristics.
• Testing and Validation:
  • Perform extensive testing of relationships and queries in a staging environment to ensure they function as expected in PostgreSQL.

By thoroughly assessing the data complexity, you can anticipate and address potential issues that may arise during migration. This proactive approach ensures a smoother transition, maintains data integrity, and optimizes performance in the new PostgreSQL environment.

Step 2: Schema Conversion

The core of the migration process lies in converting your database schema from the SQL Server format to a PostgreSQL-compatible structure. Converting the database schema from MS SQL Server to PostgreSQL involves translating the structure of your databases, including tables, indexes, constraints, and other objects, into a format that PostgreSQL can understand and utilize efficiently. This step is critical because it ensures that the migrated database retains its integrity and functionality.

Schema Extraction

The first step is to extract the schema definition from your SQL Server database. You have a couple of options for this:

• SQL Server Management Studio (SSMS): The document mentions using SSMS to generate scripts for the database schema. This is a common and convenient approach, as SSMS provides a graphical interface for selecting the objects you want to script and customizing the output.
• SQL Server Data Tools (SSDT) or Third-Party Tools: If you prefer a more programmatic or automated approach, you can use SSDT or other specialized tools to extract the schema definitions.

Example SQL Script to Extract Schema:

-- Script to generate the schema for a table
USE CustomerDB;
GO
EXEC sp_helptext 'dbo.Customers';

Schema Conversion

Once you have the SQL Server schema scripts, the next step is to convert them to PostgreSQL format. Again, you have a couple of options:

• SQL Server Migration Assistant (SSMA): SSMA is a tool provided by Microsoft to assist in database migrations. It can automate the conversion of schema scripts, data types, and even some simple T-SQL objects to their PostgreSQL equivalents.

• Manual Adjustment: For more complex schemas or scenarios where SSMA might not provide a perfect conversion, manual adjustments to the scripts might be necessary. This requires a good understanding of both SQL Server and PostgreSQL syntax and features.

• Use tools like SQL Server Migration Assistant (SSMA) to automate the conversion of the schema.

• Manually adjust the schema scripts if needed to address any conversion issues.

Key Considerations During Conversion:

• Data Types: Ensure that data types are correctly mapped from SQL Server to PostgreSQL.
• Constraints and Indexes: Verify that all constraints (primary keys, foreign keys, unique constraints) and indexes are properly converted.
• Functions and Stored Procedures: T-SQL functions and stored procedures will need to be converted to PL/pgSQL or other suitable PostgreSQL languages. This can involve rewriting logic and adapting to PostgreSQL’s procedural language capabilities.
• Views: Views define virtual tables based on underlying queries. Convert views, paying attention to any functions or expressions used within them.
• Triggers: Triggers automate actions in response to data changes. Convert triggers, considering the differences in trigger syntax and event handling between the two databases.
• Defaults: Handle default values for columns appropriately, as the syntax might differ.
• Sequences: PostgreSQL uses sequences to generate auto-incrementing values, whereas SQL Server uses identity columns. Convert identity columns to sequences and adjust any code that relies on them.

Example conversion

-- SQL Server Table Definition
CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    FirstName nvarchar(50),
    LastName nvarchar(50),
    BirthDate datetime
);

-- PostgreSQL Table Definition
CREATE TABLE Customers (
    CustomerID integer PRIMARY KEY,
    FirstName varchar(50),
    LastName varchar(50),
    BirthDate timestamp
);

Validate and Optimize

After the conversion, it’s crucial to validate the PostgreSQL schema:

• Syntax Check: Ensure the converted scripts are free of syntax errors and comply with PostgreSQL standards.
• Test Creation: Use tools like pgAdmin or the psql command-line tool to execute the scripts and create the database objects in a PostgreSQL environment.
• Optimization: Review the schema for potential performance improvements. PostgreSQL offers various features like indexing strategies, partitioning, and data compression that can be leveraged to optimize the database structure.

Implement Schema in PostgreSQL

Finally, deploy the converted schema in your PostgreSQL environment:

• Execute Scripts: Run the schema scripts to create the tables, indexes, constraints, and other objects.
• Verification: Confirm that all objects are created successfully and that the database structure matches your expectations.

Additional Considerations

• Schema Complexity: The complexity of your SQL Server schema will directly impact the effort required for conversion. Large and intricate schemas might necessitate more manual intervention and careful validation.
• Tool Limitations: Automated conversion tools might not handle all scenarios perfectly. Be prepared to make manual adjustments and address any conversion issues.
• Testing: Thoroughly test the migrated schema with sample data and queries to ensure data integrity and functionality.

Step 3: Data Migration

The next crucial phase in the SQL Server to PostgreSQL migration process is Data Migration. The essence of this step lies in transferring the actual data from your SQL Server database to the newly prepared PostgreSQL schema. The method you choose for data migration will depend on factors like the size of your database, the acceptable level of downtime during migration, and the resources available to you.

There are three primary methods for data migration:

1. Dump and Restore: This method involves creating a backup (dump) of your SQL Server database and then restoring it into your PostgreSQL environment. It’s a relatively straightforward approach, suitable for smaller databases or situations where some downtime is acceptable. Tools like pg_dump and pg_restore are commonly used for this purpose.

2. Logical Replication: This method leverages PostgreSQL’s built-in logical replication capabilities to stream data changes from the SQL Server database to the PostgreSQL database in real-time. This minimizes downtime and allows for a more seamless transition, especially for larger databases or applications that require continuous availability.

3. Physical Replication: This approach involves establishing a direct, low-level copy of the SQL Server database at the storage level and then transferring it to the PostgreSQL environment. It’s typically used for very large databases or scenarios where minimal downtime is critical.

Dump and Restore

The dump and restore method involves creating a logical backup (dump) of your SQL Server database and then restoring it into your PostgreSQL environment. This method is relatively straightforward and is best suited for smaller databases or scenarios where some downtime is acceptable.

Logical replication

Logical replication uses PostgreSQL’s built-in capabilities to stream data changes from the SQL Server database to the PostgreSQL database in real-time. This method minimizes downtime and is suitable for larger databases or applications that require continuous availability.

Physical Replication

Physical replication involves creating a low-level, byte-for-byte copy of the SQL Server database at the storage level and then transferring it to the PostgreSQL environment. This method is typically used for very large databases or scenarios where minimal downtime is critical.