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

> Before you type your first query at a customer site, find a replica to read from. If you have to use the primary, lock the session to read-only and put a time limit on every statement.

Original: https://fdetimes.net/en/guides/safe-queries-customer-database-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:

```sql
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.

**Key point:** READ ONLY only stops you from changing data. Your queries still use CPU, still generate I/O and still take a place in the lock queue.

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:

```sql
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:

```javascript
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.

**Try this week:**

- Write a safe_session.sql file containing the four SET commands from this guide, run it on a local PostgreSQL instance, then try a write to confirm it is rejected.
- Use information_schema.columns to list every column in a sample database and sketch the relationships between its tables within 30 minutes.
- Write a MongoDB aggregation that samples 1,000 documents and counts how often each field appears, then note which fields exist in only some of the documents.

## Sources

- [What Is NoSQL? NoSQL Databases Explained | MongoDB](https://www.mongodb.com/nosql-explained)

- [26.4. Hot Standby (PostgreSQL 18 Documentation)](https://www.postgresql.org/docs/current/hot-standby.html)

- [19.11. Client Connection Defaults (PostgreSQL 18 Documentation)](https://www.postgresql.org/docs/current/runtime-config-client.html)

- [SET TRANSACTION (PostgreSQL 18 Documentation)](https://www.postgresql.org/docs/current/sql-set-transaction.html)

- [Chapter 35. The Information Schema (PostgreSQL 18 Documentation)](https://www.postgresql.org/docs/current/information-schema.html)

- [Read Preference - Database Manual - MongoDB Docs](https://www.mongodb.com/docs/manual/core/read-preference/)

- [SQL Tutorial: Learn SQL from Scratch for Beginners](https://www.sqltutorial.org/)