Forem: José Thomaz

Using NoSQL Features in PostgreSQL

José Thomaz — Wed, 18 Jun 2025 13:32:40 +0000

Do you really need to use NoSQL databases? Or can a relational database satisfy your semi-structured data requirements?

PostgreSQL is a very complete and popular relational database, but did you know that it also has several functionalities of a non-relational database?

How does PostgreSQL support non-relational data?

PostgreSQL supports non-relational data in 3 different ways:

XML
JSON
JSONB

XML

PostgreSQL supports XML through a native xml data type. You can store and query XML data using XPath.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  data XML
);

INSERT INTO products (data)
VALUES (
  '<product><name>Smartphone</name><price>999.99</price></product>'
);

-- Extract the product name using XPath
SELECT
  xpath('//name/text()', data) AS name
FROM products;

XML support is useful when you're integrating with legacy systems or external APIs that still use XML formats. But it's not very efficient compared to JSON for most modern use cases.

JSON

PostgreSQL introduced native support for the json data type to store plain text JSON structures. It's great for storing semi-structured data.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  profile JSON
);

INSERT INTO users (profile)
VALUES (
  '{
    "name": "John Doe",
    "age": 21,
    "skills": ["golang", "typescript", "clickhouse"]
  }'
);

-- Querying JSON data
SELECT
  profile->>'name' AS name,
  profile->'skills' AS skills
FROM users;

The json type preserves the original formatting, including whitespace and ordering of keys, which is good for logging or raw data storage. However, it’s not the most efficient format for querying, even though writes are fine.

JSONB

jsonb stands for binary JSON. It stores JSON in a binary format that's optimized for indexing and querying. If you're going to filter or search data inside JSON structures, prefer jsonb.

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  metadata JSONB
);

INSERT INTO events (metadata)
VALUES (
  '{
    "type": "click",
    "user_id": 42,
    "timestamp": "2025-06-18T09:00:00Z"
  }'
);

-- Filter events by user_id
SELECT *
FROM events
WHERE metadata->>'user_id' = '42';

-- Create a GIN index for better performance
CREATE INDEX idx_metadata ON events USING GIN (metadata);

jsonb allows indexing with GIN (Generalized Inverted Indexes), which makes querying large JSON documents much faster. It also removes duplicate keys and ignores whitespace. If GIN indexing doesn't solve your case, you can use B-Tree and Hash indexes as well; however, they might not be extremely useful.

Final Comparison

To help you decide which one to use, here's a table comparing these three NoSQL in Postgres:

Feature	JSON	JSONB	XML
Data type	Text	Binary format	Text
Storage efficiency	Less efficient	More efficient	Less efficient
Read performance	Slower	Faster	Slower
Write performance	Faster (no parsing on insert)	Slower (parsing + conversion)	Slower
Index support	Limited	Supports GIN, B-Tree and Hash	Functional indexes only
Key order preserved	✅ Yes	❌ No	✅ Yes
Duplicate keys allowed	✅ Yes	❌ No (last key kept)	✅ Yes
Schema enforcement	❌ None	❌ None	❌ None
Ideal use case	Logging, preserving raw data	Querying, filtering, indexing	Legacy systems, XML APIs

Conclusion

PostgreSQL has a solid toolset for handling semi-structured data, making it extremely suitable for performing as a NoSQL database when needed. For this reason, if you are planning a new system that will require semi-structured data processing, you should consider using PostgreSQL if it's already part of your team's tech stack. It can be a very good choice without increasing complexity or introducing a new element with a steeper learning curve.

It's important to notice that there are many cases where you will need an actual NoSQL database, like MongoDB, Cassandra, or Scylla, etc. So, remember to evaluate each one's strengths and weaknesses before making your architecture decision.

[Boost]

José Thomaz — Mon, 16 Jun 2025 20:19:42 +0000

Pedro H Goncalves

Jun 16 '25

Diagnosing and fixing critical PostgreSQL performance issues: A deep dive

#database #postgres #advanced #performance

Comments 1

14 min read

Forecasting the Dollar rate with Python

José Thomaz — Thu, 15 May 2025 15:29:53 +0000

José Thomaz

May 15 '25

Why is it so hard to forecast the Dollar rate? An attempt with Python

#datascience #economics #math #python

Comments

10 min read

Why is it so hard to forecast the Dollar rate? An attempt with Python

José Thomaz — Thu, 15 May 2025 14:36:55 +0000

As a Brazilian working for a foreign company, I'm always keeping an eye on the USD to BRL exchange rate to plan my finances for the month. In the first few months, I tried predicting the dollar rate on my payday using all sorts of methods: checking forecasts from financial institutions, looking into futures, using statistics, but none of them really worked. The value was always different from what I expected, sometimes higher, sometimes lower.

Because of that, I decided to write a post about time series forecasting. I wanted to dig deeper into why it's so hard to make accurate predictions and share some of the insights I’ve learned along the way.

3 rules to know if something can be predicted

Not everything can be forecasted, some assumptions need to be met.

Rule 1: We need to know and understand the factors that affect the indicator that we are trying to predict

Condition matched for Dollar Rate forecasting? ❌

The dollar rate is influenced by dozens of factors, and worse, many of them are unpredictable, or we simply don't know whether a change will push the rate up or down. Interest rates, inflation, political stability, commodity prices, global financial crises, wars, elections... the list goes on.

Even if you could model some of these factors (like Central Bank decisions or inflation rates), sudden geopolitical events or unexpected government actions can easily throw any forecast completely off track. We simply don't have access to all the variables at play.

Rule 2: There must be enough data available

Condition matched for Dollar Rate forecasting? ✅

This condition is certainly matched, since we have lots of historical data on the USD Rate against most of the currencies of the world. When we want to forecast something, it's important to have enough data, so we can correctly identify patterns, trends, and seasonality.

Rule 3: The forecast itself should not change the indicator value

Condition matched for Dollar Rate forecasting? ❌

For a forecast to be reliable, the act of making the prediction itself shouldn't influence the result. If everyone changes their behavior based on the forecast, the forecast stops being valid.

Imagine a big and respected financial institution, like Goldman Sachs, publishing a report saying:

"We expect the dollar to rise by 5% over the next month against the BRL."

Investors and companies don't want to miss the opportunity.
As soon as the report is published, there’s a rush to buy dollars, creating extra demand and making the dollar rise immediately.
Very likely, the price will reach — or even exceed — the predicted 5% rise before the month ends.
At that point, the original forecast becomes flawed: reality has already changed because of the forecast itself.

This is a classic effect described by the Efficient Market Hypothesis (EMH): In efficient markets, as soon as new information appears, prices adjust immediately. You can’t profit easily from forecasts, because once a prediction is public, the market incorporates it into the price.

Trying anyway

As we saw, only one of the three conditions was met, so we can assume that forecasting the Dollar Rate with precision is not possible, or at least extremely difficult.

But, since we are already here, let's try it anyway.

How to forecast time series data?

The first step in forecasting time series data is choosing a model.
The most popular models are:

Moving Averages: smooth out short-term fluctuations to highlight longer-term trends or cycles.
ARIMA (AutoRegressive Integrated Moving Average): combines autoregression, differencing (to make data stationary), and moving averages to predict future values.
SARIMA (Seasonal ARIMA): an extension of ARIMA that also models seasonality patterns (like peaks that happen every year or month).
Prophet: developed by Facebook, designed to handle time series with strong seasonal effects and holidays, with a more intuitive and flexible configuration.
LSTM (Long Short-Term Memory networks): a type of recurrent neural network (RNN) capable of learning long-term dependencies, often used for complex time series where classical models struggle.

For this article, let's proceed with Prophet, since it's a very reliable, modern, and easy-to-use model. You can try with more sophisticated models if you want, such as custom LSTMs, NeuralProphet, and others.

Prophet

Prophet is an open-source library developed by Facebook designed to simplify the process of forecasting time series data. It has some assumptions:

The data must have seasonality
It assumes that the data has a trend, and that this trend can be shifted at different points in time

Currencies usually have a trend, their movement is not purely random, which is good for our model. However, the seasonality of currencies is not so simple, there are many factors involved. Despite this complexity, historical data reveals certain seasonal behaviors. For instance, the U.S. dollar typically experiences weakness in December, often attributed to year-end tax strategies by U.S. companies, and tends to reverse the performance in January, with a good performance.

Config

