As Julien says, Python applications can handle millions of requests. Well, we know here that it’s easier on them when they are using PostgreSQL of course!
Today I want to react to an article that claims that Relational Algebra Is the Root of SQL Problems in which the author hand-waves the following position:
SQL becomes more a hindrance to data manipulation than an efficient tool. SQL’s greatest problem isn’t in the implementation level, but at its theory foundation. The problem can’t be solved by application optimization. Relational algebra isn’t sophisticated enough for handling the complicated data manipulation scenarios.
Then they go on to several arguments from authority to “prove” their point. My reading of the article is that SQL is very hard when you didn’t care to learn it, as most technologies are.
In this article, we’re going to look at the simple examples provided where apparently SQL makes it so much harder to find a solution compared to writing some Java or C++ code. Contrary to the original article, we go as far as to actually writing both the SQL solution and a complete Python solution, so that we can compare.
In another article here, entitled on JSON and SQL, we saw in great details how to import a data set only available as a giant JSON file. Then we normalized the data set, so as to be able to write SQL and process our data. This approach is sometimes very useful and was a good way to learn some of the JSON functions provided by PostgreSQL.
In this article, we’re going to use SQL to export the data from our relational model into a JSON document. The trick that makes it complex in this example is that we have a recursive data model, with a notion of a parent row that exists in the same table as the current one. That’s a nice excuse to learn more about the SQL construct WITH RECURSIVE.
It seems to be usual nowadays to review the previous year, and readers apparently like Top-N Lists — that’s you now, so let’s hope that my understanding works with you too.
Of course 2018 will see its own amount of new and original content added to this blog, with a continuous focus towards how to make the best out of the SQL powerful programming language, and its advanced concurrency semantics.
You might have read it in the news already in Citus’ blog post by Sumedh Pathak: PostgreSQL Expert Dimitri Fontaine joins Citus Data. I am very happy to join a talented team here at Citus, and excited to work on an Open Source solution for distributed SQL on-top of PostgreSQL! In this article I’m going to cover my first technical contributions to Citus database, as it happens that a few patches of mine made it to the main source tree already.
TL;DR It’s good to be working on PostgreSQL related source code again, and to have the opportunity to solve PostgreSQL related problems at scale!
After having been involved in many migration projects over the last 10 years, I decided to publish the following White Paper in order to share my learnings.
The paper is titled Migrating to PostgreSQL, Tools and Methodology and details the Continuous Migration approach. It describes how to migrate from another relational database server technology to PostgreSQL. The reasons to do so are many, and first among them is often the licensing model.
As I’ve been organizing the first edition of pgDay Paris back in 2015, this very conference is special to me! It’s been an amazing experience launching a new PostgreSQL conference…
PostgreSQL ships with an interactive console with the command line tool named psql. It can be used both for scripting and interactive usage and is moreover quite a powerful tool. Interactive features includes autocompletion, readline support (history searches, modern keyboard movements, etc), input and output redirection, formatted output, and more.
Florent Fourcot has read Mastering PostgreSQL in Application Development and has seen tremendous inprovements in his production setup from reading the first chapters and applying the book advices to his use case.
Here’s an interview run with Florent where he explains the context in which such improvements has been made!
The Enterprise Edition of Mastering PostgreSQL in Application Development ships with a docker image that hosts both a PostgreSQL server instance with a pre-loaded database, the one that’s used throughout the book examples, and also with a Jupyter Network notebook that hosts SQL queries thanks to the sql_magic plugin.
The PostgreSQL community made the explicit choice some times ago that they would not use the infamous master and slave terminology. Instead, the documentation introduces the concepts of High Availability, Load Balancing, and Replication with the terms Primary and Standby, and the even more generic term Replica is used in contexts when only the data flow is considered, rather than the particular role of a node.