25 Features your Developers are missing when not using Postgres! | MongoDB, PostgreSQL and Open source solutions. Comparing MySQL and Postgres 9.0 Replication. By Robin Schumacher and Gary Carter, EnterpriseDB Replication is one of the most popular features used in RDBMS’s today. Replication is used for disaster recovery purposes (i.e. backup or warm stand-by servers), reporting systems where query activity is offloaded onto another machine to conserve resources on the transactional server, and scale-out architectures that use sharding or other methods to increase overall query performance and data throughput.
Replication is not restricted to only the major proprietary databases; open source databases such as MySQL and PostgreSQL also offer replication as a feature. While MySQL has offered built-in replication for a number of years, PostgreSQL replication used to be accomplished via community software that was an add-on to the core Postgres Server. That all changed with the release of version 9.0 of PostgreSQL, which now offers built-in streaming replication that is based on its proven write ahead log technology. An Overview of MySQL Replication. Oracle Pulls the Rug Out From Under PostgreSQL. Before the Oracle acquisition, Sun was contributing three servers to the build farm for the PostgreSQL project to test updates and ensure stability on Solaris.
Even though PostgreSQL was technically a competitor to Sun's MySQL, the company still supported development of the project and contributed DTrace support and other features to the platform. This week, Oracle pulled the plug on those servers with no warning, causing a frantic search for new hosts. This report first surfaced on the Australian business site, iTNews.
The move by Oracle seemed spiteful based on iTnews' interview with Andrew Dunstan who built the PostgreSQL build farm. "Since I have personally been involved in migrating large apps from Oracle to PostgreSQL, I can understand why they [Oracle] might feel like [competitors]," said Duntan, "but in the open source community, we try to be a bit less dog-eat-dog. " PostgreSQL is used by Yahoo, Skype, MySpace, and many other organizations and independent developers. PostgreSQL SQL Tricks - Pgsql.cz. SQL is a language where one task can be solved multiple ways with different efficiency. You can see effective use of PostgreSQL's possibilities on this page. Older tricks How to unnest an array with element indexes Sometimes we need to unnest a array with indexes. Author: Pavel Stěhule (PostgreSQL 9.1) CREATE OR REPLACE FUNCTION unnest_rownum(anyarray) RETURNS TABLE (id int, element anyelement) AS $$ BEGIN id := 1; FOREACH element IN array $1 LOOP RETURN NEXT; id := id + 1; END LOOP; RETURN; END $$ LANGUAGE plpgsql; postgres=# select * from unnest_rownum(ARRAY['A','B','C']); id | element ----+--------- 1 | A 2 | B 3 | C (3 rows) Author: Tom Lane (PostgreSQL 9.3) SELECT i, arraycol[i] FROM tab, LATERAL generate_subscripts(arraycol, 1) as i; 9.4 supports clause WITH ORDINALITY: postgres=# SELECT * FROM unnest(ARRAY['A','D','C']) WITH ORDINALITY; unnest | ordinality --------+------------ A | 1 D | 2 C | 3 (3 rows) Allow only one NULL in column Author: Pavel Stěhule Bytea to BLOB conversion Usage:
WITH (so much drama in the CTE) - David Fetter's blog.