Marco Slot
Marco Slot
I've always found distributed systems to be the most fascinating branch of computer science. I think the reason is that distributed systems are subject to the rules of the physical world just like we are. Things are never perfect, you cannot get everything you want, you’re always limited by physics, and often by economics, or by who you can communicate with. Many problems in distributed systems simply do not have a clean solution, instead there are different trade-offs you can make. While at Cit...
Read MoreElizabeth Christensen
Elizabeth Christensen
If you’ve ever dug under the hood of Postgres a bit, you’ve probably heard about the page. This is the on-disk storage mechanism and it's limited to an 8kb size. But what happens when you have data bigger than that 8kb? TOAST is made. Postgres TOASTs data by splitting it up into smaller chunks. TOAST stands for The Oversized Attribute Storage Technique. TOAST happens automatically, you don’t set up anything, it just comes with Postgres out of the box. So why should you care? Well TOAST can impac...
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres is a robust data platform . Yes, it's more than a boring old relational database. It has rich indexing, data types (including JSON ), and so much more. It also has support for a variety of extensions that can further broaden it's already great functionality. Two of those extensions when coupled together make Postgres a very compelling approach for IoT architectures. Today we're going to start from the ground up on how you would design your architecture with Postgres along with the Ci...
Read MoreCraig Kerstiens
Craig Kerstiens
If you're building a B2B app chances are it's multi-tenant, meaning one customer data is separated and doesn't intermingle with other customer data. When building the app itself you'll typically have some URL route defining the specific tenant. When it comes to your database there are a number of design patterns that you can use that offer different advantages, trade-offs, and scaling opportunities. When you begin building your app, time to market is essential, but you don't want to make decisio...
Read MoreDavid Christensen
David Christensen
Recently we published an article about some of the best sql subquery tools and we were talking about all the cool things you can do with CTEs. One thing that doesn’t get mentioned near enough is the use of CTEs to do work in your database moving things around. Did you know you can use CTEs for tuple shuffling? Using CTEs to update, delete, and insert data can be extremely efficient and safe for your Postgres database. PostgreSQL 15 included the MERGE statement, which can be similar. There ar...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
Version 1.21 of PgBouncer , the Postgres connection pooler, has added a long awaited feature: support for prepared statements inside of transaction mode. Prior to this, one had to choose between using prepared statements (a performance win), and using PgBouncer's transaction mode (also a large performance win). Now, we can have our cake and eat it too 🎂 🎉. In Postgres, every SQL command you send to the server is parsed , prepared , and then executed . If you are running the exact same quer...
Read MoreJohn Dalton
John Dalton
Disk IOPS (Input/Output Operations Per Second) is a key metric for measuring the performance of any disk system. It represents the number of read and write operations that can be performed per second. For PostgreSQL, which relies heavily on disk access, understanding and optimizing disk IOPS is crucial for achieving optimal performance. Today I want to go through the key topics involved with IOPs - what is it, how does it impact Postgres, how to measure it, and what to change for better performa...
Read MoreBrian Pace
Brian Pace
Support for logical replication arrived at Postgres just over five years ago with Postgres 10. Since then it's had a steady stream of improvements, but mostly logical replication has been limited to migrating data or unidirectional change data capture workflows. With Postgres 16 freshly released today, Postgres now has a better foundation to leverage logical replication for active-active setups. If you're unfamiliar with the concepts of logical replication or what does active-active mean we've g...
Read MorePaul Ramsey
Paul Ramsey
In my last blog post , I showed four ways to access a remotely hosted CSV file from inside PostgreSQL: • Using the command with the option, • Using the http extension and some post-processing, • Using a PL/Python function, and • Using the ogr_fdw foreign data wrapper. Using the command with the option, Using the http extension and some post-processing, Using a PL/Python function, and Using the ogr_fdw foreign data wrapper. In this post, we are going to explore ogr_fdw a little...
Read MoreChristopher Winslett
Christopher Winslett
We have a lot of Postgres foreign data wrapper ( FDW ) users here at Crunchy. The postgres_fdw lets you connect to remote servers and in some cases can be an alternative for traditional ETL/ELT processes. As I see use of the Postgres foreign data wrapper expanding, I wanted to make some recommendations on how to approach performance. When you query from a single Postgres cluster, the database cleverly uses internal statistics to auto-tune performance . However, when querying from the Postgr...
Read More