At the same time, developers are also facing added pressure to speed application delivery and respond more directly to line of business needs. This has given rise to ‘shadow IT’ efforts – a term used to describe IT systems and solutions built or used within an organization without the involvement of IT leadership
.. Integrating Foreign Data Types in Postgres
Postgres has a solution for this dilemma: a feature called a Foreign Data Wrapper(FDW), the first of which was developed four years ago by PostgreSQL community leader Dave Page and based on the SQL standard SQL/MED (SQL Management of External Data). FDWs provide a SQL interface for accessing remote and large data objects in remote data stores, enabling DBAs to integrate data from disparate sources and bring them into a common model under the Postgres database.
Through this, DBAs can access and manipulate data being managed by other systems as if it were within a native Postgres table. For example, using the FDW for MongoDB, database administrators can query the data from the document database and join it with data from the local Postgres table using SQL. Using this method, users can view, sort and group the data as rows and columns or as JSON documents. They can even write (insert, update or delete) data directly in the source document database from Postgres as if were a single seamless deployment. The same could be done with a Hadoop cluster or MySQL deployment. FDWs allow Postgres to function as a central federated database, or ‘hub,’ for enterprises
.. According to Gartner, “By 2017, 50% of data stored in NoSQL DBMSs will be damaging to the business due to a lack of applied information governance policies and programs.
MoSQL: a MongoDB → SQL streaming translator
At Stripe, we love MongoDB. We love the flexibility it gives us in changing data schemas as we grow and learn, and we love its operational properties. We love replsets. We love the uniform query language that doesn’t require generating and parsing strings, tracking placeholder parameters, or any of that nonsense.
The thing is, we also love SQL. We love the ease of doing ad-hoc data analysis over small-to-mid-size datasets in SQL. We love doing JOINs to pull together reports summarizing properties across multiple datasets. We love the fact that virtually every employee we hire already knows SQL and is comfortable using it to ask and answer questions about data.
Common misconceptions about locking in PostgreSQL
Developers tend to forget that there’s a lock queue. Let me show you why you should always remember that.
Let’s go back to our two concurrent sessions again. First one has an open transaction that holds an AccessShareLock on our table, second has a waiting AccessExclusiveLock in order to drop the table.
Let’s fire up two more sessions. Observe:
Solving the Traveling Salesman Problem with Postgres Recursive CTEs
Recursive CTEs are more powerful – they reference themselves and allow you to explore hierarchical data. While that may sound complicated, the underlying concept is very similar to a for loop in other programming languages.
.. Here’s a simple recursive CTE that generates the numbers 1 to 10. The anchor member selects the value 1, and the recursive member adds to it up to the number 10:
with recursive incrementer(prev_val) as (
select 1 — anchor member
union all
select — recursive member
incrementer.prev_val + 1
from incrementer
where prev_val < 10 -- termination condition ) select * from incrementer