Let's begin by defining some parameters for our model, such as the date range for our prediction, the API KEY for the FRED API, which is where we will get data about the US interest rates, and some other info related to holidays and special dates.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
from datetime import datetime, timedelta
import requests
from prophet import Prophet
from fredapi import Fred
from prophet.make_holidays import make_holidays_df

# ====== CONFIG ======
FRED_API_KEY = os.getenv("FRED_API_KEY")

end_date = datetime.today()
start_date = end_date - timedelta(days=365 * 3)
start_str = start_date.strftime('%d/%m/%Y')
end_str = end_date.strftime('%d/%m/%Y')
start_iso = start_date.strftime('%Y-%m-%d')
end_iso = end_date.strftime('%Y-%m-%d')

url_usdbrl = f"https://api.bcb.gov.br/dados/serie/bcdata.sgs.1/dados?formato=json&dataInicial={start_str}&dataFinal={end_str}"
url_brazil_interest_rates = f"https://api.bcb.gov.br/dados/serie/bcdata.sgs.1178/dados?formato=json&dataInicial={start_str}&dataFinal={end_str}"

# COPOM is the committee that defines Brazil's interest rates
copom_meeting_dates = [
    # 2025
    "2025-06-18", "2025-05-07", "2025-03-19", "2025-01-29",
    # 2024
    "2024-12-11", "2024-11-06", "2024-09-18", "2024-07-31",
    "2024-06-19", "2024-05-08", "2024-03-20", "2024-01-31",
    # 2023
    "2023-12-13", "2023-11-01", "2023-09-20", "2023-08-02",
    "2023-06-21", "2023-05-03", "2023-03-22", "2023-02-01",
    # 2022
    "2022-12-07", "2022-10-26", "2022-09-21", "2022-08-03",
    "2022-06-15", "2022-05-04", "2022-03-16", "2022-02-02"
]
fed_meeting_dates = [
    # 2025
    "2025-12-17", "2025-11-05", "2025-09-17", "2025-07-30",
    "2025-06-18", "2025-05-07", "2025-03-19", "2025-01-29",
    # 2024
    "2024-12-11", "2024-11-06", "2024-09-18", "2024-07-31",
    "2024-06-12", "2024-05-01", "2024-03-20", "2024-01-31",
    # 2023
    "2023-12-13", "2023-11-01", "2023-09-20", "2023-07-26",
    "2023-06-14", "2023-05-03", "2023-03-22", "2023-02-01",
    # 2022
    "2022-12-14", "2022-11-02", "2022-09-21", "2022-07-27",
    "2022-06-15", "2022-05-04", "2022-03-16", "2022-01-26"
]
us_holidays = make_holidays_df(
    year_list=[2019 + i for i in range(10)], country='US'
)
br_holidays = make_holidays_df(
    year_list=[2019 + i for i in range(10)], country='BR'
)

Fetching the historical data for USD-BRL

response = requests.get(url_usdbrl)
data_usdbrl = response.json()

df_usdbrl = pd.DataFrame(data_usdbrl)
df_usdbrl['ds'] = pd.to_datetime(df_usdbrl['data'], format='%d/%m/%Y')
df_usdbrl['y'] = df_usdbrl['valor'].astype(float)
df_usdbrl = df_usdbrl[['ds', 'y']]

Adding regressors to enhance our model's performance

A regressor can be any variable in a regression model that is used to predict a response variable. Prophet allows us to add multiple regressors, so we will have 2:

Interest Rates
Central Bank Meeting Dates

*We also have the holidays, but they are not considered external regressors, since they are "part of the equation".

Interest Rates

# ====== GETTING INTEREST RATES FROM BRAZIL ======
response = requests.get(url_brazil_interest_rates)
data_selic = response.json()

df_selic = pd.DataFrame(data_selic)
df_selic['ds'] = pd.to_datetime(df_selic['data'], format='%d/%m/%Y')
df_selic['selic'] = df_selic['valor'].astype(float)
df_selic = df_selic[['ds', 'selic']]

# Fill missing dates if needed
today = pd.to_datetime(datetime.today().date())
if today not in df_selic['ds'].values:
    last_selic = df_selic.sort_values('ds')['selic'].iloc[-1]
    new_row = pd.DataFrame({'ds': [today], 'selic': [last_selic]})
    df_selic = pd.concat([df_selic, new_row], ignore_index=True)
    df_selic = df_selic.sort_values('ds').reset_index(drop=True)

# ====== GETTING INTEREST RATES FROM US ======
fred = Fred(api_key=FRED_API_KEY)
fedfunds = fred.get_series('FEDFUNDS', observation_start=start_iso, observation_end=end_iso)

df_fedfunds = fedfunds.reset_index()
df_fedfunds.columns = ['ds', 'fedfunds']
df_fedfunds['ds'] = pd.to_datetime(df_fedfunds['ds'])
df_fedfunds['fedfunds'] = df_fedfunds['fedfunds'].astype(float)
full_dates = pd.DataFrame({'ds': pd.date_range(start=df_fedfunds['ds'].min(), end=df_selic['ds'].max())})
df_fedfunds = full_dates.merge(df_fedfunds, on='ds', how='left')
df_fedfunds['fedfunds'] = df_fedfunds['fedfunds'].ffill()

# ====== MERGING IT ALL ======
df = df_usdbrl.merge(df_selic, on='ds', how='left')
df = df.merge(df_fedfunds, on='ds', how='left')

Central Bank Meeting Dates

# ====== CREATE DATAFRAME WITH MEETINGS DATES ======
copom_df = pd.DataFrame(copom_meeting_dates, columns=["ds"])
copom_df["ds"] = pd.to_datetime(copom_df["ds"])
fed_df = pd.DataFrame(fed_meeting_dates, columns=["ds"])
fed_df['ds'] = pd.to_datetime(fed_df['ds'])

all_meetings_df = pd.concat([copom_df, fed_df]).drop_duplicates().sort_values("ds").reset_index(drop=True)

# ====== CATEGORIZE IF IT'S NEAR A CENTRAL BANK MEETING ======
def is_near_central_bank_meeting(date, days_before=5, days_after=1):
    date = pd.to_datetime(date)
    for meeting_date in all_meetings_df['ds']:
        if -days_before <= (date - meeting_date).days <= days_after:
            return 1
    return 0

df['near_cb_meeting'] = df['ds'].apply(is_near_central_bank_meeting)

Setting up the Holidays

# Add a new column to distinguish between the two countries
us_holidays['country'] = 'US'
br_holidays['country'] = 'BR'

# Concatenate the two DataFrames into a single one
combined_holidays = pd.concat([us_holidays, br_holidays], ignore_index=True)

Instantiate the model and plug the regressors

Now we enter our final step: create the model, plug the regressors we have previously created, and then run it to forecast the dollar rate for the next 45 days.

model_prophet = Prophet(
    changepoint_range=0.9,
    changepoint_prior_scale=0.1,
    yearly_seasonality=True,
    weekly_seasonality=True,
    daily_seasonality=False,
    holidays=combined_holidays
)
model_prophet.add_regressor('near_cb_meeting')
model_prophet.add_regressor('selic')
model_prophet.add_regressor('fedfunds')
model_prophet.fit(df)

# ====== FORECAST ======
future = model_prophet.make_future_dataframe(periods=45)
future['near_cb_meeting'] = future['ds'].apply(is_near_central_bank_meeting)

last_selic = df['selic'].ffill().iloc[-1]
last_fedfunds = df['fedfunds'].ffill().iloc[-1]
future = future.merge(df[['ds', 'selic', 'fedfunds']], on='ds', how='left')
future['selic'].fillna(last_selic, inplace=True)
future['fedfunds'].fillna(last_fedfunds, inplace=True)

forecast_prophet = model_prophet.predict(future)

Interpreting the results

To better interpret the results, it is essential that we visualize them, so let's plot some charts:

Forecast of the USD/BRL Exchange Rate Considering Central Bank Meetings

The chart above shows the forecast of the USD/BRL exchange rate using the Prophet model. The black dots represent historical exchange rate values. The blue line in the middle is the main prediction made by the model for each day, it’s not a trend, but the central estimate of where the exchange rate is expected to be.

The shaded blue area around the line shows the uncertainty in the forecast. It gives us an idea of how much the exchange rate could go up or down at this point in time, based on the model's confidence (80% by default). The further into the future, the wider this area gets, reflecting more uncertainty.

