Forem: Sandeep

Dead Tuples in PostgreSQL.

Sandeep — Sun, 04 Sep 2022 15:13:36 +0000

Today we are going to learn about Dead Tuples in PostgreSQL. As we know PostgreSQL is based on MVCC Architecture.

MVCC is a Multi Version Concurrency Control basically "A single rows has multiple versions."
E.g, If a user insert a record of a Person whose AGE is 21. Now, he update the record with AGE 22, so what PostgreSQL does here, PostgreSQL stored those two records with the different versions.
Old version: Age = 21
New version: Age = 22
By default user can only select the new version of the record, and old version marked as an unused record.
That types of record called Dead Tuple or Dead Row.

How to find Live Tuples or Dead Tuples using pg_stat_user_tables.



select n_live_tup, n_dead_tup, relname from pg_stat_user_tables;

Find tuples from a specific table.



SELECT n_live_tup, n_dead_tup, relname FROM
pg_stat_user_tables where relname = 'users';

using Alias



SELECT relname AS Table_Name, n_live_tup AS Live_Tuples,
n_dead_tup AS Dead_Tuples FROM
pg_stat_user_tables where relname = 'users';

SQL Result:

How to avoid to create Dead Tuple while Inserting the duplicate data.

If a table have some unique constraint and a user trying to Insert the same data again, PostgreSQL will return an error >
Uniqueness violation. duplicate key value violates unique constraint. We can avoid this by using ON CONFLICT DO NOTHING clause.



INSERT INTO users VALUES ('name', 'email@gmail.com')
ON CONFLICT DO NOTHING;

It'll return INSERT 0 0 indicates that nothing was inserted in the table, the query didn't error out. In the case of ON CONFLICT DO NOTHING no dead tuples are generated because of the pre-check.

How to delete Dead Tuples using VACUUM -



VACUUM users;

It'll free up the space within the users table and only users table can use this space. If you want to use this free space by system then this command should be run.



VACUUM FULL users;

Fix the CORS - and How the Access-Control-Allow-Origin Header Works

Sandeep — Mon, 05 Jul 2021 08:42:04 +0000

Why was the CORS error there in the first place?

The error stems from a security mechanism that browsers implement called the same-origin policy.

The same-origin policy fights one of the most common cyber attacks out there: cross-site request forgery. In this maneuver, a malicious website attempts to take advantage of the browser’s cookie storage system.

For every HTTP request to a domain, the browser attaches any HTTP cookies associated with that domain. This is especially useful for authentication, and setting sessions. For instance, it’s feasible that you would sign into a web app like facebook-clone.com. In this case, your browser would store a relevant session cookie for the facebook-clone.com. And this is great! The session cookie gets stored. And every time you re-visit the facebook-clone.com tab, and click around the app, you don’t have to sign in again. Instead, the API will recognize the stored session cookie upon further HTTP requests.

The only trouble is that the browser automatically includes any relevant cookies stored for a domain when another request is made to that exact domain. Therefore, a scenario like this can happen. Say you clicked on a particularly trick popup add, opening evil-site.com.

The evil site also has the ability send a request to facebook-clone.com/api. Since the request is going to the facebook-clone.com domain, the browser includes the relevant cookies. Evil-site sends the session cookie, and gains authenticated access to facebook-clone. Your account has been successfully hacked with a cross-site request forgery attack.
Luckily, in this situation, like a hawk ready to strike, the browser will step in and prevent the malicious code from making an API request like this. It will stop evil-site and say “Blocked by the same-origin policy. 🕶️”

How does the same-origin policy work under the hood?

Under the hood, the browser checks if the origins of the web application and the server match. Above, the origins were simplified to the frontend application and backend server domains. But really, the origin is the combination of the protocol, host, and port. For example, in https://www,facebook-clone.com, the protocol is https://, the host is www.facebook-clone.com, and the hidden port number is 443 (the port number typically used for https).

To conduct the same-origin check, the browser accompanies all requests with a special request that sends the domain information receiving server. For example, for an app running on localhost:3000, the special request format looks like this:

Origin: http://localhost:3000

Reacting to this special request, the server sends back a response header. This header contains an Access-Control-Allow-Origin key, to specify which origins can access the server’s resources. The key will have one of two values:

One: the server can be really strict, and specify that only one origin can access it:

Access-Control-Allow-Origin: http://localhost:3000

Two: the server can let the gates go wide open, and specify the wildcard value to allow all domains to access its resources:

Access-Control-Allow-Origin: *

Once the browser receives this header information back, it compares the frontend domain with the Access-Control-Allow-Origin value from the server. If the frontend domain does not match the value, the browser raises the red flag and blocks the API request with the CORS policy error.

Fix two: send your request to a proxy

You can’t ask your users to trick their browsers by installing a plugin that applies an header in the frontend. But you can control the backend address that the web app’s API requests are going to.

The cors-anywhere server is a proxy that adds CORS headers to a request. A proxy acts as an intermediary between a client and server. In this case, the cors-anywhere proxy server operates in between the frontend web app making the request, and the server that responds with data. Similar to the Allow-control-allow-origin plugin, it adds the more open Access-Control-Allow-Origin: * header to the response.

It works like this. Say your frontend is trying to make a GET request to:

https://joke-api-strict-cors.appspot.com/jokes/random

But this api does not have a Access-Control-Allow-Origin value in place that permits the web application domain to access it. So instead, send your GET request to:

https://cors-anywhere.herokuapp.com/https://joke-api-strict-cors.appspot.com/jokes/random

The proxy server receives the https://joke-api-strict-cors.appspot.com/jokes/random from the url above. Then it makes the request to get that server’s response. And finally, the proxy applies the Access-Control-Allow-Origin: * to that original response.

This solution is great because it works in both development and production. In summary, you’re taking advantage of the fact that the same origin policy is only implemented in browser-to-server communication. Which means it doesn’t have to be enforced in server-to-server communication!

The one downside of the cors-anywhere proxy is that can often take a while to receive a response. The latency is high enough to make your applications appear a bit sluggish.

This brings us to a final, even better approach.

Fix three: build your own proxy

The fix I recommend in situations like this, is to build your own proxy! Exactly like the previous solution, you’re utilizing the fact that the same origin policy is not enforced within server-to-server communication. In addition, you eliminate the latency concern. You don’t need to share the cors-anywhere proxy with other consumers, and you can dedicate as many resources as you need to your own servers.

Here’s some quick Node.js code that uses the express web framework to create a proxy server around the same https://joke-api-strict-cors.appspot.com/ from above:

const express = require('express');
const request = require('request');

const app = express();

app.use((req, res, next) => {
  res.header('Access-Control-Allow-Origin', '*');
  next();
});

app.get('/jokes/random', (req, res) => {
  request(
    { url: 'https://joke-api-strict-cors.appspot.com/jokes/random' },
    (error, response, body) => {
      if (error || response.statusCode !== 200) {
        return res.status(500).json({ type: 'error', message: err.message });
      }

      res.json(JSON.parse(body));
    }
  )
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => console.log(`listening on ${PORT}`));

How does this work? The proxy uses express middleware to apply a Access-Control-Allow-Origin: * header to every response from the server. At its own jokes/random GET endpoint, the proxy requests a random joke from another server. The same-origin policy doesn’t step in to block the request, even though the domains are different. After all, this is a server-to-server request. Finally, the proxy creates a response to the original requester (an app on the browser) consisting of the resulting data and the middleware-applied Access-Control-Allow-Origin: * header.