Forem: Tshering Lama

LIKE vs. PGroonga in PostgreSQL: Which One Should You Use?

Tshering Lama — Thu, 13 Feb 2025 15:12:45 +0000

Introduction

When it comes to searching text in PostgreSQL, many developers start with LIKE or ILIKE. While these operators work fine for simple queries, they become inefficient for large datasets and complex multilingual search needs. Enter PGroonga, a powerful full-text search extension that significantly improves performance, especially for languages like Japanese, Chinese, and Korean.

In this blog, we’ll compare LIKE vs. PGroonga, highlighting their differences, performance, and best use cases.

What is `LIKE` in PostgreSQL?

LIKE and ILIKE are basic pattern-matching operators in PostgreSQL:

LIKE is case-sensitive
ILIKE is case-insensitive
% is used as a wildcard for any number of characters
_ is used as a wildcard for a single character

Example of `LIKE` Query in Laravel Eloquent:

$searchTerm = 'database';
$results = Document::where('content', 'LIKE', "%{$searchTerm}%")->get();

Limitations of `LIKE`

❌ Slow for large datasets (full table scan required)
❌ No proper support for Japanese, Chinese, or Korean
❌ No ranking or relevance-based results
❌ Does not work well for full-text search

What is PGroonga?

PGroonga is a PostgreSQL extension that provides high-speed full-text search with support for multiple languages, including English, Japanese, Chinese, and Korean.

Why PGroonga is Better Than `LIKE`

✅ Super fast (indexed search instead of full table scan)
✅ Understands word boundaries for Japanese, Chinese, and Korean
✅ Works on normal text columns and JSONB fields
✅ Provides ranking for search relevance

Example of PGroonga in Laravel Eloquent:

$searchTerm = 'データベース'; // or 'database'
$results = Document::whereRaw("content &@~ ?", [$searchTerm])->get();

Performance Comparison: `LIKE` vs. `PGroonga`

Feature	`LIKE` / `ILIKE`	`PGroonga`
Speed	🐢 Slow (full table scan)	⚡ Super fast (indexed search)
Index Support	❌ No	✅ Yes
Case Sensitivity	❌ `LIKE` is case-sensitive, `ILIKE` is case-insensitive	✅ Supports case-insensitive search
Multilingual Support	❌ No proper handling of Japanese/Chinese/Korean	✅ Perfect for multilingual search
Ranking of Results	❌ No ranking, just matches	✅ Results ranked by relevance
Works on JSONB	❌ No	✅ Yes

How to Set Up PGroonga in PostgreSQL

Step 1: Install the PGroonga Extension

Run this command in PostgreSQL:

CREATE EXTENSION pgroonga;

Step 2: Create an Index for Faster Search

For normal text columns:

CREATE INDEX documents_pgroonga_idx ON documents USING pgroonga (content);

For JSONB fields:

CREATE INDEX documents_pgroonga_jsonb_idx ON documents USING pgroonga ((data->>'content'));

Step 3: Use PGroonga Search in Laravel

$searchTerm = 'データベース';
$results = Document::whereRaw("content &@~ ?", [$searchTerm])->get();

Conclusion: When to Use What?

For small datasets or simple searches → LIKE is fine
For large datasets, multilingual search (Japanese, Chinese, Korean) → ✅ Use PGroonga
For ranking search results and better performance → ✅ Use PGroonga
For JSONB fields and flexible text search → ✅ Use PGroonga

🚀 Want to boost your PostgreSQL search performance? Switch to PGroonga today!

Best practices with example to write feature test in laravel

Tshering Lama — Fri, 25 Aug 2023 03:13:42 +0000

Hey there! When it comes to writing feature tests in Laravel, there are some really helpful best practices you can follow to make sure your tests stay effective and easy to maintain. Check out this example below to see how you can structure and write a feature test in Laravel, and keep these best practices in mind as you go!

