DEV Community: Aditya

PGBadger | Postgresql log analysis made easy

Aditya — Sat, 08 Jul 2023 06:27:37 +0000

Why the need for postgresql query log analysis?

While writing sql for any flow, we sometimes focus on the correctness of the query and not on the performance of the query. Sometimes, performance issues creep in when updating/iterating a flow iteratively over multiple devlopment cycles. It becomes important to analyze the performance of the queries, especially to handle any scaling. This is where query log analysis comes in.

In one of our cases, a complicated workflow was significantly slow when we load tested it. We were trying to figure out the bottleneck both in code structure and with query performance. Our go-to tool for query performance analysis became pgBadger.

What is a postgresql query log?

A query log is a log of all the queries that are executed on the database. It is a good practice to enable query logging on the database server. This helps in debugging and performance analysis. The query log contains the following information:

Query
database user
database name
date and time of query execution
time taken to execute the query etc.

What is pgBadger?

The most succinct description comes from pgBadger itself:

pgBadger is a PostgreSQL log analyzer built for speed with fully detailed reports and professional rendering.
It outperforms any other PostgreSQL log analyzer.

links -

How to use pgBadger

Install pgBadger

brew install pgbadger Link

Set up logging in postgresql

This can be done in multiple ways (You can refer to pgbadger documentation for more details pgBadger postgres configuration):

1. Edit postgresql.conf

We need to edit the postgresql.conf file to enable logging. We can do this by editing the file directly or by using the ALTER SYSTEM command.



# Edit postgresql.conf
vim /usr/local/var/postgres/postgresql.conf

# OR

# Edit postgresql.conf using ALTER SYSTEM
psql -U postgres -c "ALTER SYSTEM SET log_destination = 'stderr';"
psql -U postgres -c "ALTER SYSTEM SET logging_collector = 'on';"
psql -U postgres -c "ALTER SYSTEM SET log_directory = 'logs';"
psql -U postgres -c "ALTER SYSTEM SET log_filename = 'postgresql.log';"
psql -U postgres -c "ALTER SYSTEM SET log_min_duration_statement = 0;"
psql -U postgres -c "ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';"
psql -U postgres -c "ALTER SYSTEM SET log_checkpoints = 'on';"
psql -U postgres -c "ALTER SYSTEM SET log_connections = 'on';"
psql -U postgres -c "ALTER SYSTEM SET log_disconnections = 'on';"
psql -U postgres -c "ALTER SYSTEM SET log_lock_waits = 'on';"
psql -U postgres -c "ALTER SYSTEM SET log_temp_files = 0;"
psql -U postgres -c "ALTER SYSTEM SET log_autovacuum_min_duration = 0;"
psql -U postgres -c "ALTER SYSTEM SET log_error_verbosity = 'verbose';"

Make sure you edit the postgresql.conf file and not the postgresql.auto.conf file. The postgresql.auto.conf file is generated by postgresql and will be overwritten when the server restarts.

2. Update docker-compose.yml

We update our docker-compose.yaml file



mydb:

  image: 'postgres:11'

  ports:

    - '5432:5432' # machine:image

  command:

    [

      'postgres',

      '-c',

      'logging_collector=on',

      '-c',

      'log_destination=stderr',

      '-c',

      'log_directory=logs',

      '-c',

      'log_filename=postgresql.log',

      '-c',

      'log_min_duration_statement=0',

      '-c',

      'log_line_prefix=%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ',

      '-c',

      'log_checkpoints=on',

      '-c',

      'log_connections=on',

      '-c',

      'log_disconnections=on',

      '-c',

      'log_lock_waits=on',

      '-c',

      'log_temp_files=0',

      '-c',

      'log_autovacuum_min_duration=0',

      '-c',

      'log_error_verbosity=verbose',

    ]

environment:

  TZ: 'Europe/Stockholm'

POSTGRES_USER: 'postgres'

POSTGRES_PASSWORD: 'postgres'

POSTGRES_DB: 'db_dev'

volumes:

  - database-data:/var/lib/postgresql/data/ # persist data even if container shuts down

  - ./:/var/lib/postgresql/data/logs/ # persist logs even if container shuts down, logs will show up in the current directory

  - ./postgresql.conf:/etc/postgresql/postgresql.conf # custom postgresql config

networks:

  - default

Restart the database

docker-compose down && docker-compose up -d Link

Generate some queries

Start using your application and generate some logs. You can also generate some logs by running some queries manually OR even running load tests if you have them.

Run pgBadger

Just ask pgBadger to digest logs and create a report at ./report.html.

pgbadger -I -O ./report.html ./postgresql.log Link

View the report

Open the report in your browser and you will see something like this:

It has heaps of information. You can see the top queries, slow queries, errors, etc.

Conclusion

pgBadger is a great tool to analyze postgresql query logs. It is easy to use and provides a lot of information. It is a must-have tool for any postgresql developer really.

Run cypress tests in parallel on CircleCI

Aditya — Fri, 26 Nov 2021 00:00:00 +0000

Background

I use and enjoy Cypress for testing, and use CircleCI for CI/CD purposes.

In the spirit of learning by tinkering, I wanted to tweak our CircleCI workflows to see if we could cut down on our CI run times and potentially save on some credits.

I wanted to dynamically split up tests and use parallel machines each running only some of the tests to speed up the testing step.

Our current workflow, and then the updated one

The jobs are as follows in simple terms -

npm ci, eslint and prettier
Run our tests - we use both react testing library + jest and cypress
In parallel with Step 2, make sure app can build

I restructured them a bit, and separated react testing library tests from cypres tests. Now it looked like this -

First step
- Start with npm ci
- Run eslint and prettier for obvious reasons
- Save to workspace
Run react testing library tests and build app in parallel
If everything goes well, run the comprehensive cypres testing suite.

CircleCI Parallelism

CircleCI allows you to run containers in parallel for any of the jobs with extreme ease -

# Resource Class allows you to choose a machine level to adjust cpu/memory requirements
resource_class: medium+
# Parallelism allows you to launch multiple machines which run same code
parallelism: 4

However, the issue was that ALL the tests were running in all the containers which defeated the purpose of parallel testing.

Next was to figure out how to split up our cypress tests.

Setup for cypress

We use two types of tests for cypress -

Normal js tests
Using Cucumber

It deserves a separate post about how I set up Cucumber/Cypress and why it’s awesome!

Anyways, the first question in front me was - How can I split up tests across multiple machines? Answer - CircleCI to rescue!

They provide an extremely easy way of splitting tests up.

So I used this -

- run:
    command: |
      mkdir cypress/tmp
      mv $(CircleCI tests glob cypress/integration/*.spec.js | CircleCI tests split) cypress/tmp || true
      mv $(CircleCI tests glob cypress/integration/*.feature | CircleCI tests split) cypress/tmp || true
      ["$(ls -A cypress/tmp)"] && npm run test

I create a temp directory
I run CircleCI test splitting on both types of files - *.spec.js and *.feature
Lastly, I just point cypress to the newly formed temp folder and let it loose!
npm run test just runs cypress run with custom env defined in package.json

This worked reasonably well already. Then I realized that the default browser used was electron and I wanted to use Chrome to run tests.

Adding chrome to the mix

I first told cypress to use chrome as a browser when it ran the tests

cypress run --browser chrome

In the first run, it complained that chrome was not installed on the system.

Fixing issues

After some struggle, I found a ( hacky ?) solution to get it to work -

Add docker image

docker:
  - image: cimg/node:17.1.0-browsers

Install Chrome and chromedriver

steps:
  - browser-tools/install-chrome
  - browser-tools/install-chromedriver

I was all excited to see the magic happen when I was hit by an error about xvfb in my face! Time to google again !!

Finally I added xvfb-run -a npm run test to the yml file and all worked well.

What’s still missing

As I said, this was tinkering at best and more can be added.

CircleCI allows test splitting by timing data, which will certainly optimize a run.
Adding chrome like this seems hacky to me, surely there are simpler ways to do it.
Just by writing better tests, I should cover more user flows quicker.

In the end, this was a fun side quest for me, and learnt a few things about setting up CircleCI flows.

Cypress | How to handle auth JWT using env and commands

Aditya — Sat, 24 Apr 2021 00:00:00 +0000

1. Setting the scene
2. Need for writing explicit network requests in cypress?
3. Why not mock API responses OR seed the data OR both?
4. What are cypress commands?
5. How does the login command look?
6. How to access redux store?
7. Exploring Cypress env
8. How to set and access cypress env
9. TLDR: Some Example code

1. Setting the scene

A typical case of application structure is when we have a client paired with API. In such a scenario, its very useful to set up end-to-end tests using Cypress, which will help you mimic actual user behavior across the application and builds a lot of confidence in your implementation.

If you want some api endpoints protected behind auth, using JWT is a very common solution.

This complicates our cypress testing a bit, and one of the recommended approaches we can take to speed it up is to use network requests in Cypress.

2. Need for writing explicit network requests in cypress?

There are two ways you can trigger network requests in cypress:

The UI way - navigate to the form using cypress, fill in the details and click on the button. Let the application build the payload and make the network request for you, mimicking the expected user flow.
The network request way - Build your payload and use cy.request to make a direct network request. Then handle the response (Eg - If you are using redux, dispatch the correct action).

In the UI way, you do not have to know anything about the application is handling the state, and it would be a complete black box testing. It also gets slower and slower the more you do it. Imagine, you have 50 tests and for each test you need to create 10 products before you run the actual test!

The network request way is more involved in the sense that you need full knowledge of payload, auth, state management, and other cascading actions needed to handle the network request. It is much quicker though and speeds up the test run duration for a larger test suite.

Ideal combination is to test the flow using UI components ONCE, and in other places use the network requests when that UI flow is not the focus of the test.

3. Why not mock API responses OR seed the data OR both?

These are all very valid questions, and in some situations, the best course of action. However, in true end-to-end testing, we want to test how the application behaves FOR THE USER which includes front end and back end both.

Sometimes seeded data is not updated after API updates and then tests pass but application fails and so on.

Simply put, we need the most accurate mimicking of actual user flows while we are running the end-to-end tests. At the same time, we should also consider developer experience, if your CI/test run is taking a lot of time after every push, it gets frustrating pretty quickly.

4. What are cypress commands?

Commands are a great way to abstract away repeatedly used code, and also we can override default behavior of cypress core commands as well. Read about them here on the Cypress website.

5. How does the login command look?

A simple login command can look like this -

Commands.js

Cypress.Commands.add('login', (email, password) => {
  // make login call to endpoint
  cy.visit('/')
  cy.request('POST', `${BACKEND}/login/`, {
    email: email,
    password: password,
  })
})

We can also store username/email and password in cypress.json and access it.

We call it in our test like so -

create-product.spec.js

it('Authenticated user can create a new product', () => {
  cy.login('user@test.com', 'password') // logs in the user
})

6. How to access redux store?

It’s pretty simple once you see it in action! Read it here.

7. Exploring Cypress env

Read it here.

8. How to set and access cypress env

You set it using

Cypress.env(<env key> : <value you want to store>)

You access it using

const value = Cypress.env(<env key>)

Simple really.

9. TLDR: Some Example code

To show it in action - Let’s assume product creation requires authenticated request with payload to POST /products.

In this example, I am storing JWT in me:{token: <token>} format in my redux store.

This is how the command could look like -

Commands.js

Cypress.Commands.add('setUserToken', () => {
  cy.window()
    .its('store')
    .invoke('getState')
    .its('me')
    .then((me) => Cypress.env('token', me.token))
  // Here, me is the user stored in redux
})

Cypress.Commands.add('login', (email, password) => {
  // make login call to endpoint, and push info to redux store
  cy.visit('/')
  cy.request('POST', `${BACKEND}/sessions/`, {
    email: email,
    password: password,
  }).then((response) =>
    cy
      .window()
      .its('store')
      .invoke('dispatch', { type: 'LOGIN', body: response.body })
  )
  // response.body contains `user` object
  // which is stored in redux store as `me`
  cy.setUserToken()
})

Cypress.Commands.add('createProduct', (name) => {
  const payload = {
    name: name,
  }

  const token = Cypress.env('token')
  // We access the user JWT token stored in env

  cy.request({
    method: 'POST',
    url: BACKEND + '/products/',
    body: JSON.stringify(payload),
    headers: {
      authorization: 'Bearer ' + token, // consume the token
    },
  }).as('createProduct')
  cy.get('@createProduct')

  // We can also add assertions about
  // request/response structures here
})

and this is how it is used in a test -

create-product.spec.js

it('Authenticated user can create a new product', () => {
  cy.login('user@test.com', 'password') // logs in the user
  // Assert that product does not exist
  cy.createProduct('My new product') // Creates product in BE
  // Assert that product exists
})

I have extracted out setting the token as env logic as a separate command setUserToken, to make it more explicit and clean.

How to access Redux state in Cypress

Aditya — Sat, 20 Jun 2020 00:00:00 +0000

Set up our application

I borrowed the code from react-redux.js.org which showcases react + redux in action using a todo list application.

Set up and scaffold Cypress

Cypress is a fantastic testing framework. It is easy to set up and can be picked up pretty quickly.

Setting up Cypress is pretty straight forward - just run:

$ npm install cypress
or
$ yarn add cypress

We will also install the recommeded dependency

$ npm install -D start-server-and-test

start-server-and-test is a cool tool that basically

Starts server, waits for URL, then runs test command; when the tests end, shuts down server

as explained on their github repo.

How to access the store in Cypress

We don't have access to store() object ordinarily, but cypress can access window() object.
We use this to attach the store to window in our index.js.

import React from 'react'
import ReactDOM from 'react-dom'

import { Provider } from 'react-redux'
import store from './redux/store'

import TodoApp from './TodoApp'

import * as serviceWorker from './serviceWorker'

ReactDOM.render(
  <Provider store={store}>
    <TodoApp />
  </Provider>,
  document.getElementById('root')
)

if (window.Cypress) {
  window.store = store
}

Now we have access to store() and more importantly the state inside it.

Writing the test

Now that we have the store available, we can access redux state at

cy.window().its('store').invoke('getState')

The final version of the test will look like this

/// <reference types="Cypress" />

describe('Tests functionality and redux state', () => {
  it('Successfully uses todo application', () => {
    cy.visit('/')

      // assertions on view, tabs and redux
      .get('[data-cy=Header]')
      .should('have.text', 'Todo List')
      .get('.add-todo')
      .should('have.text', 'Add Todo')
      .get('.todo-list')
      .should('have.text', 'No todos, yay!')
      .get('.visibility-filters')
      .should('include.text', 'all')
      .get('.visibility-filters')
      .should('include.text', 'completed')
      .get('.visibility-filters')
      .should('include.text', 'incomplete')

      .window()
      .its('store')
      .invoke('getState')
      .then((state) => {
        expect(state.visibilityFilter).to.be.a('string').and.equal('all')
        expect(state.todos.allIds).to.be.a('array').and.to.be.empty
        expect(state.todos.byIds).to.be.a('object').and.to.be.empty
      })

      // add a todo, add another todo , mark the first one as complete
      .get('input')
      .type('My First Todo')
      .get('.add-todo')
      .click()
      .get('input')
      .type('My Second Todo')
      .get('.add-todo')
      .click()
      .get('.todo-list')
      .eq(0)
      .click()

      // assertions on view, tabs and redux
      .get('.filter')
      .eq(0)
      .should('include.text', 'all')
      .click()
      .get('.todo-item')
      .should('include.text', '👋 My First Todo')

      .get('.filter')
      .eq(2)
      .should('include.text', 'incomplete')
      .click()
      .get('.todo-item')
      .should('include.text', '👋 My First Todo')

      .get('.filter')
      .eq(1)
      .should('include.text', 'completed')
      .click()
      .get('.todo-item')
      .should('have.text', '👌 My Second Todo')
      .eq(0)
      .children()
      .should('have.class', 'todo-item__text--completed')
      .get('.filter')
      .eq(0)
      .should('include.text', 'all')
      .click()

      .window()
      .its('store')
      .invoke('getState')
      .then((state) => {
        expect(state.visibilityFilter).to.be.a('string').and.equal('all')
        expect(state.todos.allIds).to.be.a('array').and.to.have.lengthOf(2)
        expect(state.todos.byIds)
          .to.be.a('object')
          .and.to.deep.equal({
            1: { content: 'My First Todo', completed: false },
            2: { content: 'My Second Todo', completed: true },
          })
      })
  })
})

Want more ?

We can also dispatch redux actions like this.

cy.window()
  .its('store')
  .invoke('dispatch', {
    type: 'ADD_TODO',
    payload: { content: 'Dispatched TODO', completed: true },
  })

How to test your react websocket implementation using cypress

Aditya — Sat, 20 Jun 2020 00:00:00 +0000

Set up demo application

I scaffold a standard react application

$ npx create-react-app ws-demo

Clean up and update app.js to set up a websocket client.

import React, { useState } from 'react'

const App = () => {
  const [message, setMessage] = useState('websocket is closed')

  return (
    <div className="App">
      <p id="websocket">{message}</p>
      <WebsocketHandler setMessage={setMessage} />
    </div>
  )
}

export default App

// WebsocketHandler does what the name suggests - launch/close websocket client and receive messages

const websocketUrl = 'ws://127.0.0.1:5000'
const WebsocketHandler = ({ setMessage }) => {
  const ws = new WebSocket(websocketUrl)
  ws.onopen = () => {
    console.log('conn open')
    ws.send('connection is open')
  }
  ws.onmessage = (message) => {
    setMessage(message.data)
    console.log('onmessage triggered', message)
    ws.send('message received')
  }
  ws.onclose = () => {
    console.log('connection closed')
  }
  return null
}

As you can see, I have a WebsocketHandler component which handles websocket and sets state in app.js when it receives the message.

Set up and scaffold Cypress

Cypress is a fantastic testing framework.
It is easy to set up and can be picked up pretty quickly.

Setting up Cypress is pretty straight forward - just run:

$ npm install cypress
or
$ yarn add cypress

I will also install the recommended dependency

$ npm install -D start-server-and-test

start-server-and-test is a cool tool that basically

Starts server, waits for URL, then runs test command; when the tests end, shuts down server

as explained on their github repo.

I install that package and use it in the package.json script -

"cy:test": "start-server-and-test start http://localhost:3001 cy:open"

Install the manual-web-socket package

I am going to install a websocket testing package manual-web-socket
(github) and use it.

Writing the test

My Cypress test will follow the following steps-

Require manual-web-socket package
Use the nifty onBeforeLoad to access websocket services and attach them to our beloved win object in cypress
Finally, set up a mock connection
- Change connection status to OPEN
- send a message, and assert that it shows up in our react app

The test itself (with comments) -

/// <reference types="Cypress" />

const manualWebSocket = require('manual-web-socket') // import the package

describe('Tests websocket', () => {
  it('Successfully processes websocket message from server', () => {
    cy.visit('/')
      .get('[id=websocket]')
      .should('have.text', 'websocket is closed')

    cy.visit('/', {
      onBeforeLoad(win) {
        var script = win.document.createElement('script')
        script.innerText = manualWebSocket.getScript()
        win.document.head.appendChild(script)
        win.mws.track(['ws://127.0.0.1:5000']) // we start tracking ws connection here
      },
    }).then((win) => {
      const mws = win.mws
      const trackedConnection = mws.trackedConnections.getByUrl(
        // get connection by URL
        'ws://127.0.0.1:5000'
      )
      trackedConnection.readyState = mws.readyState.OPEN // set the ws state to OPEN
      const connOpenMessage = 'connection open with client'
      const payload = { data: 'Cypress is connected via websocket' }
      trackedConnection.addServerScenario(
        // addServerScenario to mock ws server on the other side
        'connection open with client',
        (connection, message) => {
          connection.reciveMessage(payload)
        }
      )
      trackedConnection.send(connOpenMessage) // send message to ws client
      cy.get('[id=websocket]').should(
        'have.text',
        'Cypress is connected via websocket' // Assert the change in client state
      )
      trackedConnection.readyState = mws.readyState.CLOSED // close ws connection
    })
  })
})

The test goes green.

How to easily set up Material UI theme toggle in React

Aditya — Wed, 20 May 2020 00:00:00 +0000

Scaffold React Application

A new react app is easily scaffolded using


$ npx create-react-app my-app

Install dependencies

I need to install material ui core package.


// with npm

$ npm install @material-ui/core



// with yarn

$ yarn add @material-ui/core

Wrap application in Theme Provider

For the purpose of this demo, I will use App.js to set up everything.

Add Light and Dark themes


export const light = {

  palette: {

  type: 'light',

  },

}

export const dark = {

  palette: {

  type: 'dark',

  },

}

Import ThemeProvider and createMuiTheme


import { ThemeProvider } from '@material-ui/core'

import { createMuiTheme } from '@material-ui/core/styles'

Set up the toggle logic


const [theme, setTheme] = useState(true)

const icon = !theme ? <Brightness7Icon /> : <Brightness3Icon /> 
// Icons imported from `@material-ui/icons`

const appliedTheme = createMuiTheme(theme ? light : dark)

Wrap the render inside ThemeProvider and pass the appliedTheme


return (

 <ThemeProvider theme={appliedTheme}>

 //rest of the code

 </ThemeProvider>

)

Trigger toggle using onClick


 onClick={() => setTheme(!theme)}

Now our theme toggle logic is in place.

Add rest of the material ui components and see the toggle in action!

You can see a working example here, along with the code.