MoreRSS

site iconThe Practical DeveloperModify

A constructive and inclusive social network for software developers.
Please copy the RSS to your reader, or quickly subscribe to:

Inoreader Feedly Follow Feedbin Local Reader

Rss preview of Blog of The Practical Developer

Securing Cross-Border E-Commerce with SafeLine: A Case Study of Protecting an Independent Online Store

2025-11-25 15:51:52

For cross-border e-commerce businesses, the digital marketplace is filled with opportunities, but it also comes with its share of risks. When running an online store that serves customers in different countries, the website is constantly exposed to a range of cyber threats, from bot attacks and credit card fraud to data scraping and denial-of-service (DoS) attacks.

One such e-commerce business, an independent store selling specialized products across multiple countries, faced a growing number of security challenges. After suffering from bot-driven fraud attempts, scraping, and some SQL injection attempts, the owner turned to SafeLine, a self-hosted Web Application Firewall (WAF), to enhance security, regain control, and ensure the integrity of the business. This case study details how SafeLine helped the business secure its website and protect customer data.

The Problem: Threats to an Expanding E-Commerce Business

As the business expanded internationally, the site began receiving higher traffic from various regions. While this was great for sales, it also attracted cybercriminals looking to exploit vulnerabilities. The site, which handled sensitive customer data and processed payments, became a target for:

  • Credential stuffing: Automated bots attempting to breach customer accounts using stolen credentials.
  • Scraping: Bots scraping product listings, reviews, and prices to undercut the business.
  • SQL injection: Malicious requests targeting the site’s backend to exploit potential vulnerabilities.
  • DDoS attacks: Attempts to flood the website with excessive traffic, aiming to take it down and disrupt sales.

Despite implementing some basic security measures through a cloud-based firewall, the owner quickly realized that the generic solutions in place weren’t sufficient for the unique needs of an e-commerce business. Bots continued to evade detection, and attackers were targeting payment processing routes with more sophisticated techniques.

The Solution: SafeLine’s Self-Hosted WAF

After researching different options, the e-commerce business decided to implement SafeLine, a modern, self-hosted WAF that combines intelligent threat detection with full control over web traffic. Unlike traditional cloud-based WAFs, SafeLine’s self-hosted approach allows businesses to retain full ownership of their security infrastructure, ensuring data privacy and providing advanced customizability.

Why SafeLine?

The key reasons the business opted for SafeLine were:

  • Advanced Bot Protection: SafeLine’s behavioral and semantic analysis can detect and block advanced bots that simple signature-based WAFs miss.
  • Granular Control: SafeLine allows full customization of security rules and settings, providing the flexibility to define security policies based on the business’s specific needs.
  • Data Privacy: Being self-hosted, all traffic inspection and data processing occurs on the business’s own servers, ensuring sensitive customer information remains protected.
  • Scalability: As the business grows, SafeLine’s load balancing and high availability features can easily handle increased traffic without compromising security.
  • Reduced False Positives: SafeLine’s contextual threat detection helps reduce the chances of blocking legitimate traffic, a common pain point with traditional WAFs.

Deployment and Integration

The deployment process for SafeLine was straightforward. The business used Docker for installation, which allowed for a clean, quick setup on their server infrastructure.

Here’s how SafeLine was integrated:

  • Step 1: The business deployed SafeLine on a dedicated server running Docker.
  • Step 2: SSL/TLS certificates were configured to secure communication between SafeLine and the website.
  • Step 3: Custom rules were defined for sensitive endpoints, such as payment gateways and login forms.
  • Step 4: Bot protection was activated, including fingerprinting techniques and rate-limiting for high-risk routes.

Once set up, SafeLine acted as a reverse proxy, analyzing every incoming request and blocking malicious traffic before it could reach the e-commerce platform. The business could now monitor traffic in real-time through SafeLine’s intuitive dashboard.

Immediate Impact: Protecting the Website from Various Attacks

Credential Stuffing Attack Blocked

The first notable success came when SafeLine detected and blocked an ongoing credential stuffing attempt. Automated bots were trying to log into customer accounts using large datasets of stolen usernames and passwords. SafeLine’s intelligent rate-limiting feature was able to detect the abnormal login patterns and block the bots before they could cause any damage.

This proactive protection saved the business from a potential data breach and reduced customer frustration by ensuring legitimate users could still log in without interference.

Bot-Driven Scraping Stopped

Soon after, a bot scraper started scraping product listings and reviews from the site. This content was being harvested to sell competing products at a lower price point. SafeLine’s advanced bot detection, which analyzes behavioral patterns such as the speed and accuracy of user actions, identified the scraper and blocked it. By challenging the scraper with a dynamic bot verification page, SafeLine neutralized the attack without impacting real users.

The business was now able to protect its intellectual property and prevent competitors from undercutting its prices based on stolen data.

SQL Injection Attempt Prevented

Another successful intervention came when SafeLine blocked a complex SQL injection attempt targeting the backend database. The attacker embedded malicious code within the URL parameters, attempting to gain access to sensitive customer data. SafeLine’s semantic analysis engine, which looks at the context of requests, was able to identify this as an attack and block it before it reached the application layer. This prevented potential data leaks and further fortified the website's security posture.

The Business Owner’s Experience with SafeLine

Since implementing SafeLine, the business owner has been able to sleep better at night, knowing that the site is protected from various types of attacks. The ability to monitor security events in real-time through the SafeLine dashboard has been invaluable. The owner can see which attacks were blocked, track any unusual traffic patterns, and tweak rules when needed.

