PostgreSQL Troubleshooting

Feb 22, 2023

Logical Replication

Logical Replication is how data is primarily replicated.

SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');

Inactive Replication

If logical replication is not running, then it is wasting space, and we need to ensure that it is dropped if it is not in use. 

SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag, active FROM pg_replication_slots ;

# SELECT pg_drop_replication_slot('Your_slotname_name');

Disk Usage

Check what tables are taking up storage space.

SELECT datname, temp_files AS "Temporary files",pg_size_pretty(temp_bytes) AS "Size of temporary files" FROM pg_stat_database ;

SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;

Understand the Sizes of Indexes

SELECT datname, temp_files AS "Temporary files",temp_bytes AS "Size of temporary files" FROM pg_stat_database ;

DataOps

Cloud Storage AWS S3 vs Azure Storage vs Cloudflare R2

Feb 20, 2023

What storage provider should I use?

It seems more and more that Cloudflare R2 is the best option for most users that don't have strict compliance requirements. With its S3-compatible API, it is easy to migrate existing data and integrate into your existing applications.

What are other considerations when choosing between storage providers?

You may want to consider the usage pattern of your own application. For example, if your app is constantly reading and writing to S3 you may get a better performance with S3 as Cloudflare R2 requests will require reaching outside of the AWS network.

What is the pitfalls of using Cloudflare R2 vs AWS S3?

One of the major considerations of whether to use S3 or R2 is that R2 is a newish platform while S3 has been around for 17 years (as of 2023). There is a lot of knowledge and AWS provides as lot of different ways to optimize S3 for your workloads. R2 may require certain rearchitecture of your platform to use well.

DataOps
1