Tuesday, October 24

PG Phriday: pglogical and Postgres 10 Partitions

pglogical, Shaun's PlanetPostgreSQL
During the Postgres Open 2017 conference in San Francisco, someone came to the 2ndQuadrant booth and struck up a conversation with me. During our shameless geeking out over database mechanics, he asked me if pglogical supported the new Postgres 10 partitions. Given my noted expertise in all things Postgres, I answered in the appropriate manner: "I have no idea. I'll have to look into that." Well, after a bit of experimentation, I have a more concrete answer, and it's reassuringly positive. The Problem Given a table on a provider node, is it possible to capture only INSERT traffic such that it accumulates on a subscribed system for archival purposes? It's a fairly common tactic, and allows an active OLTP system to regularly purge old data, while a reporting OLAP system keeps it (more…)

Setting up a build machine for Visual Studio 2017

Andrew's PlanetPostgreSQL
Recently we've had a patch submitted to support the latest incarnation of the Microsoft build tools, Visual Studio 2017. I didn't have a spare Windows machine available to test with, so I set up Windows machine on Amazon AWS to test with. I chose Windows Server 2016, a t2.medium instance with a 50 GB root disk (The default 30Gb is a bit tight.) This costs about US$0.065 per hour to run, so it's pretty cheap. The first things I did, as I always do with these machines, were to turn off Internet Enhanced Security, which has a habit of getting in the way, and then install the Firefox web browser. Then I installed the Chocolatey package manager for Windows. This is a pretty useful tool, somewhat similar to yum, dnf and apt. You need to install this via an administrative command shell. Once (more…)

PostgreSQL 10 Logical Replication with OmniDB

OmniDB, William's PlanetPostgreSQL
1. Introduction Logical replication uses a publish/subscribe model and so we create publications on the upstream (or publisher) and subscriptions on downstream (or subscriber). For more details about it, please refer to this blog post from my colleague Petr Jelinek, and also to the PostgreSQL documentation. Here we will show how to build a test environment to play with this new feature from PostgreSQL 10, and how to configure it using OmniDB 2.1. 2. Building test environment Let's build a 2-node test environment to illustrate how to configure PG10 logical replication feature within OmniDB. 2.1. Pull OmniDB repo The first thing you need to do is to download OmniDB in the repo from GitHub and make sure you are in the development branch. Run the following: 2.2. Create 2 virtual (more…)

OmniDB: Average execution time of PostgreSQL webmethods

OmniDB, William's PlanetPostgreSQL
1. Introduction Being a web application, OmniDB is composed of many webmethods. A webmethod is a special kind of function hosted on the server side (the web server), called from the client side (the web browser), when the user performs some action on OmniDB web interface. For instance, OmniDB has several webmethods that know how to communicate with all supported versions of PostgreSQL. This set of webmethods is called PostgreSQL webmethods of OmniDB. We automated the execution of all PostgreSQL webmethods through the Django unit tests feature. For each supported version of PostgreSQL (from 9.2 to 9.6 and also 10), there are 56 unit tests that simulate common user behavior. The database used for the tests is a well known PostgreSQL sample called dellstore2. Using vagrant, we (more…)

Java Object Oriented Querying (jOOQ)

Umair's PlanetPostgreSQL
jOOQ is an ORM-alternative that is relational model centric rather than domain model centric like most ORMs. For example, while Hibernate lets you write Java code and then automatically translates it to SQL, jOOQ let’s you write relational objects in your database using SQL and then automatically generates Java code to map to those relational objects. The writers of jOOQ believe in the power of SQL and assume that you want low level control of the SQL running for your application. This means that you can write your database and your schema without having to worry about how (and if!) it can be handled in Java. Why Use jOOQ? While JPA provides a huge framework with a great deal of flexibility and power, it can very quickly become quite complex. jOOQ provides a simpler interface for (more…)

PG Phriday: Smooth Operator

Shaun's PlanetPostgreSQL
One of the handy things Oracle does with dates is allow manipulation with standard arithmetic. Want tomorrow's date? Add one. Want a week ago? Subtract seven. Postgres does something close with its INTERVAL syntax, under the explanation that we don't necessarily want to make assumptions about what is being added to a date or timestamp. But Postgres has a big secret in the fact we can arbitrarily create operators to get the behavior we desire. No Place for Beginners or Sensitive Hearts So what happens if we happen to know that the developers and applications targeting the database prefer the Oracle approach, and that integers should always increment or decrement a date value? Let's start with something basic like adding a integers to a date. What happens if we try this with a (more…)

PG Phriday: Crazy Correlated Column Crusade

Shaun's PlanetPostgreSQL
For a long time, the Postgres query planner has sported a huge blinking neon blind-spot that frustrated and enraged DBAs throughout the universe to a level just shy of murderous frenzy. How is this even possible? What terrible lurking horror could elicit such a visceral response from probably the most boring and straight-laced people ever to draw breath? What else? Correlated statistics. The Adventure Begins! The Postgres query planner is a cost-estimation engine. Postgres gathers statistics on table contents such as most and least frequent values, value cardinality, rudimentary histograms, and so on. Along with multiple metrics related to hardware responsiveness, it generates multiple viable execution strategies, and chooses the one that probably costs the least resources. So far, this (more…)