Key Benefits:

  • Improved Security: SafeLine has significantly reduced the number of attacks the website receives, including bot-driven fraud, scraping, and injection attempts.
  • Scalability: The website can now handle higher traffic volumes without compromising security or performance.
  • Customizability: SafeLine’s flexible rule management allows the business to create tailored security policies for different parts of the website, such as payment gateways and customer login pages.
  • Peace of Mind: With SafeLine handling the heavy lifting of traffic filtering, the business owner can focus on growth without worrying about security breaches.

Conclusion: Why Self-Hosted WAFs Are Essential for Cross-Border E-Commerce

For growing e-commerce businesses, especially those operating internationally, securing the website from malicious traffic and protecting sensitive customer data should be a top priority. SafeLine’s self-hosted WAF provided a flexible, scalable, and cost-effective solution that enabled the business to defend against advanced attacks like credential stuffing, scraping, and SQL injection.

Unlike cloud-based WAFs, SafeLine offered the e-commerce business full control over its security infrastructure, ensuring data privacy while allowing for precise customization. As the business continues to expand, SafeLine remains an essential part of its security strategy.

For e-commerce businesses looking for a robust, self-hosted solution to protect their web assets, SafeLine is an excellent choice.

Want to protect your e-commerce site?

Learn more about SafeLine: SafeLine WAF

The Library Heist: How PostgreSQL Indexes Are Like Planning the Perfect Crime (And Why You're Probably Using the Wrong One)

2025-11-25 15:51:35

The Crime Scene

It was 2 AM on a Tuesday when my phone exploded with alerts. Our e-commerce platform was dying. Response times had ballooned from 200ms to 45 seconds. The CPU graphs looked like a heart monitor during a cardiac arrest.

I logged in, hands shaking, coffee forgotten. The culprit? A seemingly innocent query:

SELECT * FROM products 
WHERE description @@ to_tsquery('wireless bluetooth headphones')
ORDER BY created_at DESC 
LIMIT 20;

My "fix" from earlier that day: adding a B-Tree index on the description column.

The problem: I'd brought a lockpick to a safe-cracking job.

This disaster taught me something crucial: PostgreSQL indexes aren't just about "making queries faster." They're specialized tools, each designed for specific types of heists—I mean, queries. Using the wrong one is like trying to break into Fort Knox with a butter knife.

Let me tell you about the six types of indexes in PostgreSQL's arsenal, when to use each, and more importantly, when NOT to.

The Master Plan: Understanding Your Target

Before we choose our tools, let's understand what we're dealing with. Think of your database like a massive library:

PostgreSQL Database = Library
├── Tables = Bookshelves
├── Rows = Books
└── Indexes = Finding Systems

But here's where it gets interesting: not all finding systems are created equal.

B-Tree indexes are like the Dewey Decimal System—perfect for ordered searches.
Hash indexes are like a magical catalog that teleports you directly to one book.
GIN indexes are like a full-text search engine that knows every word in every book.
GiST indexes are like a map showing spatial relationships between books.
SP-GiST indexes are like a hierarchical filing system for weird organizational schemes.
BRIN indexes are like sticky notes saying "Books 1-1000 are roughly in this area."

Now, let's plan our heists.

Heist #1: The Vault (B-Tree Indexes)

The Target: Ordered data, range queries, equality searches

The Tool: B-Tree (Balanced Tree)

B-Tree is the Swiss Army knife of indexes. It's the default, and honestly, it solves 80% of your problems. But that's also why developers (including past-me) slap it on everything without thinking.

When B-Tree Shines

-- The classic: finding a user by ID
CREATE INDEX idx_users_id ON users(id);
SELECT * FROM users WHERE id = 12345;

-- Range queries on dates
CREATE INDEX idx_orders_created ON orders(created_at);
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Sorting operations
CREATE INDEX idx_products_price ON products(price);
SELECT * FROM products ORDER BY price DESC LIMIT 10;

The B-Tree Structure (Simplified)

                    [50]
                   /    \
              [25]        [75]
             /   \       /   \
          [10] [40]   [60] [90]
          / \   / \   / \   / \
        [...leaves with actual data pointers...]

Why it works: B-Trees maintain balance and order. Search, insert, and delete operations are all O(log n). They're predictable, reliable, and fast for most operations.

When B-Tree Fails Spectacularly

Remember my production disaster? Here's what I learned:

-- DON'T DO THIS
CREATE INDEX idx_description_btree ON products(description);

-- Why? Because:
SELECT * FROM products WHERE description LIKE '%bluetooth%';
-- B-Tree can't optimize this! It needs to scan the entire index.

-- Also terrible:
CREATE INDEX idx_json_btree ON logs(data);
-- JSON data isn't ordered in a meaningful way for B-Tree

Real-world gotcha: B-Trees are fantastic for equality and range queries, but they're nearly useless for:

  • Pattern matching with wildcards at the start (%term)
  • Full-text search
  • JSON queries
  • Geometric data
  • Array contains operations

Performance benchmark from my disaster:

Before B-Tree on description: 200ms (seq scan)
After B-Tree on description: 45 seconds (index scan + sort nightmare)
After switching to GIN: 12ms (proper full-text search)

Heist #2: The Word Vault (GIN Indexes)

The Target: Full-text search, arrays, JSON documents

The Tool: GIN (Generalized Inverted Index)

GIN is what I should have used. Think of it as an inverted index—instead of "Document 5 contains words X, Y, Z," it stores "Word X appears in documents 3, 5, 7."

The GIN Solution to My Problem

-- Add a tsvector column for full-text search
ALTER TABLE products ADD COLUMN description_tsv tsvector
GENERATED ALWAYS AS (to_tsvector('english', description)) STORED;

-- Create the GIN index
CREATE INDEX idx_description_gin ON products USING GIN(description_tsv);

-- Now this flies
SELECT * FROM products 
WHERE description_tsv @@ to_tsquery('english', 'wireless & bluetooth & headphones')
ORDER BY ts_rank(description_tsv, to_tsquery('english', 'wireless & bluetooth & headphones')) DESC
LIMIT 20;

GIN's Structure (Conceptual)

Word "bluetooth":     → [doc_1, doc_5, doc_7, doc_23, ...]
Word "wireless":      → [doc_1, doc_8, doc_12, doc_23, ...]
Word "headphones":    → [doc_1, doc_5, doc_23, doc_45, ...]

Query: bluetooth AND wireless AND headphones
Result: intersection → [doc_1, doc_23]

When to Use GIN

-- 1. Full-text search (my use case)
CREATE INDEX idx_articles_content ON articles 
USING GIN(to_tsvector('english', content));

-- 2. Array operations
CREATE INDEX idx_tags_gin ON posts USING GIN(tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];

-- 3. JSONB queries
CREATE INDEX idx_metadata_gin ON events USING GIN(metadata);
SELECT * FROM events WHERE metadata @> '{"user_type": "premium"}';

-- 4. Multiple columns
CREATE INDEX idx_multi_search ON products 
USING GIN(to_tsvector('english', name || ' ' || description));

The Dark Side of GIN

Warning: GIN indexes are LARGE. Like, really large.

-- Check your index sizes
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

In my case:

  • Table size: 2 GB
  • B-Tree indexes: ~200 MB total
  • GIN index: 800 MB

Trade-off: GIN indexes also slow down inserts and updates because they need to update the inverted index structure. For high-write workloads, this matters.

Pro tip: Use GIN(description_tsv) WITH (fastupdate = on) to batch updates and reduce write overhead.

Heist #3: The Geometry Puzzle (GiST Indexes)

The Target: Geometric data, ranges, custom types

The Tool: GiST (Generalized Search Tree)

GiST is the elegant solution for "nearness" problems. Finding restaurants within 5km? Checking if date ranges overlap? GiST is your friend.

Real-World Example: Location-Based Search

-- Enable PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- Create a table of coffee shops
CREATE TABLE coffee_shops (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    location GEOGRAPHY(POINT, 4326)
);

-- The magic: GiST index on geographic data
CREATE INDEX idx_location_gist ON coffee_shops USING GiST(location);

-- Find coffee shops within 1km of a point
SELECT 
    name,
    ST_Distance(location, ST_MakePoint(-122.4194, 37.7749)::geography) as distance_meters
FROM coffee_shops
WHERE ST_DWithin(
    location,
    ST_MakePoint(-122.4194, 37.7749)::geography,
    1000  -- 1km in meters
)
ORDER BY location <-> ST_MakePoint(-122.4194, 37.7749)::geography
LIMIT 5;

GiST Structure (Spatial Example)

                [Bounding Box: Entire City]
                      /          \
        [Downtown Area]          [Suburbs]
           /       \              /      \
    [Block A] [Block B]    [Region 1] [Region 2]
      /  \       /  \          /  \       /  \
   [shops....]              [shops....]

Non-Spatial GiST Uses

-- Range overlap queries
CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    room_id INT,
    booking_period TSTZRANGE
);