The dashed orange vertical lines mark the dates of Central Bank meetings in Brazil and the US, which can have a strong impact on the exchange rate, especially if there are changes in interest rates or economic outlook.

See the code for this chart here

USD/BRL Trend

The chart above shows the underlying trend of the USD/BRL exchange rate over time, extracted using Prophet’s trend component. The black line represents the observed exchange rate, while the blue line shows the long-term movement of the exchange rate, smoothing out short-term fluctuations.

We can clearly see that forecasting the trend gives us much more accurate results than trying to predict the exact dollar rate at a specific point in time. The trend, if well modeled, won't be affected by short-term fluctuations and outliers. By visualizing the trend, it becomes easier to identify structural movements over time.

See the code for this chart here

Validating the Results

To assess the accuracy of the USD/BRL forecast model, we performed cross-validation using Prophet. The training window was set to two years, and new forecasts were generated every 15 days for a 45-day horizon.

The chart above presents the error metrics across the forecast horizon:

MAE (Mean Absolute Error) – shown in blue – quantifies the average absolute difference between predicted and actual values. It increases steadily with the horizon, reflecting the growing difficulty of long-term forecasting.
MAPE (Mean Absolute Percentage Error) – shown in black – it is like MAE, but in percentage, showing a percentage of how much the values differ. We can see that it is under 5% most of the time, which is excellent.
RMSE (Root Mean Squared Error) – shown in green – gives more weight to larger errors. It remains the second lowest among the four metrics, but still shows a clear upward trend.
MSE (Mean Squared Error) – shown in red – grows consistently and sharply across the forecast window, indicating increasing squared deviations from actual values.

This diagnostic confirms a common behavior in time series forecasting: prediction errors tend to increase as we forecast further into the future. The consistent upward trajectory across all metrics reinforces the need to interpret longer-horizon forecasts with more caution.

Limitations of forecast accuracy in the face of unpredictable events

Even though the error metrics (MAE, MAPE, RMSE, MSE) for short-term horizons like 5, 10, or 15 days ahead are relatively low, indicating that the model performs well under normal conditions, it is essential to recognize the inherent unpredictability of exchange rate dynamics, particularly for the USD/BRL pair.

Exchange rates are highly sensitive to macroeconomic shocks and geopolitical events, many of which are impossible to anticipate using historical patterns alone. For example:

A foreign government could announce a major monetary policy shift (e.g., unexpected interest rate cuts, capital controls, or intervention in currency markets) over a weekend, while markets are closed. When trading resumes, the exchange rate can experience a sudden gap that no model can foresee.
Similarly, unforeseen global events, such as military conflicts, health crises, or abrupt changes in commodity prices, can trigger volatility spikes that exceed the bounds of even the most accurate statistical forecasts.

These scenarios expose that we cannot capture "black swan" events or model the reactions of market participants to breaking news.

How to improve the model?

The model we built in this article isn't perfect, and surely it can be improved, but how? Here are some suggestions for you to try by yourself:

Gather news and perform sentiment analysis on social media to understand how the market is reacting
Track commodities prices as a regressor
Try to replace Prophet with NeuralProphet or a custom LSTM
Add other economic indicators like inflation, or unemployment
Combine different models and compare their performance

There’s a lot to explore here, and testing different approaches is part of the fun.

You can check the full code here: https://github.com/josethz00/dollar-forecast

References

Benchmark: Snowflake vs UUIDv4

José Thomaz — Sat, 17 Feb 2024 20:10:48 +0000

This benchmark compares the performance of Snowflake and UUID. It was built using Go, Docker, SQLx, and PostgreSQL.

The graphs below were generated using Python3.

It is important to notice that, this is a micro-benchmark. An optimization that will be worth only in specific scennarios. As you read the article, you will see that many times the optimization is of some miliseconds/microseconds.

Understanding the benchmark

Go is the main programming language used for the benchmark. Each ID type (Snowflake or UUID) has its own .go file for running the benchmark separately. The benchmarked operations include:

SELECTION (SELECT * FROM)
ORDERED SELECTION (SELECT ORDER BY)
ID GENERATION
ID GENERATION + INSERT
ID SEARCH (SELECT WHERE)
UPDATE

For each benchmark, 200k IDs are generated (either Snowflake or UUID) before performing each benchmark operation. To reduce variability, the database is truncated after each benchmark run, and 30 iterations are performed to obtain a larger and more statistically significant sample.

How to run?

Install Go
Install Docker
Install Python3
Run the following commands:



docker-compose up -d

and then, run the Snowflake benchmark:



./run-snowflake.sh

or the UUID benchmark:



./run-uuid.sh

How to analyze the data

After running the benchmarks, you can analyze the data by running the following command:



python3 benchmark_analysis.py

this will generate the graphs and print the data analysis. The generated graphs will be saved in the images folder. The data from the benchmarks will be saved in the reports folder.

Results

Now, let's interpret the results!

ID Generation

For ID Generation, UUIDs have very high variance and standard deviation, in counterpart Snowflakes were pretty stable during the 30 benchmark iterations.

____	UUIDv4	SnowflakeID
Mean	103.4446	49.0516
Variance	987.8148	0.3929
Std Deviation	31.4295	0.6268

ID Insertion

For ID Insertion, UUIDs have very high variance and standard deviation, the same happened with SnowflakeIDs, but with lower values, performing the operations in fewer milliseconds.

____	UUIDv4	SnowflakeID
Mean	78328.77	58455.25
Variance	27940797.04	5445730.74
Std Deviation	5285.90	2333.60

ID Selection

For ID Selection, UUIDs and Snowflake IDs had very high variance and standard deviation. Also, the mean wasn't that different, showing that Snowflake IDs are indeed faster than UUIDs but by a small margin.

____	UUIDv4	SnowflakeID
Mean	192.1073	181.9380
Variance	59.0411	38.1682
Std Deviation	7.6838	6.1780

ID Search (WHERE)

For ID Search, the results were very similar to ID Selection, with the margin being even smaller. For this operation, the variance and standard deviation can be considered low for both ID types.

____	UUIDv4	SnowflakeID
Mean	1.0309	0.9924
Variance	0.1609	0.0223
Std Deviation	0.0259	0.1494

Ordered Selection (SELECT + ORDER BY)

For Ordered Selection, Snowflake IDs performed much better than UUIDs, having lower values for mean, variance, and standard deviation. UUIDs had a quite high variance value.

____	UUIDv4	SnowflakeID
Mean	276.9748	179.8317
Variance	145.4764	19.3050
Std Deviation	12.0613	4.3937

Update

For the UPDATE operation, the results were very similar to the ID Search: Snowflake IDs performed a little bit better but by a very close margin. In this case, Snowflake IDs had a higher variance and standard deviation if compared to UUIDs.

____	UUIDv4	SnowflakeID
Mean	4.6903	4.1918
Variance	1.4904	2.2434
Std Deviation	1.2208	1.4978

Validating Results

The results were validated by running the benchmarks multiple times (30), calculating the average and the standard deviation, and running a t-test to compare the means of the two groups. The t-test was performed with a 95% confidence level.

T-Test

T-tests are very good for saying if a benchmark or an optimization is significant, or relevant. For that, we use a 95% confidence level, which means that if the result of your T-test is within 0.000 (~100%) and 0.05 (95%), it lies in a high confidence interval, so you can state that your optimization is TRUE and relevant!

We can see that for UPDATE and ID SEARCH, the performance gap between Snowflake and UUID is not relevant according to the T-test. At the same time, we can notice that for all the other four operations benchmarked, the difference is evident and very clear. Snowflake IDs with a better overall in ordered selection, ID generation, and insertion.

Why is UUID's variance so high?

UIDs, with their design focus on randomness, inherently exhibit high variance in generation times. Here’s a concise breakdown:

Randomness Source: UUIDs depend on a pool of entropy for random number generation. Fluctuations in this pool's availability can lead to variability in creation time.
System Factors: The system’s workload and resource allocation can also impact the speed of UUID generation, causing inconsistent timing.
Inherent Entropy: With 122 bits dedicated to randomness out of 128, a UUID is highly entropic by nature, leading to intrinsic variability.

These factors combined mean that UUID generation can be a process with variable timing, reflecting the complex interplay between system entropy and resource demands.

Conclusion 📝

Is UUIDv4 bad?

No way! UUIDs are excellent and very useful. UUIDs are perfect for huge distributed systems and for scenarios where uniqueness is the most important factor. Even though those are the main strengths of UUIDS, they are also its main weaknesses. UUIDs are purely random and unique, making them unpredictable, unsortable, and too big. It can be a problem sometimes.

