First time in a customer's database: read the schema, query a replica, lock the session to read-only

It is Monday morning at the customer’s office. The IT team hands you a connection string and says: “Here’s the database, feel free to look around.” You open DBeaver and find more than 200 undocumented tables. Your first instinct is to run SELECT COUNT(*) on the biggest one.

Stop there. If the connection string points at the primary, that harmless-looking statement competes for resources with the customer’s real orders. “Feel free to look around” is the IT team being polite. Their system can’t take any extra load just because they said it.

At a new customer, you need to understand their data quickly without breaking anything. This guide sets out a process you can use tomorrow: pick the right place to query, lock down your session, read both PostgreSQL and NoSQL schemas, and avoid the mistakes newcomers tend to make.

Where you query is the first question

PostgreSQL has a mode called hot standby. In this mode you can connect to a replica server and run read-only queries while it continues to receive data from the primary. This is where an FDE should work, and on day one you should ask the IT team: “Is there a standby I can connect to?”

A hot standby also gives you built-in protection. According to the PostgreSQL documentation, every connection to a standby is strictly read-only; you cannot even write to temporary tables. If you accidentally run an UPDATE, it is rejected and the data is untouched.

MongoDB is different. By default, applications send all reads to the primary of the replica set, so you have to switch the read preference to secondary yourself to avoid adding load to production. It takes a single line, as the example later in this guide shows.

Lock the session before you type the first query

Some customers have no standby, or give you only a user on the primary. In that case you have to build your own guardrails. Paste these four lines at the start of every session:

SET default_transaction_read_only = on;
SET statement_timeout = '30s';
SET lock_timeout = '2s';
SET idle_in_transaction_session_timeout = '60s';

Each line guards against a different kind of accident. default_transaction_read_only is off by default, meaning every transaction can write, so you turn it on to make new transactions read-only by default. statement_timeout cancels any statement that runs longer than the limit you set, so a JOIN with a missing condition cannot run all afternoon.

The other two lines deal with locks. lock_timeout cancels a statement if it waits too long for a lock on a table, index or row, so your query does not sit in a queue blocking real transactions. idle_in_transaction_session_timeout kills any session that opens a transaction and leaves it hanging, for example when you open a GUI tool, run one query and go to lunch.

The PostgreSQL documentation also states plainly that READ ONLY is a high-level notion of read-only and does not prevent every write to disk. But the main reason you need all four lines lies elsewhere: read-only puts no limit on how long a query can use CPU, generate I/O or wait for locks. The three timeouts set those limits.

Example: reading the schema at a delivery company

Imagine the customer is a delivery company. Orders live in PostgreSQL; drivers’ event logs live in MongoDB. You have been asked to find out why two departments’ reports on late-delivery rates do not match.

For PostgreSQL, start with information_schema. It is a set of views defined by the SQL standard, so the query will look familiar to anyone who has worked with an RDBMS:

SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

Export the result to a file and read through the columns by name. In this example, the pair orders.driver_id and drivers.id already suggests a relationship between the two tables, even though there is no foreign key. There is one limitation: information_schema does not cover PostgreSQL-specific features. To examine those, you have to query the system catalogs in pg_catalog.

MongoDB has no tables to list. NoSQL schemas are flexible and each document can hold different data types, so the real structure only appears when you sample:

db.getMongo().setReadPref("secondaryPreferred")
db.driver_events.aggregate([
  { $sample: { size: 1000 } },
  { $project: { kv: { $objectToArray: "$$ROOT" } } },
  { $unwind: "$kv" },
  { $group: { _id: "$kv.k", n: { $sum: 1 } } },
  { $sort: { n: -1 } }
])

Suppose the result shows delivered_at in all 1,000 documents but delivered_ts in only 300. Most likely the application renamed the field in some release, and the two departments are reading different fields. That is the first hypothesis you take to the meeting, with concrete numbers everyone can check.

The cost of reading from a replica

A replica is safer than the primary, but it has limits of its own. On a hot standby, a long query can conflict with changes the standby is replaying from the primary, such as a DROP TABLE. When the wait exceeds max_standby_streaming_delay or max_standby_archive_delay, PostgreSQL cancels your query.

If you hit that error, don’t ask the DBA to raise the parameters. Break the query into smaller pieces, for instance running it week by week instead of over a whole year. Short queries are cancelled less often, and when they are, you lose less work.

In MongoDB, every read preference other than primary can return stale data, because secondaries replicate from the primary asynchronously. You can set maxStalenessSeconds to cap the acceptable lag.

More broadly, most NoSQL systems guarantee only eventual consistency. When you report a figure to the customer, state where it was read from and when.

Mistakes that cost an FDE trust

The most common mistake is trusting the connection string without checking it. Run SELECT pg_is_in_recovery(); to find out whether you are on a standby or the primary. It takes two seconds.

Next is drawing conclusions about a NoSQL schema from a handful of documents opened with find(). Because each document can have a different structure, use $sample or sort by _id so you see both old and new documents before concluding anything.

Another is using figures read from a secondary in a meeting that needs real-time numbers without saying so up front. When the customer notices the numbers are off, they may start doubting the rest of your analysis too. As for transactions left hanging in a GUI tool, idle_in_transaction_session_timeout will deal with them for you, provided you have set it.

Turning this skill into an advantage in interviews

When reading a job description for an FDE role, look for phrases such as “work directly with customer data” or “integrate with existing systems”. They signal that your first week will look a lot like the scene at the start of this guide.

On your CV, don’t just write “proficient in SQL”. Say that you built a read-only access process covering sessions with timeouts, querying on replicas and sampling NoSQL schemas. A line like that shows exactly what you have done with real data. “Proficient in SQL” doesn’t.

A customer may forget how many clever queries you wrote. But if you ever slowed down their ordering system, even once, they will remember it for a long time.