4 ms·
My previous job(s) were primarily MSSQL and I was excited to get to use PG in my current position. In general I agree with the premise of the article - for jus
by netcraft 10y ago
My previous job(s) were primarily MSSQL and I was excited to get to use PG in my current position. In general I agree with the premise of the article - for just about everything PG is better. For me, the only places where MSSQL has an edge is:
- Management Studio is superior to any standard query manager ive found for PG. I currently use what is in intellij (which is similar to their datagrip product) which I find perfectly acceptable and in some ways superior to Management Studio, but PGAdmin isn't in the same ballpark.
- MSSQL visual query explainer is great - ive found some alternatives (http://tatiyants.com/pev/#/plans http://tatiyants.com/pev/#/plans) and in general ive gotten much better at reading and understanding the text output of PG's explain, but I do miss MSSQL in this regard.
- MSSQL CTE's not being optimization fences meant that I got to use them in more places. But I can do more things with CTE's in PG (such as using an ORDER BY) - this is relatively minor but annoying - readability with CTE's vs subqueries is much improved.
- Snapshot and transaction logs with MSSQL is much better than any alternative ive found with PG, but tbf I haven't looked into it very far. When it comes up though it would be really nice to be able to restore to point in time easily with PG.
- I do occassionally come across missing functions in PG, but thats getting more and more rare.
- Cloud offerings - they exist for PG and are great for what they offer, but many extensions are not available or not fully configurable which is also sometimes hard to even determine.
All that said - if you aren't already invested in the MS ecosystem, PG would be my preference for sure. JSON/hstore/arrays alone make so many things possible even if you arent using those datatypes in your tables.
- xeonoex 10y agoHave you tried Valentina Studio? (https://valentina-db.com/en/ https://valentina-db.com/en/) It's not SSMS but it's the best I've used for Postgres so far.
- netcraft 10y agoI've never heard of it but ill take a look. First blush none of the screenshots show actual sql which would be my first priority - but ill check it out. thanks!
- zie 10y agopgbarman [0] will handle the restore to point in time use case above. There are other solutions as well, but I've found pgbarman to work great, and can't speak for others. [0] http://www.pgbarman.org/ http://www.pgbarman.org/
- jamesfmilne 10y agoThey missed an opportunity to call it PGBartender
- snuxoll 10y agoBarman is awesome, have been using it in production for two years now - I cannot reccomend it enough. Funny enough, I found it easier to work with than trying to set up a maintenance plan with SSMS and restores are dead-easy too.
- mst 10y agoYou may also like http://explain.depesz.com/ http://explain.depesz.com/
- alexbanks 10y agoPG Admin 3 was lightyears better than PG Admin 4, for what it's worth. For some reason in 4 they opted to update their UI to be an extremely error prone, buggy, inconsistent experience. For basic querying I use SQLTabs, for DBA-type stuff I use PG Admin 3.
- buckbova 10y ago- Triggers In PG triggers are row by row. In mssql it's one batch where you can access the inserted/deleted records.
- merb 10y agofalse. in PG triggers are either for each row OR for each statement.
- Tostino 10y agoNot false, if you need to access the rows which changed you need to use a per-row trigger in Postgres. There is work being done to support seeing all the affected rows in a per-statement trigger like you can in Sql Server, but it's not committed yet. See: https://www.postgresql.org/message-id/flat/20140616195900.GC18688%40eldon.alvh.no-ip.org#20140616195900.GC18688@eldon.alvh.no-ip.org https://www.postgresql.org/message-id/flat/20140616195900.GC...
- dsp1234 10y agoin PG (such as using an ORDER BY) You can use order by in a CTE in mssql, you just have to specify a TOP clause. ;with bar as ( select top 100 percent * from foo order by foo.name ) select * from bar
- netcraft 10y agointeresting, im pretty certain I couldnt do this with the version I was using at the time, but maybe I just didnt have the right google fu - I wonder what version that came with?