PostgreSQL vs. MS SQL Server • Hacker News Board

Re: PostgreSQL vs. MS SQL Server

#121

Post by Jweb_Guru » Thu, Apr 30, 2015, 4:42 PM UTC

ssmoot wrote: post #105

chris_wot wrote: post #96

Earlier quoted context omitted.

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.

Clustered index is something it would be nice for Postgres to have, but it would be a lot of work to implement and it's certainly not always a performance win. Looking up by that index is very fast, but looking up by a secondary index may have to traverse a second btree (it cannot point directly at data on disk because its location is tied to the clustered index). And of course there's quite a bit of overhead for transactions and modification operations.

In practice, you can recover many of the advantages of clustered indexes in PostgreSQL (along with the disadvantages) with a covering index eligible for https://wiki.postgresql.org/wiki/Index-only_scans.

Re: PostgreSQL vs. MS SQL Server

#122

Post by MichaelGG » Thu, Apr 30, 2015, 4:42 PM UTC

thomaskcr wrote: post #118

MichaelGG wrote: post #110

Earlier quoted context omitted.

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

See, even you're a bit confused by MSDN -- You're not even supposed to use the MSDN license for your own everyday use is what I've been told. So if you have outlook for email and you're using an MSDN license, that's not correctly licensed. MSDN is ONLY for development. Also it's one MSDN license per developer apparently. And no because you still get nailed -- it's not like your reseller is the one being audited, it's…

No I'm not confused - I was giving an example of stuff I've heard from clients. MSDN makes it really clear it's for dev not day-to-day ops. But people don't get that.

I'd be surprised if you get "nailed" if a reseller went over a scenario and licensed you. Worst case is if they can prove maliciousness and fine you. If the reseller calculated things and came to a reasonable (but lower) number I'd be surprised if that's gonna really hurt.

Agreed though that MS's licensing is terribly annoying. But it's a lower Total Cost of Ownership, right?!

Re: PostgreSQL vs. MS SQL Server

#123

Post by MichaelGG » Thu, Apr 30, 2015, 4:44 PM UTC

dragonwriter wrote: post #112

MichaelGG wrote: post #103

Earlier quoted context omitted.

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

> It has a feature named materialized views, but it isn't close to being the real feature that MSSQL calls it. MSSQL doesn't have a featured called materialized views, it has a feature called indexed views which happens to implement a fairly robust version of the general DB pattern called materialized view. PG has less mature materialized views (which are called materialized views) starting from 9.3, with additional…

Well I can't think of a scenario where I want a single-updated materialized (or indexed) views. Because I can already do that by creating a table from a SELECT statement. So I don't see the point or use of what PG has implemented. I asked in IRC and got the same answer. It's like saying a read-only table supports updates cause you can drop it and rebuild it with the new data.

When would anyone really need PG's version of this feature over creating a table from a query? Out of all the usages of "I need an indexed/materialized view", PG covers essentially none of them, at least that I can think of.

Re: PostgreSQL vs. MS SQL Server

#124

Post by thomaskcr » Thu, Apr 30, 2015, 4:54 PM UTC

MichaelGG wrote: post #122

thomaskcr wrote: post #118

Earlier quoted context omitted.

See, even you're a bit confused by MSDN -- You're not even supposed to use the MSDN license for your own everyday use is what I've been told. So if you have outlook for email and you're using an MSDN license, that's not correctly licensed. MSDN is ONLY for development. Also it's one MSDN license per developer apparently. And no because you still get nailed -- it's not like your reseller is the one being audited, it's…

No I'm not confused - I was giving an example of stuff I've heard from clients. MSDN makes it really clear it's for dev not day-to-day ops. But people don't get that. I'd be surprised if you get "nailed" if a reseller went over a scenario and licensed you. Worst case is if they can prove maliciousness and fine you. If the reseller calculated things and came to a reasonable (but lower) number I'd be surprised if that'…

Ah sorry, yes I was one of those people -- we had like half an office using MSDN licenses. Just didn't know better (they give us 10, we'll buy another MSDN license when we run out!).

Re: PostgreSQL vs. MS SQL Server

#125

Post by glogla » Thu, Apr 30, 2015, 5:18 PM UTC

MichaelGG wrote: post #101

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…

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.

Materialized views that refresh on demand are are far from useless. In my experience they are often used on Oracle for both "analytics cache" where you don't need the newest data and the select is very complex and slow, or as parts of ETL processes to decouple data transformation from loading, merging or exporting.

(this makes me realize that postgres can do some pretty cool ETL by itself - and you can express a lot of it in simple SQL terms with foreign data wrappers and materalized views. One on-demand refreshed materialized view can be able to fetch data from many different sources, transform it and provide local access)

MS SQL server, on the other hand, can't do materialized views that update on demand, which makes them hard to use on complex and large datasets, and it makes the database less predictable (inserting one row may be simple operation or it may change 10 tables, you'll never know).

