Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL vs. MS SQL Server

pg-versus-ms.com

Post reply on HN
Print view View original

81–90 of 141 posts

Re: PostgreSQL vs. MS SQL Server

#81

Post by ta0967 »

ssmoot wrote: post #68
Jweb_Guru wrote: post #56

Earlier quoted context omitted.

Postgres absolutely has materialized views (I don't know what makes a backup / restore procedure "sane", but having had to do it for both SQL Server and Postgres I would definitely call Postgres's "saner").

re: Materialized Views: I stand corrected! Thanks. (CREATE MATERIALIZED VIEW was introduced in v9.3) The backup/restore procedure in PostgreSQL (last I looked, latest I've used is 9.2) is just statement generation. It's not a binary backup. So it's bound by INSERT performance. Which is rage inducing when you have even just tens of millions of rows. MSSQL'97 could do a backup/restore in a small fraction of the time. A…

can you be more specific with the "just statement generation" allegation? it looks like you're talking about `pg_dump --format=plain`, which is the default but far from the only option.

Re: PostgreSQL vs. MS SQL Server

#82

Post by MagicWishMonkey »

_JamesA_ wrote: post #64
MagicWishMonkey wrote: post #20

Earlier quoted context omitted.

The closest thing to SQL Server Management Studio I've found is Navicat. It's not as solid as Management Studio, and it's incredibly expensive, but it is much better than most of the other tools out there. If you can expense the cost of the license you should definitely check it out.

I have found DbVisualizer [1] to be a superior tool and it supports almost every database in existence, Postgres and SQL Server included. Plus it's completely cross-platform and runs on Linux, Windows and OS X. [1]: http://www.dbvis.com

That's pretty slick, and it's much more competetively priced than Navicat.

Re: PostgreSQL vs. MS SQL Server

#83

Post by [deleted] »

breakingcups wrote: post #31

The last time this article came up the consensus was that the author was pretty biased towards Postgres and had little to no experience with actual MS SQL Server use. Also, the lack of author identity was frowned upon. Lastly, the conjecture and attitude towards Microsoft lacks some substance. Conclusion: The author is free to write whatever he likes, but take this resource with a pinch of salt. I use both Postgres a…

[deleted]

Re: PostgreSQL vs. MS SQL Server

#84

Post by atwebb »

ssmoot wrote: post #62
adekmm wrote: post #12

I've been SQL Server user for many years. And for last two years I started also using PostgreSQL. Today I use both of them in my project, and as a developer/dba I see pros and cons: MS SQL: - The tools included like Management Studio are just great. This is totally next level to the Postgres tools. - Using multiple CPU cores for a single query is really helpfull in my scenario. - Easy continous backup to the cloud. -…

The write performance comes down to lack of clustered indexes on PostgreSQL. So yeah, it'll have better INSERT performance, but the queries will be slower. There's really no way around that. A large data set on disk that's out of order will always be slower than the one that's in-order. IMO MSSQL makes the right call for the vast majority of use-cases. PostgreSQL has -no- materialized views (I stand corrected! Introd…

>The write performance comes down to lack of clustered indexes on PostgreSQL.

Well, you could just leave everything as a heap in MsSQL, someone might throw things at you, but you could do it.

Or you can pad the index to allow for inserts (or do some partitioning), and schedule some rebuilds to augment the fragmentation.

Re: PostgreSQL vs. MS SQL Server

#85

Post by ssmoot »

hahainternet wrote: post #78
ssmoot wrote: post #62

Earlier quoted context omitted.

The write performance comes down to lack of clustered indexes on PostgreSQL. So yeah, it'll have better INSERT performance, but the queries will be slower. There's really no way around that. A large data set on disk that's out of order will always be slower than the one that's in-order. IMO MSSQL makes the right call for the vast majority of use-cases. PostgreSQL has -no- materialized views (I stand corrected! Introd…

> No view update support 9.3 > No partitioned view support Inheritance (ish) > No sane backup/restore process wat > doesn't support backing up and restoring the database in binary format http://www.postgresql.org/docs/9.4/static/app-pgbasebackup.h... is half of what you want, but I expect you're wanting something I don't expect.

I want to be able to say: BACKUP clientdb_a, get a clientdb_a.bak file, and be able to restore it without statement generation.

Which seems like a reasonable baseline expectation if you were to ask a lay-person what they'd imagine a database backup to be. pgbasebackup can't do that without some serious constraints (like, you only have a single database in your PGDATA).

Re: PostgreSQL vs. MS SQL Server

#86

Post by swasheck »

salibhai wrote: post #75
Amezarak wrote: post #8

> In MS SQL Server, a CREATE PROCEDURE statement cannot appear halfway through a batch of SQL statements. There's no good reason for this, it's just an arbitrary limitation. It means that extra manual steps are often required to execute a large batch of SQL. Manual steps increase risk and reduce efficiency. It's been a while, but I am pretty sure all you have to do is put GO before/after the CREATE PROCEDURE. I'm abs…

GO creates a lot of headaches actually. If you are trying to do something in a transaction it breaks it

why would you submit and terminate a batch halfway through a transaction? that doesnt really make a whole lot of sense so i cant really take this criticism seriously.

Re: PostgreSQL vs. MS SQL Server

#87

Post by ssmoot »

ta0967 wrote: post #81
ssmoot wrote: post #68

Earlier quoted context omitted.

re: Materialized Views: I stand corrected! Thanks. (CREATE MATERIALIZED VIEW was introduced in v9.3) The backup/restore procedure in PostgreSQL (last I looked, latest I've used is 9.2) is just statement generation. It's not a binary backup. So it's bound by INSERT performance. Which is rage inducing when you have even just tens of millions of rows. MSSQL'97 could do a backup/restore in a small fraction of the time. A…

can you be more specific with the "just statement generation" allegation? it looks like you're talking about `pg_dump --format=plain`, which is the default but far from the only option.

I'm using that for short-hand yea. But it was my experience pg_restore is no faster outside of basic parallelization.

Re: PostgreSQL vs. MS SQL Server

#88

Post by edgarvm »

MagicWishMonkey wrote: post #20
adekmm wrote: post #12

I've been SQL Server user for many years. And for last two years I started also using PostgreSQL. Today I use both of them in my project, and as a developer/dba I see pros and cons: MS SQL: - The tools included like Management Studio are just great. This is totally next level to the Postgres tools. - Using multiple CPU cores for a single query is really helpfull in my scenario. - Easy continous backup to the cloud. -…

The closest thing to SQL Server Management Studio I've found is Navicat. It's not as solid as Management Studio, and it's incredibly expensive, but it is much better than most of the other tools out there. If you can expense the cost of the license you should definitely check it out.

TOAD DBA Suite for SQL Server is nice too, very expensive though

Re: PostgreSQL vs. MS SQL Server

#89

Post by toolz »

msl09 wrote: post #46
kidmenot wrote: post #40

Earlier quoted context omitted.

Agreed on Navicat. pgAdmin doesn't quite cut it just yet. Which sometimes made me wonder if, with Postgres becoming more popular by the day, there would be a market for a management tool cheaper than Navicat.

Postgresql have such an amazing documentation and CLI that I prefer to use it over pgAdmin.

This is why I prefer postgres. Jumping into the CLI is easy and much quicker than mousing around some clustered up feature overloaded GUI.

Re: PostgreSQL vs. MS SQL Server

#90

Post by glogla »

ssmoot wrote: post #62
adekmm wrote: post #12

I've been SQL Server user for many years. And for last two years I started also using PostgreSQL. Today I use both of them in my project, and as a developer/dba I see pros and cons: MS SQL: - The tools included like Management Studio are just great. This is totally next level to the Postgres tools. - Using multiple CPU cores for a single query is really helpfull in my scenario. - Easy continous backup to the cloud. -…

The write performance comes down to lack of clustered indexes on PostgreSQL. So yeah, it'll have better INSERT performance, but the queries will be slower. There's really no way around that. A large data set on disk that's out of order will always be slower than the one that's in-order. IMO MSSQL makes the right call for the vast majority of use-cases. PostgreSQL has -no- materialized views (I stand corrected! Introd…

You are correct about clustered indexes (1) if and only if the table clustering is selected well. Which is not always the case - especially with Azure SQL which doesn't allow for non-clustered tables or tables clustered by anything other than primary key.

Table clustering is mostly betting on "I think most access is going to happen using this columns" which might be good choice or it might not, but it's not something that should happen implicitly or by the only choice at all.

I think Markus (2) explains this much better than I do, so I'll just link his text.

(1): which is pretty stupid name, btw - Oracle call it "index organized tables" which is much clearer way to describe the concept.

(2): http://use-the-index-luke.com/sql/clustering/index-organized...

Post reply on HN