In today’s world, writing code is no longer the hard part — anyone can do it. The real challenge lies in building systems that are fast, scalable, and resilient. As modern applications generate massive volumes of data, database design becomes a critical factor in delivering high-performance, efficient applications.

Two important approaches stand out in tackling these challanges: SQL Partitioning and NoSQL Sharding. In this article, we’ll break them down from a technical perspective and explore when each should be your weapon of choice.

PostgreSQL Partitioning

Since version 10, PostgreSQL has offered native partitioning, making it easier to manage large tables. Partitioning divides a single logical table into smaller child tables (partitions) to improve performance.

Types of Partitioning

• Range Partitioning: Split data by ranges (e.g., date ranges).
• List Partitioning: Split data by specific sets of values (e.g., country codes).
• Hash Partitioning: Evenly distribute data using a hash function.

Example

CREATE TABLE transactions (
    id BIGSERIAL PRIMARY KEY,
    store_id INT,
    created_at DATE,
    amount NUMERIC
) PARTITION BY RANGE (created_at);

CREATE TABLE transactions_2025_q1 PARTITION OF transactions
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE transactions_2025_q2 PARTITION OF transactions
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

Advantages

• When query over table, it fetches data from related partition and improves query performance on large tables.
• Indexes and constraints can be managed per partition.
• Simplifies data archiving.

Disadvantages

• Limited to a single database node (vertical scaling bottleneck).
• Extremely large datasets may hit hardware limits.

Oracle Partitioning

Oracle has supported advanced partitioning features since version 8i. With 11g, it introduced powerful options such as interval partitioning, reference partitioning, and composite (range-hash / range-list) partitioning.

Partition Types (Oracle)

• Range Partitioning: Based on ranges of dates or numbers.
• List Partitioning: Based on defined value sets.
• Hash Partitioning: Even distribution using hashing.
• Interval Partitioning: Automatically creates partitions based on defined intervals (e.g., monthly).
• Composite Partitioning: Two-level partitioning (e.g., RANGE (date) + HASH (store_id)).
• Reference Partitioning: Child tables automatically inherit partitioning from the parent table.

Simple Range Example (Oracle)

CREATE TABLE transactions (
  id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  store_id  NUMBER NOT NULL,
  created_at DATE   NOT NULL,
  amount    NUMBER(12,2)
)
PARTITION BY RANGE (created_at) (
  PARTITION p2025q1 VALUES LESS THAN (DATE '2025-04-01'),
  PARTITION p2025q2 VALUES LESS THAN (DATE '2025-07-01')
);

-- Local index: separate segment per partition
CREATE INDEX idx_transactions_store_created
  ON transactions (store_id, created_at)
  LOCAL;

Interval Partitioning (Automatic Monthly Partitions)

CREATE TABLE transactions_int (
  id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  store_id   NUMBER NOT NULL,
  created_at DATE   NOT NULL,
  amount     NUMBER(12,2)
)
PARTITION BY RANGE (created_at)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_base VALUES LESS THAN (DATE '2025-01-01')
);
-- After 2025-01-01, a new partition is automatically created each month.

Composite Range-Hash (Balancing and Parallelism)

CREATE TABLE transactions_comp (
  id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  store_id   NUMBER NOT NULL,
  created_at DATE   NOT NULL,
  amount     NUMBER(12,2)
)
PARTITION BY RANGE (created_at)
SUBPARTITION BY HASH (store_id) SUBPARTITIONS 8
(
  PARTITION p2025q1 VALUES LESS THAN (DATE '2025-04-01'),
  PARTITION p2025q2 VALUES LESS THAN (DATE '2025-07-01')
);

Common Administrative Operations

-- Split a partition
ALTER TABLE transactions SPLIT PARTITION p2025q2 AT (DATE '2025-06-01')
  INTO (PARTITION p2025q2a, PARTITION p2025q2b);

-- Merge partitions
ALTER TABLE transactions MERGE PARTITIONS p2025q2a, p2025q2b INTO PARTITION p2025q2;

-- Move a partition to another tablespace
ALTER TABLE transactions MOVE PARTITION p2025q1 TABLESPACE ts_fast;

-- Exchange partition with staging table
ALTER TABLE transactions EXCHANGE PARTITION p2025q1 WITH TABLE transactions_stage WITHOUT VALIDATION;

PostgreSQL vs Oracle Partitioning: Quick Notes

+-------------------------+-----------------------------+-----------------------------+
| Feature                 | PostgreSQL                  | Oracle                      |
+-------------------------+-----------------------------+-----------------------------+
| Automatic Partitioning  | Not automatic; managed with | Native support with         |
|                         | cron/jobs or extensions     | INTERVAL partitioning       |
+-------------------------+-----------------------------+-----------------------------+
| Indexes                 | Separate indexes per        | Clear distinction between   |
|                         | partition; no global        | LOCAL and GLOBAL indexes    |
|                         | indexes                     |                             |
+-------------------------+-----------------------------+-----------------------------+
| Composite/Reference     | Supports composite;         | Mature support for composite|
|                         | reference partitioning      | and reference partitioning  |
|                         | limited                     |                             |
+-------------------------+-----------------------------+-----------------------------+
| Maintenance Operations  | Relies on ATTACH/DETACH and | Rich DDL: SPLIT, MERGE,     |
|                         | manual patterns             | MOVE, EXCHANGE              |
+-------------------------+-----------------------------+-----------------------------+
| Partition-wise Join /   | Pruning and predicate       | Advanced optimizer features;|
| Pruning                 | pushdown supported; less    | extensive partition-wise    |
|                         | advanced optimizer          | join capabilities           |
+-------------------------+-----------------------------+-----------------------------+

NoSQL Sharding

Sharding splits data across multiple physical nodes to improve scalability. Popular NoSQL databases like MongoDB and Cassandra provide sharding as a core feature.

How It Works

• Shard Key: Field that determines which node stores the data.
• Coordinator / Router: Directs queries to the correct shard.
• Replication: Each shard is replicated for fault tolerance.

Advantages

• True horizontal scaling (add more nodes to increase capacity).
• Can handle extremely large datasets.
• Better distribution of workload.

Disadvantages

• More complex architecture (routers, replication, rebalancing).
• Joins and cross-shard queries can be expensive.

MongoDB (user-based sharding):

// Enable sharding for a database
sh.enableSharding("app")

// Shard a collection by hashed user_id
sh.shardCollection("app.users", { user_id: "hashed" })

// Query for a single user routes to one shard
use app
db.users.find({ user_id: 12345 })

Cassandra (IoT time-series sharding):

CREATE TABLE iot.events (
  device_id uuid,
  day       date,
  ts        timestamp,
  value     double,
  PRIMARY KEY ((device_id, day), ts)
);

-- Queries remain single-partition per device per day
SELECT * FROM iot.events WHERE device_id=? AND day=?;

These simple examples illustrate how sharding keys (e.g., user_id, (device_id, day)) determine how data is distributed and how queries are routed efficiently.

SQL Partitioning vs NoSQL Sharding

+--------------+-----------------------------+--------------------------------+
|   Feature    |      SQL Partitioning       |         NoSQL Sharding         |
+--------------+-----------------------------+--------------------------------+
| Approach     | Logical split within one DB | Split across multiple DB nodes |
| Scalability  | Limited by vertical scaling | Strong horizontal scaling      |
| Complexity   | Moderate                    | High                           |
| Join Support | Full                        | Limited / none                 |
| Use Cases    | Transactional workloads     | Big data, high-traffic systems |
+--------------+-----------------------------+--------------------------------+

Real-World Scenarios

• PostgreSQL Partitioning: An e-commerce platform with millions of transaction records. Partitioning by date accelerates reporting queries.
• Oracle Partitioning (Interval): A large bank’s customer transaction history table. Interval partitioning automatically creates monthly partitions, simplifying archiving.
• Oracle Partitioning (Composite): A telecom billing table. RANGE (billing period) + HASH (customer_id) composite partitioning improves both time-based queries and load balancing.
• Oracle Partitioning (Reference): Orders and order_items tables. Parent table is partitioned by order date, and child tables inherit the same structure automatically.
• NoSQL Sharding: A global social media platform. User data is distributed by user_id as the shard key, enabling millions of users to be served simultaneously.

Conclusion

Partitioning and sharding address different aspects of scalability. If your workload is transactional and fits within a single database server, SQL Partitioning (PostgreSQL or Oracle) is often sufficient. For truly massive datasets or globally distributed systems, NoSQL Sharding is the better approach, it is related with your business requirements and database design choose. In practice, you may think about hybrid solutions that combine SQL partitioning with NoSQL sharding across regions are increasingly common.

• 5.12. Table Partitioning
• Partitioning Overview
• Sharding - Database Manual - MongoDB Docs

In Python, there is no direct equivalent of Java’s “interface,” but similar functionality can be achieved using the abc module (Abstract Base Classes). Since Python embraces duck typing, enforcing a specific interface is not mandatory. However, for purposes like type checking or improving code readability, Python provides tools such as the abc module and Protocol (introduced in PEP 544).

1. Abstract Base Classes (ABC) as Interfaces

The abc module allows you to define abstract base classes, which can enforce that subclasses implement specific methods.

from abc import ABC, abstractmethod

class PaymentProcessor(ABC):
    @abstractmethod
    def process_payment(self, amount: float) -> None:
        pass

