Testing

I was recently reviewing a database partitioning definition in YugabyteDB (the postgres “ysql” API), and realized the partition distribution might not be what the developer intended.

What is database partitioning?

Database partitioning is used to divide large tables into smaller tables (partitions). While the data is physically separate, the application can access the data logically as a single table.

This can help performance through a process called partition pruning. The database planner skips partitions that don’t hold the data. For example, if a table is partitioned on months of the year, a query on a single month only has to access the rows in the single partition for that month.

I had to create a 10 million row table for testing recently, and put together a query to generate random data for it.

INSERT INTO my_table
(id,
mydatetime,
string1,
string2)

SELECT
(random() * 70 + 10)::int,
TIMESTAMP '2024-01-01 00:00:00.000000' + interval '1 millisecond' * (random() * 86400 * 1000 * 365),
(array['alligator','bear','cat','dog'])[(random() * 3 + 1)::int],
substr(md5(random()::text), 1, 10)

FROM generate_series(1, 10);

The id field is just a random integer in this example, but you’d probably use an identity column.

I’m writing this the night before we’re going to get some very bad weather. In North Carolina this time of year, that could mean an ice storm. Ice coats all the big and little branches on all the trees, and then they start to shatter onto roofs and overhead cables. I’ve lived all over the lower 48 and in Alaska, and ice storms are by far the worst, especially in a state that doesn’t bury the electrical lines.

Here’s a very quick way to set up YugabyteDB on your Mac for functional testing. It assumes you already have Homebrew installed.

brew tap yugabyte/yugabytedb
brew install yugabytedb

In the future, you can upgrade the version by running this:

brew upgrade yugabytedb

Verify the installation and check the version:

yugabyted version

Set up local networking:

sudo ifconfig lo0 alias 127.0.0.2
sudo ifconfig lo0 alias 127.0.0.3

Then you can set up a three-node YugabyteDB cluster. Change the data directory if you’d like.

Generating random test data in MySQL is straightforward with SQL techniques and functions. Practical examples show how to populate tables with realistic data for development and testing, streamlining the process of building and validating applications.

Read more!