Reclaiming Disk Space: Efficient Techniques for Freeing Storage without Dropping Indexes or Deleting Data

Every few months we get an alert from our database monitoring to warn us that we are running high on disk usage. Usually, we provision more storage and forget about it. We thought this was an excellent opportunity to do some cleanups that would otherwise be much more challenging.

The Usual Suspects

Provisioning storage is something we do from time to time, but before we throw money at the problem we like to make sure we make good use of the storage we already have. To do that, we start with the usual suspects.

Unused Indexes

Unused indexes are double-edged swords; you create them to make things faster, but they end up taking space and slow inserts and updates. Unused indexes are the first thing we always check when we need to clear up storage.

To find unused indexes we use the following query:

1SELECT 2 relname, 3 indexrelname, 4 idx_scan, 5 idx_tup_read, 6 idx_tup_fetch, 7 pg_size_pretty(pg_relation_size(indexrelname::regclass)) as size 8FROM 9 pg_stat_all_indexes 10WHERE 11 schemaname = 'public' 12 AND indexrelname NOT LIKE 'pg_toast_%' 13 AND idx_scan = 0 14 AND idx_tup_read = 0 15 AND idx_tup_fetch = 0 16ORDER BY 17 pg_relation_size(indexrelname::regclass) DESC;

 

 

To find the unused indexes you can actually drop, you usually have to go over the list one by one and make a decision. This can be time-consuming the first couple of times, but after you get rid of most unused indexes it becomes easier.

Index and Table Bloat

The next suspect is bloat. When you update rows in a table, PostgreSQL marks the tuple as dead and adds the updated tuple in the next available space. This process creates what's called "bloat", which can cause tables to consume more space than they really need. Bloat also affects indexes, so to free up space, bloat is a good place to look.

Estimating bloat in tables and indexes is apparently not a simple task. After running, the below queries you will most likely find some bloat, so the next thing to do is clear up that space.

Clearing bloat in indexes

To clear bloat in an index, you need to rebuild it. There are several ways to rebuild an index:

1. Re-create the index: If you re-create the index, it will be built in an optimal way.

2. Rebuild the index: Instead of dropping and creating the index yourself, PostgreSQL provides a way to re-build an existing index in place using the REINDEX command:

1REINDEX INDEX index_name;

 

1. Rebuild the index concurrently: The previous methods will obtain a lock on the table and prevent it from being changed while the operation is in progress, which is usually unacceptable. To rebuild the index without locking it for updates, you can rebuild the index concurrently.

1REINDEX INDEX CONCURRENTLY index_name;

When using REINDEX CONCURRENTLY, PostgreSQL creates a new index with a name suffixed with _ccnew and syncs any changes made to the table in the meantime. When the rebuild is done, it will switch the old index with the new index, and drop the old one.

 

If for some reason you had to stop the rebuild in the middle, the new index will not be dropped. Instead, it will be left in an invalid state and consume space. To identify invalid indexes that were created during REINDEX, we use the following query:

 

1-- Identify invalid indexes that were created during index rebuild 2SELECT 3 c.relname as index_name, 4 pg_size_pretty(pg_relation_size(c.oid)) 5FROM 6 pg_index i 7 JOIN pg_class c ON i.indexrelid = c.oid 8WHERE 9 -- New index built using REINDEX CONCURRENTLY 10 c.relname LIKE '%_ccnew' 11 -- In INVALID state 12 AND NOT indisvalid 13LIMIT 10;

 

Utilizing Partial Indexes

To find suitable candidates for partial index we wrote a query to search for indexes on fields with high null_frac, the percent of values of the column that PostgreSQL estimates are NULL:

 

