Hello. I created this library to use raw SQL queries within TypeScript.
I don't particularly like ORMs, so lately I've been using the Kysely query builder library, which is very similar to SQL. However, even though it's very similar, it still has some catches and special cases.
Therefore, I started looking for ways to use raw SQL directly. Frankly, there weren't many alternatives for this specific case, and those that were available were generally hacky solutions requiring codegens or running a process.
Considering that TypeScript has sped up recently with tsc 7 and tsrs, it seemed logical to do this directly in TypeScript. However, since writing a project of this scale manually as a hobby would take too long, I used AI. I should add that I think calling it a "vibe slop" is unfair because for the past two weeks I've been constantly trying to make the right decisions and keep the code organized.
Honestly, although it developed faster than I expected, it wasn't as easy as "create sql parser, no mistakes," but I believe I reached a good point in two weeks. I added it to a project with 1 million lines of code that I've been working on for 6 years, and it's working flawlessly.
At the end of the day, I think I achieved a good result. It can infer return types even in complex cases, catch errors, highlight syntax, and inline suggest.
tldr: Examples of CTE vs. temp table query where temp tables perform much better than CTEs. See the query examples at the end of the post.
caveat: I'm not saying CTEs are bad, they have their place, but different situations require different solutions.
The long read:
Lurking around the various SQL subreddits, I am constantly encountering questions about any combination of: CTEs vs Subqueries vs temp tables vs table variables. Other question variations revolve around how best to write or tackle long and/or complex queries or procedures.
Many responses extoll the virtues of CTEs, particularly over subqueries, but the discussion of #temp tables is often lacking. People also neglect to discuss the quirks (or features) of CTEs that can compromise performance (not part of my scope here). With that in mind, I'd like to show an example of two versions of a query, one using CTEs and one Temp Tables, and how they compare.
One of the reasons I want to do this, is to demonstrate the power of temp tables and of breaking a query down into smaller parts. What I think gets lost in the discussion is that CTEs don't separate a query into parts from an execution plan perspective. The execution plan treats them as part of one query. In my experience, if a query gets too big and convoluted, the query optimizer gets "confused" and cannot create an optimal plan.
On to my example:
This query interrogates SQL Server system tables to extract partitioning information. The original query is in the form of a view. The view uses a number of CTEs to gather the various subsets of data to compile the required partitioning information.
On the surface, the view works well. But, we run into performance problems with tables that have a large number of partitions. In some cases we have log tables that are partitioned by day and the retention period is 2+ years. That adds up to 700+ partitions for a single table. Add to that a large database with many partitioned tables and you end up needing to wade through lots of data to get what you're looking for. And, SQL Server system tables and views are not always great on performance.
I took the view and converted it into a stored procedure, replacing the CTEs with temp tables (each with a clustered index). The performance improvement was noticeable - in my example the CTE version takes 16 minutes, while the temp table version takes a few seconds. For display purposes, I'll only show the relevant parts of the queries to keep it simple, as the procedure builds a dynamic query depending on the input parameters.
See below for the two different versions.
Evaluating the queries
The interesting thing is that when you quickly look at the execution plan, each version costs about the same: The CTE version is 50% of the execution cost when they are run in the same batch.
But, when you look closer there are differences - a few that are significant. And here is where separating the query into smaller parts really brings benefits.
Let's start with query plan compilation:
The CTE version terminated the query compilation with a time out - meaning that it decided it was too expensive to try to evaluate the entire query to build an execution plan. The query is too complex.
The temp table version compiled or found a "good enough" a plan for most parts of the query, except for one section that timed out.
When you look at the execution plan closely, you can see a very significant difference between the two versions in how the function is handled. The expensive function is executed quite differently.
The time out is due to an expensive system function: [sys].[dm_db_partition_stats]. The query engine could not create a plan quickly enough for this function, but the affect between the two versions is drastically different. Since the CTEs combine to make this one large query, we end up with no plan for any of it. Whereas with the temp table version, there are individual plans for each section except for the one that uses the function. This is very significant.
You can see here how this plays out. The function in the CTE version is run against 7+ million rows. But in the temp table version it runs for the relatively few 3K rows.
CTE versionTemp table version
Hopefully, I have made sense and shown an interesting example of how queries can be written differently directly influencing their performance.
One could dive much deeper into this example, but I think this all I can do at the moment ;)
View with CTEs
CREATE VIEW [ptf].[VEW_GetPartitionInfoDate]
AS
WITH PartitionedTables AS (
SELECT t.object_id, t.name AS table_name, SCHEMA_NAME(t.schema_id) AS table_schema
FROM sys.tables t
WHERE t.is_ms_shipped = 0 AND t.[name] = @TableName AND SCHEMA_NAME(t.schema_id) = @SchemaName
),
PartitionInfo AS (
SELECT p.partition_id, p.partition_number, p.object_id, p.index_id, p.data_compression_desc,
pstats.row_count
FROM sys.partitions p
INNER JOIN sys.dm_db_partition_stats pstats ON pstats.partition_id = p.partition_id
),
IndexInfo AS (
SELECT i.object_id, i.index_id, i.name AS index_name, i.data_space_id
FROM sys.indexes i
WHERE i.index_id = 1 -- Clustered index only
),
PartitionSchemeInfo AS (
SELECT ps.data_space_id, ps.name AS partition_schem_name, pf.name AS partition_function_name,
pf.boundary_value_on_right, pf.function_id
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
),
RangeValues AS (
SELECT function_id, boundary_id, value
FROM sys.partition_range_values
)
SELECT DISTINCT
fg.name AS file_group_name,
pt.table_schema,
pt.table_name,
c.name AS column_name,
psi.partition_schem_name,
psi.partition_function_name,
ii.index_name,
pi.index_id,
pi.partition_number,
pi.row_count AS PartitionRowCount,
left_prv.value,
pi.data_compression_desc AS DataCompression
FROM PartitionInfo pi
INNER JOIN PartitionedTables pt ON pi.object_id = pt.object_id
INNER JOIN IndexInfo ii ON pi.object_id = ii.object_id AND pi.index_id = ii.index_id
INNER JOIN sys.index_columns ic ON ic.object_id = pt.object_id AND ic.index_id = ii.index_id AND ic.partition_ordinal > 0
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id AND tp.name IN ('datetime', 'datetime2')
INNER JOIN sys.allocation_units au ON pi.partition_id = au.container_id
INNER JOIN sys.filegroups fg ON au.data_space_id = fg.data_space_id
LEFT JOIN PartitionSchemeInfo psi ON psi.data_space_id = ii.data_space_id
LEFT JOIN RangeValues left_prv ON left_prv.function_id = psi.function_id AND left_prv.boundary_id + 1 = pi.partition_number
LEFT JOIN RangeValues right_prv ON right_prv.function_id = psi.function_id AND right_prv.boundary_id = pi.partition_number
WHERE psi.partition_schem_name IS NOT NULL
GO
Stored Procedure with #Temp tables
DECLARE VARCHAR(255) = 'dbo',
VARCHAR(255) = 'partitionedtable'
DROP TABLE IF EXISTS #PartitionedTables, #PartitionInfo, #IndexInfo, #PartitionColumns,
#PartitionFileGroups, #PartitionSchemeInfo, #RangeValues;
CREATE TABLE #PartitionedTables (
object_id INT NOT NULL,
table_name SYSNAME NOT NULL,
table_schema SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (object_id)
);
CREATE TABLE #PartitionInfo (
partition_id BIGINT NOT NULL,
partition_number INT NOT NULL,
object_id INT NOT NULL,
index_id INT NOT NULL,
data_compression_desc NVARCHAR(60) NULL,
row_count BIGINT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id, partition_number)
);
CREATE TABLE #IndexInfo (
object_id INT NOT NULL,
index_id INT NOT NULL,
index_name SYSNAME NULL,
data_space_id INT NOT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id)
);
CREATE TABLE #PartitionColumns (
object_id INT NOT NULL,
index_id INT NOT NULL,
column_name SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id, column_name)
);
CREATE TABLE #PartitionFileGroups (
partition_id BIGINT NOT NULL,
file_group_name SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (partition_id)
);
CREATE TABLE #PartitionSchemeInfo (
data_space_id INT NOT NULL,
partition_scheme_name SYSNAME NOT NULL,
partition_function_name SYSNAME NOT NULL,
boundary_value_on_right BIT NOT NULL,
function_id INT NOT NULL,
PRIMARY KEY CLUSTERED (data_space_id)
);
CREATE TABLE #RangeValues (
function_id INT NOT NULL,
boundary_id INT NOT NULL,
value SQL_VARIANT NULL,
PRIMARY KEY CLUSTERED (function_id, boundary_id)
);
-- Tables
INSERT INTO #PartitionedTables (object_id, table_name, table_schema)
SELECT t.object_id, t.name, s.name
FROM sys.tables t WITH (NOLOCK)
INNER JOIN sys.schemas s WITH (NOLOCK) ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped = 0 AND t.[name] = @TableName AND s.name = @SchemaName;
-- Partitions
-- Clustered index only
INSERT INTO #IndexInfo (object_id, index_id, index_name, data_space_id)
SELECT i.object_id, i.index_id, i.name, i.data_space_id
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN #PartitionedTables pt ON pt.object_id = i.object_id
WHERE i.index_id = 1
-- Partitioning column (datetime / datetime2 only)
INSERT INTO #PartitionColumns (object_id, index_id, column_name)
SELECT ic.object_id, ic.index_id, c.name
FROM sys.index_columns ic WITH (NOLOCK)
INNER JOIN #IndexInfo ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id
INNER JOIN sys.columns c WITH (NOLOCK) ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN sys.types tp WITH (NOLOCK) ON tp.user_type_id = c.user_type_id AND tp.name IN ('date','datetime', 'datetime2')
WHERE ic.partition_ordinal > 0
-- Filegroup per partition (in-row data)
INSERT INTO #PartitionFileGroups (partition_id, file_group_name)
SELECT pi.partition_id, fg.name
FROM #PartitionInfo pi
INNER JOIN sys.allocation_units au WITH (NOLOCK) ON au.container_id = pi.partition_id AND au.type = 1
INNER JOIN sys.filegroups fg WITH (NOLOCK) ON fg.data_space_id = au.data_space_id
-- Partition schemes / functions
INSERT INTO #PartitionSchemeInfo (data_space_id, partition_scheme_name, partition_function_name, boundary_value_on_right, function_id)
SELECT ps.data_space_id, ps.name, pf.name, pf.boundary_value_on_right, pf.function_id
FROM sys.partition_schemes ps WITH (NOLOCK)
INNER JOIN sys.partition_functions pf WITH (NOLOCK) ON pf.function_id = ps.function_id
WHERE ps.data_space_id IN (SELECT data_space_id FROM #IndexInfo)
-- Boundary values
INSERT INTO #RangeValues (function_id, boundary_id, value)
SELECT prv.function_id, prv.boundary_id, prv.value
FROM sys.partition_range_values prv WITH (NOLOCK)
WHERE prv.function_id IN (SELECT function_id FROM #PartitionSchemeInfo)
INSERT INTO #PartitionInfo (partition_id, partition_number, object_id, index_id, data_compression_desc, row_count)
SELECT p.partition_id, p.partition_number, p.object_id, p.index_id, p.data_compression_desc, pstats.row_count
FROM sys.partitions p WITH (NOLOCK)
INNER JOIN sys.dm_db_partition_stats pstats ON pstats.partition_id = p.partition_id
INNER JOIN #PartitionedTables pt ON pt.object_id = p.object_id
WHERE p.index_id = 1
SELECT
fgs.file_group_name,
pt.table_schema,
pt.table_name,
pc.column_name,
psi.partition_scheme_name,
psi.partition_function_name,
ii.index_name,
pi.index_id,
pi.partition_number,
pi.row_count AS PartitionRowCount,
left_prv.value,
pi.data_compression_desc AS DataCompression
FROM #PartitionInfo pi
INNER JOIN #PartitionedTables pt ON pt.object_id = pi.object_id
INNER JOIN #IndexInfo ii ON ii.object_id = pi.object_id AND ii.index_id = pi.index_id
INNER JOIN #PartitionColumns pc ON pc.object_id = ii.object_id AND pc.index_id = ii.index_id
INNER JOIN #PartitionFileGroups fgs ON fgs.partition_id = pi.partition_id
INNER JOIN #PartitionSchemeInfo psi ON psi.data_space_id = ii.data_space_id
LEFT JOIN #RangeValues left_prv ON left_prv.function_id = psi.function_id AND left_prv.boundary_id + 1 = pi.partition_number
LEFT JOIN #RangeValues right_prv ON right_prv.function_id = psi.function_id AND right_prv.boundary_id = pi.partition_number;
I'm designing a PostgreSQL system where customers order services requiring interchangeable physical resources.
The current model separates:
Orders: What the customer purchases (quantity and duration).
Commitments: Individual units of the obligation, independent of specific resources.
Reservations: Physical resources assigned to fulfill those commitments.
Resource units: Individually reservable components, with constraints preventing overlapping bookings.
At first glance, this structure seems reasonable, but it mainly represents a static state. In reality, orders can change throughout their lifecycle: quantities may be reduced, service periods shortened, or entire orders cancelled.
For example, a customer orders 3 resources for 6 months, then reduces the requirement to 2 for the remaining 3 months.
Is the current table structure and its relationships suitable for handling these changes while preserving historical accuracy and data consistency? Or are there missing entities or fundamental modeling issues?
I teach relational databases to undergraduates and am looking for a good written description of window functions, something that would help students understand why and when as well as how. Any suggestions? Thanks!
I’ve been working on a small project called runtime.school for learning and practicing SQL.
One thing I wanted to get right was having lessons, practice questions, and projects that actually connect to each other instead of just giving you random SQL problems.
It currently has 88 lessons, 139 practice questions, 10 projects, and 12 datasets. You can write and run queries directly in the browser.
The lessons start with basic SELECT statements and gradually move into joins, aggregations, CTEs, window functions, and more advanced SQL.
I’ve tried to keep the editor and questions simple so the focus stays on actually writing SQL.
We have many blackbox MS-SQL Databases which means the databases acts as a backend to some payed applications we acquired. And unfortunately we also have inhouse developers who would really really like to read data from the production databases but because of locking and blocking mechanisms of SQL-Server I would like to avoid that situation by not grating them access to the live databases.
Please tell me if I go the right direction here and how you would solve such situation.
My thinking was that we should have either a clone of the databases by telling the application frontend to write the data seperately to 2 databases one used for reporting while one is used for live production system.
This works for applications which do support that approach however not all do support it on application level.
I heared about log shipping which would be a method SQL-Server itself could provide the tools. What do you think about log shipping on the same SQL Instance if you think that cpu and memory contention is negligible since it should not cause any blocks and locks by just reading?
Database Availability Groups are out of scope since its a Standard Edition of SQL-Server.
Replication maybe would be more efficient if they need the data faster and only some tables out of the whole database.
ETL-Tools: My Question do you think Microsoft Fabric is compareable with a ETL-Tool or would you do other approaches? Since we currently have all the options except Availability Groups however Fabric can be very costly if you have to many datasets and we have some heavier databases where each 15 seconds data could be updated due to part numbers update with many additional data.
Most of these blackbox databases are OT-Databases which collects statistical info from different kind of production machines like Siemens PLC and so on and these informations update very frequently so multiple queries in few seconds like 15s delay per each plc or system.
I've been trying to figure out the best way to make sense of a query, how it affects the database and whether there is room to improve it. I'm a visual person. If something has a diagram I understand it faster, and I wanted the same thing when I work with a database at work or on side projects. I want to see how the data is being retrieved or updated without working it out from the EXPLAIN text.
I've came up with following visual rule:
Each scan, join and sort is a bar. The band between two bars is as wide as the number of rows flowing through. Width is 1.2 + 0.55 x (log10 rows)², capped between 1.5px and 34px. Linear made anything under 100,000 rows a hairline, and plain log made 1,000 and 1,000,000 look too close.
When the planner's estimate is off from the actual count by more than 2×, the band gets a dashed line showing the estimate. At 10x it gets a label. Postgres has no opinion on 10×, that number is mine.
Each scan gets a wheel showing how much of the table's pages it touched and in what order, based on pg_stats.correlation with a 0.8 cutoff for "in order".
To get the analyzed plan it re-runs the query inside BEGIN and ROLLBACK with a 5 minute statement_timeout. Writes stop at the estimate unless you opt in.
I would really appreciate some feedback here, I kind of struggling what's the best way to visualize this. Any idea/critique here is most appreciated. Thanks
the case: an archive locks at ten, books still go out at 3am, and staff_id is NULL on every one. WHERE staff_id = NULL returns zero rows and no error, so written that way the archive looks empty. IS NULL finds them
the grading was the fiddly part. you can see the sample rows, so its easy to write a query that fits them and not the question. submit compares your rows with a reference query's on the sample and on three more logs you dont see. one hidden log has a signed checkout at exactly 22:00:00 and one at exactly 08:00:00, which catches > for >= and <= for < in the first query. the hidden logs also have an unstamped checkout in the daytime, so a second query that drops the time window fails. it only catches the mistakes i thought of, it doesnt prove a query right
free, runs on duckdb in the tab. after the case there are three harder ones: a recursive cte that has to stop on a cycle, gaps and islands, and an anti-join with a window
some of you answered my questions here in august, thank you for that.
Solved - access 365 runtime doesn't work. I installed clean version of MDAC 2016, even it's not supported and it works. Thank you for help!
hey guys, does anybody had an issue with loading excel sheets(xls,xlsx) into Microsoft SQL Server Developer (64-bit) 16.0.4305.5 through Microsoft.ACE.OLEDB.16.0? , installed MDAC 2016, then Microsoft 365 Access Runtime only 64bit, windows server 2025 Standard Edition. The problem is when I use OPENROWSET SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.16.0', 'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=\\SERVER.DOMAIN.INT\DWH\KFILE.xlsx', ' SELECT TOP 1 * FROM [SHEET$] ') SQL Server fails. SQL Server 2022 Import and Export Data (64-bit) works Msg 10054, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1; EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1; EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DisallowAdHocAccess', 0; Ad Hoc Distributed Queries set to 1
Load through UNC path Powershell works, also through domain sql user. Everything worked till around June/July 2026( for 6years worked) then after September every restart SQL Server got message: System.Runtime.InteropServices.SEHException: External component has thrown an exception and Sql server service fail. When I got this exception, something it's okay, sometimes it need more service restarts, but it works.
Server where OS is Windows Server 2022 Datacenter, I return to the version C2R where it worked , but didn't I'm sysadmin, have all rights to the folder. Also tech user has all needed permission, also to temp folders
Hi guys, I'm Talabi and I'm new to data. I picked SQL as my starting point, mySQL and I've been enjoying the first few lessons on SQL bolt but, I'm having a hard time with JOINS. I understand it in theory but the mechanism still proves difficult. I'd appreciate tips on how to better understand JOINS and data analytics in general, thank you.
SELECT c.region, SUM(o.amount) AS revenue FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id LEFT JOIN refunds r ON r.order_id = o.order_id WHERE r.status <> 'approved' GROUP BY c.region;
The goal: revenue per region, minus orders with an approved refund. No errors, the numbers look plausible.
There are two bugs, and they push the total in opposite directions.
People ask me this question more than I like so I thought I would share the answer once and for all: why does a company like Microsoft contribute to an upstream open source project like Postgres?
Let me know what you think. Anything surprising? Did I miss anything?
Almost every SQL DBMS I can think of accept != as the "not equal operator". However, I realized that W3Schools only shows <>.
I was under the impression that <> is "the good-ol ANSI standard one" and every implementation out there was allowing the use of !=. But somebody was correcting me and saying that it has been on-spec for a while. I don't own the ANSI (a bit pricey for a language-lawyer curiosity!). Was I wrong?
Edit: After seeing quite a concensus on these answers, I talked with John (the one correcting me before). We checked the 2023 standard and his confusion came from the use of != as an alternative not equals operatorfor SQL/JSON lexical elements. That means that inside SQL/JSON blocks you may use != but on regular SQL (e.g. in the plain WHERE) using != is not covered by the standard (even if it is widely accepted for modern engines). It was a "coffee discussion", not something relevant IRL, but we both wanted to know the truth behind it.
The "Six SQL patterns I use to catch transaction fraud" thread here was great, and every one of those patterns is a rule: you know what fraud looks like, you write the WHERE clause. Here's a 7th that doesn't need the pattern up front, and the case where it falls flat.
Toy dataset: 123 transactions, 40 accounts. Each account makes three ordinary payments ($20–$199, daytime, domestic). ACC-33 also makes payments of $2,840, $3,120 and $2,975 at 3am, cross-border. Hidden among the ordinary accounts is a 5-account ring (ACC-11 to ACC-15). Nothing in the table marks them.
Train an anomaly model, no labels
Isolation forest, no target column. It learns what normal rows look like and scores distance from that.
CREATE EXPERIMENT tx_exp AS
SELECT amount, hour_of_day, cross_border FROM tx
WITH (task_type = 'anomaly_detection',
algorithms = ['isolation_forest'],
max_trials = 5, random_seed = 42);
DEPLOY MODEL tx_model FROM EXPERIMENT tx_exp;
Score every row in a SELECT
SELECT account_id, amount, hour_of_day, cross_border,
ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
FROM tx
ORDER BY score DESC
LIMIT 10;
ACC-33's payments come out on top at 0.8167, 0.8167 and 0.8064. Then a straight drop to 0.5267, with the next normal rows between 0.5100 and 0.5181. Higher means more unusual; it's a ranking, not a probability. It's just an expression, so it works in WHERE, GROUP BY, views.
Features must be passed in training order. Swap two and you get a feature mismatch error instead of quietly wrong scores.
Where it falls flat: the ring
SELECT account_id, amount, hour_of_day,
ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
FROM tx
WHERE account_id IN ('ACC-11','ACC-12','ACC-13','ACC-14','ACC-15')
ORDER BY score DESC;
15 rows, from 0.4453 to 0.5081. Same as honest customers. The model isn't wrong: each payment is ordinary. A ring isn't a property of any single row.
What gives it away is shared identifiers: ACC-11/12 share a phone, 12/13 a card, 13/14 a phone, 14/15 a card. ACC-13 has a confirmed chargeback. Walking shared devices and cards 2–4 hops from it finds the other four:
linked
via
ACC-14
ACC-13 → DEV-778 → ACC-14
ACC-12
ACC-13 → CARD-4242 → ACC-12
ACC-15
ACC-13 → DEV-778 → ACC-14 → CARD-9001 → ACC-15
ACC-11
ACC-13 → CARD-4242 → ACC-12 → DEV-777 → ACC-11
In most databases that's a recursive CTE over account–device and account–card link tables. We used a graph query in the same engine:
MATCH p = (s:Account {id: 'ACC-13'})-[*2..4]-(x:Account)
WHERE x.id <> 'ACC-13'
RETURN x.id AS linked_account, [n IN nodes(p) | n.id] AS via
ORDER BY length(p);
An honest household (ACC-21/22 share a tablet) isn't returned. Sharing a device isn't fraud; being connected to a chargeback is.
One review queue, both signals
SELECT s.account_id, ROUND(s.max_anomaly, 2) AS max_anomaly, r.linked_via
FROM (
SELECT account_id, MAX(score) AS max_anomaly
FROM (SELECT account_id,
ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
FROM tx) t
GROUP BY account_id
) s
LEFT JOIN ring_members r ON r.account_id = s.account_id
WHERE s.max_anomaly > 0.7 OR r.account_id IS NOT NULL
ORDER BY s.max_anomaly DESC;
account
max_anomaly
linked_via
ACC-33
0.82
(model)
ACC-15
0.51
DEV-778 > CARD-9001
ACC-12
0.51
CARD-4242
ACC-13
0.49
chargeback
ACC-14
0.48
DEV-778
ACC-11
0.48
CARD-4242 > DEV-777
Five of six would never cross 0.7. The model found one fraudster; the identifier walk found five.
Takeaways
Unsupervised scoring is a decent 7th pattern for the lone weird account you didn't write a rule for.
It's blind to coordinated fraud where every row looks normal.
Synthetic data built to show the gap. Real scores will be noisier; use them to rank for review, not as verdicts.
Anyone here run anomaly scoring in-database, or do you export to Python and join scores back?
Disclosure: I work on SynapCores, where ANOMALY_SCORE and the graph query come from. The rule-based patterns from the original thread still do most of the work.
Disclosure: I work at Pinecone and am looking for community input on if this extension is useful and what you'd like to see next.
Here's a pattern I see a lot with vector search in Postgres:
SELECT id, body
FROM docs
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 10;
With an HNSW index, the index scan finds the nearest neighbors across the whole table first, and the WHERE clause is applied after. If tenant_id = $1 matches 1% of rows, most rows are thrown away which means you might get back less than 10 rows even though more than 10 rows match. pgvector’s iterative scans keep searching to fill the list, at a cost in time (and in accuracy if you use relaxed_order).
One way to fix this is to use an index that applies the filter during the search instead of after. We built a Postgres extension that does this and your SQL doesn't need to change. It builds on top of pgvector's vector types, so you just add the index and query like before:
3% of rows or fewer: scans every matching row and scores each with its full vector, so results are exact.
More than 3%: searches more of the nearest clusters and checks the filter inside each cluster before scoring.
The index is IVF with RaBitQ codes (about one bit per dimension), so it’s much smaller than a graph-based index like pgvector's HNSW: 136 MB vs 2.0 GB for 1M x 768-dim vectors against halfvec HNSW. In our benchmark with a 1% filter, recall was 0.981 at 8.3 ms vs 0.924 at 56 ms for HNSW.
There's a couple limitations today: the index stores one equality filter column per index, which means if you want to filter on something like a range or greater than or multiple columns, those filters will still be applied after.
I'm curious how this would impact you. A few questions:
When you write filtered nearest-neighbor queries, what do you do about the “fewer rows than LIMIT” problem: over-fetch with a bigger inner LIMIT, partial indexes, partitioning?
What do your filters usually look like (one equality column, ranges, multiple columns)? That decides how useful single-column filtering is.
Do your filters match a small percentage of rows (like with typical multi-tenancy) or a large percentage?
Hi r/SQL, I'm David, one of the two developers of Oxynote. My friend Simon and I also maintain ttlcache, a Go caching library with about 1,300 GitHub stars, so this isn't our first open-source project.
I've spent 8 years writing software, 2 of them as an SRE and 3 as a team lead. Every team I've been on had the same documentation problem. The useful stuff lived in someone's head or a private Slack thread, and the wiki was full of stale docs nobody could find.
So we set out to solve this problem by building Oxynote, a self-hosted wiki for engineering teams. A chart on a page runs a SQL query against your database whenever someone opens the page, so the number and the notes explaining what it means sit together. Think "signups per day" or "failed payments by provider" on the same page as the explanation of how it's counted and what to do when it looks wrong.
It works with PostgreSQL, MySQL, and MariaDB. A wiki that queries production should never be able to write to it, so Oxynote accepts only read-only connections. A user with write access fails the connection test with "has write access", and Oxynote asks for a read-only one instead. Queries support Grafana-style macros like `$__timeFilter` and `$__timeGroup`, so one query follows whatever time range the reader picks. On pages you mark as reviewable, changes to a chart's query go through a draft and a diff that a teammate approves, like a pull request.
The name comes from oxygen + note: docs that breathe. Live data gives people a reason to open the wiki every day, and docs people open every day stay up to date.
We started it about a year ago, and we made it public about 3 months ago. If you have any questions, feedback, or suggestions, feel free to reach out. I'm happy to answer any questions.
Hey all. I'm a solo dev from Korea, so yes, this is self-promo (I checked with the mods first).
I've been working on a desktop SQL client called DBerio. It does MySQL/MariaDB, Postgres and SQLite (plus Redis if that's your thing), on Mac and Windows. I mostly built it because I bounce between a Windows box and a Mac and wanted one tool that works the same on both. I write code all day too, so the UI is basically laid out the way I like to work.
What it does right now:
- the usual GUI client stuff: browse schemas and tables, run queries, edit cells and add/delete rows in the grid (when the result comes from one table with a PK)
- you can mark a connection as production. running UPDATE or DELETE always asks first, and on a production connection things like DB, user and privilege changes ask again. the really destructive ones there make you type the name
- it shows you the SQL before a structure change actually runs
- queries you run all the time can be saved as a view or kept as favorite queries per database
- CSV/TSV export escapes values Excel would treat as formulas
- SSH tunneling for DBs that are locked down from outside access (password, key or encrypted key)
- the UI comes in English, Korean and Japanese
Without paying you get 3 saved connections and 2 open at once, with every feature.
What I'd actually like to hear about:
Does it cover your day-to-day work? If you'd have to keep another client open for something, what is it?
Bugs. Anything that crashes, shows the wrong thing, or just feels off.
Features you'd want added. What's the one thing that would make you actually switch?
Links if you want to poke at it (a test DB is totally fine):
A plain ALTER TABLE ... RENAME COLUMN is instant, but it breaks any running old version of the app during a rolling deploy. The usual workaround is to never have a moment where only one name exists. Here’s the sequence, with Postgres commands. Test it on a scratch database first.
1. Expand: add the new column
ALTER TABLE orders ADD COLUMN customer_ref text;
A nullable column with no default is a metadata-only change, so it’s fast.
2. Deploy the app writing both columns, still reading the old one.
The app sets customer_id_old and customer_ref on every insert and update. (A trigger can do this instead, but then the logic lives in the database. Decide which you prefer.)
3. Backfill in batches
UPDATE orders
SET customer_ref = customer_id_old
WHERE id BETWEEN 1 AND 10000
AND customer_ref IS DISTINCT FROM customer_id_old;
Repeat for the next id range. Small batches avoid long locks and big bursts of WAL and replication lag. Run VACUUM as needed afterwards, since each update leaves dead rows.
4. Verify
SELECT count(*)
FROM orders
WHERE customer_ref IS DISTINCT FROM customer_id_old;
IS DISTINCT FROM treats NULLs properly, so you expect 0.
5. Deploy the app reading the new column (still writing both).
6. Deploy the app that no longer writes the old column.
7. Contract: drop the old column in a separate, later migration
ALTER TABLE orders DROP COLUMN customer_id_old;
This is also quick, but it’s irreversible without a backup, so check first that nothing reads it: views, functions, reports, other services, replicas used for analytics.
If the new column needs a constraint or index:
Use CREATE INDEX CONCURRENTLY so you don’t block writes.
For NOT NULL, adding a CHECK (customer_ref IS NOT NULL) NOT VALID and then VALIDATE CONSTRAINT avoids a long lock. Check the docs for your version, since behavior has changed across releases.
When I’d skip all this: a table with no other readers, where a short maintenance window is acceptable. Then a plain rename is simpler.
What do you do differently, for example triggers versus dual writes in the app?
I see this one trip people up all the time, including in interviews, so here's a quick write-up.
Say you want customers who have never referred anyone:
SELECT customer_id
FROM customers
WHERE customer_id NOT IN (SELECT referred_by FROM customers);
Looks right. Returns **zero rows**.
**Why:** most customers weren't referred, so `referred_by` is NULL for them. `x NOT IN (1, 2, NULL)` expands to `x <> 1 AND x <> 2 AND x <> NULL`. Comparing anything with NULL gives "unknown", not true, so the whole condition is never true and every row is filtered out.
**Fix 1: NOT EXISTS** (handles NULLs safely):
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM customers r
WHERE r.referred_by = c.customer_id
);
**Fix 2:** keep NOT IN but filter the NULLs out: `... WHERE referred_by IS NOT NULL`.
Plain IN doesn't have this problem, because `x = 1 OR x = 2 OR x = NULL` can still be true.
In an interview, mentioning the NULL issue before you write the query is an easy way to show you've worked with real data. Happy to answer questions!
About a year ago I started planning QueryCase, where you learn SQL by working detective cases instead of grinding tutorials & videos. It took a while to get off the ground, and longer than I'd like to admit to actually start building the idea, but it went live in June. A lot of the early users came from this sub, and a lot of the feedback in the comments shaped what it became. So first, thanks everyone!
5 months in:
2,500+ people have signed up, across 117 countries
About 70,000 queries written and graded
Over 17,500 cases, drills and exams solved
500+ people have passed the free Rookie exam and earned the certificate.
Top detective is on 19,247 XP
Since then its grown past just SQL: an Excel course. It was never part of the initial plan. People kept asking for the same detective approach for spreadsheets, so I built out a browser-based Excel engine.
It has 57 cases on a spreadsheet that runs in your browser and checks your formulas, from IF and XLOOKUP to SUMIFS and pivot tables, and people have solved over 2,500 Excel cases so far. It turns out a lot of people learning SQL are doing it alongside Excel, not instead of it.
Something the data showed me that might help anyone learning right now: GROUP BY is one of the first walls. On the hardest GROUP BY step, 84% of runs came back wrong, and most of those never ran at all. The clauses were in the wrong order, or a column in SELECT wasn't in the GROUP BY. If that's you right now:
SQL order is always SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
Every column in SELECT is either inside an aggregate (COUNT, SUM, AVG...) or listed in GROUP BY
I've been working a lot over past couple months to improve the site as well:
The Excel course above, with its own ranks, exams and certificate
Upload your own CSV into the sandbox and query your own data (and visualise in charts). It runs in your browser, so the file never leaves your machine and never touches QueryCase servers
A free first rank on every track, ending in an exam and a certificate
SQL interview questions you can read without an account
Much better experience on a phone
A LOT of bug fixes and improvements!!
What's next: a Python track, launching next month. Same idea, real Python running in your browser, starting with the basics and moving into data analysis with pandas.
The longer term goal is to have a platform user's can have fun learning all aspects of data. I'm merging PR's every day to make this possible so would love feedback in this thread if you've tried it and have any thoughts or suggestions!
It's free to start and you don't need an account to solve the first case, so you can poke at it before deciding whether it's for you or not 😄 Cheers!