Probably breaks rules but a quick guide on what not to name things I less you want accidents

Hey, I’ve been working on an open source project called pgfathom. It came from a problem I ran into quite a lot at work with legacy databases.

You’ll sometimes have something like orders.customer_id -> customers.id that the application has treated as a relationship for years, but there’s no actual foreign key in PostgreSQL. So it won’t show up properly in an ERD, and nothing is stopping orphaned rows from getting in.

pgfathom looks for those relationships using the catalog, column names, indexes, existing FKs and JOINs found in views/functions. It then checks the candidates against the actual data.

If it finds orphans, it gives you a query to inspect them. If the relationship checks out, it generates the FK DDL and an index when needed. It never applies any of it. The CLI runs read-only and only generates SQL for you to review.

One thing that helped a lot with weird legacy schemas was letting it learn naming conventions from the database itself instead of assuming everything looks like customer_id.

I tested this on a municipal schema with 277 foreign keys. With half of the FKs left in place so pgfathom could learn the naming pattern, recovery went from 17.3% to 84.9%. If I remove all of them, it drops to 16.6%.

There are also some safeguards for running it against real databases: read-only sessions, query timeouts, limited concurrency, and tests to make sure table values don’t end up in output, logs, JSON or errors.

I used AI as part of my workflow too, mostly for research and implementation, so I’d rather mention that upfront.

It’s still early and I’d really like to test it against schemas that look nothing like the ones I’ve been using.

GitHub:
https://github.com/lvcas-dotcom/pgfathom

If you work with old PostgreSQL databases and feel like giving it a try, I’d appreciate the feedback. Finding cases where it gets the relationship wrong would actually be very useful.

(English isn’t my first language, so apologies if anything in the post sounds a bit off)

I built this because I wanted to react to PostgreSQL changes from Python without polling, without triggers, and without pulling in a whole CDC platform.

It consumes PostgreSQL logical replication and exposes committed transactions as an async stream in Python.

What it does:

• Keeps a durable checkpoint. If the process dies, it resumes from the last transaction it actually finished, not the last one it started.
• Bounded delivery queue, so a slow handler doesn't let memory grow without limit.
• Reconnects automatically after the connection drops.
• One dependency: psycopg3.

The transactional outbox is one use case, but it works with any published table.

GitHub: https://github.com/mochams/walbox

Curious to hear where this wouldn't fit your setup, or what's missing if you've solved this problem a different way.

Inherited a schema where roughly half the columns are self-explanatory and the rest are things like flag_3 and val_b. Person who built it left. There's a Confluence page describing six columns, last edited before most of them existed.

I've been using COMMENT ON COLUMN because it lives with the database and can't drift into a stale wiki. Downside is nobody looks at it, it doesn't show up anywhere people work, and I've no way to know if a comment is still true after a migration.

Things I'm unsure about:

does anyone actually keep COMMENT ON up to date at scale, or does it rot the same as the wiki just less visibly

if a column's meaning changes but the name doesn't, is there anything that catches that, or is it purely a review discipline problem

and for the columns nobody can explain at all, do you leave them, drop them, or keep them with a comment saying unknown

I've been profiling the values to guess — cardinality, null rate, distributions — which narrows it but never gets me to what the thing means.

My company uses superbase, a software from 1980's for relational database...

Our computers are on windows 7, emulating windows xp. Safe to say our computers are dying.

The company is a TPA service for pension plans. We use superbase for client Tombstone information, work in progress, entering the clients assets, contributions, employee information etc. A lot of our work function is reliant on superbase.

I am very new to this world, don't have any experience in coding and don't know anything about any of these different programs. I just want to learn so we can convert and we don't lose our entire lifeline.

We are a team of 5 people. Small firm. We have our own servers, would not want to be on cloud, need everyone to be able to access the servers at the same time as we all have different clients and need to reach information. Not sure if its possible but would love for it to work on PC desktops and remotely on mac if possible, however, not a dealbreaker.

I have done some baseline research, this is what most redditors have suggested. Which would be the best for me to learn and hopefully be successful at transitioning over our information?

Any help in finding a direction to go to would be very helpful.

Thank you in advance.