Materialized views in MS SQL have rather bad support for aggregation functions and other computations - you can't compute average in them, for example, as AVG is not supported and doing SUM/COUNT is also not supported because of the division, etc.

I'd say that materialized views in MS SQL are more nice for caching or something like that, but you can't very well use them for analytics or ETL, like you usually see in Oracle, though Oracle can do both on-demand and automatic refresh, of course.

EDIT: the materialized views thing was one of my most amusing experiences with SQL Server. I tried to use materialized view instead of trigger for updating data dependent table in geographic application and I needed to compute average and sums for zoomed out map layers.

The way you add materialized view is by itself confusing - you add a view, then make index on that view, then select from it, but you have to use special keyword so the system uses the materialized view and not just the view.

I tried to use AVG but was told by the server "AVG is not supported, use SUM/COUNT". I tried SUM/COUNT but was told "COUNT is not supported, use COUNT_BIG". I tried SUM/COUNT_BIG and was finally told "using arithmetic in materialized view is not supported".

To this day, I have no idea why one error message suggested doing something that's impossible.

Re: PostgreSQL vs. MS SQL Server

#126

Post by MichaelGG » Thu, Apr 30, 2015, 5:35 PM UTC

glogla wrote: post #125

MichaelGG wrote: post #101

Earlier quoted context omitted.

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.

Materialized views that refresh on demand are are far from useless. In my experience they are often used on Oracle for both "analytics cache" where you don't need the newest data and the select is very complex and slow, or as parts of ETL processes to decouple data transformation from loading, merging or exporting. (this makes me realize that postgres can do some pretty cool ETL by itself - and you can express a lot…

What's the difference between refresh on demand and just creating a table based on a SELECT? It seems like some minor syntactic sugar (which is great, I'm all for that), but not much of a feature.

Re: PostgreSQL vs. MS SQL Server

#127

Post by glogla » Thu, Apr 30, 2015, 5:39 PM UTC

dragonwriter wrote: post #112

MichaelGG wrote: post #103

Earlier quoted context omitted.

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

> It has a feature named materialized views, but it isn't close to being the real feature that MSSQL calls it. MSSQL doesn't have a featured called materialized views, it has a feature called indexed views which happens to implement a fairly robust version of the general DB pattern called materialized view. PG has less mature materialized views (which are called materialized views) starting from 9.3, with additional…

SQL server can't do materialized views that refresh on demand, while postgres can't do materialized views that refresh automatically.

Personally, I saw materialized views that refresh on demand used much more than materialized views that refresh automatically, even though Oracle can be both.

Re: PostgreSQL vs. MS SQL Server

#128

Post by glogla » Thu, Apr 30, 2015, 5:48 PM UTC

MichaelGG wrote: post #126

glogla wrote: post #125

Earlier quoted context omitted.

Materialized views that refresh on demand are are far from useless. In my experience they are often used on Oracle for both "analytics cache" where you don't need the newest data and the select is very complex and slow, or as parts of ETL processes to decouple data transformation from loading, merging or exporting. (this makes me realize that postgres can do some pretty cool ETL by itself - and you can express a lot…

What's the difference between refresh on demand and just creating a table based on a SELECT? It seems like some minor syntactic sugar (which is great, I'm all for that), but not much of a feature.

You can read the materialized view while it is refreshing, you just get the old version of the data. You could do that by doing delete and insert as select in a transaction, but that's not practical for tens or hundreds of thousands of rows.

On Oracle, this is also useful because it works kind of like creating and dropping partition, instead of doing delete then insert, and that's better for the DB because of how Oracle deals with tablespace and blocks (and Oracle's truncate is non-transactional, I think), but that doesn't do much on SQL Server vs. postgres discussion :)

Re: PostgreSQL vs. MS SQL Server

#129

Post by MichaelGG » Thu, Apr 30, 2015, 7:12 PM UTC

glogla wrote: post #128

MichaelGG wrote: post #126

Earlier quoted context omitted.

What's the difference between refresh on demand and just creating a table based on a SELECT? It seems like some minor syntactic sugar (which is great, I'm all for that), but not much of a feature.

You can read the materialized view while it is refreshing, you just get the old version of the data. You could do that by doing delete and insert as select in a transaction, but that's not practical for tens or hundreds of thousands of rows. On Oracle, this is also useful because it works kind of like creating and dropping partition, instead of doing delete then insert, and that's better for the DB because of how Ora…

Or just insert into a new table then change the name in a transaction?

Re: PostgreSQL vs. MS SQL Server

#130

Post by ironchef » Thu, Apr 30, 2015, 7:28 PM UTC

kidmenot wrote: post #40

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.

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.

AquaDataStudio was my choice at the time. Expensive, but worked on pretty much all the DBMS I had to use at the time (which included oracle, pg, teradata, mssql, mysql to name a few).