Organize your tests:
- Place your tests in the tests/Feature directory.
- Create a separate file for each feature you're testing.
- Name your test files with descriptive names that indicate the feature being tested, such as UserRegistrationTest.php.
Use descriptive test names:
- Give your test methods descriptive names that clearly explain what aspect of the feature is being tested.
- Use a naming convention like test_<WhatIsBeingTested>_<ExpectedBehavior>.
Set up the test environment:
- Use Laravel's built-in testing tools to set up and tear down your test environment.
- Laravel provides the RefreshDatabase trait, which automatically migrates the database before each test and rolls back the changes after each test.
Use assertions and helpers:
- Laravel provides a wide range of assertion methods and helpers to simplify your tests.
- Use methods like assertStatus(), assertRedirect(), assertSee(), and assertDatabaseHas() to verify the expected behavior of your application.
Mock external dependencies:
- Use Laravel's mocking facilities to mock external dependencies such as API calls or database interactions.
- This ensures that your tests focus on the specific feature being tested, rather than relying on real-world dependencies.
Arrange, Act, Assert (AAA) pattern:
- Structure your tests using the AAA pattern: Arrange, Act, and Assert.
- Arrange: Set up the necessary preconditions for the test.
- Act: Perform the actions or operations that you want to test.
- Assert: Verify that the expected results or behavior occurred.

Here's an example of a feature test for a user registration feature:

<?php

namespace Tests\Feature;

use Illuminate\Foundation\Testing\RefreshDatabase;
use Tests\TestCase;

class UserRegistrationTest extends TestCase
{
    use RefreshDatabase;

    public function test_user_can_register()
    {
        // Arrange
        $userData = [
            'name' => 'John Doe',
            'email' => 'johndoe@example.com',
            'password' => 'password123',
        ];

        // Act
        $response = $this->post('/register', $userData);

        // Assert
        $response->assertStatus(302);
        $response->assertRedirect('/home');
        $this->assertDatabaseHas('users', ['email' => 'johndoe@example.com']);
    }
}

In this example, we have a test named test_user_can_register. It sets up the user data, sends a POST request to the /register endpoint with the data, and then asserts the expected behavior: a successful redirect to /home and the presence of the user in the database.

Remember to tailor your tests to your specific application and features, and aim for clear and concise tests that cover the essential aspects of the feature being tested.

By Using Factory

Using factories in Laravel is a common practice for generating fake data to use in tests. Factories make it easy to create test objects with realistic data and can help streamline the process of setting up test scenarios. Here's an example of how you can use factories in a feature test in Laravel:

Define a Factory:
- Create a factory class for the model you want to generate fake data for.
- Use the factory function to define the factory and specify the model class.
- Within the factory definition, use the Faker library to generate random data for each field.
Use the Factory in Your Test:
- Within your test method, call the factory to create instances of the model.
- Customize the generated data as needed to match the specific test scenario.
- Use the created instances in your test assertions.

Here's an updated example of a feature test for a user registration feature that utilizes a factory:

<?php

namespace Tests\Feature;

use App\Models\User;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Tests\TestCase;

class UserRegistrationTest extends TestCase
{
    use RefreshDatabase;

    public function test_user_can_register()
    {
        // Arrange
        $userData = [
            'name' => 'John Doe',
            'email' => 'johndoe@example.com',
            'password' => 'password123',
        ];

        // Create a user using the factory
        $user = User::factory()->create($userData);

        // Act
        $response = $this->post('/register', $userData);

        // Assert
        $response->assertStatus(302);
        $response->assertRedirect('/home');
        $this->assertDatabaseHas('users', ['email' => 'johndoe@example.com']);
        $this->assertNotNull($user->fresh()->email_verified_at);
    }
}

In this example, we have added the use App\Models\User; statement to import the User model. We then create a user using the factory with the provided $userData. This will generate a new user instance with the specified data and insert it into the database.

Using factories can help make your tests more flexible and scalable. You can easily generate multiple instances of models with varying data to test different scenarios or test cases. Additionally, if your model relationships require additional data, you can use the factory's associations to automatically generate related models.

Remember to define your factories in the appropriate factory classes within the database/factories directory of your Laravel application.