CREATE INDEX idx_booking_period ON bookings USING GiST(booking_period);

-- Find overlapping bookings (double-booking detection)
SELECT * FROM bookings 
WHERE booking_period && '[2024-12-25 14:00, 2024-12-25 16:00)'::tstzrange;

-- Exclusion constraints (prevent overlaps)
ALTER TABLE bookings ADD CONSTRAINT no_overlap
EXCLUDE USING GiST (room_id WITH =, booking_period WITH &&);

When GiST Outperforms Everything

Use case: IP address range lookups

CREATE TABLE ip_locations (
    ip_range inet,
    country VARCHAR(2)
);

CREATE INDEX idx_ip_gist ON ip_locations USING GiST(ip_range inet_ops);

-- Lightning fast IP geolocation
SELECT country FROM ip_locations 
WHERE ip_range >>= '192.168.1.1'::inet;

Heist #4: The Hierarchical Vault (SP-GiST Indexes)

The Target: Hierarchical or partitioned data

The Tool: SP-GiST (Space-Partitioned GiST)

SP-GiST is the hipster of indexes—less commonly used but perfect for specific scenarios. It's designed for data with a natural hierarchy.

The Classic Use Case: Phone Numbers

CREATE TABLE phone_directory (
    id SERIAL PRIMARY KEY,
    phone_number VARCHAR(20)
);

-- SP-GiST for prefix searches
CREATE INDEX idx_phone_spgist ON phone_directory 
USING SPGIST(phone_number);

-- Fast prefix matching
SELECT * FROM phone_directory 
WHERE phone_number LIKE '415%';

SP-GiST Structure (Quad-tree example for 2D points)

                    [Origin]
                    /   |   \   \
            [NW]  [NE]  [SW]  [SE]
            /  \   |     |    /  \
         [...]  [...]  [...] [...]

When SP-GiST Shines

-- 1. IP network hierarchies
CREATE INDEX idx_network_spgist ON networks USING SPGIST(cidr_column);

-- 2. Quadtree spatial indexes (alternative to GiST)
CREATE INDEX idx_point_spgist ON locations USING SPGIST(point_column);

-- 3. Text prefix matching
CREATE INDEX idx_prefix_spgist ON words USING SPGIST(word text_ops);

Performance comparison (prefix search on 10M phone numbers):

  • No index: 2,300ms
  • B-Tree: 180ms
  • SP-GiST: 12ms

Why? SP-GiST naturally partitions the search space by prefixes, while B-Tree has to scan all matching prefixes.