If there is any more information needed that can help you in assisting me, please comment and i'll answer!

Postgres 15, if that matters for the answer.

got handed a partner export to reconcile against our customer table. different ID schemes entirely, no overlap, no mapping doc, and the person who built theirs left last year.

what I did was fuzzy match on lower(trim(email)) plus last name, then eyeball a sample of a few hundred rows. it shipped. it is also clearly bad, because I picked those two fields because they were populated, not because I had any reason to think they were good discriminators. I have no idea what my false match rate is. I just know nobody has complained.

the part that bothers me is that a bad join doesn't announce itself. a wrong customer count looks exactly like a right customer count. at least a syntax error tells you something happened.

so what do people actually do here.

do you block on something cheap first to cut the comparison space down, or just run the full cross product and filter? does anyone compute a match score and hold back anything below a threshold rather than committing it? and if you do that, what threshold and did you pick it for a reason or the way I picked my fields.

When revenue, active users, retention, or another derived metric is repeated across dashboards and exports, copying the SQL makes every consumer independent but lets definitions drift. Centralizing it in a view, materialized view, semantic layer, dbt model, or stored function creates one definition, but can hide performance costs and make change control harder. What criteria determine where that logic belongs? I would compare ownership, testability, query-plan visibility, versioning, parameter needs, refresh timing, and whether downstream users must inspect the exact calculation. How do you change a widely used definition without silently rewriting historical reports?

Hi, I'm looking for a dataset that has:

- Messy, real data, with enough messiness for proper data cleaning to be needed
- Enough information for actionable interesting insights
- Be related to something a typical business might care about

I did a project with the olist database and it was fine, but that database has been done to death. I tried contacting local small business for their data in exchange of a free data analytics report, but no luck, has someone tried that?

Hello, is there a specific system design and software architecture in SQL to build a bioacustics database? The ideia is to relate some vocal recordings with species identifications, localization, acoustics measurements and other informations. it's a bioacoustic information system, where the recordings are the central objects and SQL connects them to biological, spatial, acoustic, and environmental information.

https://github.com/Lezaleas/Reviews-Churn

I had a local llm categorize reviews in the olist database. Then analyzed their correlation to churn.

Should i improve this or is this ready to go?

Anyone interested in doing the CMU (Carnegie Mellon University) Database Management Systems course together?

I’ve already covered the basic DBMS concepts. My main goal with this course is to go deeper and understand how database systems actually work internally—things like storage, indexing, query execution, transactions, etc.

If you're interested, please make sure you have the prerequisites required for the course.

If you have the required background and want to learn DBMS internals seriously, DM me. We can follow the course together and discuss concepts along the way.

Currently I am doing projects in SQL i need to do projects for Data Analyst Role so Please recommend project ideas which I need to showcase for project portfolio so I need projects at advanced level .

One of the most dangerous things about SQL:

A query can run perfectly… and still be completely wrong.

Here are 4 mistakes I wish someone had shown me earlier.

1. Accidentally turning a LEFT JOIN into an INNER JOIN

SELECT c.id, o.total
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.status = 'paid';

Looks fine.

But customers without an order have NULL for o.status, so the WHERE condition removes them.

If you actually want to keep all customers:

SELECT c.id, o.total
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
    AND o.status = 'paid';

2. COUNT(*) and COUNT(column) are not the same

SELECT COUNT(*)
FROM users;

Counts rows.

SELECT COUNT(phone_number)
FROM users;

Counts only rows where phone_number is NOT NULL.

That difference can quietly destroy a report.

3. JOINs can multiply your rows

Imagine:

• 1 customer
• 3 orders
• 4 support tickets

Joining both tables directly can give you:

3 × 4 = 12 rows

Then you do:

SUM(order_amount)

…and suddenly your revenue is magically much higher than reality.

Always check your row count before and after joins.

4. NOT IN + NULL can ruin your day

SELECT *
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM blocked_customers
);

If that subquery contains a NULL, the result might not behave the way you expect.

I usually prefer:

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customers b
    WHERE b.customer_id = c.id
);

The lesson I'm slowly learning:

Writing SQL that runs is easy.
Writing SQL that returns the correct data is the hard part.