When working on large projects, it's important to establish a well-organized folder structure for your feature tests. A clear and logical folder structure makes it easier to locate and maintain your tests, especially as the number of tests grows. Here's a recommended folder structure pattern for feature tests in a large Laravel project:

tests

Feature
- Authentication
  - LoginTest.php
  - LogoutTest.php
  - RegistrationTest.php
- UserManagement
  - CreateUserTest.php
  - UpdateUserTest.php
  - DeleteUserTest.php
- OrderManagement
  - CreateOrderTest.php
  - UpdateOrderTest.php
  - CancelOrderTest.php
- ProductManagement
  - CreateProductTest.php
  - UpdateProductTest.php
  - DeleteProductTest.php
Let's break down the folder structure:
- The tests directory is the main directory for all your tests.
- Inside the tests directory, you have a subdirectory named Feature to store your feature tests specifically.
- Within the Feature directory, create subdirectories based on the major features or functional areas of your application. For example, you could have subdirectories like Authentication, UserManagement, OrderManagement, ProductManagement, etc.
- Inside each subdirectory, create individual test files for each specific feature or functionality. For example, in the Authentication directory, you might have test files like LoginTest.php, LogoutTest.php, RegistrationTest.php, etc.
- Repeat this pattern for each major feature or functional area of your application.
- If your project also includes unit tests, you can create a separate Unit directory at the same level as the Feature directory.
When you organize your feature tests this way, you can find and move through your tests based on what you're testing. This structure helps keep things separate and makes it easier to manage and run tests for specific parts of your application.

Make sure to adjust this folder structure to fit your project's needs and design. The example is just a starting point, and you can change it based on what your project requires.

Keep up the good work! Keep coding! Keep on exploring!

Optimizing Laravel Applications for Scalability and Performance

Tshering Lama — Thu, 13 Jul 2023 09:55:03 +0000

Introduction:
Hello there!

In today's digital world, it's essential to ensure your web application can scale and perform well. Laravel, a popular PHP framework, can help you build dynamic and feature-rich applications. As your application grows, it's crucial to optimize it so that it can handle increased traffic and deliver a great user experience. In this blog post, we'll explore different strategies and best practices for optimizing Laravel applications.

We hope you find this post helpful! Let us know if you have any questions or concerns.

Use Caching:
Caching is a powerful technique to improve performance by storing frequently accessed data in memory. Laravel offers several caching mechanisms, including file, database, and in-memory caching. We'll discuss how to implement caching in Laravel and leverage tools like Redis and Memcached to enhance application speed.

// Retrieving data from cache
$data = cache()->get('key');

if ($data === null) {
    // Data not found in cache, fetch it from the database
    $data = DB::table('your_table')->get();

    // Store the fetched data in cache for future use
    cache()->put('key', $data, $expirationTimeInSeconds);
}

// Use the data from cache
foreach ($data as $item) {
    // Process the data
}

Optimize Database Queries:
Database queries can be a significant bottleneck in performance. We'll explore techniques such as eager loading, query optimization, and indexing to improve the efficiency of database operations. Additionally, we'll discuss the Query Builder and ORM features of Laravel and how they can be utilized to write optimized and maintainable queries.

// Fetching data with eager loading
$users = User::with('posts')->get();

foreach ($users as $user) {
    foreach ($user->posts as $post) {
        // Process each post
    }
}

// Optimizing queries with selective column retrieval
$users = User::select('id', 'name')->get();

foreach ($users as $user) {
    // Access only the required columns (id and name)
    echo $user->id . ' - ' . $user->name;
}

// Query optimization with indexing
Schema::table('users', function ($table) {
    $table->index('email');
});

$users = User::where('email', 'example@example.com')->get();

foreach ($users as $user) {
    // Process the user
}

