Wednesday, December 12

Tag: Postgres-BDR

PostgreSQL for IoT Data Retention and Archiving

Haroon's PlanetPostgreSQL, Internet of Things
We do understand that IoT revolution is resulting in enormous amounts of data. With brisk data growth where data is mostly time series append-only, relational databases and DBAs have a rather tough task to store, maintain, archive and in some cases get rid of the old data in an efficient manner. In my previous posts, I talked about various strategies and techniques around better scalability for data storage using PostgreSQL and Postgres-BDR extension. Data retention is becoming ever so important. So let’s see what PostgreSQL 10 and above have to offer to efficiently manage your data retention needs. PostgreSQL has supported time based partitioning in some form for quite some time. However, it wasn’t part of the core PostgreSQL. PostgreSQL 10 made a major improvement in this area by (more…)

PostgreSQL and IoT Data Localization, Integration, and Write Scalability

Haroon's PlanetPostgreSQL, Internet of Things
In my previous post we looked at various partitioning techniques in PostgreSQL for efficient IoT data management using IoT Solution. We do understand that the basic objective behind time based partitions is to achieve better performance, especially in IoT environments, where active data is usually the most recent data. New data is usually append only and it can grow pretty quickly depending on the frequency of the data points. Some might argue on why to have multiple write nodes (as would be inherently needed in a BDR cluster) when a single node can effectively handle incoming IoT data utilizing features such as time based partitioning. Gartner estimated 8.4 billion connected devices in 2017, and it expects that this number will grow to over 20 billion by 2020. The scale at which (more…)

PostgreSQL Time-based Partitioning for IoT Data using pg_partman

Haroon's PlanetPostgreSQL, Internet of Things
This blog continues the discussion from my previous post on IoT Solution's scalability for IoT workloads where I discussed how declarative partitioning in PostgreSQL 10 can help achieve scalability. While native declarative partitioning is a good start, the experience of creating and maintaining the same partitions I did in my last post becomes much more fun with pg_partman. pg_partman is an extension to create and manage both time-based and serial-based table partition sets. Native partitioning in PostgreSQL 10 is supported as of pg_partman v3.0.1. It is important to note that all the features of trigger-based partitioning are not yet supported in native, but performance in both reads and writes is significantly better. Since Postgres-BDR runs as an extension on PostgreSQL, we can (more…)

Scaling IoT Time Series Data with Postgres-BDR

2ndQuadrant, Haroon's PlanetPostgreSQL, Internet of Things
A couple of weeks back, I wrote about how to use Windows Functions for time series IoT analytics in Postgres-BDR. This post follows up on IoT Solution's time series data and covers the next challenge: Scalability. ‘Internet of Things’ is the new buzzword as we move to a smarter world equipped with more advanced technologies. From transport to building industry, smart homes to personal gadgets, it’s not just about gadgets and sensors anymore. In reality, it is all about data. Not just simple data, but data that grows at an enormous rate. Businesses and application developers in Internet of Things domain face some similar questions today in terms of finding the best combination of technologies to support them. Without a doubt, database remains at the core of any such decision (more…)

Postgres-BDR 3.0 with OmniDB

OmniDB, William's PlanetPostgreSQL
Introduction OmniDB 2.8 introduced support for Postgres-BDR 3.0, the ground-breaking multi-master replication tool for PostgreSQL databases, announced last month in PostgresConf US. Here we have 2 virtual machines with Postgres-BDR 3.0 installed and we will use OmniDB to connect to them and setup replication between the machines. Pre-requisites Postgres-BDR 3.0 requires PostgreSQL 10 or better and also pglogical 3.0 extension should be installed, as Postgres-BDR 3.0 works on top of pglogical 3.0. Make sure you put the required entries in pg_hba.conf to make both machines communicate to each other via streaming replication. Then, in postgresql.conf you should set the following parameters in both machines: listen_addresses = '*' client_encoding = utf8 wal_level = 'logical' (more…)