Heist #5: The Timeline (BRIN Indexes)

The Target: Huge tables with naturally ordered data

The Tool: BRIN (Block Range INdex)

BRIN is the minimalist's dream. Instead of indexing every row, it stores summaries of data blocks. Tiny index size, surprisingly effective for the right use case.

The Perfect BRIN Scenario

-- Imagine a logs table with 1 billion rows
CREATE TABLE application_logs (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL,
    level VARCHAR(10),
    message TEXT
);

-- Traditional B-Tree index: ~40GB
-- BRIN index: ~40MB (1000x smaller!)
CREATE INDEX idx_timestamp_brin ON application_logs 
USING BRIN(timestamp) WITH (pages_per_range = 128);

-- Still fast for time-range queries
SELECT * FROM application_logs
WHERE timestamp BETWEEN '2024-12-01' AND '2024-12-02';

BRIN Structure (Conceptual)

Block Range 1 (rows 1-10000):    timestamp MIN: 2024-01-01, MAX: 2024-01-05
Block Range 2 (rows 10001-20000): timestamp MIN: 2024-01-05, MAX: 2024-01-10
Block Range 3 (rows 20001-30000): timestamp MIN: 2024-01-10, MAX: 2024-01-15
...

Query plan: Postgres skips entire block ranges that couldn't contain the target data.

When BRIN Fails

-- BAD: Random updates destroy correlation
UPDATE application_logs SET timestamp = NOW() WHERE id = 5;
-- Now block 1 might have rows from 2024 and 2025!

-- GOOD: Append-only tables
INSERT INTO application_logs (timestamp, level, message)
VALUES (NOW(), 'INFO', 'User logged in');

Real Numbers from Production

-- My time-series data table: 500M rows, 80GB
-- Comparing index approaches:

CREATE INDEX idx_ts_btree ON metrics(timestamp);
-- Index size: 15GB, Query time: 45ms

CREATE INDEX idx_ts_brin ON metrics USING BRIN(timestamp);
-- Index size: 15MB, Query time: 52ms

-- For a 1000x size reduction, 7ms slower? I'll take it.

Golden rule: If your data has natural physical ordering (timestamps, sequential IDs, geographic regions in order), BRIN is your secret weapon.

Heist #6: The Direct Teleport (Hash Indexes)

The Target: Exact equality matches only

The Tool: Hash Index

