Setting up PostgreSQL streaming replication
This tutorial will show you how to setup up and configure streaming replication for PostgreSQL 13 & how to set up database servers quickly.
TLS: Demystifying communication encryption in PostgreSQL
TLS is a technology widely used in combination with PostgreSQL to encrypt client / server connections. This blogpost explains how TLS works.
PostgreSQL: Understanding deadlocks
"ERROR: deadlock detected" - message - what should I do? This article explains what this really means and how deadlocks can be prevented.
Why Content Is Such A Fundamental Part Of The Web Design Process — Smashing Magazine
As designers, we are often burdened by the responsibility of producing and managing website content. It’s not our job to write it, but it’s not the client’s either. In many cases a vacuum emerges which ultimately gets filled with poor content. In this article, Matt Saunders will show you that you can avert disaster by including content production in the design process.
Dynamic ProxySQL Query Rules - Shopify
Resiliency is important at Shopify and although we have many tools to help us during incidents, some problems need to be fixed at the database level. At Shopify, we use ProxySQL to manage connections between applications and our MySQL databases
Announcing Cloudflare’s Database Partners
Cloudflare introduces Macrometa and Fauna as Edge database partners and adds database connectors for DynamoDB and Aurora to Cloudflare Workers, expanding what developers can build at the edge.
pg_read_all_data - a system level "read all data" role for PostgreSQL!
A new feature has arrived: "pg_read_all_data" - a predefined role that can run an unfettered pg_dump without that role being a superuser.
Cleaning up a large number of BLOBs in PostgreSQL
Basically, there are two ways to delete BLOBs (binary large objects):Change the PostgreSQL configuration or delete the BLOBs in chunks.
Getting started with QGIS, PostgreSQL and PostGIS
Visualizing spatial data is an important task. This tutorial helps you with getting started with visualizing OSM data with PostGIS and QGIS.
PostgreSQL: Get member roles and permissions
PostgreSQL allows you to define users (=roles), groups, etc. This post explains how to retrieve information on roles & role membership.
How we scaled the GitHub API with a sharded, replicated rate limiter in Redis
About a year ago, we migrated an old rate limiter in order to serve more traffic and accommodate a more resilient platform architecture. We adopted a replicated Redis backend with…
When to use tablespaces in PostgreSQL
This article explains what PostgreSQL tablespaces are, when to use them and how they are different from Oracle tablespaces.
Choosing A New Serverless Database Technology At An Agency (Case Study) — Smashing Magazine
Choosing to use a new technology can often bring much desired productivity, security, and efficiency to a project. It is also fraught with risk and uncertainty. How and when to adopt a new technology for client projects is at the heart of leading a great agency. In this article, Michael Rispoli explains how he evaluated the decision of whether or not to adopt a serverless database for client proje
Setting up SSL authentication for PostgreSQL
How to set up SSL authentication for PostgreSQL. Handle secure client-server connections in the best possible way.
Running Postgres in Docker - why and how?
This article gives an introduction from Postgres to Docker and whether you should run production Postgres workloads in a docker container.
PostgreSQL GitHub Actions - Continuous Integration
GitHub Actions are a piece of excellent machinery for PostgreSQL related tasks on your repo with support for Windows, Ubuntu and macOS environments.
Estimating connection pool size with PostgreSQL database statistics
This article shows how you can you use PostgreSQL database statistics to get an upper limit for the correct size for a connection pool.
PostgreSQL: How to write a trigger
UPDATED 2023: A trigger is a way to automatically respond to events. This is a tutorial on how to manage and program triggers in PostgreSQL.
Read Consistency with Database Replicas - Shopify
At Shopify, we’ve long used database replication for redundancy and failure recovery, but only fairly recently started to explore the potential of replicas as an alternative read-only data source for applications.
Benchmark (YCSB) numbers for Redis, MongoDB, Couchbase2, Yugabyte and BangDB - High Scalability -
This is guest post by Sachin Sinha who is passionate about data, analytics and machine learning at scale. Author & founder of BangDB. This article is to simply report the YCSB bench test results in detail for five NoSQL databases namely Redis, MongoDB, Couchbase, Yugabyte and BangDB and compare the result side by side. I have used latest versions for each NoSQL DB and have followed the recommendat
"Catchment areas" with PostgreSQL and PostGIS
This article explains how to partition an area of interest spatially: Approximation of customer potential using PostGIS. Catchment areas.
GitHub reduces Marketplace transaction fees, revamps Technology Partner Program
At GitHub, our community is at the heart of everything we do. We want to make it easier to build the things you love, with the tools you prefer to…
Golden Proportions in PostgreSQL
Learn how PostgreSQL can help with basic calculations regarding golden proportions. Do mathematics in your database development work.
Tips and tricks to kick-start the Postgres year 2021
Start learning some new Postgres stuff again in 2021 and find some tips: Partial indexing, estimating average row size and more.
Day 32: A Rails model that doesn't use the database with ActiveHash
Day 32: A Rails model that doesn't use the database with ActiveHash
Accessing PostgreSQL databases in Go - Eli Bendersky's website
The shibboleth of PostgreSQL
A shibboleth is a phase whose pronounciation determines if you belong or not. This article tells you how not to pronounce "PostgreSQL".
Building A Stocks Price Notifier App Using React, Apollo GraphQL And Hasura — Smashing Magazine
In this article, Ankita Masand will show you how to build an event-based application and send a web-push notification when a particular event is triggered. She’ll set up database tables, events, and scheduled triggers on the Hasura GraphQL engine and wire up the GraphQL endpoint to the front-end application to record the stock price preference of the user.
Upgrading and updating PostgreSQL
Update/ upgrade: what's the difference? This blog post covers the best ways of upgrading or updating PostgreSQL to the latest release.
Scaling Datastores at Slack with Vitess
From the very beginning of Slack, MySQL was used as the storage engine for all our data. Slack operated MySQL servers in an active-active configuration. This is the story of how we changed our data storage architecture from the active-active clusters over to Vitess — a horizontal scaling system for MySQL. Vitess is the present…
How to Build a Production Grade Workflow with SQL Modelling - Shopify
I’ll show you how we moved to a SQL modelling workflow by leveraging dbt (data build tool) and created tooling for testing and documentation on top of it.
Dealing with streaming replication conflicts in PostgreSQL
Replication conflicts can cause problems with streaming replication. This article tells you what they are and how to deal with them.
How to Introduce Composite Primary Keys in Rails - Shopify
One line of code can 5x your Rails application performance. What if you could make a small change to your database design that would unlock massively more efficient data access? At Shopify, we dusted off some old database principles and did exactly that with the primary Rails application.
PostgreSQL: Foreign keys and insertion order in SQL
Foreign keys will introduce some issues which have to be considered when writing applications. If a foreign key is in place the inseration order starts to matter.
CG/SQL: Easy, accurate code generation for SQLite
What it is: CG/SQL is a code generation system for the popular SQLite library that allows developers to write stored procedures in a variant of Transact-SQL (T-SQL) and compile them into C code that uses SQLite’s C API to do the coded operations. CG/SQL enables engineers to create highly complex stored procedures with very large […]
Generating a normal distribution in SQL
SQL and PostgreSQL can be used to create sample data which possesses certain statistical properties like a normal distribution.
PostgreSQL vs PostgreSQL vs Postgres vs Oracle vs all the rest
How can you find data even if there are typos? In PostgreSQL there are various solutions to the problem of a search with odd spelling.
MySQL on Azure Performance Benchmark – ScaleGrid vs. Azure Database - High Scalability -
Microsoft Azure is one of the most popular cloud providers in the world, and a natural fit for database hosting on applications leveraging Microsoft across their infrastructure. MySQL is the number one open source database that’s commonly hosted through Azure instances. While Microsoft offers their own Azure Database product, there are other alternatives available that may be able to help you impr
PostgreSQL: GROUP BY expression
GROUP BY is an integral part of SQL and PostgreSQL. You might not know that the GROUP BY expression can handle more than just simple fields.
Setting Up An API Using Flask, Google’s Cloud SQL And App Engine — Smashing Magazine
Flask makes it possible for developers to build an API for whatever use case they might have. In this tutorial, Wole Oyekanmi will show you how to set up Google Cloud, Cloud SQL, and App Engine to build a Flask API. (Cloud SQL is a fully managed platform-as-a-service (PaaS) database engine, and App Engine is a fully managed PaaS for hosting applications.)
Understanding recursive queries in PostgreSQL
This article gives a simple introduction with examples to recursive queries in PostgreSQL. It also includes a comparison to Oracle recursive queries.
pgwatch2 v1.8.0 released
The most significant changes in pgwatch2 v1.8. PGWatch2 is a PostgreSQL monitoring tool - useful dashboard
PostgreSQL: Useful new data types - CREATE DOMAIN
UPDATED 08.05.2023 How to create useful new data types with CREATE DOMAIN in PostgreSQL | Advanced server side check constraints
Calculating differences between rows in SQL
Calculating differences between rows | How can one calculate the difference between the current and the previous row? | timeseries data
SQL trickery: Hypothetical aggregates
“If we had this data what would it mean?” - read more about this PostgreSQL technique, what it is good for & how it works.
Wrapping DB2 with PostgreSQL
Connection of DB2 with PostgreSQL done with the extension db2_fwd | Migration of db2's data to PostgreSQL | Read data from DB2
The Serverlist: Serverless Computing vs. Containers, Serverless MySQL at Scale, and more!
Check out our sixteenth edition of The Serverlist below. Get the latest scoop on the serverless space, get your hands dirty with new developer tutorials, engage in conversations with other serverless developers, and find upcoming meetups and conferences to attend.
Mirage JS Deep Dive: Understanding Factories, Fixtures And Serializers (Part 2) — Smashing Magazine
Factories are a faster way to create new database records., A fixture is a state of a set or collection of objects that serve as a baseline for running tests, and a serializer is responsible for transforming the response. In this second part of the Mirage JS Deep Dive series, Kelvin Omereshone will be looking at Mirage JS’ Factories, Fixtures, and Serializers. You’ll see how they enable rapid API
Partition management - do you really need a tool for that?
How to use partition management to speed up queries in PostgreSQL and make tables more manageable as data amounts grow.
7 Ways to Make Your SQL Workshop Beginner-friendly - Shopify
Here are some of the approaches we use to help data workshop participants walk away equipped and excited to start using data to power their work.
Three bugs in the Go MySQL Driver
Check out what we learned from shipping our busiest Go service in production—we found 3 bugs in the Go MySQL driver.
How to count hits on a website - concurrent counting in PostgreSQL
How to grant users access to a certain piece of data a limited number of times. What happens if two users access it concurrently?
Reducing Your Database Hosting Costs: DigitalOcean vs. AWS vs. Azure - High Scalability -
If you’re hosting your databases in the cloud, choosing the right cloud service provider is a significant decision to make for your long-term hosting costs. This is especially apparent in today's world where organizations are doing whatever they can to optimize and reduce their costs. Over the last few weeks, we have been inundated with requests from SMB customers looking to improve the ROI on the
How To Build A Vue Survey App Using Firebase Authentication And Database — Smashing Magazine
This tutorial would take you on a step by step guide to build a functional survey app using Vue.js and Firebase. From validating the user’s data through Vuelidate, to authentication, storing the user’s data, route protection and sending data to Firebase servers. All the steps used in the tutorial are practical, and can be reproduced in any real-life project, even with a custom backend.
PostgreSQL v13 new feature: tuning autovacuum on insert-only tables
PostgreSQL v13 introduces autovacuum for insert-only tables. This article tells you why and how to make use of this new feature.
Django Highlights: Models, Admin, And Harnessing The Relational Database (Part 3) — Smashing Magazine
The admin panel is one of the most powerful, flexible features that the Django web framework provides, combining instant off-the-shelf functionality with infinite customization. Using an example project based on a library inventory system, we’ll use the admin panel to learn about creating models and interacting with relational databases in Django.
How To Set Up An Express API Backend Project With PostgreSQL — Smashing Magazine
In this article, Chidi Orji will create a set of API endpoints using [Express](https://expressjs.com/) from scratch in ES6 syntax, and cover some development best practices. Find out how all the pieces work together as you create a small project using Continuous Integration and Test-Driven Development before deploying to Heroku.
Scaling MongoDB at Mailbox
Creating Secure Password Flows With NodeJS And MySQL — Smashing Magazine
Reset password functionality is table stakes for any user-friendly application. It can also be a security nightmare. Using NodeJS and MySQL. Today, Darshan Somashekar demonstrates how to successfully create a secure reset password flow so you can avoid these pitfalls. Darshan will be using NodeJS and MySQL as the base components. If you’re writing using a different language, framework, or database
Important Health Checks for your MySQL Master-Slave Servers - High Scalability -
In a MySQL master-slave high availability (HA) setup, it is important to continuously monitor the health of the master and slave servers so you can detect potential issues and take corrective actions. In this blog post, we explain some basic health checks you can do on your MySQL master and slave nodes to ensure your setup is healthy. The monitoring program or script must alert the high availabili