Posted on

How-To: The PGExercises PostgreSQL Tutorial Running on a Distributed SQL Database

PgExercises is a sample dataset used to power the PostgreSQL Exercises website. The site is comprised of over 80 exercises designed to be used as a companion to the official PostgreSQL documentation. The exercises on the PGExercises site range from simple SELECT statements and WHERE clauses, through JOINs and CASE statements, then on to aggregations, window functions, and recursive queries.

The dataset consists of 3 tables (members, bookings, and facilities) and table relationships as shown in the ER diagram below:

Posted on

PCC Social Media ”Like” Scenario: Database Design and Performance Stress Testing

Background

The Performance Challenge Championship (PCC) is an event organized by ArchNotes. After learning about the rules of the competition, I found PostgreSQL is very suitable for this scenario. The scenario is reproduced as it is, implemented with PG, but how does it perform?

The competition is described as follows (page in Chinese, but Chrome can translate): https://github.com/archnotes/PCC

Posted on

The Northwind PostgreSQL Sample Database Running on a Distributed SQL Database

The Northwind database is a sample database that was originally created by Microsoft and used as the basis for their tutorials in a variety of database products for decades. The Northwind database contains the sales data for a fictitious company called “Northwind Traders,” which imports and exports specialty foods from around the world. The Northwind database is an excellent tutorial schema for a small-business ERP, with customers, orders, inventory, purchasing, suppliers, shipping, employees, and single-entry accounting. The Northwind database has since been ported to a variety of non-Microsoft databases including PostgreSQL.

In this post, we are going to walk you through how to download and install the PostgreSQL-compatible version of Northwind on the YugaByte DB distributed SQL database.

Posted on

PostgreSQL Independent Event Correlation Analysis: User-Car Fitting

Background

Independent event correlation analysis is very interesting. For example, the "Passing By" function points of some apps can indicate that you've been in a similar location with a different person at a different time. Independent event correlation analysis can also be used for public opinion analysis, sales portfolio for commodities, and user-car fitting.

User-car fitting in the safety system processes spatio-temporal data for fitting drivers, passengers, and vehicles.

Posted on

How Data Sharding Works in a Distributed SQL Database

Enterprises of all sizes are embracing the rapid modernization of user-facing applications as part of their broader digital transformation strategy. The relational database (RDBMS) infrastructure that such applications rely on suddenly needs to support much larger data sizes and transaction volumes. However, a monolithic RDBMS tends to quickly get overloaded in such scenarios. One of the most common architectures to get more performance and scalability in an RDBMS is to “shard” the data. In this blog, we will learn what sharding is and how it can be used to scale a database. We will also review the pros and cons of common sharding architectures, plus explore how sharding is implemented in distributed SQL-based RDBMS like YugaByte DB.

What Is Data Sharding?

Sharding is the process of breaking up large tables into smaller chunks called shards that are spread across multiple servers. A shard is essentially a horizontal data partition that contains a subset of the total data set, and hence is responsible for serving a portion of the overall workload. The idea is to distribute data that can’t fit on a single node onto a cluster of database nodes. Sharding is also referred to as horizontal partitioning. The distinction between horizontal and vertical comes from the traditional tabular view of a database. A database can be split vertically — storing different table columns in a separate database, or horizontally — storing rows of the same table in multiple database nodes.

Posted on

Table Inheritance: What’s It Good For?

Table inheritance is one of the most misunderstood -- and powerful -- features of PostgreSQL. With it, certain kinds of hard problems become easy. While many folks who have been bitten by table inheritance tend to avoid the feature, this blog post is intended to provide a framework for reasoning about when table inheritance is actually the right tool for the job.

Table inheritance is, to be sure, a power tool and thus something to use only when it brings an overall reduction in complexity to the design. Moreover, the current documentation doesn't provide a lot of guidance regarding what the tool actually helps with and where are the performance costs and because inheritance sits orthogonal to relational design, working this out individually is very difficult.

Posted on

2019 Open Source Database Report

Ready to transition from a commercial database to open source, and want to know which databases are most popular in 2019? Wondering whether an on-premise vs. public cloud vs. hybrid cloud infrastructure is best for your database strategy? Or, considering adding a new database to your application and want to see which combinations are most popular? We found all the answers you need at the Percona Live event last month, and broke down the insights into the following free trends reports:

2019 Top Databases Used

So, which databases are most popular in 2019? We broke down the data by open source databases vs. commercial databases:

Posted on

Build a Simple Spring Boot App With Spring Data JPA and Postgres

Just about every application needs a way to save and update data, typically a resource server that is accessible via HTTP. Generally, this data must be secured. Within the Java ecosystem, Spring makes building secure resource servers for your data simple. When coupled with Okta for secure user management, you get professionally maintained OAuth 2.0 and JWT technologies easily integrated into Spring Boot via Spring Security.

In this tutorial, you’re going to build a resource server using Spring Boot and Spring Data JPA. On top of that, you’re going to implement a group-based authentication and authorization layer using OAuth 2.0.

Posted on

Switch Your PostgreSQL Primary for a Read Replica Without Downtime

In my ongoing research to identify solutions and similarities between MySQL - PostgreSQL, I recently faced a simple issue. I needed to perform a slave shift from one IP to another, and I did not want to have to restart the slave that is serving the reads. In MySQL, I can repoint the replication online with the command Change Master TO, so I was looking for a similar solution in Postgres. In my case, I could also afford some stale reads, so a few seconds delay would have been okay, but I couldn't take down the server.

After brief research, I noticed that there is not a solution that allows you to do that without restarting the PostgreSQL server instance.

Posted on

How to Set Up Django With Postgres, Nginx, and Gunicorn on Ubuntu 16.04

Introduction

Django is a free, open source, and high-level Python web framework that encourages rapid development and clean, pragmatic design. Django's MVC (Model-View-Controller) architecture is maintained by the Django Software Foundation. Django is a strong web framework that can assist you to get your application online as quickly as possible. The primary goal of Django is to ease the creation of complex, database-driven websites. Django supports four major database backends including, PostgreSQL, MySQL, SQLite, and Oracle.

You can run Django in conjunction with Apache, Nginx using WSGI, Gunicorn, or Cherokee using a Python module.

Posted on

Contributing to Postgres

About once a month, I get this question: “How do I contribute to Postgres?.” PostgreSQL is a great database with a solid code base, and for many of us, contributing back to open source is a worthwhile cause. The thing about contributing back to Postgres is you generally don’t just jump right in and commit code on day one. So figuring out where to start can be a bit overwhelming. If you’re considering getting more involved with Postgres, here are a few tips that you may find helpful.

Follow What’s Happening

The number one way to familiarize yourself with the Postgres development and the code community is to subscribe to the mailing lists. Even if you’re not considering contributing back, the mailing lists can be a great place to level up your knowledge and skills around Postgres. Fair warning: the mailing lists can be very active. But that’s ok, as you don’t necessarily need to read every email as it happens — daily digests work just fine. There is a long list of mailing lists you can subscribe to, but here are a few I think you should know about: