DEV Community: Pavan Kumar Dinesh

Handling huge data on Postgres

Pavan Kumar Dinesh — Thu, 04 Jun 2020 09:31:59 +0000

OK, once you're here, you already know about postgres! So, let's dive right into the topic we're looking into, how to handle a particular table when it blows onto your face.🥴

Let me just brief about my experience with huge load of data and how we optimized it. During the final semester project that we did, we had almost up to around 20 Million wifi probe records and counting in a particular table that we chose to call "Wifis" (sequelize generated it!).

Problem 1

First issue started when we were just trying to count the number of probe requests we're getting in real time and display the total count onto a grafana dashboard(which looks relatively simple),until when we found out that querying on a 20 million record table with SELECT COUNT(*) FROM "Wifis";
was a working but slow solution to the problem since, it almost took about 5 seconds! to get back the count.

Well, after a few searches found out why this was something that was built into postgres. PostgreSQL uses multiversion concurrency control (MVCC) to ensure consistency between simultaneous transactions. This means each transaction may see different rows and different numbers of rows in a table. There is no single universal row count that the database could cache, so it must scan through all rows counting how many are visible. Performance for an exact count grows linearly with table size.

So, to overcome that, we actually setup a second new table which would just hold the counts of all the necessary things that needed to be shown on the dashboard and keeps updating after every transaction on the "Wifis" table. How do we achieve this?

Enter TRIGGERS....

So the plan was simple

create a second table called count_table

CREATE TABLE row_counts (
    relationname text PRIMARY KEY,
    relationtuples bigint
);

Initialize that table with the current status/count of the records in the "Wifis" table.

INSERT INTO row_counts (relationname, relationtuples)
VALUES (
        'wifiProbes',
        (
            SELECT count(*)
            from "Wifis"
        )
    );

create a TRIGGER function that adjusts the count of row_counts table whenever there is action on the "Wifis" table.

CREATE OR REPLACE FUNCTION adjust_count()
RETURNS TRIGGER AS
$$
   DECLARE
   BEGIN
   IF TG_OP = 'INSERT' THEN
      EXECUTE 'UPDATE row_counts set relationtuples=relationtuples +1 where relationname = ''' || TG_RELNAME || '''';
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      EXECUTE 'UPDATE row_counts set relationtuples=relationtuples -1 where relationname = ''' || TG_RELNAME || '''';
      RETURN OLD;
   END IF;
   END;
$$
LANGUAGE 'plpgsql';

Next create TRIGGER on the "Wifis" table

CREATE TRIGGER probes_count BEFORE
INSERT
    OR DELETE ON "Wifis" FOR EACH ROW EXECUTE PROCEDURE adjust_count();

So the next time you just want to get the count of the objects in your huge table, just query the relatively smaller table row_counts which is updated everytime an operation occurs on the main table, hence bringing down your COUNT queries from 5 Seconds! to a mere milliseconds.🚀

Problem 2

The other issues we found during this exercise is that we were querying that exact same table "Wifis" to display some data on the frontend, we had a necessity to ORDER them by timestamp.

First query we ran took almost 25 Seconds! to respond and by that time the frontend HTTP request already had timed out.

Further testing what was actually taking up time, we found that postgres actually took so long to sort them and respond.(you can see on that more below)

pi_production=# select * from "Wifis" where "timestamp" >= timestamp '2020-06-01T18:30:00.000Z'  and "timestamp" <= timestamp '2020-06-03T15:11:06.276Z' order BY "timestamp" limit 50
                               QUERY PLAN
----------------------------------------------------------------------------------
 Limit (cost=185076.20..185076.23 rows=50 width=11)
       (actual time=896.739..896.740 rows=50 loops=1)
        -> Sort (cost=185076.20..197576.20 rows=20789184 width=11)
                (actual time=896.737..896.738 rows=10 loops=1)
           Sort Key: x
           Sort Method: top-N heapsort Memory: 25kB
           -> Seq Scan on t_test (cost=0.00..77028.00 rows=20789184 width=11) 
                                 (actual time=1.154..282.408 rows=20789184 loops=1)
Planning time: 0.087 ms
Execution time: 24.768 s
(7 rows)

As you can see, going through/scanning through in layman terms took a whole lot of time.

Reading more about it seemed to point out the issue. Since, these rows are not in memory for postgres to process them, it actually has to get them from disk, load them onto memory and then process them.So, to actually speed it up, all we have to do is just hold them in memory. So INDEX them in postgres. i.e INDEX the column that you'd want to use more often, in my case it was timestamp since we would order the items in the table more often. In case you'd search for an item more often, INDEX the id.

pi_production=# create index timestamp_index on "Wifis"(timestamp);
CREATE INDEX
Time: 40252.817 ms

after creating the index, the same query of ORRDER BY timestamp took a mere 6.808ms all the way from ~25 Seconds.🚀

pi_production=# explain analyze select * from "Wifis" where "timestamp" >= timestamp '2020-06-01T18:30:00.000Z'  and "timestamp" <= timestamp '2020-06-03T15:11:06.276Z' order BY "timestamp" limit 50;
                                                                              QUERY PLAN                                               

---------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
 Limit  (cost=0.56..169.98 rows=50 width=268) (actual time=0.240..0.316 rows=50 loops=1)
   ->  Index Scan using timestamp_index on "Wifis"  (cost=0.56..955416.29 rows=281963 width=268) (actual time=0.227..0.294 rows=50 loop
s=1)
         Index Cond: (("timestamp" >= '2020-06-01 18:30:00'::timestamp without time zone) AND ("timestamp" <= '2020-06-03 15:11:06.276'
::timestamp without time zone))
 Planning Time: 2.482 ms
 Execution Time: 0.475 ms
(5 rows)

Phew!! That was a lot of searching and implementing, remember when we have a huge table that often has operations on it and if you want it to be fast(Duh!) always INDEX them.🤗

If there's more problems with handling huge data on postgres, please be a star and drop them in the discussions, so I'm prepared to handle them next time.(Thanks to you)

Final project 🥳

Pavan Kumar Dinesh — Wed, 20 May 2020 09:46:47 +0000

My Final Project

As part of my bachelor's degree project, we spend the final semester working on a project. We were a team of 3. We worked on developing an analytics platform specifically targeted at in-store shopping, by using wifi-enabled devices (Probe requests) and also by using cameras placed at the stores. We first pushed ourselves to get a POC and then got feedback on the same and got an MVP out before the final review.

Demo Link

Our app is live but data on it depends on whether the hardware is set up at the facility or not. (Also we'd be experimenting on the same!)
Shop Analytics

Link to Code

itspa1 / shop-analytics-pi

Raspberry-pi script to capture in-store people data and send them to a server

Shop-Analytics-Pi

This can run multiple types of collection of people data

By MAC address identification i.e Capturing probe-requests nearby
By Camera using people detection

How Each of them works

Capturing probe-requests
- This is done by either putting the wifi-card of the device on monitor mode and running a tcpdump with appropriate flags.
- If the native wifi-card does not support monitor-mode, you could plug an esp8266 microcontroller to run as a wifi-sniffer.
People Detection
- Several models can be run using the tf module present in this, you just need to swap out the appropriate tensorflow models by downloading from their Github repo.
- YOLO is also supported to run on this by using the native CV2 library.

Steps to Run this on a raspberry pi

Ensure the pi's wifi card supports monitor mode (if using Native Sniffer module)
Clone this repo onto the raspberry pi
add gitignored files
- env.json
```
{
"main": {
```
…

View on GitHub

itspa1 / shop-analytics-backend

The backend service to store the captured probes from the pi.

Pi Sniffer Backend

The Backend server code to store all the frames coming from the raspberry pi and also enable api's interaction for the same.

Steps to run this backend

clone this repo
ensure node.js is installed (used version: lts(12.14.0))
run npm install
add main.js in config directory

  module.exports = {
      clientUrl: "frontend service",
      port: 3000,
      mqttUsername: "mqtt service username",
      mqttPassword: "mqtt service password ",
      mqttHost: "mqtt service host",
      mqttPort: mqtt service port,
      mqttTopics: ["frame_topic","cache_frame_topic"]
  };

add config.js in config directory

  module.exports = {
  development: {
    username: "someUser",
    password: "somePassword",
    database: "someDatabase",
    host: "someHose",
    dialect: "postgres",
    logging: false,
    pool: {
      max: 5,
      idle: 10000,
    },
  }
};

run node index.js

View on GitHub

itspa1 / shop-analytics-frontend

The Frontend service to display the captured probes from the pi.

This project was bootstrapped with Create React App.

Available Scripts

In the project directory, you can run:

`npm start`

Runs the app in the development mode.
Open http://localhost:3000 to view it in the browser.

The page will reload if you make edits.
You will also see any lint errors in the console.

`npm test`

Launches the test runner in the interactive watch mode.
See the section about running tests for more information.

`npm run build`

Builds the app for production to the build folder.
It correctly bundles React in production mode and optimizes the build for the best performance.

The build is minified and the filenames include the hashes.
Your app is ready to be deployed!

See the section about deployment for more information.

`npm run eject`

Note: this is a one-way operation. Once you eject, you can’t go back!

If you aren’t satisfied with the build tool…

View on GitHub

How I built it (what's the stack? did I run into issues or discover something new along the way?)

We used Python to write scripts for the Raspberry-pi.
We enabled a connection between the devices via the MQTT Protocol which enables a lightweight data transfer between IoT devices.
We used Tensorflow and also YOLO to enable the people detection which has several models which have the capability to run on embedded devices with low computing power. (I personally like YOLO!😅)
We chose to use Node.Js for the backend because of the asynchronous, event-based actions that it enables us to work with.
We used react for the frontend (cause why not!🚀)
Also, we used ZMQ for low-latency streaming of frames between the hardware and the backend server.
Finally, we also used Grafana for the analytics part of it, where it was capable of querying and displaying graphs, and mostly because it has a lot of functionality as such.

Additional Thoughts

We're actually proud of the work that we've done till now. There is also a lot more than can be done on the project(De-anonymization of MAC address). Also, we're shocked by the fact that we collected almost close to 20 Million Probe request packets in the span of 4 months. (Also, a lot of data to deal with TBH!😂).

DEV Community: Pavan Kumar Dinesh

Handling huge data on Postgres

Problem 1

Problem 2

Final project 🥳

My Final Project

Demo Link

Link to Code

itspa1 / shop-analytics-pi

Raspberry-pi script to capture in-store people data and send them to a server

Shop-Analytics-Pi

This can run multiple types of collection of people data

How Each of them works

Steps to Run this on a raspberry pi

add gitignored files

itspa1 / shop-analytics-backend

The backend service to store the captured probes from the pi.

Pi Sniffer Backend

The Backend server code to store all the frames coming from the raspberry pi and also enable api's interaction for the same.

Steps to run this backend

itspa1 / shop-analytics-frontend

The Frontend service to display the captured probes from the pi.

Available Scripts

npm start

npm test

npm run build

npm run eject

How I built it (what's the stack? did I run into issues or discover something new along the way?)

Additional Thoughts

`npm start`

`npm test`

`npm run build`

`npm run eject`