Posts

File & file descriptor File In Linux, everything is a file - file descriptors represent many kernel objects. Besides the regular file, these resources are considered as file: block device, terminal, pipe, socket. A file means it typically can be accessed by using the file interface: read(), write(), close() and open(). There are a few exceptions where it doesn’t support full methods in the interface. File descriptor A file descriptor is a non-negative integer indexing an entry in a process’s file-descriptor table. A file descriptor refers to a open file description. Multiple file descriptors can refer to the same open file description. A file descriptor is unique within a process. Open File Description ...

Note: This article describes the runtime at Go 1.26. These internal details are not part of Go’s compatibility guarantee. Goals of the Go Runtime Scheduler Minimize OS thread overhead (context switch, resource usage) Support high concurrency Scale across CPU cores for true parallelism Ensure fairness among tasks (best-effort) Architecture The scheduler isn’t a single runtime component. It’s a collection of algorithms and data structures that coordinate goroutines, OS threads, processors, timers, network polling, and runtime work. Its general architecture is described in the diagram below. ...

I often hear that batch insert can help to increase the throughput. Instead of insert row by row, we can combine many rows into one batch and insert once. But I want to understand two things: why is batching help increase insert throughput if batching increase the throughput, why don’t i just use a very huge batch. Is there a upper limit for a batch size. Benchmark setup Environment Sysbench running on EC2 t3.micro 2 vCPU, 1GB RAM Postgres 18 RDS db.t4g.micro 2 vCPU, 1GB RAM, 20GB storage, 90MB shared buffer Both EC2 and RDS are in the same region I choose Sysbench over PgBench because it help me to build the batch data from client with Lua script easily Scripts Schema 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 -- unlogged table for faster seeding CREATE UNLOGGED TABLE transactions ( id BIGSERIAL PRIMARY KEY, account_id BIGINT NOT NULL, merchant_id BIGINT NOT NULL, amount NUMERIC(12,2) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'USD', status SMALLINT NOT NULL DEFAULT 0, type SMALLINT NOT NULL DEFAULT 0, reference_id UUID NOT NULL DEFAULT gen_random_uuid(), description VARCHAR(255) NOT NULL, ip_address INET NOT NULL, device_id VARCHAR(64) NOT NULL, metadata JSONB NOT NULL DEFAULT '{}', created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp() ); -- seeding INSERT INTO transactions ( account_id, merchant_id, amount, currency, status, type, reference_id, description, ip_address, device_id, metadata, created_at, updated_at ) SELECT (random() * {{NUM_ACCOUNTS}})::bigint, (random() * {{NUM_MERCHANTS}})::bigint, (random() * 10000)::numeric(12,2), (ARRAY['USD','EUR','GBP','AUD','SGD'])[(random()*4)::int + 1], (random() * 3)::smallint, (random() * 1)::smallint, gen_random_uuid(), 'Payment ref-' || g, ('10.' || (random()*255)::int || '.' || (random()*255)::int || '.' || (random()*255)::int)::inet, 'device-' || (random() * {{NUM_ACCOUNTS}})::bigint, jsonb_build_object('channel', (ARRAY['web','mobile','pos'])[(random()*2)::int + 1], 'attempt', (random()*3)::int + 1), NOW() - (random() * INTERVAL '90 days'), NOW() - (random() * INTERVAL '90 days') FROM generate_series(1, {{NUM_ROWS}}) g; ALTER TABLE transactions SET LOGGED; CREATE INDEX ON transactions (account_id); CREATE INDEX ON transactions (merchant_id); CREATE INDEX ON transactions (account_id, created_at DESC); CREATE INDEX ON transactions (status, created_at) WHERE status IN (0, 2); CREATE INDEX ON transactions (created_at); -- update table's statistic VACUUM ANALYZE transactions; CHECKPOINT; -- warm the table SELECT COUNT(*) FROM transactions; Sysbench Lua script 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 -- batch_insert.lua -- Sysbench Lua script for pg batch INSERT benchmark. -- Accepts --batch-size=N on the CLI (passed via sysbench_options in mybench). -- All row data is generated in the Lua VM (client side) before the query is sent. -- -- Usage (standalone): -- sysbench batch_insert.lua \ -- --pgsql-host=localhost --pgsql-port=5432 \ -- --pgsql-db=bench --pgsql-user=postgres \ -- --threads=8 --time=180 --batch-size=100 run -- --------------------------------------------------------------------------- -- Custom CLI options -- --------------------------------------------------------------------------- sysbench.cmdline.options = { batch_size = {"Number of rows to INSERT per transaction", 1} } -- --------------------------------------------------------------------------- -- Per-thread setup / teardown -- --------------------------------------------------------------------------- local CURRENCIES = {"USD", "EUR", "GBP", "AUD", "SGD"} local CHANNELS = {"web", "mobile", "pos"} function thread_init() drv = sysbench.sql.driver() con = drv:connect() end function thread_done() con:disconnect() end -- --------------------------------------------------------------------------- -- Main benchmark event -- Each call = one transaction inserting `batch_size` rows. -- The entire VALUES list is built in Lua (client side) before the query fires. -- --------------------------------------------------------------------------- function event() local batch_size = tonumber(sysbench.opt.batch_size) local values = {} for i = 1, batch_size do local account_id = sysbench.rand.uniform(1, 100000) local merchant_id = sysbench.rand.uniform(1, 10000) local amount = math.floor(sysbench.rand.uniform(1, 1000000)) / 100.0 -- 2 decimal places local currency = CURRENCIES[sysbench.rand.uniform(1, #CURRENCIES)] local status = sysbench.rand.uniform(0, 3) local txn_type = sysbench.rand.uniform(0, 1) local channel = CHANNELS[sysbench.rand.uniform(1, #CHANNELS)] local attempt = sysbench.rand.uniform(1, 4) local device_id = "device-" .. sysbench.rand.uniform(1, 100000) local ip = sysbench.rand.uniform(0, 255) .. "." .. sysbench.rand.uniform(0, 255) .. "." .. sysbench.rand.uniform(0, 255) .. "." .. sysbench.rand.uniform(1, 254) -- Escape single quotes in description just in case local description = "Payment ref-" .. sysbench.rand.uniform(1, 1000000) values[i] = string.format( -- account_id, merchant_id, amount, currency, status, type, -- reference_id, description, ip_address, device_id, metadata, -- created_at, updated_at "(%d, %d, %.2f, '%s', %d, %d, gen_random_uuid(), '%s', '%s'::inet, '%s', " .. "'{\"channel\":\"%s\",\"attempt\":%d}'::jsonb, clock_timestamp(), clock_timestamp())", account_id, merchant_id, amount, currency, status, txn_type, description, ip, device_id, channel, attempt ) end local sql = "INSERT INTO transactions " .. "(account_id, merchant_id, amount, currency, status, type, " .. "reference_id, description, ip_address, device_id, metadata, " .. "created_at, updated_at) VALUES " .. table.concat(values, ",") con:query("BEGIN") con:query(sql) con:query("COMMIT") end Run Parameters Number of threads: 2. Meaning two sysbench clients will concurrently send requests to RDS Duration: 180 seconds Batch size: 1, 10, 50, 100, 500, 1000, 2000, 5000, 10000 Parameter value was used in the seeding file: 1 2 3 NUM_ACCOUNTS 100000 NUM_MERCHANTS 10000 NUM_ROWS 10000 Sample sysbench command: ...