class PayPalProcessor(PaymentProcessor):
    def process_payment(self, amount: float) -> None:
        print(f"Processing ${amount} payment via PayPal.")

processor = PayPalProcessor()
processor.process_payment(100.0)  # Valid

If the subclass does not provide its own implementation of the process_payment method, it will raise an error and cannot be instantiated. This is because @abstractmethod enforces that all subclasses of PaymentProcessor must define the behavior of this method.

2. Using Protocols (PEP 544)

Introduced in Python 3.8, Protocol provides a lightweight and flexible way to define expected behaviors. Unlike abc, Protocols do not enforce method implementation at runtime. Instead, they are primarily used for static type checking.

from typing import Protocol

class PaymentProcessor(Protocol):
    def process_payment(self, amount: float) -> None:
        pass

class StripeProcessor:
    def process_payment(self, amount: float) -> None:
        print(f"Processing ${amount} payment via Stripe.")

class PayPalProcessor:
    def process_payment(self, amount: float) -> None:
        print(f"Processing ${amount} payment via PayPal.")

class CryptoProcessor:
    def process_payment(self, amount: float) -> None:
        print(f"Processing ${amount} payment via Cryptocurrency.")

def handle_payment(processor: PaymentProcessor, amount: float) -> None:
    processor.process_payment(amount)

# Usage
stripe = StripeProcessor()
paypal = PayPalProcessor()
crypto = CryptoProcessor()

handle_payment(stripe, 100.0)  # Output: Processing $100.0 payment via Stripe.
handle_payment(paypal, 200.0)  # Output: Processing $200.0 payment via PayPal.
handle_payment(crypto, 300.0)  # Output: Processing $300.0 payment via Cryptocurrency.

3. What is Duck Typing?

Duck typing is a programming principle in Python (and other dynamic languages) that focuses on an object’s behavior rather than its type. If an object has the required methods or attributes, it is considered valid for use, regardless of its explicit type.

“If it walks like a duck and quacks like a duck, it must be a duck.”

Duck Typing in Action

Instead of relying on type hierarchies, you check whether an object implements the necessary methods.

class Dog:
    def speak(self):
        return "Woof!"

class Cat:
    def speak(self):
        return "Meow!"

def make_animal_speak(animal):
    # We don’t care about the type, just that the object has a 'speak' method
    print(animal.speak())

dog = Dog()
cat = Cat()

make_animal_speak(dog)  # Output: Woof!
make_animal_speak(cat)  # Output: Meow!

Here, the function make_animal_speak does not check the type of the object passed to it. It simply assumes that the object has a speak method.

Benefits of Duck Typing

1. Flexibility: Code is less tied to specific types, enabling reuse with different objects.
2. Reduced Dependency: Functions rely on behavior rather than class inheritance.
3. Polymorphism: Multiple classes can provide the same interface, allowing uniform handling of different objects.

Challenges with Duck Typing

Because duck typing avoids explicit type checking, errors are only detected at runtime.

Example of an Error:

class Fish:
    def swim(self):
        return "I am swimming!"

# Fish lacks a 'speak' method.
make_animal_speak(Fish())  # AttributeError: 'Fish' object has no attribute 'speak'

Safer Duck Typing

To avoid runtime errors, you can combine duck typing with hasattr or modern type hinting mechanisms like Protocol.

def make_animal_speak_safe(animal):
    if hasattr(animal, 'speak') and callable(animal.speak):
        print(animal.speak())
    else:
        raise TypeError("Object does not implement the 'speak' method!")

Or, using Protocol for type safety:

from typing import Protocol

class Animal(Protocol):
    def speak(self) -> str:
        pass

def make_animal_speak(animal: Animal):
    print(animal.speak())

Which One Should I Choose?

• Use Duck Typing when you prioritize simplicity and flexibility. It’s ideal for small projects or quick prototypes where enforcing strict rules is unnecessary. However, it lacks static type safety and can lead to runtime errors if misused.
• Use ABC when you need runtime enforcement of interface rules. It’s useful for critical applications where strict compliance with a defined interface is required, and you want to prevent instantiation of incomplete classes.
• Use Protocol for static type checking in modern Python projects. It’s the best choice when working with tools like mypy or when you want to combine the flexibility of duck typing with type safety.

While Protocol Pattern and interfaces share some conceptual similarities, Python’s dynamic nature often makes duck typing sufficient for many use cases. Choose the approach based on the complexity, size, and type-safety requirements of your project.

Originally developed for Java and later adapted for Python, testcontainers has quickly become a popular choice for managing integration test dependencies efficiently and reliably. It ensures tests are realistic and maintainable by mirroring real environments, leading to more reliable results and greater confidence in your software's performance.

