Marco Slot
Marco Slot
Operational and analytical workloads have historically been handled by separate database systems, though they are starting to converge. We built Crunchy Data Warehouse to put PostgreSQL at the frontier of analytics systems, using modern technologies like Iceberg and a hybrid query engine . Combining operational and analytical capabilities is extremely useful, but it is not meant to drive all your workloads into a single system. In most organizations, application developers and analysts work...
Read MoreLouise Grandjonc Leinweber
Louise Grandjonc Leinweber
Postgres does a great job of making queries really efficient. By gathering data in internal statistics tables, Postgres estimates before a query is run lots of things - like will an index scan be better than a sequential scan. How to pull data for the WHERE statement. What Postgres doesn’t know …. is how your columns are related to each other. Postgres isn’t a machine learning algorithm. It is not going to learn over time as you query things what is related and what isn't. It uses the same stat...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
Someone recently asked on the Postgres mailing lists about how to remove unwanted duplicate rows from their table. They are “unwanted” in that sense that the same value appears more than once in a column designated as a primary key. We’ve seen an uptick in this problem since glibc was kind enough to change the way they sorted things. This can lead to invalid indexes when one upgrades their OS and modifies the underlying glibc library. One of the main effects of a corrupted unique index is allowi...
Read MoreBrian Pace
Brian Pace
Postgres' ability to execute queries in parallel is a powerful feature that can significantly improve query performance, especially on large datasets. However, like all resources, parallel workers are finite. When there aren't enough available workers, Postgres may downgrade a parallel query to a serial (non-parallel) execution. This sounds reasonable unless the performance of the downgraded query is well beyond the required response times needed by the application. While helping our clients w...
Read MoreElizabeth Christensen
Elizabeth Christensen
Materialized views are widely used in Postgres today. Many of us are working with using connected systems through foreign data wrappers, separate analytics systems like data warehouses , and merging data from different locations with Postgres queries. Materialized views let you precompile a query or partial table, for both local and remote data. Materialized views are static and have to be refreshed. One of the things that can be really important for using materialized views efficiently is inde...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
It is rare that a Postgres table keeps the exact same structure year after year. New columns get added. Old columns get dropped. Column data types need to change. Those are all done with the ALTER TABLE command. One big drawback to these changes is that they may force a complete table rewrite. A rewrite means a completely new copy of the table is created, and then the old one is dropped. This can take a very long time for large tables. Worse, everything else is blocked/locked from using the tabl...
Read MorePaul Ramsey
Paul Ramsey
The number of cool things you can do with the http extension is large, but putting those things into production raises an important problem. The amount of time an HTTP request takes, 100s of milliseconds, is 10- to 20-times longer that the amount of time a normal database query takes. This means that potentially an HTTP call could jam up a query for a long time. I recently ran an HTTP function in an update against a relatively small 1000 record table. The query took 5 minutes to run, and durin...
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
The Postgres hackers mailing list ( pgsql-hackers@postgresql.org ) is an invaluable resource for anyone wanting to contribute to the PostgreSQL code. The Postgres project does not use PRs (pull requests) or GitHub issues. So if you want to contribute an idea, or help with code reviews, the hackers mailing list is the canonical way to do so. More information on contributing is on the Postgres wiki at: https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer ? My colleague Elizabeth Christ...
Read MoreKeith Fiske
Keith Fiske
Crunchy Data is pleased to announce a new open source pgMonitor Extension . Crunchy Data has worked on a pgMonitor tool for several years as part of our Kubernetes and self-managed Postgres deployments and recently we’ve added an extension to the tool set. Two primary scenarios motivated the creation of the pgMonitor extension : 1. Quicker Metrics : Monitoring metrics often need quick response times to allow for frequent updates. We've noticed that certain metrics become slower as the datab...
Read MoreJesse Soyland
Jesse Soyland
There’s a couple super common Postgres errors you’re likely to encounter while using this database, especially with an application or ORM. One is the PG::DiskFull: ERROR: could not resize shared memory segment. It will look something like this. We see a good amount of support tickets from customers on this topic. If you see this error pass by in your logs. Don’t worry. Seriously. There’s no immediate reason to panic from a single one of these errors. If you’re seeing them regularly or all th...
Read More