A Problem Geeked Into Big Data

-
Today my article got published in OmniTI Seeds. it was a very interesting project, i had a lot of fun while doing it, and even if its not exactly Postgres related, I wouldn't be able to do it without Postgres. It begun from the basic everyday operation that all dba's are doing (log parsing / analysis) but it got to a whole different level. I hope you like it.

A Problem Geeked Into Big Data

Comments

Post a Comment

Popular posts from this blog

Tuning checkpoints

-
Image
We recently had the chance to help a customer with some IO related issues that ended up being unconfigured checkpoints. Something that may not always be obvious but can actually be somewhat common. Let's start with how things roughly work. Postgres smallest IO unit is a disk block that is 8kb (by default). Each time postgres needs a new block it will fetch it from the disks and load it to an area in RAM called shared_buffers. When postgres needs to write, it does it in the same manner: Fetches the block(s) from the disks if the block is not in shared_buffers Changes the page in shared buffers. Marks the page as changed (dirty) in shared buffers. It writes the change in  a "sequential ledger of changes" called WAL to ensure durability. This basically means that the writes are not yet "on disk". This operation is taken care of by a postgres process called checkpointer. Checkpoints are how postgres guarantees that data files and index files will be upda
Read more

Accessing PostgreSQL data from AWS Lambda

-
Image
All databases need some maintenance that we usually perform using scheduled tasks. For example, if you have an RDS instance and you want to get a bloat report once a month, you’ll probably need a small EC2 instance just to do these kinds of things. In this post, we will talk about accessing RDS, getting a result from a SQL statement and reporting it to a slack channel using a scheduled Lambda function; let’s call this poor man’s crontab :) Before we start, psycopg2 isn’t supported by lambda so it needs to be packaged into the lambda deployment package along with any other dependencies, but since psycopg2 requires libpq it needs to be compiled with libpq statically linked. There are many binaries of psycopg2 that you can download, but I would suggest you compile your own, using the latest PostgreSQL source code. We’ll talk about all these steps throughout this post. We’ll cover the following: Creating a new AWS postgres RDS instance How to compile psycopg (we’ll use docker for
Read more

AWS Aurora Postgres, not a great first impression

-
Image
Recently we had a customer facing some memory issues with an Aurora PostgreSQL. What was happening is that while loading data or creating GiST indexes they got the following error : 2018 - 01 - 04 14 : 03 : 06 UTC: 171 . 32 . 45 . 250 ( 35508 ):root @ test:[ 20457 ]:ERROR: out of memory 2018 - 01 - 04 14 : 03 : 06 UTC: 171 . 32 . 45 . 250 ( 35508 ):root @ test:[ 20457 ]:DETAIL: Failed on request of size 23 . 2018 - 01 - 04 14 : 03 : 06 UTC: 171 . 32 . 45 . 250 ( 35508 ):root @ test:[ 20457 ]: STATEMENT : INSERT INTO test_table1 VALUES ( 'domain1.com' , 'Manual' , '[198.168.1.148,198.168.1.250]' , 't' , 'f' , '2016-02-16 15:58:58.016626' , '' , '' ), ( 'domain2.com' , 'Manual' , '[192.168.1.1,192.168.1.174]' , 't' , 'f' , '2016-02-16 15:58:58.023136' , '' , '' ), ... At the same time they had a major drop in freeable memory
Read more