Implement Caching Layers:
By implementing caching layers, we can minimize the number of database queries and improve response times. We'll delve into techniques like query result caching, page caching, and HTTP caching (using Laravel's built-in middleware) to reduce server load and enhance scalability.
1. Retrieve Data from Cache or Database: First, we attempt to retrieve the data from the cache. If it exists, we use the cached data. Otherwise, we fetch the data from the database and store it in the cache for future use.
```
use Illuminate\\Support\\Facades\\Cache;

// Define a unique cache key for the data
$cacheKey = 'your_data_cache_key';

// Retrieve data from cache
$data = Cache::get($cacheKey);

if ($data === null) {
    // Data not found in cache, fetch it from the database
    $data = YourModel::where(/* conditions */)->get();

    // Store the fetched data in cache for future use
    Cache::put($cacheKey, $data, $expirationTimeInSeconds);
}

// Use the data from cache or database
foreach ($data as $item) {
    // Process the data
}
```
1. Invalidate Cache on Data Updates: When the underlying data changes, we need to invalidate the cache to ensure fresh data is fetched on subsequent requests. This can be done by removing the cached item or clearing the cache entirely.
```
use Illuminate\\Support\\Facades\\Cache;

// Invalidate the specific cache item
Cache::forget($cacheKey);

// Clear the entire cache
Cache::flush();
```
Hi there! I wanted to offer a suggestion that might help improve your website's response times. One way to do this is by implementing caching layers, which can minimize the number of database queries needed. Here's how it works: the data is fetched from the database and then stored in the cache. This means that subsequent requests can retrieve the data directly from the cache, without having to repeat the database query. To get started, make sure to configure your cache driver in Laravel's configuration file (config/cache.php). You can choose from several caching mechanisms, such as file caching, database caching, or in-memory caching using Redis or Memcached. Hope this helps!
Optimize Autoloading:
Laravel utilizes Composer for autoloading classes, which can sometimes lead to performance issues. We'll explore techniques like classmap autoloading and optimizing the Composer autoloader to reduce autoloading overhead and improve application response times.
- Classmap Autoloading
  1. By using classmap autoloading, you can generate a mapping of all classes in your application, which improves autoloading performance. To generate the classmap, run the following command:
```
composer dump-autoload --optimize --classmap-authoritative
```
  This command generates a classmap file that maps each class to its corresponding file, allowing for faster autoloading without relying on the Composer autoloader to search for the files.

- Optimizing the Composer Autoloader

    Composer's autoloader can also be optimized to improve performance. Add the following configuration to your **`composer.json`** file:

    ```bash
    {
        "config": {
            "optimize-autoloader": true
        }
    }
    ```

    Then, run the following command to regenerate the optimized autoloader:

    ```bash
    composer dump-autoload --optimize
    ```

    This optimizes the autoloader by generating a more efficient class map and reducing the number of file system checks.

- Class Aliasing

    If you frequently use long class names, you can improve autoloading performance by adding class aliases. Instead of using the full class name, you can use shorter aliases. For example:

    ```php
    use App\Models\User as UserModel;

    // ...

    $user = new UserModel();
    ```

    By aliasing the class, the autoloader doesn't need to resolve the entire namespace and class name, resulting in faster autoloading.

- Utilize PSR-4 Autoloading

    I just wanted to let you know that following the PSR-4 autoloading standard can really help optimize the autoloading process in your application. All you need to do is make sure your classes are structured in accordance with the PSR-4 directory structure and namespace conventions. This way, Composer's autoloader can efficiently locate and autoload the classes without any extra configuration.

    By optimizing autoloading, you can really boost the performance of your Laravel application by reducing the overhead of locating and loading classes. This leads to faster application bootstrapping and improved response times.

    Just don't forget to re-run the autoloader optimization commands whenever you add or remove classes in your application. This will help keep the autoloading performance optimized.

    Hope this helps!

    *Note: While optimizing autoloading can provide performance benefits, it's important to consider the trade-off between performance and maintainability. Carefully assess the impact and ensure that the optimizations align with the needs of your application.*

Utilize Queues and Job Processing:
Laravel's built-in queue system allows you to offload time-consuming tasks to background workers, improving responsiveness and scalability. We'll discuss how to implement queues and job processing using Laravel's Queue system and explore tools like Redis and Beanstalkd for queue management.

Job

namespace App\Jobs;

use App\Mail\OrderConfirmation;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\Mail;

class ProcessOrderJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected $order;

    public function __construct($order)
    {
        $this->order = $order;
    }

    public function handle()
    {
        // Process the order

        // Send order confirmation email
        Mail::to($this->order->email)
            ->send(new OrderConfirmation($this->order));
    }
}

Dispatch Job

use App\Jobs\ProcessOrderJob;

// Dispatch the job
ProcessOrderJob::dispatch($order);

Implement Caching on the Frontend:

Caching techniques can also be applied on the front end to reduce the number of requests made to the server. We'll discuss strategies like browser caching, asset caching, and content delivery networks (CDNs) to cache static assets and improve overall performance.

Optimize Asset Loading:
Efficient asset loading is crucial for fast page rendering. We'll explore techniques such as asset minification, bundling, and deferred loading to reduce network requests and improve the loading speed of JavaScript, CSS, and images. Here's an example of how you can implement caching on the frontend:

Browser Caching

Leverage browser caching by adding cache control headers to your server's response. These headers instruct the browser to cache static assets like CSS, JavaScript, and images, reducing the number of requests made to the server.

In your Laravel application's .htaccess file (for Apache), add the following lines to enable browser caching:

<IfModule mod_expires.c>
    ExpiresActive On
    ExpiresByType text/css "access plus 1 year"
    ExpiresByType application/javascript "access plus 1 year"
    ExpiresByType image/jpeg "access plus 1 year"
    ExpiresByType image/png "access plus 1 year"
    ExpiresByType image/gif "access plus 1 year"
    # Add more file types as needed

    # Cache control for fonts (optional)
    ExpiresByType application/vnd.ms-fontobject "access plus 1 year"
    ExpiresByType application/x-font-ttf "access plus 1 year"
    ExpiresByType application/x-font-opentype "access plus 1 year"
    ExpiresByType application/x-font-woff "access plus 1 year"
    ExpiresByType application/font-woff2 "access plus 1 year"
    # Add more font types as needed

    # Cache control for other static files (optional)
    ExpiresByType text/html "access plus 15 minutes"
    ExpiresByType application/pdf "access plus 1 month"
    # Add more file types as needed

    # Set cache control headers for proxied files (optional)
    ExpiresDefault "access plus 1 year"
</IfModule>

- Assets Versioning

    To ensure that the latest version of your static assets is served to users, it's recommended to use asset versioning. Laravel provides a helper function, **`mix()`**, that automatically appends a version hash to your asset URLs based on the file's last modified timestamp.

    In your Blade template, use the **`mix()`** helper function to generate URLs for your assets:

    ```
    <link href="{{ mix('css/app.css') }}" rel="stylesheet">
    <script src="{{ mix('js/app.js') }}"></script>
    ```

    The **`mix()`** function will generate URLs with the appropriate version hash, ensuring that when you update your assets, the browser requests the latest versions.

- Content Delivery Networks (CDNs)

    Consider utilizing a content delivery network (CDN) to serve static assets. CDNs cache your assets on servers located closer to the users, reducing latency and improving performance. Laravel integrates seamlessly with popular CDNs like Cloudflare, KeyCDN, and Amazon CloudFront.

    Configure your CDN provider and update your asset URLs to point to the CDN URL. This way, the CDN will cache and serve your static assets.

    By implementing caching on the front end, you can reduce the number of requests made to your server and improve the loading speed of static assets, resulting in a faster and more responsive application.

    Remember to perform proper testing and monitoring after implementing caching to ensure that changes are effective and don't cause any unexpected behavior.

Monitor and Profile Performance: To identify bottlenecks and areas for improvement, monitoring and profiling your application's performance is essential. We'll discuss tools like Laravel Telescope, Blackfire, and New Relic that can help you analyze and optimize your application's performance.

Conclusion:
Optimizing Laravel applications for scalability and performance is an ongoing process that involves several techniques and best practices. By implementing caching, optimizing database queries, leveraging queues, and monitoring performance, you can ensure that your application can handle increased traffic and deliver an exceptional user experience. With these optimization strategies in place, you'll be well-equipped to scale your Laravel application and provide a fast and responsive platform for your users. Keep up the good work!