Microsoft SQL Server and PostgreSQL are both very popular RDBMS havingrich set of administration tools and programming APIs. However, MS SQL has quite restrictive licensing policyand high total cost of ownership while PostgreSQL is open source released under Open Source Initiative Approved License.
Shall we consider this economic aspect more precisely. License prices of SQL Server are ranged from $3K for the Standard Edition to more than $10K for the Enterprise edition (two cores). A free version of this database management system is only available to students and developers for internal using. The use of PostgreSQL is free even for any commercial purpose.
That reason forces many organizations migrate their databases from SQL Server to PostgreSQL. However, before beginning this complicated procedure it is reasonable to compare two DBMS to find all possible bottlenecks of the migration.
Scalability
In terms of scalability SQL Server provides enhancements to the on-disk storage for memory-optimized tables. It offers multiple concurrent threads to make memory-optimized tablespersistent, multithreaded recovery and merge operations, dynamic management views.
PostgreSQL implements scalability though indexing and partitioning options to improve data operations and query performance on tables. Table scalability can be greatly improved since partitions and Indexes can be placed in separate tablespaces on different disk file systems. Horizontal table partitioning is not supported in PostgreSQL.
Computed Columns
SQL Server supports computed columns with a number of limitations: it cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. Also, computed columns cannot be used in INSERT or UPDATE statements in MS SQL.
PostgreSQL supports the same feature since v12 declared by the term ‘generated columns’.Generated columns cannot be part of a partition key; they can only reference the current row and cannot use subqueries. Generated values cannot be included INSERT or UPDATE,however DEFAULT constraint is accepted.
Identity Columns
SQL Server uses identity column property for generating unique key values for rows. Identity declaration includes initial value for the first row (seed) and amount to increase value over the previous row (increment).
PostgreSQL provides special data type SERIAL that allows to assign a unique value to an identity column automatically. Unique constraint or primary key over SERIAL column must be specifiedjust like for other data types.
Spatial Data
SQL Server provides‘geography’ data type for storing spatial data.
PostgreSQL does not have native support for spatial data. The 3rd party open-source PostGIS resource can be used to support for spatial objects.
Views
SQL Server provides user-defined and system-defined views to restrict access to the data. Views can be automatically updated using triggers. Materialized views are called ‘Indexed Views’ in SQL Serves. Unlike materialized views in other RDBMS, indexed views are synched to the underlying data and updated automatically.
PostgreSQL supports generic and updatable views. Views created with simple queries (without recurrency, subqueries, etc.) can be updated; complex views can be updated using rules. The DBMS also supports materialized views. Toupdate data in materialized views the statement‘REFRESH MATERIALIZED VIEW’can be used.
Migration Tools
Differences between SQL Server and PostgreSQL listed above prove that migration between the twodatabase management systems requires many efforts when doing it manually. Human factor leads to high risk of data loss or corruption caused bymigration errors. This issue can be resolved using special database conversion software.
One of such conversion tools is SQL Server to PostgreSQL developed by Intelligent Converters, companyspecializing in database migration and synchronization between all popular database management systems like MS SQL, PostgreSQL, Oracle, MySQL and others. Theconverter has extremely high performance due to direct importing and exporting data without anymiddleware libraries or components. It supports all modern versions of MS SQL and PostgreSQL including cloud solutions. Command line support is a powerful option to automate the database migration.
For those cases where PostgreSQL server declines remote connection, the program can convert the source data into local SQL script file containing statements to create tables, indexes, constraints and to insert rows. Then the person responsible for database migration can load that file into PostgreSQL database via standard tools like psql or pgAdmin.