When to use each?

UUIDs (v4): if you want simplicity, easy setup, and compatibility, at the same time your system doesn't expect a very high throughput; use UUIDs. A UUIDv4 has a size of 128 bits.

Snowflake IDs: Snowflakes are an "enhanced" BigInt, so if you are willing to improve performance, sort your data, and maintain secure public IDs; use Snowflake. A SnowflakeID has a size of 64 bits.

To improve ✨

This benchmark isn't perfect and has some points to improve and to cover in the next version. Some of them:

Concurrent testing
Test in different databases besides Postgres
Benchmark other UUID versions (ULID, UUIDv7)
Display results in req/s (requests per second)
Use even larger samples
Measure the disk used by each

Links 🔗

Github Repo

Gist about UUIDs performance

Preparing and Evaluating Benchmarks

Math for Devs - Gambler's fallacy and Monte Carlo Simulation

José Thomaz — Sat, 20 Jan 2024 19:14:11 +0000

Understanding the Gambler's Fallacy

The Gambler's Fallacy, often referred to as the Monte Carlo Fallacy, is a common misconception related to probability and independent events. It's the erroneous belief that if an event occurs more frequently than normal over a certain period, it's less likely to happen in the future. Despite its prevalence, this line of thinking is fundamentally flawed.

Everyday Examples of the Gambler's Fallacy

Let's delve into some everyday instances where the Gambler's Fallacy might surface:

Sports: How often have we thought, "This team hasn't won in their last 10 matches; they can't keep losing forever. They're due for a win soon, maybe in the next game"?
Casinos: This is a classic example, and we'll explore it more in-depth later in this article.
Lottery Numbers: "I'm picking number 23 for sure. It hasn't come up in the last 30 weeks, so it must be due soon, right?"

The Historical Context

The Gambler's Fallacy is a fascinating phenomenon that finds its roots in the world of gambling, a domain where luck and chance reign supreme. This fallacy, also known as the "Monte Carlo Fallacy," is named after a notorious event that occurred at the Monte Carlo Casino in 1913. On this day, the roulette wheel landed on black 26 times in a row, an incredibly rare event. Gamblers lost millions betting against blacks, believing that red was due to occur next. This belief stems from the Gambler's Fallacy, the mistaken idea that past random events can influence future ones in a purely random process.

The probability of a sequence of either red or black occurring 26 times in a row is around 1 in 66.6 million. So the gamblers kept thinking "somehow the blacks gotta stop", and it took 26 times to stop, until then, they lost lots of money, and the casino became even richer.

But why is this a fallacy?

The core of the Gambler's Fallacy lies in the misunderstanding of independence in probability. Take coin tosses, for example. If you flip a coin and get tails three times in a row, you might think heads is "due" next. However, the probability remains 50/50. The coin doesn't remember its past flips; each toss is independent, and the universe doesn't conspire to balance the outcomes. It's all random, with each flip having an equal chance of being heads or tails.

Demonstrating the Fallacy with Python

To illustrate the Gambler's Fallacy, let's simulate a roulette wheel using Python. This code will show how the outcomes of each spin are independent of each other:

import random

def roulette_spin():
    return 'black' if random.randint(0, 1) == 0 else 'red'

def simulate_spins(num_spins):
    results = {'black': 0, 'red': 0}
    for _ in range(num_spins):
        result = roulette_spin()
        results[result] += 1
    return results

# Simulate 100 spins
spin_results = simulate_spins(100)
print(f"After 1000 spins: {spin_results}")

Monte Carlo Simulation

The Gambler's Fallacy and that memorable event at the Monte Carlo Casino actually led to something pretty cool – the Monte Carlo Simulation. Invented in the 1940s by John von Neumann and Stanislaw Ulam, this method is all about running a bunch of simulations, or iterations, of an event we want to predict. These simulations are key for bringing in randomness and getting rid of outliers. Let's dive into this with the same example of coin tosses, and see how it plays out in a Monte Carlo Simulation:

import random

def roulette_spin():
    return 'black' if random.randint(0, 1) == 0 else 'red'

def simulate_spins(num_spins):
    results = {'black': 0, 'red': 0}
    for _ in range(num_spins):
        result = roulette_spin()
        results[result] += 1
    return results

num_of_simulations = int(input("How many simulations do you want to run? "))
spin_results = simulate_spins(num_of_simulations)
print(f"After {num_of_simulations} spins: BLACK: {spin_results['black']/num_of_simulations*100}% RED: {spin_results['red']/num_of_simulations*100}%")

The code was adapted to run multiple simulations, to reduce the outliers and get a closer result to the real probabilities. That's what Monte Carlo Simulation is about: getting the real probabilities of an event by running it many times.

OBS: NOTICE THAT THE BIGGER THE NUMBER OF SIMULATIONS, MORE ACCURATE IS THE RESULT.

5 coin flips

How many simulations do you want to run? 5
After 5 spins: BLACK: 0.0% RED: 100.0%

10 coin flips

How many simulations do you want to run? 10
After 10 spins: BLACK: 70.0% RED: 30.0%

100 coin flips

How many simulations do you want to run? 100
After 100 spins: BLACK: 61.0% RED: 39.0%

1000 coin flips

How many simulations do you want to run? 1000
After 1000 spins: BLACK: 48.3% RED: 51.7%

10000 coin flips

How many simulations do you want to run? 10000
After 10000 spins: BLACK: 49.53% RED: 50.470000000000006%

100000 coin flips

How many simulations do you want to run? 100000
After 100000 spins: BLACK: 49.85% RED: 50.14999999999999%

1 million coin flips

How many simulations do you want to run? 1000000
After 1000000 spins: BLACK: 49.9431% RED: 50.056900000000006%

1 billion coin flips

How many simulations do you want to run? 1000000000    
After 1000000000 spins: BLACK: 49.994978% RED: 50.005022%

Fastify + NestJS file upload

José Thomaz — Sat, 28 Oct 2023 17:02:51 +0000

We all know that Fastify is faster than Express, and for this reason, many people are switching from the Express micro-framework to Fastify. However, sometimes you might want to build a more robust system with better tooling. In such cases, you can choose between Meteor, Adonis, or NestJS. Many developers opt for NestJS because it is reminiscent of SpringBoot from Java and is built on top of Express. However, it also offers compatibility with Fastify, providing developers with the best of both worlds.

I always choose NestJS with Fastify, it works incredibly well in most cases. However, even though NestJS offers full support for Fastify, not everything is rosy, uploading files can be a little bit tricky in a NestJS environment powered by Fastify. So, in this article, I am gonna teach you how to do this, as it is not present in the official docs.

Installing the dependencies

First, you need to install the necessary packages. You can do this using your preferred package manager. Here are the packages you need:

@fastify/multipart
@blazity/nest-file-fastify

To install the packages, run one of the following commands:

yarn add @fastify/multipart @blazity/nest-file-fastify

npm install @fastify/multipart @blazity/nest-file-fastify

Modify your `main.ts` file

Import the @fastify/multipart package
Register the package within Fastify

The code should look something like this:

// other imports
import multiPart from '@fastify/multipart';

/**
 * Main function
 */
async function bootstrap(): Promise<void> {
  const app = await NestFactory.create<NestFastifyApplication>(
    AppModule,
    new FastifyAdapter({
      logger: true,
    }),
  );

    await app.register(multiPart);
  const httpAdapterHost = app.get(HttpAdapterHost);

    // rest of the code ...

  await app.listen(3333, process.env.HOST);
}

Create a route for uploading files in a NestJS controller

Now, you need to create a controller with a route for handling file uploads. Here’s an example of how you can do it:

import {
  FileFieldsInterceptor,
  MemoryStorageFile,
  UploadedFiles,
} from '@blazity/nest-file-fastify';
import {
  Body,
  Controller,
  Post,
  UseInterceptors,
} from '@nestjs/common';

@Controller('example')
export class ExampleController {

    /**
   * Route for uploading files
   */
  @Post('/upload')
  @UseInterceptors(
    FileFieldsInterceptor([
      { name: 'image', maxCount: 1 },
      { name: 'imageTwo', maxCount: 1 },
    ])
  )
  async register(
    @Body() data: Record<string, unknown>,   // other data that you might want to pass along with the files
    @UploadedFiles()
    files: { image?: MemoryStorageFile[0]; imageTwo?: MemoryStorageFile[0] }
  ): Promise<void> {
    Object.values(files).forEach((file) => {
      this.s3Service.uploadFile(file[0]);
    });
  }

}

