Crunchy Data joins Snowflake. Read the announcement
Elizabeth Christensen
Elizabeth Christensen
Vanilla Postgres has native partitioning? Yes! And it's really good! We frequently get questions like: Can Postgres handle JSON? Can Postgres handle time series data? How scalable is Postgres? Turns out the answer is most usually yes! Postgres, vanilla Postgres, can handle whatever your need is without having to go to a locked in proprietary database. Unless you're really close to the Postgres internals and code releases you might have missed that Postgres natively has partitioning. Our head of...
Read MorePaul Ramsey
Paul Ramsey
The PostgreSQL BRIN index is a specialized index for (as the documentation says) "handling very large tables in which certain columns have some natural correlation with their physical location within the table". For data of that sort, BRIN indexes offer extremely low insert costs (good for high velocity data) and extremely small index sizes (good for high volume data). But what data has this "natural correlation"? Most frequently, data with a timestamp that is continuously adding new rows. • A l...
Read MorePaul Ramsey
Paul Ramsey
It should be the easiest thing in the world: you are modeling your data and you need a column for some numbers, what type do you use? PostgreSQL offers a lot of different number types, and they all have advantages and limitations. You want the number type that is going to: • Store your data using the smallest amount of space • Represent your data with the smallest amount of error • Manipulate your data using the correct logic Store your data using the smallest amount of space Represent your da...
Read MoreElizabeth Christensen
Elizabeth Christensen
Logical replication has been around since Postgres 10 and it's just one of the most useful things out there. Need to get your data to a business analytics engine? Want to create an ETL pipeline from Postgres to your data warehouse? Want to test a new version of Postgres locally with your data? Want to consolidate a few databases into one? Logical replication can do all those things! It is a replication tool, which means it will copy some or all of your data to a new location and continue updatin...
Read MoreDavid Christensen
David Christensen
When using monitoring tools like PgMonitor or pganalyze , Crunchy clients will often ask me about high numbers of locks and when to worry. Like most engineering-related questions, the answer is: "it depends". In this post, I will provide a little more information about locks, how they are used in PostgreSQL, and what things to look for to spot problems vs high usage. PostgreSQL uses locks in all parts of its operation to serialize or share access to key data. This can come in the form of two...
Read MoreBrian Pace
Brian Pace
Getting frequent copies of data for development and testing purposes is a very common use case with our enterprise customer base. We have customers getting data copes for each sprint or development cycle. This increasing data copy problem can put a strain on IT budgets with the storage consumed and the hours spent performing database refreshes. A common process is to build or refresh environments using database restores. There can be some challenges with this approach. First, the data must be mo...
Read MorePaul Ramsey
Paul Ramsey
The sheer cleverness of relational databases is often discounted because we so frequently use them for very simple data management tasks. Serialize an object into a row, store with unique key. yawwwn Search for unique key, deserialize row into an object. yawwwwwwn The real power of relational databases is juggling "relations" (aka tables) in large numbers and figuring out on-the-fly the most effective way to filter out rows and find an answer. PostgreSQL has an undeniably clever query plannin...
Read MoreBrian Pace
Brian Pace
If you're checking archives or working with Postgres replication, data reconciliation can be a necessary task. Row counts can be one of the go to comparison methods but that does not show data mismatches. You could pull table data across the network and then compare each row and each field, but that can be a demand on resources. Today we'll walk through a simple solution for your Postgres toolbox - using Foreign Data Wrappers to connect and compare the two source datasets. With the foreign data...
Read MoreChristopher Winslett
Christopher Winslett
TL;DR : keep your data-layer simple, you'll need the agility it offers when budgets get stretched. Over the past 12 years of helping people run databases for their businesses, I have discovered that there are only 2 financial phases in the lifecycle of a database: • Phase 1 : Absolute costs are low, thus marginal costs do not matter. • Phase 2 : Absolute costs are high, thus marginal costs are all that matter. Phase 1 : Absolute costs are low, thus marginal costs do not matter. Phase 2 : Absolut...
Read MoreAndrew L'Ecuyer
Andrew L'Ecuyer
Modern day production ready Postgres solutions require quite a bit of sophistication and automation. Changes need to be applied in a uniform and safe way. DevOps and SRE teams need to be in control system updates while limiting disruption to their users. With the release of PGO v5.1 , we are excited to announce enhancements in each of these areas. Not only does PGO v5.1 now automatically rollout all PostgreSQL configuration changes, but it allows you to protect your running databases against ou...
Read More