Author: Muhammad Haroon

Feed: Planet PostgreSQL. If you are building a Spring Boot application for your next project, you would also be preparing and planning for the PostgreSQL database to store the data generated by your customers. In some previous posts on the topic of RESTful services in Spring Boot, we discussed how we can use JPA to automatically create and automatically apply the schema to the PostgreSQL database. In this post, we will see why letting Hibernate control the schema changes is not best approach, and how to manage the schema, apply it to PostgreSQL database, synchronize your Java objects and PostgreSQL database ... Read More

Feed: Planet PostgreSQL. A typical database-oriented application setup only must work with single operation SQL or RESTful execution. Your customers might only be sending the INSERT or UPDATE queries one at a time. We have covered this approach in part 2 for our series and have created a simple RESTful API that allows users to perform CRUD operations on a PostgreSQL database. More advanced and high-velocity solutions require an efficient solution that can handle thousands of database operations per second, per client. Bulk or batch operations make sense when you are handling loads of data from a single origin—say, IoT or ... Read More

Feed: Planet PostgreSQL. This article is an extended version atop of the previous article which was a kickstart for building an application using Spring Boot and PostgreSQL. There is no internal feature supported by Java which offers mapping between class objects and database tables; therefore, we use Object Relational Model (ORM). JPA is an abstraction above JDBC and works as an ORM to map Java Objects with the entities in database using metadata. Querying database for all the user operations is another task of JPA for which it uses the concept of “repository”. Consequently, with JPA the development of your ... Read More

Feed: Planet PostgreSQL. Spring Boot is a framework that is used to build stand-alone Java applications following the Spring framework but with less hassle and minimal configuration. Learning by practical examples is always great; therefore, this article will walk you through the creation of a RESTful CRUD (create-read-update-delete) application using Spring Boot and its connectivity with PostgreSQL. Though Spring Boot presents a complete application development framework with backend as well as the front-end, the greater emphasis in this article will be on a RESTful API, database connectivity and its injection and integration with other components in the application.  Pre-requisites In ... Read More

Feed: Planet PostgreSQL. A regular expression is a special text string used to describe a search pattern. PostgreSQL’s regular expressions supports three separate approaches to pattern matching:POSIX-style regular expressions (BREs and EREs) SIMILAR TO operator added in SQL:1999 SQL LIKE operator There are some more advanced techniques for advanced pattern matching requirements but those will very likely involve writing some user defined functions using Perl or Tcl. POSIX regular expressions provide a more powerful means for pattern matching than the LIKE and SIMILAR TO operators. This post is first in the series of blogs I plan to write on the ... Read More

Feed: Planet PostgreSQL. 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 ... Read More

Feed: Planet PostgreSQL. In my previous post we looked at various partitioning techniques in PostgreSQL for efficient IoT data management. 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 ... Read More

Feed: Planet PostgreSQL. This blog continues the discussion from my previous post on 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 ... Read More

Feed: Planet PostgreSQL. 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 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 ... Read More

Feed: Planet PostgreSQL. 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 ... Read More