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 … Continue reading How to load table with JSONB data type into Aurora PostgreSQL using AWS Glue

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 … Continue reading Export table from Aurora PostgreSQL to Amazon S3

namedtuple to JSON – Python

In pgdb - PostgreSQL DB API, the cursor which is used to manage the context of a fetch operation returns list of named tuples. These named tuples contain field names same as the column names of the database query. An example of a row from the list of named tuples - Row(log_time=datetime.datetime(2019, 3, 20, 5, … Continue reading namedtuple to JSON – Python

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 … Continue reading PostgreSQL – LWLock:multixact_offset

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 … Continue reading PostgreSQL – CPU Utilization and Index

AWS DMS – Target TableName Differs

AWS DMS is a tool that supports both homogenous and heterogeneous migration, helping to migrate to aws cloud. During most of the migrations, the source and target table names remain the same, in which case the Mappings.json file is pretty simple. As an example (Oracle to PostgreSQL)  { "rules": [ { "rule-type": "selection", "rule-id": "1", … Continue reading AWS DMS – Target TableName Differs

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 … Continue reading PostgreSQL – Unique constraint and null value

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 … Continue reading FillFactor for UPDATE

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 … Continue reading Oracle to Postgres — Index Skip Scan