Roberto Mello
Roberto Mello
Postgres 16 is hot off the press with the beta release last week. I am really excited about the new feature that allows logical replication from standbys, allowing users to: • create logical decoding from a read-only standby • reduce the workload on the primary server • have new ways to achieve high-availability for applications that require data synchronization across multiple systems or for auditing purposes create logical decoding from a read-only standby reduce the workload on the primary se...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
A question I hear from time to time with Crunchy Data clients and the Postgres community is: When was my Postgres database table created? Postgres does not store the creation date of tables, or any other database object. But fear not, there are a plethora of direct and indirect ways to find out when your table creation happened. Let's go through some ways to do this, ranging from easy to somewhat hard. All these solutions apply to indexes and other database objects, but tables are by far the mos...
Read MoreCraig Kerstiens
Craig Kerstiens
Over the past few weeks we've had several customers ask how they should architect their analytics pipeline. Common questions are: • Should we use some kind of data warehouse or time series database? • Is Postgres suitable for that type of workload? • What are the pitfalls that I should worry about before I get started? Should we use some kind of data warehouse or time series database? Is Postgres suitable for that type of workload? What are the pitfalls that I should worry about before I get sta...
Read MoreCraig Kerstiens
Craig Kerstiens
Today, we wanted to address some basic principles for better managing data architecture. Postgres is well regarded as a database for traditional system of record. More recently we've been fielding questions on what else can it do, such as: Can it be good for analytics and metrics? The short answer is "yes". When applications expand outside their standard system of record, they add in new types of data and data stores, which introduces complexity managing multiple types of systems. Some common wo...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
In an earlier post, I went into a lot of detail about unlogged tables . But tables are not the only thing to get the unlogged treatment - as of version 15 of Postgres, sequences can be unlogged as well! If you want to create your own, it's simply a matter of adding the keyword to your statement: The use case for unlogged sequences in Postgres is primarily to keep the sequence data for an unlogged table out of the WAL stream. Although unlogged tables provide a significant performance boost,...
Read MoreCraig Kerstiens
Craig Kerstiens
Is your database ready for production? You've been building your application for months, you've tested with beta users, you've gotten feedback and iterated. You've gone through your launch checklist, email beta users, publish the blog post, post to hacker news and hope the comments are friendly. But is your database ready for whatever may come on launch day or even 2 months in? Here's a handy checklist to make sure you're not caught flat footed. • Backups❓ • High availability❓ • Logs properly co...
Read MoreJesse Soyland
Jesse Soyland
Integer overflow occurs when a computer program tries to store an integer but the value being stored exceeds the maximum value that can be represented by the data type being used to store it. We have helped a few Crunchy Data clients navigate this recently and wanted to write up some notes. In Postgres, there are three integer types: • - A 2-byte integer, -32768 to 32767 • - A 4-byte integer, -2147483648 to 2147483647 • - An 8-byte integer, -9223372036854775808 to +9223372036854775807 - A 2-by...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
While supporting customers at Crunchy Data , I sometimes see users add unlogged tables. In this post, I'm going to review some of the specifics about this. Unlogged tables in Postgresql are a way of preventing specific tables from generating WAL (Write Ahead Log) information by adding the keyword to a command: You can also change existing tables to unlogged, and switch them back again: While there are strong advantages to using unlogged tables, you must use them carefully as their disadvant...
Read MoreBrian Pace
Brian Pace
The Postgres Write Ahead Log ( WAL ) is a functional component to the database. WAL makes a lot of key functionality possible, like Point-in-Time-Recovery backups , recovering from an event , streaming replication , and more. From time to time, those deep inside the database will need to work directly with WAL files to diagnose or recover . Recently in working with one of Crunchy Data's customers, I came across a situation where understanding the names and sequence numbers was important....
Read MoreCraig Kerstiens
Craig Kerstiens
We spend a lot of time at Crunchy Data helping people dig into the performance of their Postgres. If you're setting up a new Postgres database or already running on in production there are a number of very basic steps you can take that will save your tail in the future when it comes to investigating performance. Here is your guide that'll take less than 5 minutes to get in place. Future you will thank you for doing this today. Pg_stat_statements records and parameterizes queries, how long the...
Read More