PostgreSQL for the SQL Server Guy

Submitted by Dave Wentzel on Fri, 2015-04-03 15:49

PostgreSQL is an open source RDBMS that is very similar to SQL Server. In this post I'll compare the features and syntax of the two. Learning a new DBMS tool can be a lot of fun and will help you understand SQL Server even better.

I've been using PostgreSQL a lot lately and I'm really loving it. Whenever I learn a new database manager I like to compare its general features to what I already know best, namely SQL Server and Oracle. I did an entire blog series on Vertica where I compared its features to SQL Server's and dove deeper where Vertica had compelling features. I'm not going to go into that much detail with Postgres.

Who cares?

If you are a SQL Server Guy, you may be wondering why you should bother learning Postgres? Very simple. When you learn a new database manager you begin to think critically, or at least differently, about SQL Server. For instance, Oracle has no concept of "clustered indexes", yet their queries don't need them. Why is that? Well, when you learn the Oracle architecture you discover that something like "a sorted table" really isn't all that important. Another example...Oracle DBAs never bother "reindexing" their tables. For years that was a standard practice in the SQL Server world. Only recently have DBAs stopped reindexing their tables. Why? They realized, like Oracle people have known for years, that in most cases if you have fragmentation you really don't need to worry about unless you have a scan-heavy workload. Fragmentation doesn't matter for seeks. And if you have heavy fragmentation with a scan-heavy workload, the better fix involves changing your FILLFACTORs (known as PCTFREE in Oracle) as well as your general design.

Do you see how we can all learn from each other?

It's also very common to meet experts ofother DBMS vendor technologies who will spout lies and misconceptions about SQL Server. You don't want to be one of those people. When someone spouts a stupid comment you want to be knowledgeable enough about the competition to be able to refute those lies. Don't just be the SQL Server Guy who hates Hadoop...be the SQL Server Guy who hates Hadoop for valid, innumerable reasons. Don't be ignorant of a technology which then causes you to make stupid statements.

The following tables list the key features of any RDBMS and then compares SQL Server to PostgreSQL. This is not meant to be an exhaustive list. Just something to whet your appetite.

General Features

Feature

SQL Server

PostgreSQL

Pronunciation (you don't want to sound "uninformed" when you discuss a technology)

"sequel server"

"POST grez" or "Post grez Queue El"

Licensing

Anywhere from "free" (SQL Express) to super-expensive

similar to MIT or BSD. Very liberal (no attribution is even required). Postgres' biggest open source competitor is probably MySQL, which is GPL-licensed. IMO, GPL licensing can be dangerous for ISVs. See Open Source Licensing.

default port number (defined with IANA)

1433

5432

determine the version

select @@version

SELECT version();

Primary GUI administration/query tool

SSMS

pgAdmin

command line query execution tool

sqlcmd/osql

psql

redo transaction logs

transaction logs (.ldf files)

write-ahead logs (WAL)...very similar to Oracle's redo logs

recovery mode

FULL or SIMPLE

archive_mode = on is the same as FULL.

transaction isolation and concurrency semantics

various, but mostly READ COMMITTED AND SNAPSHOT ISOLATION are the most common

MVCC (multi-version concurrency control) which is identical to RCSI (including the concept of the row version store)

(pgAdmin screenshot)

Basic DBA Tasks

Feature

SQL Server

PostgreSQL

Primary Backup Method

BACKUP DATABASE which creates a self-contained backup

hot physical backups of files with continuous archiving. This closely resembles Oracle's hot backups. Postgres, nor Oracle, has a concept similar to SQL Server's backup method. You essentially take each "file" offline and use scp, rsync or any other copy tool to copy it elsewhere, as well as the write-ahead logs (WAL).

Logical Backups

SQL Server has no concept of this natlvely. Maybe something like "copy database wizard"

Logical recovery executes SQL to re-create the database objects. pg_dump > outfile.sql generates the dump...psql dbname < infile.sql restores the .sql file. Oracle works identically. pg_dump simply executes SQL statements against the database to unload the data. The data is consistent, thanks to MVCC, as of the point-in-time when pg_dump started. You can restore this data and then apply the WAL. But you can restore selective lists of objects, schemas, or just structure and no data, etc etc.

Replication

Yes

Has similar feature set to SQL Server.

"Log Shipping"

Yes

Very similar. The "hot physical backup files" are copied to the destination and then the WAL files are copied automatically when they "roll over". Also called "log shipping"

Can queries run on Log Shipped database?

Yes, if db is in STANDBY mode. Queries must be quiesced whenever log backups are restored

Yes. This is called "hot standby" or "continuous replication". In fact, unlike SQL Server, Postgres allows the queries to continue running while the logs are being applied.

Programming Features

Feature

SQL Server

PostgreSQL

main procedure language

Transact-SQL

PL/pgSQL. This is so similar to TSQL that you can pick it up in just a few minutes.

object names can contain spaces and mixed case characters if enclosed in double quotes. Otherwise object names are assumed to be lower-cased. If you create your objects using quoted names, then you need to write your SQL using quoted names. Converts all non-quoted names to lowercase when used within an SQL statement. This can be tricky. It's best to keep all object names in lower case only.

data loading tool

bcp

\COPY something FROM something.txt CSV HEADER or use pgloader which is a bit more friendly.

windowing functions

row_number() over()

same. all windowing functions from SQL Server are present in Postgres. In fact, Postgres is even richer.