Blog of Ihnat Zakreuski
Just after lunch on a Monday, an alert fired: FIX session disconnected . That was new. The session dropped at 09:30 NY time — the US market open, when the accumulated pre-market orders execute and a significant share of the day’s volume goes through in minutes. For a drop-copy session, a disconnection at the open is the expensive kind: trading carries on without you, and every trade that clears…
Improving query performance is always challenging, especially when working with large-scale databases. While adjusting indexes is a common approach, testing these optimizations becomes complex when your tables contain terabytes of data. I recently faced this challenge: I needed to test potential improvements for a large-scale table, but there was no simple way to work with terabytes of data on my…
Visualization is a great opportunity to see some complex structures from another side. Let’s assume we want to inspect how trees actually look. We will use Breadth-first search (BFS) to traverse graph (or tree in our case) and show connections between nodes. def bfs ( node ) queue = [ node ] discovered = [] while queue . length > 0 do current = queue . shift discovered << current current .…
SQL triggers is something that everybody heard a lot about. Before start we need to talk about benefits and burdens. Pros It’s very fast. If you are using any kind of adapter you pay for all the wrappers and connectors. With triggers, all operations are very cheap because they are handled by PostgreSQL directly. It works like magic. There is no code in your repository, no callbacks on your models.…
Let’s assume you want to find all node leaves. To keep it simple for all examples we will use DB from previous post. Leaf is a node without children. If you have parent_id it seems easy to find all leaves SELECT * FROM "properties" WHERE "id" NOT IN ( SELECT DISTINCT "parent_id" FROM "properties" WHERE "parent_id" IS NOT NULL ); We just excluding all nodes that are parents for any node and that’s…
PostgreSQL provides you ltree extension to organize tree-like structures. You can enable this extension with command CREATE EXTENSION IF NOT EXISTS ltree; or you can wrap it with ActiveRecord def up execute "CREATE EXTENSION IF NOT EXISTS ltree" end In Ruby, you can use ltree_hierarchy gem to simplify main operations for manipulating with tree’s nodes. It updates path column, that contains all…
Materialized view is an object that contains the query’s results. Unlike a database table it doesn’t support INSERT / UPDATE / DELETE operations. Since these operations are unsupported, to update a materialized view you need to call a refresh operation. In PostgreSQL materialized views support was introduced in version 9.3. From PostgreSQL documentation you can see how to create materialized view.…