PROGRAMMING AND SOMETHING MORE...
- SQL and pl/SQL dialects: PostgreSQL 9.5, Firebird 2.5, MySQL 4/5, MariaDB 10, MS SQL Server Enterprise 2012
- NoSQL: Redis, MongoDB
- MS SQL Server environment: T-SQL stored procedures, SS Agent, SS Profiler, SS Service Broker, SS Integration Services - SSIS, SS Management Studio - SSMS, Business Intelligence Development Studio - BIDS, TCP/HTTP/SOAP endpoints
- Web servers: Nginx (OpenResty), Apache
- OS: Ubuntu, Debian, Mac OS X
- Low level development: C (x86 MS-DOS: tcc, djgpp; x86 GNU/Linux: gcc; x86 Win32: bcc32, cl; AVR: avr-gcc (WinAvr, AVR-Eclipse)), assembler (x86 MS-DOS: tasm, masm, nasm; x86 GNU/Linux: nasm; AVR: AVR Studio)
- Development environment: Ubuntu/Mac OS X, IntelliJ/CLion/PhpStorm, Vagrant, Docker, Ant, Phing, Ansible, shell scripting, Git (SourceTree)
- MS Office 97: MS Access with VBA (Firebird frontend), MS Excel with VBA, MS Word with VBA, MS PowerPoint
- OpenOffice/LibreOffice: Calc, Writer, Draw, Impress
- CAD: Autodesk AutoCAD 14, Autodesk Inventor 7, ASCON Kompas-3D 13, Eagle (PCB design)
Something about me - coder, part-time PostgreSQL evangelist, professional SQL Server hater, Linux & Mac user, amateur draftsman, BMW driver...
SQL Server not allowed - restricted area - authorized ANSI SQL only
"Developers are more likely to port SQL Server to Postgres because it is open source, supports more ANSI features, runs on more platforms, uses a different ACID model and has a great optimizer." Joe Celko
"After having to inherit and deal with a multi-terabyte mongo cluster in a production environment, I will never use it again. Especially with Postgres' composite types, jsonb querying and indexing, materialized views, plv8, and numerous intergrated transaction and locking capabilities.... It has everything I've needed in a database." Reddit user - "Why you should never, ever, ever use MongoDB"
Something about reliability - another opinion same as above:
"I am DBA since 1992, working first with Ingres (the previous "release" of Postgres) and Oracle. What I can say is that with an Ingres cluster production server with 32 nodes and a customer base of 1,5 million customers, I was able to manage it alone during 2 years at TIM mobile company. The same customer base went to Oracle 8i paralel (so it means only 2 nodes) and 4 dbas spend some nights regularly in order to get it 24x7." Ingres/Oracle DBAOracle has fine SQL implementation like PostgreSQL, but SQL Server not... T-SQL is EVIL!!! It is worst SQL dialect I've ever seen. It's like damned Internet Explorer in the databases world. Try this in SQL Server: "DROP SCHEMA IF EXISTS schema_name CASCADE" :-D You end up with custom recursive procedures and scanning system tables to do this operation, which needs only one fucking line in PostgreSQL! CASCADE is available since PostgreSQL 7.3 (2002) or even earlier. IF EXISTS is available since PostgreSQL 8.2 (2006). SQL Server doesn't support CASCADE clause, IF EXISTS is available since SQL Server 2016 (sic!). Date and time arithmetic in SQL Server - one big WTF?! DDL implementation - it's a joke! Sequences? Forget! Available since 2012! WTF?! No LIMIT/OFFSET support (TOP n is not the same), crazy "N" prefixed CHAR types and string literals, crappy non-standard compliant date and time types with insane functions, incomplete XML schema validator...
It's my favourite one fuck-up: triggers. Every RDBMS, which I used, offers virtual rows NEW and OLD at the trigger logic level. But SQL Server of course not! You have to run special SELECT queries on virtual read-only tables (inserted, deleted) to obtain primary key of the inserted/modified/deleted row and other columns. WTF?! What a stupid idea! Instead of overwrite some columns in NEW row or check columns in OLD/NEW virtual rows, before sending it to the table, you have to select rows from these fucking tables, get primary keys and run additional UPDATE query with selected primary key value to modify some columns in this new row after trigger activation (BEFORE UPDATE for example). Do you understand? You have to run extra UPDATE query from trigger logic to modify the same row in the table instead of modify NEW row associated with triggered BEFORE UPDATE action before writing it in the table. NEW/OLD rows should be available at trigger context like in other databases. WTF?! Unecessary additional, unintuitive SQL code and lower performance.
I found many more situations like this. Just a little example: you can't pass expressions directly as arguments to the stored procedures/functions - you have to assign them to the temporary variables and pass them to the called subroutines. Another one: XML validation schemas don't support ALTER expression - only CREATE and DROP. What a fucking inconsistency! It doesn't support full XSD specification for XML Schema Collections validation. Almost every single DDL operation requires stupid GO command which closes current session. Forget about transactions in DDL and batch execution of DDL commands, like in PostgreSQL you can do!
So yes - SQL Server sucks and you have to pay for it. Everywhere ugly hacks, workarounds, redundant procedural T-SQL spaghetti code with stupid NOLOCK hints unknown in other databases with full MVCC support. I know - SQL Server 2005 and higher supports READ COMMITTED SNAPSHOT mode with storing rows in tempdb which is default disabled... Is it a temporary, buggy, low performance, marketing solution for MVCC, because other databases have it from the beginning, like Oracle, PostgreSQL, InterBase/Firebird, MySQL (InnoDB)? Why it is disabled by default? It's a bad joke... Compare COMMIT and ROLLBACK time of big UPDATE between PostgreSQL and SQL Server. In PostgreSQL it's almost instant, because it stores versioning data in table structure instead of throwing them away to the external tempdb database (if snapshot mode is enabled of course, if not - SELECT deadlocks FROM sql_server). I know that this mechanism requires auto vacuum, but it is a price for better concurrency and transactions management almost without deadlocks (no need to run super profiler tool to investigate another fucking deadlock in SS Agent job).
SQL Server is always faster than PostgreSQL? It's a bullshit. Both of them are comparable at speed, sometimes SQL Server is better, sometimes PostgreSQL is better, sometimes both engines do some tasks at the same performance. Because of MVCC architecture, COUNT(*) without WHERE clause is slower in PostgreSQL, but try to load metadata in SQL Server and compare loading time to the PostgreSQL time. LOL! What operation is more useful and done more frequently? Stupid COUNT without conditions whether loading database structure in a database administration tool? If you properly use PostgreSQL features unavailable in SQL Server, I bet it will be a lot faster than any SQL Server configuration for the same tasks. See above: transactions management and COMMIT/ROLLBACK time. Don't forget that it will be much cheaper and more friendly for database developers also. Read this: Postgres looks to me like it's saving us like 5X in hardware costs as we continue to grow.
"About a 5x-10x CPU and Disk I/O improvement migrating a pretty large project from [a major proprietary database mentioned in the article] to Postgres. CPU and Disk I/O Graphs below."
"I went through a similar switch from MSSQL to postgres. We moved 7 applications of various sizes over and the biggest hurdle was office politics. The migration never took more than a day for each app. ETL and reporting were not affected since we had previously integrated messaging (rabbitmq) pretty heavily into every system at the company and ETL gets their data from there. We also switched the DB servers to over to linux and got a huge performance boost from that. We did rewrite some stuff, but that was mostly to take advantage of postgres features like array types (instead of a article tags table, you'd have an tags array) which we used to speed up some applications. One we sped up by a factor of 10, just by using array types and offloading json generation to postgres: select array_to_json(array_agg(row_to_json(t))) from ( some query ) t" SQL Server 2014 Standard Edition SucksSo what you said about poor PostgreSQL's performance? I can't hear it. PostgreSQL will be even better at IO operations if development team remove unnecessary system calls and replace pages caching via OS disk cache buffer with custom implementation at the RDBMS side. They know about this limitation and I think they improve this in the future.
But why not SQL Server Enterprise Edition? It has cool features: compression, no RAM and CPU cores limit, SSIS, SSRS, SSAS, super ultimate ETL for BI, beautiful creators and wizards for monkeys, blah blah blah...
- Processing around 10 million events per second and generating 1.7 million aggregates
- Running 10 Million PostgreSQL Indexes In Production (And Counting)
- Plumbing the Internet with PostgreSQL: An Open Source Case Study
- PostgreSQL affiliates .ORG domain
- Size matters: Yahoo claims 2-petabyte database is world's biggest, busiest (based on a heavily modified PostgreSQL engine, the year-old database processes 24 billion events a day)
- Learnings from Five Years as a Skype Architect
- Building A High-Availability PostgreSQL Cluster At ARIN (closely replicated the Oracle RAC system)
- Yandex.Mail: Migration from Oracle to PostgreSQL (1 PB with redundancy, 100+ billion records, 250k TPS, faster deployment)
- Pentaho - data integration and business analytics platform
- Mondrian - Online Analytical Processing server (OLAP)
- BIRT - open source data visualizations and reports
- D3.js - Data-Driven Documents
- 2UDA - big data analytics with PostgreSQL
- Orange - open source machine learning and data visualization
- Weka 3: Data Mining Software in Java
- PgAdmin 4 and PgAgent (I use it under Ubuntu and OS X)
- JetBrains DataGrip - multi-database with very good code completion and database navigation (I use it with MariaDB/PostgreSQL/SQL Server and with other JetBrains cool development tools under Ubuntu and OS X)
- Navicat for PostgreSQL with very good code completion and database navigation
- EMS SQL Manager for PostgreSQL with very good code completion and database navigation (I used it under Windows with PostgreSQL and damned SQL Server because MS SSMS sucks, except SSMS 2017)
- HeidiSQL for PostgreSQL (I used it with MySQL under OS X)
- and more...
- and more...
- PostgreSQL vs. MS SQL Server
- PostgreSQL vs. MS SQL Server at LinkedIn
- What makes PostgreSQL better than SQL Server?
- Ten Reasons PostgreSQL is Better Than SQL Server
- Top 10 Reasons I like Postgres Over SQL Server
- SQL Server 2008 R2 or PostgreSQL
- How Popular Databases Deal with DDL Commands in Transactions
- Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?
- Migrating from MSSQL to PostgreSQL
- Why should you learn PostgreSQL?
- Skype Plans For PostgreSQL To Scale To 1 Billion Users
- Comparing Benchmark SQL Performance on Red Hat Enterprise Linux 5 to Windows Server Enterprise (Postgres Plus 8.3.8 vs SQL Server 2008 R2)
***Is it an idiot or stupid troll: I Hate Open Source Software - Why Open Source Sucks? Never mind. Hey, Grumpy Nerd, you corporate M$ scum! You can put all these MicroShit software like MS SQL Server, MS Internet Explorer and MS Outlook, deep in your ass! Except good products like: MS Office (without Outlook), .NET/C#, MS Visual Studio, TypeScript.
Beware of charging blue elephant
Just listen it...