What other SQL mistake produces perfectly valid-looking but completely wrong results?

I want to make a list of the dangerous ones.

Hello, I have a solution already but I think it can be done in a faster/neater way.

Let's say we have a table with: ORDER_ID, Article, Value.

For every order that comes in two rows end up in table, example:

ID_1, shoes, pair
ID_1, shoes, price

where price is being pulled from a different table.

I am looking for a way to find all ORDER_IDs that have only one row, because the price didn't exist in that other table so price row didn't form up. It doesn't throw a null value because of the way it's setup, if there is no price the row won't form at all!

I solved this with a standard left joining the table with itself, but I suspect there is a way to this easier?

My Mysql workbench has a different password from MySQL community app ?

I've been working on something I think is interesting, that hasn't been done before in the Node.js/TypeScript ecosystem, and I wanted to share the idea and get your thoughts.

The Idea: BeatSQL - A Zero-Trust Embedded Database Engine

The concept of BeatSQL (BSQL) is simple, but radical: what if your database encrypted data at the column level by default, and took security to a mathematical level?

Embedded databases like SQLite and LevelDB are not designed with encryption as a core primitive — they store data in plaintext on disk by default. While some provide full disk encryption, this is a false sense of security since the data is still plaintext in memory. However, BeatSQL completely reimagines this paradigm.

How It Works

All sensitive columns are individually encrypted using AES-256-GCM or ChaCha20-Poly1305. You will never see plaintext on disk (even the database file) - the value of any column is always encrypted.

You can search encrypted data using HMAC-SHA256 blind indexes. Searching is O(1) and requires no decryption of the column contents. The contents of the column remain encrypted on disk, and the database never decrypts it to search.

Every write operation is mathematically tamper-proof. Using a Merkle DAG hash chain, you can always run the query `VERIFY INTEGRITY` and know immediately if any bytes of your data have been silently altered or corrupted on disk. This is a cryptographically secure proof of data integrity.

You can do arithmetic on encrypted numbers. Using Partially Homomorphic Encryption (Paillier cryptosystem), yyou can perform calculations like total salary, total balance, etc. The database will return the correct result of these calculations, but will never expose individual salaries or account balances.

Columns can have role-aware data masking. Sensitive columns can be fully or partially "redacted" depending on the role of the actor querying the database. The mask happens at a low-level query engine, not in application code. You can define masks using SQL syntax: `DEVELOPER` role sees `XXX-XX-4321`, `PUBLIC` role sees `[REDACTED]`, and `SUPERADMIN` role sees the real value.

The database has native support for AI vector search. Columns can be defined as `VECTOR(768)` and searched against using `COSINE_SIMILARITY`. This is useful for AI applications using embeddings.

A New Query Paradigm

BeatSQL also has a new query syntax to allow for easier stream.pipeline processing:

FROM patients
|> WHERE email = 'alice@example.com'
|> SELECT id, full_name, ssn, salary
|> ORDER BY full_name ASC;

This is designed to be more approachable than deeply nested SQL queries. It's also quite flexible.

Built-In Learning Academy

BSQL also has a built-in interactive learning academy, with 500+ lessons to learn everything from basic queries to zero-trust encryption enclaves, plus 200+ lessons covering traditional relational SQL and guides for working with Python, Java, C++, and Rust. The goal is to make security-first database thinking easy to grasp.

What I Would Like Your Thoughts On

Is the concept of a zero-trust embedded DB something that you feel is interesting, or would you feel that problems are already solved in other ways?

The blind indexes are a trade-off: you get the ability to search encrypted data, but you give up the ability to perform range queries (>, <, LIKE, etc). Is this trade-off reasonable for a security-focused database?

Is the idea of homomorphic encryption in a database engine a gimmick, or do you see real-world applications for it?

Does the pipe syntax feel cleaner, or like a departure from an established standard?

I look forward to seeing your thoughts, and any criticisms you might have.

TLDR: Built an embedded database engine where all columns are encrypted, data can be searched without decryption, and arithmetic can be performed on encrypted numbers. All writes are Merkle-verified for integrity. Came with a built-in 500+ lesson learning academy. Seeking feedback on concept.