In this example, the @Post('/upload') route is set up to handle file uploads. The FileFieldsInterceptor is used to specify the expected file fields in the request. In this case, we expect two image files, image, and imageTwo, each with a maximum count of 1.

The uploaded files are then passed to the register method, where they can be processed as needed. In this example, the files are uploaded to an S3 service.

That’s it! Now you can send a multipart/form-data request to your NestJS server, and the files will be handled accordingly.

Speed up your PostgreSQL bulk inserts with COPY

José Thomaz — Mon, 02 Oct 2023 14:48:06 +0000

Have you ever had the task of migrating data from one database to another? Or even from one table to another? Or to make too many INSERTs all at once. If so, this article is for you: today we will discuss the COPY command. An amazing alternative to performing intensive writes to the database is very useful for data migration imports, and bulk inserts.

“I have to insert 1mi rows in X seconds”

Having to migrate/insert data in a few seconds is a very common requirement for a task. When people hear that, the first thing they will say to you is:

“Use a distributed processing tool to insert into the database”
“Add more CPU and RAM to the database”
“Use cache”
"Use a NoSQL database instead"
"Break down in microservices and put an Apache Kafka broker in front of the database”

Is this really necessary? Maybe not. For many cases, migration tools like the COPY command, are way faster than the options above, and also cheaper in terms of infrastructure. COPY is a native PostgreSQL command and its syntax is like this:



COPY table_name (column1, column2, ...) FROM '/path/to/data.csv';



COPY table_name (column1, column2, ...) FROM STDIN;

The COPY command in PostgreSQL is a powerful tool for performing bulk inserts and data migrations. It allows you to quickly and efficiently insert large amounts of data into a table.

By using the COPY command, you can avoid the need for distributed processing tools, adding more CPU and RAM to the database, or using a NoSQL database. It provides a simpler and more cost-effective solution for bulk inserts and data migrations.

So, if you have a task that requires inserting a large number of rows in a short amount of time, consider using the COPY command in PostgreSQL. It can significantly speed up your data migration and import processes.

Note: PostgreSQL 16 has improved COPY’s performance by over 300%.

How does COPY work internally?

Internally, the COPY command in PostgreSQL works by leveraging the server's file system. When you use the COPY command to import data from a file, PostgreSQL reads the file directly from the file system and writes the data directly to the table, bypassing many of the normal SQL processing steps.

This direct file-to-table transfer allows for much faster and more efficient data inserts compared to traditional INSERT statements. It also reduces the overhead on the database server, making it a preferred method for bulk inserts and data migrations.

But let’s go deeper to understand all the details.

COPY vs INSERT (multi-line)

Let’s see a quick comparison between COPY and INSERT commands, to understand why COPY performs better.

	COPY	INSERT (multi-line)
Logging	One log for the entire load	One log for each line/entry
Network	No latency, data is streamed	Latency between inserts
Parsing	Only one parsing operation	Parsing overhead
Transaction	Single transaction	Each insert statement is a separate transaction
Query Plan	Simpler query execution plan	Lots of different query execution plans

Summing up, COPY is faster because it reduces the overhead of logging, network latency, parsing, and transaction management compared to multi-line INSERT statements. It allows for a simpler query execution plan, resulting in faster and more efficient bulk inserts and data migrations. A tradeoff is that it requires direct access to the file system, so it may not be suitable for all scenarios where you need to insert data. Another tradeoff is durability, COPY generates few logs, and executes all in a single transaction, which makes it more risky.

Measuring performance

I did some code with Bun (yes, Bun), to compare the performances of INSERT and COPY loading heavy amounts of data to Postgres. Let’s see:




import { Client } from 'pg';
import fs from 'fs';
import { from as copyFrom } from 'pg-copy-streams';
import { pipeline } from 'node:stream/promises';

const client = new Client({
    host: 'localhost',
    port: 6472,
    user: 'copy-insert-db',
    password: 'copy-insert-db',
    database: 'copy-insert-db'
});

const numRecords = 2000000;
const data = Array.from({ length: numRecords }, (_, i) => [`data_${i}`, new Date().toISOString()]);

async function measureInsert() {
    const chunkSize = 1000;
    const startTime = Date.now();

    for (let i = 0; i < data.length; i += chunkSize) {
        const chunk = data.slice(i, i + chunkSize);
        const placeholders = chunk.map((_, index) => `($${index * 2 + 1}, $${index * 2 + 2})`).join(',');
        const values = chunk.flat();

        const insertQuery = `
            INSERT INTO test_table (data, time_added) VALUES ${placeholders}
        `;

        await client.query(insertQuery, values);
    }

    const duration = (Date.now() - startTime) / 1000;
    console.log(`Multi-line INSERT took: ${duration} seconds`);
}

async function measureCopy() {
    return new Promise<void>((resolve, reject) => {
        const writableStream = fs.createWriteStream("data.csv");

        writableStream.on("finish", async () => {
            try {
                const startTime = Date.now();
                const stream = fs.createReadStream("data.csv");
                const copyPsqlStream = client.query(copyFrom('COPY test_table (data, time_added) FROM STDIN WITH (FORMAT CSV)'));
                await pipeline(stream, copyPsqlStream);
                const duration = (Date.now() - startTime) / 1000;
                console.log(`COPY took: ${duration} seconds`);
                resolve();
            } catch (err) {
                reject(err);
            }
        });

        data.forEach(d => writableStream.write(d.join(',') + '\n'));
        writableStream.end();
    });
}

async function main() {
    await client.connect();

    await measureInsert();
    await measureCopy();

    await client.end();
}

main();

This code inserts 2mi rows at once into the database. Notice that, for multi-line inserts, there is a limit of 1000 lines per INSERT statement. If we run this code, we get:

Multi-line INSERT took: 37.187 seconds
COPY took: 10.088 seconds

Ramping up (5mi records)

Changing the code to load 5 million records, gives us the following result:

Multi-line INSERT took: 78.957 seconds
COPY took: 20.534 seconds

Ramping up (10mi records)

Now, with 10 mi records, we get:

Multi-line INSERT took: 134.412 seconds
COPY took: 36.965 seconds

We can see how absurdly fast is the COPY command, and why we should consider its use in favor of INSERT in some cases. But we can make COPY even faster, by parallelizing it with threads!

Improving COPY performance with multithreading

For our initial tests with 2 million records, we have:

COPY took: 5.915 seconds

Ramping up (5mi records)

Our parallelized COPY with 5 million records to insert, performs:

COPY took: 15.41 seconds

Ramping up (10mi records)

Our parallelized COPY with 10 million records to insert, performs:

COPY took: 21.19 seconds

Final results

I conducted a performance comparison using a simple plot generated with a notebook running Matplotlib. The results are as follows:

The graph clearly illustrates the superior efficiency of the COPY command in comparison to the INSERT command for this specific task. The difference in speed is quite remarkable.

Math for Devs - Birthday Paradox for random data generation

José Thomaz — Mon, 18 Sep 2023 13:59:54 +0000

The Birthday Paradox is a fascinating mathematical phenomenon, and it has significant implications for computer science and software development, especially in cryptography, hashing, and random data generation. It's a great application of probability in our day to day.

What is the Birthday Paradox?

The Birthday Paradox refers to the counterintuitive probability that in a set of just 23 people, there's a better-than-even chance that two of them share the same birthday. The paradox lies in how unexpectedly low the number 23 is, given there are 365 possible days for a birthday.

How is this possible?

Upon first hearing about the Birthday Paradox, many people find it baffling. With 365 possible days for birthdays, how can it be that with just 23 individuals, there's a 50% chance that at least two share the same birthday?

A common mistake is to think about the problem linearly, as one might do with a "rule of three" approach. To illustrate, you might think:

If 1 person covers 1 day of the year, then 23 people should cover $\frac{23}{365} )$ or about 6.3% of the year. Hence, the chance of a shared birthday should be very low.

But this reasoning isn't accurate because it doesn't consider all possible pairs of people in the group.

Let's break it down mathematically:

Consider the first person. When the second person enters the room, there's a $\frac{364}{365} )$ chance they have a different birthday than the first. As the third person enters, they now have two people to potentially share a birthday with, so the probability they have a distinct birthday from the first two is $\frac{364}{365} \times \frac{363}{365} )$ . And so on.