The question I’ve heard many people said that, in postgres, each connection is a process, so it consume more resource than thread, context switch between processes is also costlier. When there are too many processes, the context switch overhead will cause the performance go down. Those statements are intuitive. But i want to observe the system from when number of connection small to large. The setup General idea The idea is simple. I created two VPSs, one to run pgBench, another to run Postgres, both in the same region to reduce network latency. I created few tables on postgres, then run pgBench to apply some workloads to that tables. Each run I use a different number of connection, from small to large, during the run, I will collect metrics to later comparing. ...

Recently I learned that Postgres holds a lock on the rows it updates during a transaction and only releases it when the transaction finishes (commit/rollback). This surprised me because I always use select ... for update to lock the row before updating it in a transaction. I thought that other transactions could concurrently update the row that my current transaction is updating, causing a race condition. For example, I have this simple users table: ...

HDD Two important components that make up an HDD are a rapidly rotating platter and a movable magnetic head. The magnetic disk are divided into concentric circles called tracks. Each track is divided into equal smaller parts called sectors. Each sector is usually 512 bytes in size, and it is also the minimum unit of read and write operation. That means that even you only need to write or read a few bytes, the system must read the entire 512 bytes sector. To read or write any sector, the magnetic head must move to the track that contains the sector, and the platter must rotate to position the sector under the magnetic head. ...

To secure storing sensitive information, a common approach is to use envelope encryption: A master key (usually managed by a secure KMS) A row-level key (used to encrypt individual records) Each row of sensitive data (e.g., credit card information) is encrypted using a unique row key, the row key itself is encrypted using the master key and stored alongside the data in the database. This setup allows secure key rotation, better isolation, and compliance with security standards. ...

This post is part of the series Postgres for Everything. Nowadays, we typically develop stateless applications, making it easier to scale them horizontally. However, locking is an essential tool to prevent race conditions in software development. In applications with multiple instances, programming language-level locks are insufficient because they only work locally within an instance. A centralized locking mechanism, valid across all instances, is required. Redis SETNX You can use SETNX to implement a simple lock. SETNX sets a value for a key only if the key does not already exist. ...

From my previous post, I realized that pattern matching operators (LIKE, ILIKE) do not utilize indexes. As I explored further, I came across the concept of collation and decided to take some notes in this post. Encoding Encoding maps human-readable characters to numbers so computers can understand them. Essentially, it assigns a unique number to each character. Common encodings include UTF-8 and ASCII. ASCII: Represents 256 unique characters. UTF-8: Represents 1,112,064 characters, covering almost all characters from any language. Most modern programming languages, such as Go, natively support UTF-8. Unlike ASCII, which uses 1 byte per character, UTF-8 uses up to 4 bytes. Strings in programming languages are typically represented as byte arrays. In ASCII, the number of bytes corresponds to the number of characters. However, this is not true for UTF-8. ...

Have you ever asked, why do we usually listen on localhost:8080 during development process, can we listen on Google IP instead of localhost? I have a little code snippet written in NodeJS: 1 2 3 4 5 var http = require('http'); http.createServer(function (req, res) { res.writeHead(200, {'Content-Type': 'text/plain'}); res.end('Hello World\n'); }).listen(8080, 'localhost'); It doesn’t do anything complex, just starts a new server, listens on localhost:8080, and responds ‘Hello World’ to requests. I need you to do a little more thing, open your terminal, and execute this command (MacOS or Linux) to get your local IP: ...