Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more

  • Data Encryption in Postgres: A Guidebook

    Greg Nokes

    When your company has decided it's time to invest in more open source, Postgres is the obvious choice. Managing databases is not new and you already have established practices and requirements for rolling out a new database. One of the big requirements we frequently help new customers with on their Postgres adoption is data encryption. While the question is simple, there's a few layers to it that determine which is the right approach for you. Here we'll walk through the pros and cons of approach...

    Read More
  • 1 min read

    Announcing the Scheduler for Crunchy Bridge

    Craig Kerstiens

    Today we're excited to announce a new scheduler for Crunchy Bridge. Scheduler makes it easy for you to create and manage automated database maintenance tasks such as: • Refreshing materialized views • Performing automated roll-ups from raw data • Running partitioning maintenance tasks (run pg_partman automatically) • Archiving or pruning old data Refreshing materialized views Performing automated roll-ups from raw data Running partitioning maintenance tasks (run pg_partman automaticall...

    Read More
  • How We Fused DuckDB into Postgres with Crunchy Bridge for Analytics

    Marco Slot

    Last month we launched Crunchy Bridge for Analytics , a new managed PostgreSQL offering that lets you query your data lake directly from PostgreSQL. Since then, we have had quite a few exciting conversations with customers handling large amounts of data in PostgreSQL. A common question is of course: How does it work? In this post, I wanted to shed some light on the internals. Crunchy Bridge for Analytics abstracts the query engine to offer fast analytics on data in Amazon S3 in PostgreSQL. In p...

    Read More
  • 3 min read

    Converting DMS to PostGIS Point Geometry

    Elizabeth Christensen

    I love taking random spatial data and turning it into maps. Any location data can be put into PostGIS in a matter of minutes. Often when I’m working with data that humans collected, like historic locations or things that have not yet traditionally been done with computational data, I’ll find traditional Degrees, Minutes, Seconds (DMS) data. To get this into PostGIS and QGIS, you’ll need to convert this data to a different system for decimal degrees. There’s probably proprietary tools that will d...

    Read More
  • 10 min read

    Time Partitioning and Custom Time Intervals in Postgres with pg_partman

    Keith Fiske

    Whether you are managing a large table or setting up automatic archiving , time based partitioning in Postgres is incredibly powerful. pg_partman ’s newest versions support a huge variety of custom time internals. Marco just published a post on using pg_partman with our new database product for doing analytics with Postgres , Crunchy Data Warehouse . So I thought this would be a great time to review the basic and complex options for the time based partitioning. When I first started designi...

    Read More
  • Syncing Postgres Partitions to Your Data Lake in Crunchy Bridge for Analytics

    Marco Slot

    One of the unique characteristics of the recently launched Crunchy Bridge for Analytics is that it is effectively a hybrid between a transactional and an analytical database system. That is a powerful tool when dealing with data-intensive applications which may for example require a combination of low latency, high throughput insertion, efficient lookup of recent data, and fast interactive analytics over historical data. A common source of large data volumes is append-mostly time series data o...

    Read More
  • Crunchy Bridge for Analytics: Your Data Lake in PostgreSQL

    Marco Slot

    A lot of the world’s data lives in data lakes, huge collections of data files in object stores like Amazon S3. There are many tools for querying data lakes, but none are as versatile and have as wide an ecosystem as PostgreSQL. So, what if you could use PostgreSQL to easily query your data lake with state-of-the-art analytics performance? Today we’re announcing Crunchy Bridge for Analytics , a new offering in Crunchy Bridge that lets you query and interact with your data lake using PostgreSQL c...

    Read More
  • 9 min read

    Auto-archiving and Data Retention Management in Postgres with pg_partman

    Keith Fiske

    You could be saving money every month on databases costs with a smarter data retention policy. One of the primary reasons, and a huge benefit of partitioning is using it to automatically archive your data. For example, you might have a huge log table. For business purposes, you need to keep this data for 30 days. This table grows continually over time and keeping all the data makes database maintenance challenging. With time-based partitioning, you can simply archive off data older than 30 days....

    Read More
  • Building PostgreSQL Extensions: Dropping Extensions and Cleanup

    David Christensen

    I recently created a Postgres extension which utilizes the extension to schedule recurring activities using the . Everything worked great. The only problem was when I dropped my extension, it left the cron job scheduled, which resulted in regular errors: If you look in the table, you can see the SQL for the cron job is still present, even though the extension/schema isn’t: This got me thinking: how can you create a Postgres extension that can clean up after itself for cases like this? If...

    Read More
  • 3 min read

    Row Level Security for Tenants in Postgres

    Craig Kerstiens

    Row-level security (RLS) in Postgres is a feature that allows you to control which rows a user is allowed to access in a particular table. It enables you to define security policies at the row level based on certain conditions, such as user roles or specific attributes in the data. Most commonly this is used to limit access based on the database user connecting, but it can also be handy to ensure data safety for multi-tenant applications. We're going to assume our tenants in this case are part o...

    Read More