The probability $(P)$ that at least two people share a birthday in a group of 23 can be found by subtracting the probability that all of them have distinct birthdays from 1:

\left( \frac{365}{365} \times \frac{364}{365} \times \frac{363}{365} \ldots \times \frac{343}{365} \right) ]

This results in a number slightly above 50%.

It happens, because in statistics when we want to sum probabilities, we have to multiply them one for the other.

Birthday Paradox Approximation

Calculating the probability the way we have done above, as you may have noticed is very difficult and computationally intensive. So, we have formulas that act as approximations for the Birthday Paradox, the main approximation formula is this:

P(collision)≈1−e−n22N\ P (collision) ≈ 1 - e ^ {- \frac{n ^ 2}{2N}}

Where:

$n$ is the size of your sample (23 people)
$e$ is the Euler number
$N$ is the total number of possibilities (365 days)

Real word problems

Theory is good, but we need practice and applications to learn. So in this section, I will share some challenges that I had at work, which I solved by using the Birthday Paradox.

Random Codes Generation

These days I had to generate around 400k unique random codes at once and insert them into the database. I had a table in my SQL database with a UNIQUE CONSTRAINT in the code field, to ensure that the database wouldn't accept duplicates. My SQL table was like this:

CREATE TABLE IF NOT EXISTS codes (
    id SERIAL PRIMARY KEY,
    code TEXT UNIQUE,
    used INTEGER DEFAULT 0
);

After some codes were generated, I started to get this annoying error from the database: "UNIQUE Constraint Violation". It shouted that, amongst my fresh 400k codes, there were some duplicates. My first reaction was pure disbelief. I mean, how is this possible? Then I thought that maybe I wasn't using truly random data to generate the codes.

To give some context, the code format was:

Four numbers from 0 to 9 at the beginning
Three letters from A to Z at the end

Very similar to a US License Plate

If we do the math, for this shape of code, there are:

10^4 \space \times \space 26^3

$N = 175, 760, 00$ different combinations

For me, it seemed very strange and wrong, because $%\frac{400,000}{175,760,000} = 0.00227583067 \space = \space 0.23 \space \%$ . How could I have been so unlucky to land within this tiny 0.23% window?

Then it hit me – the issue wasn't with the randomness of my code generation. I was witnessing the Birthday Paradox in action! After researching a little bit on the Birthday Paradox, I revisited my calculations and found out something pretty surprising: with 400k codes generated all at once, the chance of collision is almost of 100% (99.997%)

Using the simplified approximation of the Birthday Paradox:

\approx 1 - e^{-\frac{n^2}{2N}}

Where:

p(n) is the probability of a collision.
n is the number of generated codes.
N is the total number of possible combinations.
e is the base of the natural logarithm (approximated to 2.71828).

Plugging in n = 400,000 and N = 175,760,000:

\approx 1 - e^{-\frac{400,000^2}{2 \times 175,760,000}}

This equation gives you a value close to 1, indicating that a collision is almost certain

Solution 1 - Generate codes sequentially and programmatically label them

The first solution for this problem is to generate the codes sequentially, instead of generating them in a completely random way. By doing this, we ensure that there are no collisions, as we are following the combination of codes order. To not generate repeated codes every round, we insert them into the database and label them as "used".

Let's see some code. Starting with the function to generate the codes:

Create a file named generate-codes.ts , and add the following code:

export function generateSequentialCodes(nOfCodes: number = 100): string[] {
    const codes: string[] = [];
    let nextNumber = 0;
    const nextLetters = ['A'.charCodeAt(0), 'A'.charCodeAt(0), 'A'.charCodeAt(0)]
    let nextNumberPart = '0000';
    let nextLettersPart = 'AAA';

    for (let i = 0; i < nOfCodes; i++) {
        codes.push(`${nextNumberPart}${nextLettersPart}`);
        nextNumberPart = (nextNumber + 1).toString().padStart(4, '0');

        nextLetters[2]++;  // increment the last letter

        // check if the last letter is bigger than 'Z' in the ASCII table
        if (nextLetters[2] > 90) {
            nextLetters[2] = 65;  // reset to 'A'
            nextLetters[1]++;  // increment the 2nd letter

            // check if the second letter is bigger than 'Z' in the ASCII table
            if (nextLetters[1] > 90) {
                nextLetters[1] = 65;  // reset to 'A'
                nextLetters[0]++;    // increment the 1st letter

                // if all letters are 'Z', then reset them all to 'A'
                if (nextLetters[0] > 90) {
                    nextLetters[0] = 65;
                    nextLetters[1] = 65;
                    nextLetters[2] = 65;
                }
            }
        }

        nextLettersPart = String.fromCharCode(...nextLetters);
    }

    return codes;
}

This function will be used to generate the codes sequentially, using permutation.

The next step is to generate the 400k codes and insert them into the database. This won't be covered in this article because it's not our main focus. However, I'll provide a link at the end with the complete implementation.

Solution 2 - Generating random codes in small batches

Generating random codes in small batches can be a pragmatic approach. Instead of generating 400k random codes all at once, which will almost certainly result in a collision, we can generate codes in small batches to mitigate this risk. For instance, if we generate 5k codes at a time, the collision probability stands around 6.8%.

For an even safer margin, let's generate 2.5k random codes in one go. This gives us a collision probability of about 1.8%, which is manageable and considerably low.

\approx 1 - e^{-\frac{2,500^2}{2 \times 175,760,000}} \newline p(2,500) \approx 0.0178

Let's proceed with our coding:

In our file generate-codes.ts, we'll append the following code:

export function generateRandomCode(): string {
    const numbersPart = getRndInteger(0, 9999).toString().padStart(4, '0');
    let lettersPart = '';
    const letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    for (let i = 0; i < 2; i++) {
        lettersPart += letters.charAt(getRndInteger(0, letters.length));
    }

    return numbersPart + lettersPart;
}

The function above is responsible for generating random codes.

Now we generate the 2.5k codes and check if they are repeated:

export function generate2_5kCodes(): string[] {
    const codes: string[] = [];

    while(codes.length < 2500) {
        const newCode = generateRandomCode();

        // Only add if the new code isn't already in the array
        if (!codes.includes(newCode)) {
            codes.push(newCode);
        }
    }

    return codes;
}

Solution 3 - Improving the results with Sets

The 3rd solution is an extension/improvement of the 2nd one. In the 2nd solution, we used arrays. Searching for an element in an array takes $O (n)$ time. $O (n)$ is alright, but with big data, it can slow things down a bit. To get around this, we can switch out Arrays for Sets.

Sets do the lookup job in $O (1)$ time, which is much quicker. And another cool thing about Sets? They hold only unique values. That means they automatically take out any duplicate values. Here's how the code looks when using Sets:

export function generate2_5kCodes(): string[] {
    const codes: Set<string> = new Set();

    while(codes.size < 2500) {
        codes.add(generateRandomCode());
    }

    return Array.from(codes);
}

By integrating Sets into our methodology, we've not only streamlined our process but also considerably boosted its efficiency.

HINT 💡 : To insert the codes into the database, use the COPY command instead of INSERT. For bulk inserts of simple data, COPY is way more efficient. See more about COPY: https://www.postgresql.org/docs/current/sql-copy.html

Conclusion

Throughout our exploration, we've encountered the unexpected twists brought by the Birthday Paradox while trying to generate unique codes. It served as a clear reminder that even seemingly simple tech challenges can have underlying complexities.

In this article, we didn't just talk about code. We meshed together math, probability, and data structures like arrays and sets. We examined time complexity and used it to improve our code's efficiency. As we continue our journey in tech, articles like this help in appreciating the blend of theory and practice in everyday coding challenges.

Math for Devs - Encryption Essentials

José Thomaz — Mon, 04 Sep 2023 13:08:05 +0000

Encryption is something that we use extensively in computer science. It's an indispensable technique in systems connected through a network or those containing sensitive data. But what most people don't know, is that cryptography is essentially math. So when you encrypt a message, you are basically using a mathematical algorithm to hide your message.

History of Encryption

While encryption as a way to secret a message is central to computer science, its roots go much deeper. Ancient civilizations utilized encryption techniques long before the advent of modern computing. The first known evidence of cryptography was found in an inscription carved around 1900 BC in Egypt. Another famous early cryptographic method was the "Caesar Cipher," used by the Roman consul Julius Caesar to transmit secret messages to his generals during military campaigns.