Prerequisites

• Python: Make sure Python 3.8+ is installed.
• Docker: Ensure Docker is installed and running to allow testcontainers to manage MySQL containers.

Example Scenario: Exporting a `.sql` File and Testing with MySQL

In this example, we will:

1. Create an endpoint that exports a `.sql` file containing table creation and insertion commands.

2. Use `testcontainers` to create a temporary MySQL container.

3. Test that the `.sql` file content is compitable and correctly executed within the MySQL container.

Step 1: Define the SQL Export Functionality

To start, create an endpoint to export a .sql file. We’ll use FastAPI to define an endpoint that generates the file with basic SQL commands to create a table and insert data.

# router.py
from fastapi import APIRouter
from fastapi.responses import FileResponse
import tempfile

router = APIRouter()

def generate_sql_content() -> str:
    return '''
    CREATE TABLE example_table (
        id INT PRIMARY KEY,
        name VARCHAR(100),
        age INT
    );

    INSERT INTO example_table (id, name, age) VALUES
    (1, 'Alice', 25),
    (2, 'Bob', 30),
    (3, 'Charlie', 35);
    '''

def create_temp_sql_file(content: str, suffix: str = ".sql") -> str:
    with tempfile.NamedTemporaryFile(delete=False, suffix=suffix) as temp_file:
        temp_file.write(content.encode("utf-8"))
        return temp_file.name

@router.get("/export")
async def export_sql() -> FileResponse:
    file_path = create_temp_sql_file(generate_sql_content())
    return FileResponse(path=file_path, media_type="application/sql", filename="exported_data.sql")

Step 2: Create the MySQL Container Fixture Using Testcontainers

To set up MySQL for testing, we’ll create a pytest fixture using testcontainers. The fixtures will:

1. Start a MySQL container before tests.

2. Provide connection parameters to interact with the container.

# conftest.py
import pytest
from testcontainers.mysql import MySqlContainer
from mysql.connector.connection import MySQLConnection
import mysql.connector
from typing import Generator, Callable, cast

@pytest.fixture(scope="session")
def mysql_container() -> Generator[MySqlContainer, None, None]:
"""Starts and provides a MySQL container instance, scoped to the session."""
    with MySqlContainer("mysql:8.0") as mysql:
        yield mysql

@pytest.fixture(scope="session")
def get_db_connection(mysql_container: MySqlContainer) -> Callable[[], MySQLConnection]:
"""Provides a function to establish a connection to the MySQL container."""
    def _connect() -> MySQLConnection:
        connection = mysql.connector.connect(
            host=mysql_container.get_container_host_ip(),
            port=mysql_container.get_exposed_port(3306),
            user=mysql_container.username,
            password=mysql_container.password,
            database=mysql_container.dbname,
        )
        return cast(MySQLConnection, connection)

    return _connect

Step 3: Write the Test for SQL Export and Execution

Now that we have the export endpoint and MySQL container ready, let’s write a test to:

1. Download the `.sql` file from the endpoint.

2. Execute the SQL file in the MySQL container.

3. Verify the table and data.

# test_export_sql.py
import mysql.connector
from fastapi.testclient import TestClient
import os
from testcontainers_fastapi.main import app
from typing import Callable

client = TestClient(app)

def download_sql_file(endpoint: str = "/export", filename: str = "exported_data.sql") -> str:
    response = client.get(endpoint)
    assert response.status_code == 200
    with open(filename, "wb") as f:
        f.write(response.content)
    return filename

def execute_sql_file(cursor: mysql.connector.cursor.MySQLCursor, sql_file_path: str) -> None:
    with open(sql_file_path, "r") as file:
        sql_commands = file.read().split(";")
        for command in sql_commands:
            if command.strip():
                cursor.execute(command)

def test_export_sql_to_mysql(get_db_connection: Callable[[], mysql.connector.connection.MySQLConnection]) -> None:
    sql_file_path = download_sql_file()

    connection = get_db_connection()
    try:
        cursor = connection.cursor()
        execute_sql_file(cursor, sql_file_path)
        connection.commit()

        cursor.execute("SELECT * FROM example_table;")
        rows = cursor.fetchall()
        expected_data = [(1, "Alice", 25), (2, "Bob", 30), (3, "Charlie", 35)]
        assert rows == expected_data

    finally:
        cursor.close()
        connection.close()
        os.remove(sql_file_path)

Conclusion

This setup shows how you can use testcontainers in Python to create an isolated MySQL instance for testing a `.sql` file export functionality. By using `testcontainers`, you can quickly spin up and tear down containers for each test session, making integration testing both reliable and convenient.

Full example you can find here: https://github.com/ozenck/testcontainers_fastapi