1SELECT 2 c.oid, 3 c.relname AS index, 4 pg_size_pretty(pg_relation_size(c.oid)) AS index_size, 5 i.indisunique AS unique, 6 a.attname AS indexed_column, 7 CASE s.null_frac 8 WHEN 0 THEN '' 9 ELSE to_char(s.null_frac * 100, '999.00%') 10 END AS null_frac, 11 pg_size_pretty((pg_relation_size(c.oid) * s.null_frac)::bigint) AS expected_saving 12 -- Uncomment to include the index definition 13 --, ixs.indexdef 14 15FROM 16 pg_class c 17 JOIN pg_index i ON i.indexrelid = c.oid 18 JOIN pg_attribute a ON a.attrelid = c.oid 19 JOIN pg_class c_table ON c_table.oid = i.indrelid 20 JOIN pg_indexes ixs ON c.relname = ixs.indexname 21 LEFT JOIN pg_stats s ON s.tablename = c_table.relname AND a.attname = s.attname 22 23WHERE 24 -- Primary key cannot be partial 25 NOT i.indisprimary 26 27 -- Exclude already partial indexes 28 AND i.indpred IS NULL 29 30 -- Exclude composite indexes 31 AND array_length(i.indkey, 1) = 1 32 33 -- Larger than 10MB 34 AND pg_relation_size(c.oid) > 10 * 1024 ^ 2 35 36ORDER BY 37 pg_relation_size(c.oid) * s.null_frac DESC;

The results of this query can look like this on the staging schema:

 

Is it always beneficial to exclude nulls from indexes?

No. NULL is as meaningful as any other value. If your queries are searching for null values using, these queries might benefit from an index on NULL.

 

So is this method beneficial only for null values?

Using partial indexes to exclude values that are not queried very often or not at all can be beneficial for any value, not just null values. NULL usually indicate a lack of value, and in our case, not many queries were searching for null values, so it made sense to exclude them from the index.

Conclusion

Optimizing disks, storage parameters, and configuration can only affect performance so much. At some point, to squeeze that final drop in performance you need to make changes to the underlying objects. In this case, it was the index definition.

To sum up the process we took to clear as much storage as we could:

• Remove unused indexes

• Utilize partial indexes to index only what's necessary

Hopefully, after applying these techniques you can gain a few more days before you need to reach into your pocket and provision more storage.

Exploring JSON Web Tokens: Unlocking Secure Authentication in Modern Applications

Although JWT is commonly used for managing authorization, the idea behind JWT is to define a standard way for two parties to communicate information securely.

RFC7519 standard simply dictates - how the JSON data should be structured - ways to encrypt it - ways to sign it

First, a JWT has a strictly defined structure to represent your data. A JWT token structure contains three parts, and each part is separated by a comma. HEADER. PAYLOAD.SIGNATURE

A JWT token simply ensures that your data is not tempered. To temper the data, you'll need the secret_key. All this is achieved using the signature part of the token. signature = HEADER + PAYLOAD + a_secret_key.

A few other characteristics of a JWT token are that it's compact, self-contained, and fast.

• Compact because it's just a simple string, it can be easily sent/receive via URL, post, and HTTP headers. This also helps in faster transfer.

• Self-contained because this encoded string contains all the required info about the user.
• Fast because since we've all the info available in the token, we can avoid making user details query to the database more than once

Managing High Usage Accounts

Big accounts, such as Big Bazaar, Domino's & Pantaloons, often cause hotspot issues for the payment system.

A hotspot payment account is an account that has a large number of concurrent operations on it. 

For example, when merchant A starts a promotion on Amazon Prime day, it receives many concurrent purchasing orders. In this case, the merchant’s account in the database becomes a hotspot account due to frequent updates.

In normal operations, we put a row lock on the merchant’s balance when it gets updated. However, this locking mechanism leads to low throughput and becomes a system bottleneck. 

The diagram below shows several optimizations. 

• Rate limit
  We can limit the number of requests within a certain period. The remaining requests will be rejected or retried at a later time. It is a simple way to increase the system’s responsiveness for some users, but this can lead to a bad user experience. 

• Split the balance account into sub-accounts
  We can set up sub-accounts for the merchant’s account. In this way, one update request only locks one sub-account, and the rest sub-accounts are still available.

• Use cache to update balance first
  We can set up a caching layer to update the merchant’s balance. The detailed statements and balances are updated in the database later asynchronously. The in-memory cache can deal with much higher throughput than the database.

Unleashing the Power of API Gateway: Exploring Its Functionality

  

Step 1 - The client sends an HTTP request to the API gateway.

Step 2 - The API gateway parses and validates the attributes in the HTTP request.