Cryptography assumed a more prominent role during the World Wars, especially during World War II. The Nazis developed the "Enigma" machine to encrypt radio transmissions, making communication more secure. The complexity of the Enigma encryption far surpassed that of the Caesar Cipher. It employed cipher keys for encrypting messages, and these keys were rotated daily. Polish cryptanalysts initially made significant progress in understanding how the Enigma worked. Later, Alan Turing and his team in Britain built on this work and developed a machine and algorithms to deduce the daily key settings of the Enigma.

Math concepts used in Cryptography

Number Theory: modular arithmetic, and prime numbers are widely used in public key cryptographic schemes.
Probability and Statistics: Used to evaluate the randomness of algorithms, analyze potential vulnerabilities
Combinatorics: Helpful in understanding the strength of cryptographic systems by determining possible combinations or permutations, especially in brute-force attacks. Permutations are also a way to ensure that the encryption algorithm is non-linear and "mixed" relative to the input.
Linear Algebra: Plays a role in several cryptographic protocols and systems. Classic algorithms such as "Hill Cipher" are heavily based on linear algebra, more specifically matrices operations.
Information Theory: Concepts like entropy are used to measure and ensure the unpredictability and randomness necessary in cryptographic systems.

Understanding some Cryptographic Algorithms

In this section we are gonna discuss some basic Cryptographic Algorithms, and how they use math in its implementations.

Caesar Cipher: A Linear Shift in Alphabets

The Caesar Cipher is one of the earliest and simplest forms of encryption. It involves shifting each letter in a message by a predetermined number of positions in the alphabet. In a way, it's a linear transformation of each letter in the text.

How it Works:

Imagine the alphabet as a linear array of characters:
A B C D ... Z

When you want to encrypt a letter using the Caesar Cipher, you simply move it down (or up) by a certain number of spaces, wrapping around when you reach the end of the alphabet. This "certain number" is known as the shift.

For instance, with a shift of 3:

A becomes D
B becomes E
C becomes F ... and so on.

The formula for each letter transformation is essentially:
$\space = \space (P + s) \space \space mod \space 26$
Where:

$C$ is the resulting cipher text letter
$P$ is the plaintext original letter
$s$ is the shift case (in our case 3)
$\space 26$ just ensures we wrap around if we go beyond $Z$ .

Given our example shift of 3, the message:

SCALA IS THE BEST PROGRAMMING LANGUAGE

would translate to:

VFDOD LV WKH EHVW SURJUDPPLQJ ODQJXDJH

To decrypt, you'd shift back by the same amount.

Though a Caesar Cipher is simple and easy to break today, it was innovative during its time and serves as a foundational concept in cryptography.

Hill Cipher

In classical cryptography, the Hill cipher is a substitution cipher based on linear algebra. Invented by Lester S. Hill in 1929, it is a subsitution cipher just like Caesar Cipher, but it is more powerful because it uses a public key signature to encrypt the text.

We can use this table as a base to the mod 26 alphabet mapping:

Letter	A	B	C	...	Z
Number	0	1	2	...	25

Encrypting the text

Let's suppose we have the following message that we want to encrypt:

PELE IS THE GREATEST FOOTBALL PLAYER OF ALL TIME

With the text in hands, now we define our encryption key.

1. Encryption Key

In Hill Cipher, the encryption key is a square matrix, it means that its dimensions should be 2x2 , 3x3 , 4x4 and so on. For our example, let's use a 3x3 matrix, but keep in mind that for more secure keys you should use a bigger key matrix to make brute-force attacks more challenging.

[171752118212219]\begin{bmatrix} 17 & 17 & 5 \\ 21 & 18 & 21 \\ 2 & 2 & 19 \\ \end{bmatrix}

2. Splitting the text in blocks

The second step involves dividing the input text into blocks. Given that we're working with a 3x3 matrix, it's necessary to segment the text into blocks of three characters each. So, at the end of this segmentation, our blocks will look like this:

PEL EIS THE GRE ATE STP LAY ERO FAL LTI MEE

Observe that when spaces are removed, our sentence doesn't neatly divide by three. To fix this, we've duplicated the final character to ensure a complete block of three characters.

3. Convert each block into a column vector

To do this, we have to use the table above as a reference. With the table, we can map a letter to its corresponding number. Let's see how it works:

$\rightarrow \begin{bmatrix} 15 \\ 4 \\ 11 \\ \end{bmatrix}$

$\rightarrow \begin{bmatrix} 4 \\ 8 \\ 18 \\ \end{bmatrix}$

$\rightarrow \begin{bmatrix} 19 \\ 7 \\ 4 \\ \end{bmatrix}$

$...$

$\rightarrow \begin{bmatrix} 12 \\ 4 \\ 4 \\ \end{bmatrix}$

Note: I hadn't showed all the substitutions, so you need to do the rest with the other blocks. I did only the first 3 ones and the last.

4. Multiply each column vector by the key matrix

Now, we multiply each column vector of the text block, by the key matrix, and then apply the $m o d 26$ to the resulting matrix.

$m o d 26$ is dividing a number by 26 and getting the remainder

[142013]\begin{bmatrix} 15 \\ 4 \\ 11 \\ \end{bmatrix} \times \begin{bmatrix} 17 & 17 & 5 \\ 21 & 18 & 21 \\ 2 & 2 & 19 \\ \end{bmatrix} \space \space (mod \space 26) \space \space = \space \space \begin{bmatrix} 14 \\ 20 \\ 13 \\ \end{bmatrix}

[882]\begin{bmatrix} 4 \\ 8 \\ 18 \\ \end{bmatrix} \times \begin{bmatrix} 17 & 17 & 5 \\ 21 & 18 & 21 \\ 2 & 2 & 19 \\ \end{bmatrix} \space \space (mod \space 26) \space \space = \space \space \begin{bmatrix} 8 \\ 8 \\ 2 \\ \end{bmatrix}

[201124]\begin{bmatrix} 19 \\ 7 \\ 4 \\ \end{bmatrix} \times \begin{bmatrix} 17 & 17 & 5 \\ 21 & 18 & 21 \\ 2 & 2 & 19 \\ \end{bmatrix} \space \space (mod \space 26) \space \space = \space \space \begin{bmatrix} 20 \\ 11 \\ 24 \\ \end{bmatrix}

...

...

...

[6184]\begin{bmatrix} 12 \\ 4 \\ 4 \\ \end{bmatrix} \times \begin{bmatrix} 17 & 17 & 5 \\ 21 & 18 & 21 \\ 2 & 2 & 19 \\ \end{bmatrix} \space \space (mod \space 26) \space \space = \space \space \begin{bmatrix} 6 \\ 18 \\ 4 \\ \end{bmatrix}

5. Getting it all together and matching the characters

After doing all the multiplying operations, we already have the result matrices. So, the only thing left to do is to map the matrices items to the letters. For our case, the final result will be:

OUNIICULYVWSFKKETNZJBUSDDEBMWUGMBINXZPWUTN

Conclusion

In summary, while the Caesar Cipher involves a straightforward shift of letters, the Hill Cipher employs linear algebra to provide a more complex transformation. To decrypt, you essentially reverse the encryption process using the matrix's inverse.

References

https://www.dcode.fr/hill-cipher

https://cryptii.com/pipes/caesar-cipher

https://en.wikipedia.org/wiki/Hill_cipher

Audio Embeddings: Understanding the basics

José Thomaz — Wed, 02 Aug 2023 15:23:52 +0000

In the age of Artificial Intelligence, Large Language Models, and Natural Language Processing (NLP), there's a lot of buzz around embeddings and semantic search. These Large Language Models have harnessed the potential of high-dimensional word embeddings, a process that translates text into mathematical vector representations. This methodology is very useful for NLP applications and is a cornerstone in the creation of modern, highly accurate search engines.

However, the generation of embeddings isn't restricted solely to text. It is indeed feasible to generate embeddings from a diverse range of media formats, including video, which will be the central topic of this article. By generating embeddings from audios you can perform Query-By-Example (QBE), audio pattern recognition, audio analysis and audio semantic search.

Applications of audio embeddings

You might be scratching your head and asking, "Why should I care about audio embeddings? What's the big deal?" Well, if you're jamming to tunes on Spotify or binging videos on YouTube, guess what? Audio embeddings are already working behind the scenes for you. These little math wizards help Spotify and YouTube tailor the music and recommendations specifically to your taste. Plus, they play a big part in their search system, helping you find that one specific track you've got stuck in your head.

