PROGRAMMING AND SOMETHING MORE...

I'm a full-stack PHP developer with some C/C++, Java SE and Delphi background. x86 assembly doesn't scare me. Old school classic ASP and VBScript is not fun, but I have experience with it. Pascal is fine - from good old Borland Turbo Pascal 7.0 to Borland Delphi 2007. C# is cool enough - I'm .NET newbie, but I noticed it's good for some parts of my past projects. Creative back-end web development is my main professional activity.

Connecting vary software components into one synergetic system is the way of my work. It's a mixture of PHP code and C/C++/Java/C# code if necessary. Maybe something more - I'm open for new technologies and tools. Usually back-end should be associated with a front-end layer, so JavaScript, HTML/CSS and GIMP are my next weapons, but I prefer to be hidden behind server firewall. I'm not a web designer. If I have to do something at front-end side, I prefer coding in JavaScript only. Something about data layer. PostgreSQL is my favourite RDBMS. Currently I use MariaDB, PostgreSQL and damned MS SQL Server. Some time ago I intensively used Firebird also with its best pl/SQL syntax I've ever seen. If I need to cache, I use Redis or Memcached. Asynchronous and/or pipeline processing - Gearman with GearmanManager is my choice (PHP workers).

See more:
https://www.enterprisedb.com/nosql-for-enterprise
http://developer.olery.com/blog/goodbye-mongodb-hello-postgresql
http://svs.io/post/31724990463/why-i-migrated-away-from-mongodb
https://www.userlike.com/en/blog/bye-by-mysql-and-mongodb-guten-tag-postgresql
https://www.airpair.com/postgresql/posts/sql-vs-nosql-ko-postgres-vs-mongo
https://www.linkedin.com/pulse/mongodb-32-now-powered-postgresql-john-de-goes

See more: http://developer.amd.com/wordpress/media/2013/06/1738_final.pdf

Quite interesting, isn't it? I suggest to compare statistics in CVE database for Oracle, MS SQL Server and PostgreSQL. :-) You say banking and government databases needs security which can be only available in commercial products like Oracle or SQL Server? Buhahaha!!!

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 DBA

Oracle 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 Sucks

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

Some useful projects based on PostgreSQL (PostgreSQL forks fully compatible with core version) and PostgreSQL extensions:

• Postgres-XL
• Greenplum Database
• PostGIS
• Citus
• AXLE
• PipelineDB
• PG-Strom

PostgreSQL in action:

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

Some tools for reporting, data visualization, data mining and machine learning which can be used with PostgreSQL and its forks/extensions:

• 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

Administration and development tools:

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

Commercial support:

• EnterpriseDB
• 2ndQuadrant
• CYBERTEC
• and more...

Some comparisons:

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

Powered by PostgreSQL: top level domain registry databases (info, org) - Afilias, Yahoo (PETABYTES of data in custom columnar store based on PostgreSQL), Skype, Instagram, Yandex.Mail, BASF e-commerce, Adyen, Zalando, Proximity, US State Department, US Army, FAA, NASA.

***

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.

***

I'm tired with all these crappy code produced by C#, Java and T-SQL "programmers", so please, dear "true developers", don't teach us, PHP developers and open source software users, how to code and use databases. SQL Server? No, thanks. I'm not going to play with this shit anymore!

***

So shut up Microsoft corporate MVP certified monkey and go away to play with your deadlocks and dirty reads...

See more: http://www.deviantart.com/art/Postgresql-vs-others-125826195