How to load table with JSONB data type into Aurora PostgreSQL using AWS Glue

In this blog post I will cover what is JSON data type, what options does PostgreSQL offers to store JSON data, how you can create AWS Glue connection to Aurora PostgreSQL database running in private subnet and how can you then use AWS Glue to write data into table with JSONB datatype into Aurora/RDS PostgreSQL …

Advertisement

Export table from Aurora PostgreSQL to Amazon S3

In this blog post I discuss how to export 100GB non-partitioned table from Aurora PostgreSQL to Amazon S3. I will walk you through two approaches that you can use to export the data. Firstly I will demonstrate using aws_s3, a PostgreSQL extension which Aurora PostgreSQL provides and then using AWS Glue service. The post also …

PostgreSQL – LWLock:multixact_offset

On one of the PostgreSQL db , the active sessions were waiting on IO:SLRURead and LWLock:multixact_offset causing application latency. As per PostgreSQL doc, SLRURead is waiting for a read of an SLRU page and multixact_offset is waiting for I/O on a multixact offset buffer. Diagnosing the issue took some time so cut to the chase …

PostgreSQL – CPU Utilization and Index

One of the Production Aurora PostgreSQL instance running on db.r4.16xlarge instance (64 vCPU and 488 GB ) was reporting high CPU Utilization spiking upto 100%. With such issues, one of the first thing is to look for the SQLs with high buffers shared hit. I have built a small tool called pgsnap which is something …

PostgreSQL – Unique constraint and null value

An important behavior in PostgreSQL to know about is the duplicate null values do not violate unique constraints. Oracle SQL> create table test (id number (2,0), country varchar(20) not null, state varchar(20) ); Table created. SQL> alter table test add constraint pk_test_id primary key (id); Table altered. SQL> alter table test add constraint uniq_test_cs unique …

FillFactor for UPDATE

What is FillFactor in PostgreSQL? As per offical Doc -- fillfactor (integer) The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for …

Oracle to Postgres — Index Skip Scan

I have been working on Oracle to Postgres migration and it has been a fun ride. One of the recent encounters during migration was performance issue in Postgres for a query, actually quite a simple query. SELECT count(*) AS num_incidents FROM audit_cs acs INNER JOIN audit_changes ac ON acs.audit_change_id = ac.audit_change_set WHERE acs.object_id=$1 AND ac.path …

PostgreSQL – An introduction

2017 - A year of learning !! Today technology is changing at a very rapid speed and it's very difficult to keep up to it but as said, it's never too late to start something new. This year I plan to spend some time outside of Oracle and learn some new things. I have started …