Posts about Production Postgres
Understanding Postgres IOPS: Why They Matter Even When Everything Fits in Cache
John gives us the basics of i/o in Postgres from how it works, to what parts of the database use IOPS, how to measure it, and what to do to optimize it. John also explains new view for pg_stat_io just released with Postgres 16 and how this can be used for performance analysis.
18 min readActive Active in Postgres 16
Postgres 16 is out and it has some exciting updates to logical replication. Using a new WAL origin filter, you can avoid loopback transactions. Brian lays out the updates and how to get started with an active active cluster.
8 min readRemote Access Anything from Postgres
ogr_fdw is a powerhouse of options for accessing data from within Postgres. Paul digs into samples with CSV, Excel from S3, and SQLite.
5 min readPerformance Tips for Postgres FDW
Chris has some tips on helping you get the most out of queries when working with a remote Postgres foreign data wrapper. He has an overview of how queries are executed with the fdw and then samples for using CTEs, sub-queries, materialized views, and more.
8 min readData Skews in Postgres
Skewed or uneven data is common in Postgres. Elizabeth has a quick query for finding your data distributions and recommendations on fixing indexes if you have skewed data.
pgBackRest File Bundling and Block Incremental Backup
pgBackRest has some new features that allow you to bundle files in your backup repo and do a block incremental storage. These can really help with storage efficiency and performance. David has some sample code to help you get started.
6 min readHigh-compression Metrics Storage with Postgres Hyperloglog
Hyperloglog is a Postgres extension for doing high-compression storage and query approximations. Chris shows you how to get started building a metrics system inside your Postgres database while saving on storage space and query time.
13 min readLogical Replication on Standbys in Postgres 16
Postgres 16 is in beta and we are all excited about the updated logical decoding features. You heard that right, you can now do logical replication from a non-primary database! Roberto takes us through an example setup for this.
8 min read5 Ways to Get Table Creation Information in Postgres
When was this table created? Greg dives into the 5 places where you can get information about Postgres object creation.
11 min readBuilding Customer-Facing Real-Time Dashboards with Postgres
Postgres can be a great back end to powering your customer-facing analytics. Craig gives an overview of getting started with a metrics schema and then powering up to performance.
5 min readThinking Fast vs. Slow with Your Data in Postgres
Craig gives us an introduction to working with Postgres in expanded contexts like analytics, metrics, log storage, and event data.
4 min readSolving N+1 Postgres queries for Ruby on Rails apps
Chris has some tips for working with Ruby on Rails and ActiveRecord and using better SQL to improve performance and avoid N+1 queries.
6 min readPostgreSQL Unlogged Sequences - Look Mum, no WAL!
New in Postgres 15, sequences can be unlogged. Greg reviews unlogged sequences and provides some details about using them.
9 min readIs your Postgres ready for production?
Craig gets you ready for production with this quick checklist of Postgres essentials.
5 min readThe Integer at the End of the Universe: Integer Overflow in Postgres
Integer overflow can happen if you have a sequencing data type exceeding integer limits. Jesse has a query to help you spot it and recommendations for a short term and long term fix.
7 min readPostgreSQL Unlogged Tables - Look Ma, No WAL!
Unlogged tables in Postgres can give you a big performance improvement but beware of the drawbacks. Here are some tips on how to safely give it a try.
11 min readPostgres WAL Files and Sequence Numbers
Go inside the WAL file with this in-depth look at WAL file Log Sequence Number (LSN), WAL segment positions, WAL file names, and pg_wal_switch.
7 min readExposing Postgres Performance Secrets
Craig lays out four basic things to set up today to make finding and fixing performance faster in the future.
3 min readEnums vs Check Constraints in Postgres
Enums or check constraints, which one is better? Craig breaks down why enumerated types aren’t common in Postgres.
3 min readPostgres Query Boost: Using ANY Instead of IN
Let’s show some love for the ANY Operator! Read about how to use = ANY(array) in place of the IN(list) so you can send all your parameters securely with prepared statements.
4 min read
