Lightnews — Scholar-powered news

Hacking the Postgres Statistics Tables for Faster Queries | Crunchy Data Blog

1 1 13

Crunchy Data @crunchydata.com · Apr 17

Query the cache hit with:

SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;

For Bridge customers, cache hit data is in your clusters insights.

3

Crunchy Data @crunchydata.com · Apr 17

How long has it been since you checked your cache hit ratio?

Ideally most of your frequently queried data is in the buffer cache. We recommend 98-99% in the cache for transactional workloads - analytical workloads are lower.

1 5

Crunchy Data @crunchydata.com · Apr 16

Postgres + Iceberg side-by-side next to the same query with a Postgres HEAP table.

Half a millisecond for the optimized 🔥 . 10 seconds for the long method.

www.crunchydata.com/products/war...

4

Crunchy Data @crunchydata.com · Apr 16

Crunchy Data @crunchydata.com · Apr 16

psql tip: find your config parameters that are not default with \dconfig.

List of non-default configuration parameters:

1 4

Crunchy Data @crunchydata.com · Apr 16

Postgres does a good job of keeping internal statistics of your which are used to plan how queries are executed. But Postgres doesn't always know how columns are related. In this deep dive we look at hacking the statistics for improved performance

Adding extended statistics can add information about how columns are related. Louise has some real life example queries and tips for working with extended table stats that can dramatically improve…

Crunchy Data Warehouse: Postgres + Iceberg for Your On-premises Data Lake

7

Crunchy Data @crunchydata.com · Apr 15

Miss our webinar on running Crunchy Data Warehouse on-premises? We cover an overview, hands on walk through with live querying our data lake to full Iceberg creation, and highlight of popular use cases for it. If you missed it don't worry we got you covered.

www.youtube.com/watch?v=Vojg...

YouTube video by Crunchy Data

www.youtube.com

Crunchy Data @crunchydata.com · Apr 15

The command to reset pg_stat_statements is:
SELECT pg_stat_statements_reset();

Crunchy Data @crunchydata.com · Apr 15

4) After major maintenance operations
Resetting pg_stat_statements after major operations (pg_repack, reindexes, altering table structures, etc) helps to measure how any changes affect performance.

Crunchy Data @crunchydata.com · Apr 15

3) When benchmarking query performance
If you are running benchmarks and tests to evaluate query improvements, resetting pg_stat_statements makes sure that your results reflect only the queries executed during the benchmark periods.

Crunchy Data @crunchydata.com · Apr 15

It will also help accuracy of reports (for example, the outliers insight) by preventing old, outdated queries from skewing the results.

Crunchy Data @crunchydata.com · Apr 15

2) After deploying significant query changes
If you have made significant changes to an app, optimized queries, or modified indexes, resetting pg_stat_statements helps to measure the impact of those changes more accurately.

Crunchy Data Warehouse: Postgres with Iceberg Available for Kubernetes and On-premises | Crunchy Data Blog

Crunchy Data @crunchydata.com · Apr 15

1) At the start of a new monitoring period
If you analyze performance trends daily, weekly, or monthly, resetting pg_stat_statements at the beginning of such a period helps ensure that each period starts with a clean dataset.

1 3

Crunchy Data @crunchydata.com · Apr 15

We all love pg_stat_statements but that data collects forever and can get a little stale. When should you reset it?

Here are some tips from our support team.

1 1

Crunchy Data @crunchydata.com · Apr 1

Excited to announce Crunchy Data Warehouse is now available for Kubernetes and On-premises. Need faster analytics from Postgres? Want a native Postgres data lake experience? Learn more about how it works: www.crunchydata.com/blog/crunchy...

Crunchy Data brings Postgres-native Apache Iceberg to Kubernetes and on-prem workloads.

Postgres Tips from the Experts at Crunchy Data

2 6

Crunchy Data @crunchydata.com · Mar 31

Working with psql directly in Postgres? These tips will help turn you into a psql power user -

Advanced ideas and tricks on how to use Postgres to its best.

pg_parquet: An Extension to Connect Postgres and Parquet | Crunchy Data Blog

2

Crunchy Data @crunchydata.com · Mar 27

Need to get data to or from Postgres and into Parquet? pg_parquet is an open source extension that makes this easy, no need for complicated ETL processes -

Crunchy Data is excited to release a new extension so you can write Postgres data to Parquet and or pull data from Parquet to Postgres. Craig has the details and sample code.

Citus: The Misunderstood Postgres Extension | Crunchy Data Blog

5

Crunchy Data @crunchydata.com · Mar 26

We talk with users every week about whether Citus is a good fit for them. While Citus is a very powerful Postgres extension it has very specific use cases where it does fit, here we break down the cases where Citus is a fit as well as when it's not -

What applications and use cases make the most sense for Citus.

Postgres Tips from the Experts at Crunchy Data

1 1

Crunchy Data @crunchydata.com · Mar 25

When it comes to developer tools everyone has their favorite tips and tricks. And because we love Postgres we worked to capture many of our favorite tips and tricks for Postgres here in this collection -

Advanced ideas and tricks on how to use Postgres to its best.

Automatic Iceberg Maintenance Within Postgres | Crunchy Data Blog

5

Crunchy Data @crunchydata.com · Mar 20

Excited to announce built-in maintenance for Iceberg via Postgres.

Now within Crunchy Data Warehouse we will automatically vacuum and continuously optimize your Iceberg data by compacting and cleaning up files.

Dig into the details of how this works www.crunchydata.com/blog/automat...

Iceberg can create orphan files during snapshot changes or transaction rollbacks. Crunchy Data Warehouse automatically cleans up the orphan files using a new autovacuum feature.

Citus: The Misunderstood Postgres Extension | Crunchy Data Blog

3 12

Crunchy Data @crunchydata.com · Mar 18

Citus is a Postgres extension that turns it into a sharded, distributed, horizontally scalable database. With all these buzzwords, it attracts a lot of people thinking it can solve all their problems. We dig into when it is a good fit and when it isn't - www.crunchydata.com/blog/citus-t...

What applications and use cases make the most sense for Citus.

Pi Day PostGIS Circles | Crunchy Data Blog

2 4

Crunchy Data @crunchydata.com · Mar 14

Happy pi day! 🥧

Postgres has a pi function: pi();

This can be used to calculate circular sizes and areas.

@pwramsey.bsky.social looks at pi in PostGIS today with a blog post on circular forms on PostGIS with a proof for the CIRCLELINESTRING shape.

www.crunchydata.com/blog/postgis...

For a proper Pi Day celebration in Postgres, Paul shows off a proof for CIRCULARSTRING.