PostgreSQL vs. MS SQL Server • Hacker News Board

Re: PostgreSQL vs. MS SQL Server

#101

Post by MichaelGG » Thu, Apr 30, 2015, 3:22 PM UTC

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…

PG now has materialized views, but they appear mostly useless. Their the equivalent of creating a table from a query. They don't update on every change to the source data. 9.4 I think added a way to force an update, but it just re-reads the entire source. To do it right you still have to use triggers.

Re: PostgreSQL vs. MS SQL Server

#102

Post by jackmaney » Thu, Apr 30, 2015, 3:24 PM UTC

BrentOzar wrote: post #2

If I was going to pick a relational database system, I'm not sure these would be the criteria I'd use: CSV support - if you do that much CSV extract/transform/load (or indeed, any kind of ETL work), use an ETL tool. SQL Server comes with SQL Server Integration Services for that kind of thing. Ergonomics of dropping and creating tables and stored procedures - the author's example is probably the toughest way I can thi…

> CSV support - if you do that much CSV extract/transform/load (or indeed, any kind of ETL work), use an ETL tool.

Why? CSVs are portable and much easier to deal with (when exported correctly, which PostgreSQL does and MS SQL does NOT).

Re: PostgreSQL vs. MS SQL Server

#103

Post by MichaelGG » Thu, Apr 30, 2015, 3:24 PM UTC

Jweb_Guru wrote: post #56

ssmoot wrote: post #52

Earlier quoted context omitted.

PostgreSQL doesn't have clustered indexes, materialized views, partitioned views, or a sane backup/restore procedure. If you're already a licensed MSSQL customer, I'm not sure what advantages PostgreSQL could really have compared to it's slower performance and much higher operational costs.

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").

It has a feature named materialized views, but it isn't close to being the real feature that MSSQL calls it.

Re: PostgreSQL vs. MS SQL Server

#104

Post by danneu » Thu, Apr 30, 2015, 3:25 PM UTC

mmgutz wrote: post #72

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.

Last I tried Navicat it didn't offer much over pgAdmin. The UI was more streamlined but that wasn't reason enough to switch to it. What features in navicat are must have and not found in pgAdmin3?

I use both Navicat and pgAdmin3 these days.

One thing I like about pgAdmin3 is the ability to only execute the query you have highlighted and also the scratchpad. It's nice for exploratory querying. In Navicat, I like switching between various tabs where pgAdmin3 makes me switch between windows.

Both have some hilarious idiosyncrasies. When one locks up for no reason, I generally just alt-tab to the other.

Recently I was using pgAdmin3 and set my production database color to red. I happened to notice that the color didn't repaint back to green when I switched to my dev database. And Navicat seems to consistently hang whenever the network dies or burps mid-action, requiring a command-line kill.

Idiosyncrasies. :)

Re: PostgreSQL vs. MS SQL Server

#105

Post by ssmoot » Thu, Apr 30, 2015, 3:27 PM UTC

chris_wot wrote: post #96

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…

No clustered index? What about CLUSTER? http://www.postgresql.org/docs/9.4/static/sql-cluster.html CLUSTER instructs PostgreSQL to cluster the table specified by table_name based on the index specified by index_name. The index must already have been defined on table_name. When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequ…

Yes you can manually re-order a table, with locking, but that's really not the same thing as enforcing in on INSERT/UPDATE.

Re: PostgreSQL vs. MS SQL Server

#106

Post by chris_wot » Thu, Apr 30, 2015, 3:27 PM UTC

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…

CREATE PROCEDURE puts a schema lock on the procedure. Didn't realise it doesn't release it! SQL Server only allows one Sch-M lock on an object at a time.

Re: PostgreSQL vs. MS SQL Server

#107

Post by MichaelGG » Thu, Apr 30, 2015, 3:33 PM UTC

HA and clustering. MSSQL makes this dead easy. Point and click (and it'll display the script for you to learn/reuse) and you're done. Async, sync, HA (with automatic fail over), mirroring, several types of replication - and it just works and is easy. If PG ever ships with an out-of-the-box shared nothing system, yee haw! Maybe they could package up DRDB and heartbeat into one easy script and monitoring system or something.

I hate how MSSQL has gone back on their word to let customers benefit from CPU enhancements. They mocked Oracle for charging by type and core... And now they do the same.

Also, multiple result sets was a sorely missed feature when porting stuff to PG. But record types made up for it.

Of course now, the dominating factor for a lot of people is "Will a hosting provider (Azure, AWS, Google) just run this for me, automatically giving me perfect backups and restore and HA?" SQL Azure, as I understand, not only does backups, but allows you to restore to arbitrary points in time. Sure it's just keeping txlogs, but that sounds hot when sold like that. For many cases, I can see ditching the privacy issues of "cloud" to get those features with zero capex or management overhead.

Re: PostgreSQL vs. MS SQL Server

#108

Post by MichaelGG » Thu, Apr 30, 2015, 3:36 PM UTC

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. -…

Both TSQL and pgsql miss stuff. But yesterday I wanted to turn a (customerid, key, value) table into a JSON object, one per customer. Simple one liners in other systems, but a mess in TSQL, requiring "FOR XML PATH" hackery. There's also an unsupported/deprecated/undocumented trick of updating a var inside a select (select @a = @a + column).

Re: PostgreSQL vs. MS SQL Server

#109

Post by glogla » Thu, Apr 30, 2015, 3:36 PM UTC

MichaelGG wrote: post #107

HA and clustering. MSSQL makes this dead easy. Point and click (and it'll display the script for you to learn/reuse) and you're done. Async, sync, HA (with automatic fail over), mirroring, several types of replication - and it just works and is easy. If PG ever ships with an out-of-the-box shared nothing system, yee haw! Maybe they could package up DRDB and heartbeat into one easy script and monitoring system or some…

> SQL Azure, as I understand, not only does backups, but allows you to restore to arbitrary points in time.

It does, but they take about hour to hour. Restore takes about that much time. Changing performance level of DB takes similar time.

I'm not sure hot quick Amazon or Google is, but I know lot of ops guys who are sorely disappointed by Azure slowness.

Re: PostgreSQL vs. MS SQL Server

#110

Post by MichaelGG » Thu, Apr 30, 2015, 3:39 PM UTC

thomaskcr wrote: post #47

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…

My biggest frustration with MS is their licensing is infuriating. Doing cleanup during the audit took a ridiculous amount of resources and every company gave us a different answer on what needs to be licensed differing by 1000s of dollars sometimes. The other is the barrier to entry. I was trying to get NAV2015 working for a demo using some data for a project, I spent at least 20 hours trying to figure out who to pay…

That's neat though. Shop around for the cheapest reseller, let them make the mistakes or miscalculation, enjoy?

I'm guessing it comes down to virtual cores and such, right? Although I'm surprised how many stupid-simple mistakes people make just by not reading even the basics. (Wait MSDN includes several Office keys, so obviously they don't want us to buy Office licenses for administrative assistants.)