Hash indexes used to be the "don't use these" option in PostgreSQL (pre-v10 they weren't WAL-logged). Now they're viable, but their use case is narrow.

When Hash Beats B-Tree

-- UUID lookups
CREATE TABLE sessions (
    session_id UUID PRIMARY KEY,
    user_id INT,
    data JSONB
);

-- Hash index for exact UUID lookups
CREATE INDEX idx_session_hash ON sessions USING HASH(session_id);

-- Perfect for:
SELECT * FROM sessions WHERE session_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

Hash vs B-Tree Benchmark

-- Test on 50M UUID rows
-- B-Tree index: 8.5GB, Query time: 0.8ms
-- Hash index: 6.2GB, Query time: 0.6ms

-- But...
SELECT * FROM sessions WHERE session_id > 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
-- B-Tree: 45ms
-- Hash: Can't do this at all!

The Truth About Hash Indexes

Use them when:

  • You ONLY do equality checks
  • You need slightly better space efficiency
  • Your data is high-cardinality (like UUIDs)

Don't use them when:

  • You might need range queries (even occasionally)
  • You're not sure (just use B-Tree)

Controversial take: In PostgreSQL 16, hash indexes are rarely worth the specificity. B-Trees are "good enough" for equality checks, and they give you flexibility. I've stopped using hash indexes entirely.

The Heist Debriefing: Decision Matrix

Here's how to choose your tool:

┌─────────────────────┬──────────────────────────────────────────┐
│ Query Type          │ Best Index                               │
├─────────────────────┼──────────────────────────────────────────┤
│ =, <, >, <=, >=    │ B-Tree                                   │
│ BETWEEN            │ B-Tree                                   │
│ LIKE 'abc%'        │ B-Tree                                   │
│ LIKE '%abc%'       │ GIN (with trigram extension)             │
│ Full-text search   │ GIN (with tsvector)                      │
│ @>, <@, &&, ?      │ GIN (arrays, JSONB)                      │
│ Geometric queries  │ GiST (PostGIS, ranges)                   │
│ && (overlap)       │ GiST (ranges)                            │
│ Prefix search      │ SP-GiST                                  │
│ IP address lookup  │ GiST or SP-GiST                          │
│ Time-series (huge) │ BRIN                                     │
│ = only (UUID)      │ Hash (or just use B-Tree)                │
└─────────────────────┴──────────────────────────────────────────┘

The Cost-Benefit Matrix

Index Type Size Insert Speed Query Speed Best For
B-Tree Medium Fast Fast General purpose
GIN Large Slow Very Fast Search, arrays, JSON
GiST Medium Medium Fast Geometric, ranges
SP-GiST Small Fast Very Fast Hierarchical data
BRIN Tiny Very Fast Medium Huge ordered tables
Hash Medium Fast Fast Equality only

The Master Thief's Toolkit: Advanced Techniques

1. Partial Indexes (The Precision Strike)

-- Only index active users
CREATE INDEX idx_active_users ON users(email) 
WHERE status = 'active';

-- Only index recent orders
CREATE INDEX idx_recent_orders ON orders(created_at)
WHERE created_at > NOW() - INTERVAL '90 days';

-- Combine with other index types
CREATE INDEX idx_recent_posts_gin ON posts 
USING GIN(to_tsvector('english', content))
WHERE published = true AND created_at > '2024-01-01';

Result: Smaller indexes, faster queries, less maintenance overhead.

2. Expression Indexes (The Transform)

-- Index on computed values
CREATE INDEX idx_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Index on JSON extraction
CREATE INDEX idx_user_type ON events((metadata->>'user_type'));
SELECT * FROM events WHERE metadata->>'user_type' = 'premium';

3. Multi-Column Indexes (The Combo)

-- Order matters!
CREATE INDEX idx_user_created ON logs(user_id, created_at);

-- Works great for:
SELECT * FROM logs WHERE user_id = 123 AND created_at > '2024-01-01';
SELECT * FROM logs WHERE user_id = 123;

-- Doesn't use the index:
SELECT * FROM logs WHERE created_at > '2024-01-01';  -- No user_id!

4. Index Monitoring (The Surveillance)

-- Find unused indexes
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as scans,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 
    AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find duplicate indexes
SELECT 
    indrelid::regclass as table_name,
    array_agg(indexrelid::regclass) as indexes
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1;

The Lessons from My 2 AM Disaster

  1. B-Tree isn't always the answer. It's the default, not the optimal.

  2. Index type matters more than having an index. Wrong index can be worse than no index.

  3. Size matters. GIN indexes can be 40% of your table size. BRIN can be 0.1%.

  4. Test with production-scale data. My B-Tree index worked great on 1,000 rows. At 10M rows, it collapsed.

  5. Monitor your indexes. You'll be surprised how many are unused.

  6. Partial indexes are underrated. If you're querying WHERE status = 'active' 99% of the time, don't index inactive rows.

  7. Read the query planner. EXPLAIN ANALYZE is your best friend:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM products 
WHERE description_tsv @@ to_tsquery('wireless & bluetooth');

Your Action Plan

Right now:

  1. Run the unused index query on your database
  2. Find your largest table and check if BRIN might work
  3. Look for full-text searches using B-Tree (you know who you are)

This week:

  1. Add monitoring for index usage
  2. Test different index types on your slowest queries
  3. Measure index sizes and correlate with query performance

This month:

  1. Audit all your indexes
  2. Add partial indexes where appropriate
  3. Consider GIN for your JSON queries

The Perfect Crime

The best index is invisible. Users don't notice it. Developers forget it's there. It just works, quietly making queries fast, without consuming too much space or slowing down writes.

Choose wisely. Monitor religiously. Test thoroughly.

And please, for the love of Postgres, don't put a B-Tree index on a text column for full-text search. Learn from my 2 AM phone call.

What's your index horror story? Drop a comment below. Let's learn from each other's disasters—I've shared mine.

Further Reading & Resources

Tool I built after my disaster: A simple index suggestion script:

-- Save this as index_advisor.sql
WITH table_stats AS (
    SELECT 
        schemaname,
        tablename,
        seq_scan,
        idx_scan,
        n_live_tup
    FROM pg_stat_user_tables
    WHERE n_live_tup > 10000
)
SELECT 
    schemaname || '.' || tablename as table_name,
    n_live_tup as rows,
    seq_scan,
    idx_scan,
    CASE 
        WHEN seq_scan > idx_scan * 10 THEN '⚠️  Consider adding indexes'
        WHEN idx_scan = 0 AND seq_scan > 100 THEN '🚨 Definitely needs indexes'
        ELSE '✅ Looks good'
    END as recommendation
FROM table_stats
ORDER BY seq_scan DESC
LIMIT 20;

Happy indexing, and may your queries be fast and your 2 AM phone calls be rare! 🎯

Why Lithium Batteries Fail

2025-11-25 15:49:13

Understanding the hidden failure mechanisms in Li-ion/Li-polymer batteries for better hardware design.

Lithium batteries power almost everything from IoT devices and wearables to drones and robotics. However, many developers struggle with unexpected battery failures — sudden drops in runtime, swelling, overheating, or even thermal runaway.

Understanding why lithium batteries fail is crucial for makers, engineers, and hardware developers to build reliable devices. This article breaks down the technical causes, symptoms, and engineering considerations.

1. Overcharging & Voltage Stress

What happens:

Charging a Li-ion battery beyond its maximum voltage (usually 4.2V per cell) leads to chemical stress:

  • Formation of lithium metal on the anode (lithium plating)
  • Breakdown of the electrolyte
  • Increased internal resistance
  • Accelerated capacity fade

Engineering tip:


Always use a CC/CV (Constant Current / Constant Voltage) charger designed for your battery chemistry. For high-density cells, check the manufacturer’s maximum charge voltage; some allow 4.35V, others 4.4V, but this shortens cycle life.

2. Over-Discharging & Deep Discharge Damage

What happens:

Discharging below the cut-off voltage (usually 2.75–3.0V per cell) can:

  • Trigger copper dissolution from the current collector
  • Increase self-discharge
  • Cause irreversible capacity loss

Engineering tip:

  • Implement a battery management system (BMS) with low-voltage cut-off.
  • For multi-cell packs, monitor each cell individually to avoid weak-cell over-discharge.

3. High-Temperature Effects

What happens:

Heat accelerates chemical reactions inside the cell:

  • Above 45–50°C: capacity fade speeds up
  • Above 60°C: risk of thermal runaway increases
  • Cycle life drastically reduces with prolonged heat exposure

Engineering tip:

  • Keep batteries away from heat sources.
  • Consider thermal insulation or heat sinks in drones or robotics.
  • Use temperature sensors to control charging/discharging in high-temp environments.

4. Low-Temperature Effects

What happens:

Cold temperatures (below 0°C) slow down ion movement, causing:

  • Reduced capacity temporarily
  • Lithium plating during charging if done in freezing conditions
  • Potential internal short-circuit over repeated cycles

Engineering tip:

  • Limit charging below 0°C.
  • If device operates in cold regions, select batteries with low-temperature optimized electrolytes.

5. Mechanical Stress & Swelling

What happens:

Batteries can deform under:

  • Physical compression
  • Pouch swelling due to gas formation inside the cell
  • Impact or vibration (common in robotics, drones, and wearables)

Effects include:

  • Internal short circuits
  • Increased internal resistance
  • Reduced cycle life or sudden failure

Engineering tip:

  • Use proper mechanical support / casings.
  • For pouch or curved cells, maintain the minimum bending radius.
  • Avoid over-tightening screws in battery compartments.

6. High Discharge Rates & C-Rate Stress

What happens:

Drawing current above the recommended continuous or peak discharge rate:

  • Causes voltage sag
  • Generates heat
  • Accelerates capacity degradation
  • Can damage separator material

Engineering tip:

  • Check datasheet for continuous and peak discharge current limits.
  • Use a current-limiting BMS to protect the cell.
  • For motors or wireless transmission bursts, ensure your design matches the cell’s peak capability.

7. Manufacturing Defects & Internal Shorts

What happens:

Even high-quality batteries can fail due to defects:

  • Separator puncture or uneven coating
  • Impurities in electrodes or electrolyte
  • Poor welding of tabs
  • Microscopic internal short circuits

Engineering tip:

  • Source batteries from reputable suppliers with batch testing.
  • For critical projects, perform incoming QC tests: internal resistance, capacity, and visual inspection.

8. Age & Cycle Fatigue

What happens:

All lithium batteries degrade over time and usage:

  • Capacity gradually decreases (cycle fade)
  • Internal resistance rises
  • At ~80% capacity, battery may feel “dead” for high-drain devices
  • Calendar aging also reduces life even if not used

Engineering tip:

  • Design for replaceable batteries if possible
  • Monitor cycle count and capacity in firmware
  • Avoid storing fully charged for long periods; store at 30–50% SOC.

Conclusion

Lithium batteries are powerful and versatile but also sensitive to voltage, current, temperature, and mechanical stress. Developers and engineers can prevent many failures by:

  • Following datasheet specifications for charge/discharge and C-rate
  • Using BMS with proper protection
  • Designing enclosures that protect from mechanical and thermal stress
  • Considering calendar aging and cycle fatigue in device lifetime

Understanding these failure modes not only improves device reliability but also keeps your products safe.

England's Circular Economy Growth Plan: What it Means for CRE

2025-11-25 15:39:35

The drive towards a circular economy in the UK is gaining momentum, with the Government's 'Circular Economy Growth Plan' for England now anticipated in the new year. This strategic shift, despite a slight delay from its initial autumn 2025 consultation target, signals a significant evolution in how industries, including commercial real estate (CRE), will approach resource management. For property owners and asset managers, understanding this impending framework is crucial, as it will shape future operational practices, compliance requirements, and investment decisions, moving away from a traditional 'take-make-dispose' model towards one that values reuse, repair, and recycling.

The Urgent Need for a Circular Economy in Property

The traditional linear economy model, prevalent for decades, has led to immense waste generation, resource depletion, and significant environmental impact. In commercial real estate, this manifests as substantial construction and demolition waste, inefficient material usage, and a lack of foresight regarding a building's end-of-life cycle. The new Circular Economy Growth Plan, spearheaded by the Circular Economy Taskforce and chaired by the influential Andrew Morlet of the Ellen MacArthur Foundation, aims to reverse this trend. With roadmaps planned for key sectors including construction, the initiative seeks to foster a systemic change, making resource efficiency and waste reduction not just an aspiration but a regulated expectation. This shift is vital for the property sector, which traditionally accounts for a considerable portion of national waste streams.

Implications for Commercial Real Estate and ESG Reporting

The impending Circular Economy Growth Plan brings a wave of implications for commercial property. Firstly, it will necessitate a fundamental reassessment of current waste management strategies. Mere waste disposal will no longer suffice; focus will shift to waste prevention, material recycling, and extending product lifecycles. This directly impacts building design, procurement of materials, and operational management. Secondly, tighter regulations will likely emerge, demanding more rigorous ESG reporting and transparency around material flows and waste outputs. Property owners will need robust data to demonstrate compliance and progress, influencing tenant engagement and potential green financing opportunities. This is particularly relevant for those navigating frameworks like CSRD, GRI, and GRESB, where granular, verifiable data is becoming indispensable. Inaccurate or estimated waste data will become a significant liability, paving the way for advanced solutions like AI-powered ESG reporting to ensure precision and compliance.

Embracing Data-Driven Solutions for Circularity

Transitioning to a circular economy within a commercial property portfolio may seem daunting, but modern technology offers a clear pathway. The core challenge often lies in a lack of real-time, accurate waste data. Without understanding what, when, and how much waste is being generated, effective circular strategies are impossible. This is where platforms like Wastify AI become invaluable. By providing automated, granular waste tracking, commercial buildings can move beyond estimates to actionable insights. This data empowers facility managers to identify opportunities for waste segregation improvement, optimise recycling programmes, and even automate tenant recharging based on actual waste contributions. Such a data-centric approach not only supports future circular economy mandates but also enhances operational efficiency, reduces costs, and bolsters a building's environmental credentials. Adopting real-time waste tracking technologies is no longer just an option, but a strategic necessity for future-proofing assets in a circular economy. This technological shift enables seamless integration of circular principles, from initial design concepts through to day-to-day operations, ensuring compliance and driving genuine environmental impact.

Moving Beyond Estimates to Actionable Data for a Circular Future

The UK's Circular Economy Growth Plan signals a pivotal moment for commercial real estate. It underscores a future where resource efficiency and waste reduction are paramount, transforming how buildings are designed, operated, and managed. For property professionals, preparing for this shift means prioritising robust data collection, embracing technological solutions, and rethinking traditional waste practices. By moving away from guesswork and towards verifiable, real-time insights, you can not only meet impending regulatory demands but also unlock significant operational efficiencies and enhance your portfolio's sustainability profile. Don't let your waste data hold you back from a circular future; discover how Wastify AI can transform your waste management and ESG reporting today. Visit wastify.co.uk to learn more.

The Ultimate Guide to Keyboard Sizes and Layouts: Everything You Need to Know

2025-11-25 15:39:12

When it comes to mechanical keyboards, choosing the right size and layout can make a huge difference in your typing or gaming experience. With so many options available, it’s easy to get overwhelmed by the choices. Whether you need a compact keyboard for portability or a full-sized one for a complete set of keys, understanding the key differences between keyboard sizes and layouts is essential.

In this ultimate guide, we’ll explain the various keyboard sizes and layouts, their features, and how to choose the right one based on your needs. By the end of this article, you’ll have all the information you need to make an informed decision when picking your next keyboard.

1. Understanding Keyboard Sizes

The size of a keyboard refers to the number of keys and the general layout configuration. Keyboards come in different sizes, each designed to cater to specific needs, such as gaming, typing, or portability. Here’s a breakdown of the most common keyboard sizes:

A. Full-Sized Keyboards (100%)

  • Description: The full-sized keyboard is the most common layout, featuring all the essential keys including alphanumeric keys, function keys (F1-F12), number pad, and navigation keys.
  • Key Features:

    • 104 to 108 keys
    • Includes a number pad on the right side
    • Provides a complete set of keys, making it ideal for data entry and document editing
  • Best For: Office work, data entry, and anyone who needs the full functionality of a keyboard, especially those who frequently use the number pad.

B. Tenkeyless Keyboards (TKL - 87%)

  • Description: The tenkeyless (TKL) keyboard is a full-sized keyboard without the number pad. This layout offers a compact design while retaining all other keys, such as the function keys and navigation cluster.
  • Key Features:

    • 87 keys (without the number pad)
    • More compact design
    • Offers the same functionality as a full-sized keyboard, but with more desk space saved
  • Best For: Gamers, typists, and anyone who doesn’t frequently use the number pad but still needs all the other functions. It’s also perfect for those looking to save desk space.

C. 75% Keyboards

  • Description: The 75% keyboard shrinks the layout even further, retaining most of the essential keys in a compact arrangement, but eliminating some space between the keys. It typically includes the function row, navigation cluster, and modifier keys, but removes the number pad and reduces the size of the arrow keys.
  • Key Features:

    • Around 84-87 keys
    • Compact design, saving space but still maintaining much of the functionality of a full-size keyboard
    • Ideal for users who want a smaller keyboard without losing essential features
  • Best For: Portability, minimalists, and gamers who want a compact keyboard but still need most of the standard keys.

D. 60% Keyboards

  • Description: The 60% keyboard is a minimalist design that removes the function row, number pad, and navigation cluster. It’s a smaller footprint and is often favored by those looking for portability and space-saving designs.
  • Key Features:

    • Around 61-65 keys
    • No number pad, function row, or navigation keys
    • Compact and lightweight, making it easy to carry around
  • Best For: Traveling gamers, minimalists, and those who need a portable, space-saving keyboard without sacrificing basic functionality.

E. 40% Keyboards

  • Description: The 40% keyboard is the most compact option, containing only the alphanumeric keys and a few modifier keys. It is designed for extreme portability and specialized use cases.
  • Key Features:

    • 40-45 keys
    • Highly compact, reducing the keyboard to the essentials
  • Best For: Extreme minimalists or those looking for a customizable layout for specific tasks such as coding or advanced gaming.

2. Popular Keyboard Layouts

Once you’ve decided on a keyboard size, it’s time to choose a layout. The layout refers to the arrangement of keys on the keyboard. The two most common layouts are ANSI and ISO. Understanding these layouts will help you choose the best one for your typing style and region.

A. ANSI Layout

  • Description: The ANSI layout is the most common in North America and Canada. It features a horizontal Enter key, standard-sized Shift keys, and a simple layout overall.
  • Key Features:

    • Horizontal Enter key
    • Standard-sized Shift keys and Backslash key above the Enter key
    • Widely compatible with keycap sets and accessories
  • Best For: North American users, those who are familiar with the standardized layout found in most keyboards.

B. ISO Layout

  • Description: The ISO layout is more common in Europe, particularly in countries like Germany, France, and the UK. It features a vertical Enter key, a smaller Left Shift key, and an additional key between the Left Shift and Z keys.
  • Key Features:

    • Vertical Enter key
    • Smaller Left Shift key and additional "<" key on the left
    • Popular in European languages with extra characters
  • Best For: European users, especially those who type in languages that require additional characters (e.g., German, French).

3. How to Choose the Right Keyboard Size and Layout

A. What Do You Need?

Consider the tasks you perform on your keyboard. If you type a lot of numbers or do data entry, you might want a full-sized keyboard with a number pad. For gaming, a TKL or 75% keyboard might offer the perfect balance of functionality and space-saving.

If you value portability, the 60% or 40% keyboards are ideal, especially if you’re on the go or need a setup that doesn’t take up too much desk space.

B. Typing Comfort

Comfort is subjective, but a full-sized keyboard typically provides the most ergonomic setup, with enough space for comfortable key presses. However, smaller keyboards like TKL or 60% layouts can be more comfortable for some, as they encourage a more compact typing position and reduce the distance your hands need to move.

C. Customization

If you enjoy customizing your setup, ANSI layouts are generally more compatible with a wide range of keycap sets and accessories. ISO layouts are more niche and may limit your options for customizations, but they’re still great for users who need a layout that supports European characters.

4. Conclusion: Finding Your Perfect Keyboard Setup

Choosing the right keyboard size and layout depends on your needs, typing style, and desk space. Here’s a quick recap of the most common options:

  • Full-sized keyboard for those who need a complete set of keys.
  • TKL keyboard for those who want a compact design with full functionality.
  • 75% keyboard for a minimalist layout that retains essential keys.
  • 60% keyboard for portability and a minimal footprint.
  • 40% keyboard for extreme minimalism and portability.

Ultimately, the best keyboard layout and size are those that fit your typing habits, space constraints, and customization needs. Whether you're a gamer, typist, or someone who spends long hours at their desk, there’s a keyboard size and layout out there for you.

FAQs

Q1: What is the difference between ANSI and ISO layouts?
ANSI is common in North America with a horizontal Enter key, while ISO is used in Europe with a vertical Enter key and an additional key between Shift and Z.

Q2: Which keyboard size is best for gaming?
TKL or 75% keyboards are often preferred by gamers as they offer a compact layout while still maintaining all the essential keys.

Q3: What is the best keyboard size for typing?
Full-sized keyboards are best for those who do extensive typing and need access to the number pad and function keys.

Q4: Can I switch between keyboard layouts?
Yes, you can easily switch between different keyboard layouts in your device’s settings, especially on Windows and Mac computers.

ApidogのAIテスト生成機能が便利だった話

2025-11-25 15:37:51

はじめに

正直に言うと、APIテストを書くのは地味に手間がかかります。
異常系や境界値まで全部書こうと思うと、時間がいくらあっても足りません。

そんなとき、「ApidogでAIがテストケースを自動生成できる」と聞いて、
半信半疑ながら実際に試してみました。

Apidogとは?

Apidogは、APIの設計・ドキュメント・テスト・モック・管理をひとつのプラットフォームで完結できるオールインワンのAPI開発ツールです。

Postman、Swagger Editor、Mockサーバー、APIテストなど
これまでバラバラだった作業をまとめて扱えるのが大きな特徴です。

最近はAIによるテストケース自動生成にも対応し、テスト設計の初稿づくりを大幅に効率化できます。

AIがテストケース生成

なぜAIでテストケース生成が便利に感じたか

手動でテストケースを作成するとき、いつもこんな悩みに直面していました:

  • 網羅性を確保するのが難しい
  • 仕様変更があるたびに更新が必要
  • チームによって粒度や書き方がバラつく
  • 特に異常系や境界値を漏らしやすい

「AIが初稿を作ってくれるなら、この負担が軽くなるかも」と思ったのが使い始めたきっかけです。

「AIで生成」ボタンを押してみる

AIがテストケース生成

APIを定義したあとに 「テストケース」タブ を開くと、中央付近に 「AIで生成」 ボタンがあります。

クリックすると、次の画面で生成したいテストタイプを選べます:

  • 正常系
  • 異常系
  • 境界値
  • セキュリティ

一括生成も、必要な種類だけを選択して生成も可能です。
生成後は各テストケースを確認し、採用/破棄 を選んで最終調整します。
最終的には、テストレポートをエクスポートしてチームで共有することもできます。

ちょっとしたポイント

  • 生成後は必ずレビューする(誤解・抜け漏れ防止)
  • 生成精度は使うAIモデルの性能に依存
  • AI機能は初回のみ有効化が必要
  • 特に異常系や境界値は完全ではないので手動補完が必要

AI機能を使う前の準備

AIがテストケース生成

Apidog自体はAIモデルを提供していません。
使用するには、OpenAIやClaudeなど任意のAIモデルのAPIキーを設定する必要があります。

ここで重要なのは、AIが生成するテストケースの精度はモデルの性能によって決まる ということです。
モデルが高性能であれば、より実務に近いテストケースが生成されます。

試してみて気づいたポイント

AIがテストケース生成

  • 生成内容はあくまで下書き そのまま流すのではなく、レビューして業務ロジックに合わせて微調整します。
  • 時間を大幅に節約できる 数分で十数件の初稿が出るので、ゼロから手で書くより効率的です。
  • 実務向きだが万能ではない 特にビジネスロジック依存のケースは、AIだけでは完全にはカバーできません。

私なりの活用方法

  • プロジェクト初期や大規模変更時にAIで初稿を作る
  • コアロジックや重要なパスは手動で補完
  • レビューと調整でテスト品質を確保
  • CI/CDに統合して、生成したケースを継続的に利用

まとめ:AIと一緒にテスト設計する感覚

今回の体験で感じたのは、AIに丸投げするのではなく、AIと協力してテストを作る ということです。

AIが下書きを作ってくれることで、
開発者は仕様やロジックの確認により集中でき、
テストの質とスピードがどちらも向上しました。

忙しい開発者ほど、
一度AI生成で試してみる価値は十分にあると実感しました。

この記事が役に立ったら、ぜひシェアしてください。
質問やコメントがあれば、お気軽にどうぞ。

参考

https://docs.apidog.com/jp/apidog%E3%81%AEai%E6%A9%9F%E8%83%BD%E6%A6%82%E8%A6%81-1237382m0

https://docs.apidog.com/jp/%E3%83%86%E3%82%B9%E3%83%88%E3%82%B1%E3%83%BC%E3%82%B9%E3%81%AE%E8%87%AA%E5%8B%95%E7%94%9F%E6%88%90-1625320m0