Step 3 - The API gateway performs allow-list/deny-list checks.

Step 4 - The API gateway talks to an identity provider for authentication and authorization.

Step 5 - The rate limiting rules are applied to the request. If it is over the limit, the request is rejected.

Steps 6 and 7 - Now that the request has passed basic checks, the API gateway finds the relevant service to route to by path matching.

Step 8 - The API gateway transforms the request into the appropriate protocol and sends it to backend microservices.

Steps 9-12: The API gateway can handle errors properly, and deals with faults if the error takes a longer time to recover (circuit break). It can also leverage ELK (Elastic-Logstash-Kibana) stack for logging and monitoring. We sometimes cache data in the API gateway.

Source: bytebytego

Optimizing Pagination: Best Practices for Efficient Content Navigation

Introduction

In this article, we are going to discuss several data pagination best and worst practices.

Data pagination is omnipresent in enterprise applications. Yet, most solutions, not only they offer a bad user experience, but they are also inefficient.

The problem pagination solves

If you only had a dozen of entries in your database, then you can just simply fetch all data and display it to the user. However, this is almost never the case. Most often, database table entries range from tens of rows to billions of records.

Fetching a large amount of data takes a significant amount of time. That’s because a lot of work needs to be done to move large volumes of data from the database server to the user interface:

• the data has to be scanned from the disk and loaded into the database server buffer pool
• the loaded data is sent over the network
• the application server will get the data in tabular fashion (e.g., the JDBC ResultSet)
• the application transforms the tabular-based data into tree-based structures (e.g., entities, DTOs)
• the tree-based structure is transformed to JSON and sent over the network to the browser
• the browser needs to load the entire JSON and use it to build the UI

Not only that fetching large volumes of data is extremely inefficient, but it also hurts the user experience. Imagine having to load a list of tens of thousands of entries on your mobile phone over a metered connection. Loading this particular large list will be slow, expensive, and impossible to navigate on a mobile phone with a very limited viewport.

So, for all these reasons, pagination is used so that only a small subset of the entire dataset is fetched and displayed at once.

Classic pagination layout

Now, the most common way of sampling a result set is to split it into multiple pages or subsets of data. One such example can be seen on the old Hibernate forum:

There are over 66k posts which are split into 2600 pages. While you can practically navigate to any of those pages, in reality, this is not very useful.

Why would I want to go to page number 1758? Or, how easily could I find the exact topic I’m interested in by jumping thousands of times from one page to the next?

Page number limit

When searching for a less-selective term on Google, you might end up getting a very large result set list of possible pages matching the searched keywords.

So, when searching for “Hibernate”, Google says it has 22 million results. However, Google only provides the most relevant 16 or 17 pages:

The thing is, few users ever navigate to the second or third pages. Most users don’t even need to go from one page to another because there is a much better way to find exactly what you are interested in. You just need more selective search terms.

So, when refining the search terms, we get a much better chance of finding what we were interested in:

Better ways of filtering

Pagination is good, especially because it allows you to fetch only a small subset of data at a time. However, page navigation is only useful if the number of pages is small. If you have tens or hundreds of pages, then it’s going to be very difficult for the users to find what they are interested in.

Instead, what the user wants is a better filtering tool. Instead of manually scanning each page, it would be much better if the application could do that automatically as long as you provide highly-selective search terms.

For the application developers, it means they have to provide with a way of refining the result set, either by providing more filtering criteria or via a more detailed search term description.

This way, the application can fetch the exact subset of data the user is interested in, and the user will quickly find the right entry from the narrowed result set.

Exploring NGINX's Threading Architecture: A Deep Dive into Efficient Request Handling

When NGINX reverse proxy starts it creates one thread per CPU core and these worker threads do the heavy lifting. The number of worker threads is configurable but NGINX recommends one thread per CPU core to avoid context switching and cache thrashing. In older versions of NGINX, all threads accept connections by competing on the shared listener socket (by default only one process can listen on IP/port pair). 

In recent versions of NGINX, this was changed to use socket sharding (through the SO_REUSEPORT socket option) which allows multiple threads to listen on the same port and the OS will load balance connections on each accept queue.