Have you ever used the app "Shazam"? It's a handy tool that helps you identify the title of a song you enjoy but don't know the name of. This happens often when we're listening to the radio. Shazam accomplishes this by capturing a short clip of the song and then searching for a match in its database. While it doesn't use audio embeddings directly, it utilizes a technique called audio fingerprinting, which is quite similar.

How to generate embeddings from an audio?

You can generate audio embeddings in several ways, but it will involve artificial neural networks in most times. It's a pretty recent field, hence so far (2023) there isn't a standard and "better" way to do this, you need to analyse all the algorithms and decide which one best fits to your needs. In this article we are going to review 5 different algorithms:

Mel-frequency cepstral coefficients (MFCCs)
Convolutional Neural Networks (CNNs)
Pretrained Audio Neural Networks (PANNs)
Recurrent Neural Networks (RNNs)
Transformers Architecture

If you are not familiar with the concepts of semantic search or vector embeddings, I recommend you to read this articles first: What is a vector embedding? and Vector Databases

Mel-frequency cepstral coefficients (MFCCs)

Imagine you're listening to a song. The song is made up of different pitches, volumes, and tones, which all change over time. The human ear picks up on these changes and our brain interprets them as music.

MFCCs try to mimic this process in a mathematical way. They take a sound, break it down into its different frequencies (like bass or treble), and then represent these frequencies in a way that's similar to how our ear hears them. This creates a set of numbers, or "coefficients", which can be used as the embeddings of the song.

Convolutional Neural Networks (CNN)

Convolutional Neural Networks (CNNs) are a class of deep learning models highly effective for analyzing sequential data like audio. Instead of processing grid-like structures, as in images, they process audio signals to capture temporal dependencies and patterns. By employing convolutional layers, CNNs apply filters to local segments of audio data, learning from simple features like waveforms in initial layers to more intricate sound patterns or motifs in deeper layers.

Pretrained Audio Neural Networks (PANNs)

PANNs are deep learning models that have been trained on extensive audio data. They have the capacity to recognize a wide array of acoustic patterns, which equips them to be functional in various audio-related tasks. A PANN is quite versatile because it can extract patterns from all types of audio, including noises. If you require something more specific, the model can be fine-tuned to cater to your needs.

Recurrent Neural Networks (RNNs)

RNNs are another type of deep learning model that has been widely used for audio tasks. Unlike CNNs and PANNs, which process an entire audio signal at once, RNNs process the audio signal in a sequential manner. This makes them especially well-suited to tasks involving time series data, like audio signals. Audio embeddings can be created with an RNN by passing the audio signal through the network and using the output from the last time step as the embedding.

Transformers Architecture

Transformers are the same technology used in ChatGPT and Google Bard, they are pretty recent, yet very relevant and popular. Transformers use a mechanism called attention to weigh the importance of different parts of the input data. This allows them to capture long-range dependencies in the data, which is particularly useful for audio signals. Audio embeddings can be created with a Transformer by passing the audio signal through the network and using the output from the last layer as the embedding.

Partitioning Large PDF Files with Python and Unstructured.io

José Thomaz — Sun, 18 Jun 2023 03:48:13 +0000

When dealing with large PDF files, it can often be beneficial to break them down into smaller, more manageable chunks. This process, known as partitioning, can improve processing efficiency and make it easier to analyze or manipulate the document. In this article, we will discuss how to use Python and a powerful library called Unstructured.io to partition PDF files into smaller sections.

Libraries Used

We will be using two Python libraries for this task:

PyPDF2: A library that can read, write, merge, and split PDF files.
Unstructured.io: A library that can segment PDF documents using document image analysis models.

Code Walkthrough

Here is the Python code that accomplishes this task:

from PyPDF2 import PdfReader, PdfWriter

# Read the original PDF
input_pdf = PdfReader(f'./exam-prep/{filename}')

batch_size = 100
num_batches = len(input_pdf.pages) // batch_size + 1

# Extract batches of 100 pages from the PDF
for b in range(num_batches):
    writer = PdfWriter()

    # Get the start and end page numbers for this batch
    start_page = b * batch_size
    end_page = min((b+1) * batch_size, len(input_pdf.pages))

    # Add pages in this batch to the writer
    for i in range(start_page, end_page):
        writer.add_page(input_pdf.pages[i])

    # Save the batch to a separate PDF file
    batch_filename = f'./exam-prep/output/{filename}-batch{b+1}.pdf'
    with open(batch_filename, 'wb') as output_file:
        writer.write(output_file)

    # Now you can use the `partition_pdf` function from Unstructured.io to analyze the batch
    elements = partition_pdf(filename=batch_filename)
    # Do something with `elements`...

Step 1: Reading the PDF

First, we import the necessary classes from the PyPDF2 library: PdfReader and PdfWriter. The PdfReader class is used to read the original PDF file, which is stored in a subdirectory called 'exam-prep'.

Step 2: Partitioning the PDF

We decide on a batch size, which is the number of pages each chunk of the PDF will contain. In this example, we chose a batch size of 100 pages, but this can be adjusted according to your needs.

The number of batches is then calculated by dividing the total number of pages in the PDF by the batch size. We add 1 to ensure that we capture any leftover pages if the total number of pages is not a multiple of the batch size.

Step 3: Writing the PDF Chunks

Next, we loop over each batch, creating a new PdfWriter object for each one. For each batch, we calculate the start and end page numbers and add each page in that range to the PdfWriter using the add_page method.

Once all the pages for a batch have been added, we write them to a new PDF file in the 'output' subdirectory. The filename of each chunk includes the original filename and the batch number.

Step 4: Analyzing the PDF Chunks

With the PDF divided into smaller chunks, you can now use the partition_pdf function from the Unstructured.io library to analyze each batch. This function segments a PDF document using a document image analysis model and returns a list of elements present in the pages of the parsed PDF document.

Conclusion

Partitioning large PDF files into smaller chunks can make them easier, fault tolerant and consume less memory.

Forem: José Thomaz

Using NoSQL Features in PostgreSQL

How does PostgreSQL support non-relational data?

XML

JSON

JSONB

Final Comparison

Conclusion

[Boost]

Diagnosing and fixing critical PostgreSQL performance issues: A deep dive

Forecasting the Dollar rate with Python

Why is it so hard to forecast the Dollar rate? An attempt with Python

Why is it so hard to forecast the Dollar rate? An attempt with Python

3 rules to know if something can be predicted

Rule 1: We need to know and understand the factors that affect the indicator that we are trying to predict

Rule 2: There must be enough data available

Rule 3: The forecast itself should not change the indicator value

Trying anyway

How to forecast time series data?

Prophet

Config

Fetching the historical data for USD-BRL

Adding regressors to enhance our model's performance

Interest Rates

Central Bank Meeting Dates

Setting up the Holidays

Instantiate the model and plug the regressors

Interpreting the results

Forecast of the USD/BRL Exchange Rate Considering Central Bank Meetings

USD/BRL Trend

Validating the Results

Limitations of forecast accuracy in the face of unpredictable events

How to improve the model?

References

Benchmark: Snowflake vs UUIDv4

Understanding the benchmark

How to run?

How to analyze the data

Results

ID Generation

ID Insertion

ID Selection

ID Search (WHERE)

Ordered Selection (SELECT + ORDER BY)

Update

Validating Results

T-Test

Why is UUID's variance so high?

Conclusion 📝

Is UUIDv4 bad?

When to use each?

To improve ✨

Links 🔗

Math for Devs - Gambler's fallacy and Monte Carlo Simulation

Understanding the Gambler's Fallacy

Everyday Examples of the Gambler's Fallacy

The Historical Context

But why is this a fallacy?

Demonstrating the Fallacy with Python

Monte Carlo Simulation

5 coin flips

10 coin flips

100 coin flips

1000 coin flips

10000 coin flips

100000 coin flips

1 million coin flips

1 billion coin flips

Fastify + NestJS file upload

Installing the dependencies

Modify your main.ts file

Create a route for uploading files in a NestJS controller

Speed up your PostgreSQL bulk inserts with COPY

“I have to insert 1mi rows in X seconds”

How does COPY work internally?

COPY vs INSERT (multi-line)

Measuring performance

Ramping up (5mi records)

Ramping up (10mi records)

Improving COPY performance with multithreading

Modify your `main.ts` file