Using Window Functions for Time Series IoT Analytics in Postgres-BDR

Haroon's PlanetPostgreSQL, Internet of Things, Time Series Data
Internet of Things tends to generate large volumes of data at a great velocity. Often times this data is collected from geographically distributed sources and aggregated at a central location for data scientists to perform their magic i.e. find patterns, trends and make predictions. Let’s explore what the IoT Solution using Postgres-BDR has to offer for Data Analytics. Postgres-BDR is offered as an extension on top of PostgreSQL 10 and above. It is not a fork. Therefore, we get the power of complete set of analytic functions that PostgreSQL has to offer. In particular, I am going to play around with PostgreSQL's Window Functions here to analyze a sample of time series data from temperature sensors. Let's take an example of IoT temperature sensor time series data spread over a (more…)

Introduction to Postgres-BDR [Webinar Follow-up]

2ndQuadrant, Liaqat's PlanetPostgreSQL, Webinars
The announcement of Postgres-BDR 3.0 last month at PostgresConf US had been long-awaited in the PostgreSQL community. Complex use cases from customers have driven the development of BDR far beyond the original feature set, resulting in a more robust technology than ever imagined - so we're happy to say that it was worth the wait. Postgres-BDR (Bi-Directional Replication) enhances PostgreSQL with advanced multi-master replication technology that can be used to implement very high availability applications. For an introduction to Postgres-BDR covering an overview of its complex architecture and its common use cases - 2ndQuadrant held the "Introduction to Postgres-BDR" webinar as part of its PostgreSQL webinar series. The webinar was presented by Simon Riggs, Founder and CEO of (more…)

PG Phriday: BDR Around the Globe

Shaun's PlanetPostgreSQL
With the addition of logical replication in Postgres 10, it's natural to ask "what's next"? Though not directly supported yet, would it be possible to subscribe two Postgres 10 nodes to each other? What kind of future would that be, and what kind of scenarios would be ideal for such an arrangement? As it turns out, we already have a kind of answer thanks to the latency inherent to the speed of light: locality. If we can provide a local database node for every physical app location, we also reduce latency by multiple orders of magnitude. Let's explore the niche BDR was designed to fill. What is Postgres-BDR? Postgres-BDR is simply short for Postgres Bi-Directional Replication. Believe it or not, that's all it needs to be. The implications of the name itself are numerous once fully (more…)
2ndQuadrant at PostgresConf US 2018

2ndQuadrant at PostgresConf US 2018

2ndQuadrant, PostgreSQL
The world of PostgreSQL continues to grow stronger by the year! Last week we saw it all come together at PostgresConf US 2018 in Jersey City. 2ndQuadrant was proud to participate again this year as a platinum sponsor. The conference was held to promote awareness and usage of PostgreSQL through tutorials and case-studies, as well as providing the opportunity to listen first hand to some of the best minds in the community. The 5-day event allocated two days to hands-on trainings on various PostgreSQL development and management topics. The remaining days were filled with over 80 breakout sessions covering everything related to PostgreSQL you could imagine. At 2ndQuadrant, we take pride in supporting the continued development of the world’s most advanced open source database. (more…)

Using Multimaster and BDR appropriately – LinuxConfAu

Craig's PlanetPostgreSQL
Unless you were in Sydney for linux.conf.au 2018 last week you probably didn't see my talk Geographically distributed multi-master replication with PostgreSQL and BDR. Luckily for you it's on archive.org and YouTube. If you're interested in multi-master replication of any sort, even non-PostgreSQL-based multi-master, it should be worth taking a look. The talk could've been better titled "Understand Multi-Master and if it's right for you" or "Physics is Mean". Comments and questions are welcome - @craig2ndq. You can also just read the slides (pdf), but they're really intended to support an explanation, not to stand alone. Huge thanks to Next Day Video for the amazing work they did on the recordings, to the conference conveners, the organizing team and volunteers. I (more…)