PostgreSQL: Sequences vs. Invoice numbers
What's the problem with using PostgreSQL database side sequences to create unique invoice numbers to send to clients? Let's find out.
What is an inner join in SQL? And what is an outer join?
Joins: Learn how to use an inner join and an outer join in PostgreSQL today by reading our tutorial. Also covers semi and full joins.
Git’s database internals V: scalability
This fifth and final part of our blog series exploring Git’s internals shows several strategies for scaling your Git repositories that match related database sharding techniques.
Git’s database internals IV: distributed synchronization
We’re examining Git’s internals to help make your engineering system more efficient. This post views Git as a distributed database and looks into its synchronization techniques, specifically ‘git fetch’ and ‘git push’.
Git’s database internals III: file history queries
Git’s file history queries use specialized algorithms that are tailored to common developer behavior. Level up your history spelunking skills by learning how different history modes behave and which ones to use when you need them.
Git’s database internals II: commit history queries
This post explores Git commit history as a database where ‘git log’ is the query language. Learn about Git’s custom query index – the commit-graph file – and how to make sure it’s enabled in your repositories.
Git’s database internals I: packed object store
This blog series will examine Git’s internals to help make your engineering system more efficient. Part I discusses how Git stores its data in packfiles using custom compression techniques.
Performance isolation in a multi-tenant database environment
Enforcing stricter performance isolation across neighboring tenants who rely on our storage infrastructure
Aliases for sub-SELECTS in FROM clause
Aliases are mandatory for sub-SELECTS by SQL standard. Some vendors (Oracle) don't force that rule. Will PostgreSQL follow it in the future?
Databases For Front-End Developers: The Rise Of Serverless Databases (Part 1) — Smashing Magazine
In this article, the first part of “Databases for Front-end Developers” series, Atila Fassina helps you understand the concepts behind database architecture in order to use them reliably and sheds some light on serverless databases.
Better SQL functions in PostgreSQL v14
New syntax for SQL functions was introduced in PostgreSQL v14, this article gives you vital tips for this important change & its advantages.
Handling Bonus Programs in SQL
Learn how to address the issue of bonus programs in SQL, improve your understanding of PostgreSQL, and calculate bonus points efficiently
Transaction anomalies with SELECT FOR UPDATE
This article shows how surprising transaction anomalies can happen with SELECT FOR UPDATE and what you can to to avoid them.
Using Cloudflare Tunnel and Access with Postgres
How the Database team uses Cloudflare Tunnels internally to improve security and usability of adhoc DB queries
Case-insensitive pattern matching in PostgreSQL
We discuss the options for case-insensitive comparison and pattern matching in PostgreSQL, comparing the performance of different approaches.
Sponsored Post: G-Core Labs, Close, Wynter, Pinecone, Kinsta, Bridgecrew, IP2Location, StackHawk, InterviewCamp.io, Educative, Stream, Fauna, Triplebyte - High Scalability -
Who's Hiring? * Close is building the sales communication platform of the future and we're looking for a Site Reliability Engineer to help us accomplish that goal. If you have a passion for databases (both SQL and NoSQL), significant experience building, managing, and monitoring infrastructure, databases, and backend services at scale and want to work with a rag-tag team of hardworking, but humble
The Case For Prisma In The Jamstack — Smashing Magazine
Jamstack has surged in popularity over the past few years as an approach to building websites. As developers are building Jamstack sites, finding a well-integrated method to interact with a database can be a major stumbling block. In this article, Sam Poder explores how Prisma integrates with the Jamstack and why it’s a great solution for Serverless databases in JavaScript or TypeScript-based proj
View permissions and row-level security in PostgreSQL
How view permissions are checked, and how that changes if you use the security_invoker view option from PostgreSQL v15. Use row-level security effectively.
How to cancel a hanging PostgreSQL query
This article describes how to cancel queries in PostgreSQL and shows a trick that you can use if canceling a query doesn't work.
sqlite-utils: a nice way to import data into SQLite for analysis
sqlite-utils: a nice way to import data into SQLite for analysis
Announcing D1: our first SQL database
Today, we’re excited to announce D1, Cloudflare’s first SQL database, designed for Cloudflare Workers
Time zone management in PostgreSQL
Time zone management and timestamp data types in PostgreSQL - learn how to use 'timestamp with time zone' correctly and optimize performance.
Sponsored Post: Close, Wynter, Pinecone, Kinsta, Bridgecrew, IP2Location, StackHawk, InterviewCamp.io, Educative, Stream, Fauna, Triplebyte - High Scalability -
Who's Hiring? * Close is building the sales communication platform of the future and we're looking for a Site Reliability Engineer to help us accomplish that goal. If you have a passion for databases (both SQL and NoSQL), significant experience building, managing, and monitoring infrastructure, databases, and backend services at scale and want to work with a rag-tag team of hardworking, but humble
PostgreSQL 15: Using MERGE in SQL
Merge - UPDATED 2023 - a technical preview of how to merge lists with ease. Find out about a wonderful command - add power to your PostgreSQL.
How to DROP ROLE or DROP USER in PostgreSQL
Learn how to use DROP ROLE and DROP USER in PostgreSQL. This article explains how to remove users and gives troubleshooting tips.
An update on recent service disruptions
Over the past few weeks, we have experienced multiple incidents due to the health of our database. We wanted to share what we know about these incidents while our team continues to address them.
Applying Product Thinking to Slack’s Internal Compute Platform
According to a recent Thoughtworks radar, “the industry is increasingly gaining experience with platform engineering product teams that create and support internal platforms.” They caveated this with a piece of advice: “When creating a platform, it’s critical to have clearly defined customers and products that will benefit from it rather than building in a vacuum.”…
Google Cloud PostgreSQL under pgwatch2 monitoring
Find out how to monitor PostgreSQL databases on Google Cloud with the pgwatch2 monitoring solution to get extended metrics for added clarity!
Practical Examples of Data Normalization in PostgreSQL
Data normalization - Learn how to apply the theory from part 1 of Michał Małecki's blog to real life with practical examples.
Upgrading MySQL at Shopify - Shopify
Learn how the Database Platform team performed the most recent MySQL upgrade at Shopify and how this changed our upgrade guidelines moving forward.
Introducing Relational Database Connectors
Customers can connect to a Postgres or MySQL database directly from their Workers using a Cloudflare Tunnel today. In the future, you can use Database Connectors to achieve this natively using a standardized Socket API.
Workers adds support for two modern data platforms: MongoDB Atlas and Prisma
With built-in global deployments, insane scalability and the flexibility of JavaScript, more and more applications are choosing to build on our global platform.
Debugging Systems in the Cloud: MySQL, Kubernetes, and Cgroups - Shopify
An overview of how we investigated and solved the issue of some Kubernetes Pods running MySQL starting up and shutting down slower than other similar Pods with the same data set.
WITH HOLD cursors and transactions in PostgreSQL
This article describes how cursors and transactions interact, how WITH HOLD can overcome the limitations, plus some caveats and tricks.
OpenStreetMap service by CYBERTEC
Importing OpenStreetMap data into PostGIS is time-consuming. Download OpenStreetMap offers two dataset types periodically served as sql dumps.
Gaps in sequences in PostgreSQL
How you can end up with gaps in sequences & how they can even skip backwards. How to build a gapless sequence in PostgreSQL.
JSON in PostgreSQL: how to use it right
This article describes what can go wrong when using the much-loved JSON capabilities of PostgreSQL and gives guidelines how to do it right.
Shard Balancing: Moving Shops Confidently with Zero-Downtime at Terabyte-scale - Shopify
This post explains how we’re able to balance our MySQL shards by migrating shops across shards—entirely online and with virtually zero consumer-facing downtime.
Building an Oracle to PostgreSQL migrator: Lessons learned
Oracle to PostgreSQL migration - Lessons learned in building a migration tool. How to avoid errors, null bytes and broken content.
Making Shopify’s Flagship App 20% Faster in 6 Weeks Using a Novel Caching Solution - Shopify
We built a custom write-through cache for the Shop app's home feed that reduced database load by 15% and overall app latency by about 20%.
Index bloat reduced in PostgreSQL v14
This article describes how PostgreSQL v14 reduces B-tree index bloat with "bottom-up index tuple deletion". A test case shows the difference.
Interactive Learning Tools For Front-End Developers — Smashing Magazine
There are plenty of interactive ways to learn new web development skills. We learn better by playing games. So we’ve collected interactive coding tools and games to help you learn CSS, JavaScript, SQL, React, Vim, regular expressions, Jamstack and pretty much everything in-between. Louis shares a comprehensive, categorized list of such tools covering a variety of different development technologies
Modernizing a familiar approach to REST APIs, with PostgreSQL and Cloudflare Workers
By using PostgREST with Postgres, we can build REST API-based applications. In particular, it's an excellent fit for Cloudflare Workers, our serverless function platform. Workers is a great place to build REST APIs.
How I Built a Cross-Platform Desktop Application with Svelte, Redis, and Rust
At Cloudflare, we have a great product called Workers KV which is a key-value storage layer that replicates globally. It can handle millions of keys, each of
PostgreSQL zheap: Current status
zheap has been designed as a new storage engine to handle UPDATE in PostgreSQL more efficiently. Read about the current status and more.
A Bashful Button Worth $8 Million
Most of us grumble when running across a frustrating UX experience online (like not being able to complete a transaction because of a misplaced button). We
PostgreSQL: LIMIT vs FETCH FIRST ROWS ... WITH TIES
The LIMIT/OFFSET clause is off standard and thus not portable. Read about the proper way to handle it by using “SELECT … FETCH FIRST ROWS”.
Simulating temporal tables with dblink and replication delay
How to simulate unsupported out of the box temporal tables in PostgreSQL using delayed streaming replication and dblink extension
Constraints over multiple rows in PostgreSQL
Constraints over multiple rows: How to cope with complex PostgreSQL constraints. Advanced techniques - enforce integrity; manage concurrency.
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…
