Read Only SQL Queries: Security, Patterns, and Pitfalls

Read-only SQL isn’t just about avoiding INSERT, UPDATE, and DELETE. It’s about preventing any statement that changes state or behaves like a write through locking, side effects, or implicit object creation. Whether you’re building dashboards, running analytics in production, or wiring up lightweight alerts, mastering read only SQL queries keeps your systems fast, safe, and predictable.

What Read-Only Really Means

At first glance, read-only equals SELECT. In practice, you must consider locks, transaction modes, and functions that modify state. For example, SELECT ... FOR UPDATE acquires write-style locks; SELECT INTO can create a table; calling nextval() on a PostgreSQL sequence increments it (a write). So a statement’s SQL verb isn’t the whole story.

If a statement can change state or acquire write-style locks, it isn’t read-only.

A quick checklist for read-only safety

Only uses SELECT (no INSERT, UPDATE, DELETE, MERGE, TRUNCATE, ALTER, DROP, CREATE)
No SELECT INTO or CREATE TABLE AS SELECT
No FOR UPDATE/FOR SHARE/LOCK
No functions with side effects (e.g., sequence nextval())
Runs in a read-only transaction without errors

Common Constructs: Allowed vs. Not Allowed

Construct	Read-only?	Notes
SELECT (plain)	Yes	Safe when it doesn’t call side-effecting functions
SELECT ... FOR UPDATE/SHARE/KEY SHARE	No	Acquires locks; blocks writers/readers
SELECT INTO (Postgres, SQL Server)	No	Creates a table; it’s a write
CREATE TEMP TABLE ... AS SELECT	No	DDL + write
Functions like nextval()	No	Mutates sequence state
Volatile but non-writing functions (e.g., random())	Maybe	Allowed if no state change; watch stability across rows
EXPLAIN SELECT ...	Yes	Metadata only
SHOW/DESCRIBE	Yes	Metadata only
Advisory locks (e.g., pg_advisory_lock)	No	Changes lock state

Transaction Modes That Enforce Read-Only

Enforcing a read-only transaction mode helps catch mistakes early and is a great test harness for read only sql queries.

PostgreSQL

BEGIN; -- or START TRANSACTION
SET TRANSACTION READ ONLY;
-- Your SELECTs here
ROLLBACK; -- or COMMIT, either is fine for read-only

You can also set: SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

MySQL

START TRANSACTION READ ONLY;
-- Your SELECTs here
ROLLBACK; -- or COMMIT

Note: SET SESSION TRANSACTION READ ONLY; establishes the default for subsequent transactions.

SQL Server

SQL Server lacks a direct read-only transaction toggle like Postgres/MySQL. Use a read-only login or database, or grant only SELECT permissions (and deny writes). You can also run queries against a read-only replica or snapshot database.

Roles and Permissions: Make Read-Only Inevitable

Hardening permissions ensures developers and tools can only run read-only statements, not just by convention, but by capability.

PostgreSQL: A minimal read-only role

-- Create a dedicated role
CREATE ROLE app_reader NOINHERIT LOGIN PASSWORD 'strong-password';

-- Grant schema usage and table/view SELECT
GRANT USAGE ON SCHEMA public TO app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_reader; -- allows currval but not nextval in read-only tx

-- Ensure future tables are readable
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON SEQUENCES TO app_reader;

-- Optional: set default read-only transactions per session
ALTER ROLE app_reader SET default_transaction_read_only = on;

MySQL: GRANT select-only

CREATE USER 'app_reader'@'%' IDENTIFIED BY 'strong-password';
GRANT SELECT ON mydb.* TO 'app_reader'@'%';
FLUSH PRIVILEGES;

SQL Server: Use built-in reader role

-- In the target database
CREATE USER app_reader FOR LOGIN app_reader_login;
EXEC sp_addrolemember 'db_datareader', 'app_reader';

-- Deny any writes just in case
DENY INSERT, UPDATE, DELETE, ALTER, CONTROL TO app_reader;

These patterns ensure that even if a query tries to write, it fails by permission, not just by policy.

Locking Nuances That Can Break Read-Only Assumptions

SELECT ... FOR UPDATE/SHARE: Adds locks that serialize writers; treat as non-read-only.
Sequences (Postgres): nextval() increments sequence state and fails in read-only transactions. Use currval() only after a prior nextval() in the session, or better, avoid sequences in read-only code paths.
Advisory locks: Even though called from SELECT, they change lock state; avoid.
CTEs with writes: In Postgres, WITH t AS (UPDATE ... RETURNING ...) followed by a SELECT is still a write.

Patterns for Safe Monitoring and Analytics

Many teams use read only SQL queries to monitor business events or SLOs without touching production writes. These patterns keep your queries efficient and predictable.

1) Time-window checks with idempotence

-- New orders in the last 5 minutes (Postgres)
SELECT order_id, customer_id, total_cents, created_at
FROM   orders
WHERE  created_at >= now() - interval '5 minutes'
ORDER  BY created_at DESC
LIMIT  200;

For repeated polling, consider anchoring windows using a monotonic key you save externally (e.g., max order_id seen) to avoid duplicates and missed rows:

-- Seek/pagination pattern
SELECT order_id, customer_id, total_cents, created_at
FROM   orders
WHERE  order_id > :last_seen_id
ORDER  BY order_id ASC
LIMIT  1000;

2) Window functions for rich summaries

-- Top 5 products by trailing 24h revenue (Postgres)
WITH recent AS (
  SELECT product_id, total_cents
  FROM   orders
  WHERE  created_at >= now() - interval '24 hours'
)
SELECT product_id,
       SUM(total_cents)        AS revenue,
       RANK() OVER (ORDER BY SUM(total_cents) DESC) AS rnk
FROM   recent
GROUP  BY product_id
HAVING SUM(total_cents) > 0
ORDER  BY revenue DESC
LIMIT  5;

3) Lightweight anomaly checks

-- MySQL: New user signup drop vs. trailing average
SELECT 
  COUNT(*)                        AS last_10m,
  ROUND(AVG(cnt), 2)              AS avg_last_24h_10m
FROM (
  SELECT COUNT(*) AS cnt
  FROM users
  WHERE created_at > NOW() - INTERVAL 24 HOUR
  GROUP BY UNIX_TIMESTAMP(created_at) DIV (10*60)
) t
WHERE NOW() - INTERVAL 10 MINUTE <= NOW();

All examples remain read-only, assuming no sequence increments or locks are introduced.

Performance Tips for Read-Only Workloads

Index to match filters and order-by: If you filter by created_at and order by it, add an index on (created_at) or a composite covering index for additional predicates.
Avoid SELECT *: Fetch only needed columns; this reduces I/O and memory.
Prefer seek-pagination over offsets: ORDER BY id DESC LIMIT 50 is great, but OFFSET on large tables is expensive. Use keyset pagination with WHERE id < :cursor.
Use approximate counts when exactness isn’t needed: Postgres reltuples via pg_class or extensions; MySQL EXPLAIN estimates for ad-hoc checks.
EXPLAIN before you scale: Always sanity-check plans; look for sequential scans on huge tables and missing indexes.
Limit scan width: Narrow columns and use covering indexes to keep hot queries in cache.

How to Prove a Query Is Truly Read-Only

Run it in a read-only transaction. If it fails, it wasn’t read-only. Example (Postgres):
```
BEGIN; SET TRANSACTION READ ONLY; SELECT ...; ROLLBACK; 
```
Check for hidden writes. Ensure no SELECT INTO, no CREATE TEMP TABLE, no sequence nextval(), no advisory locks.
Inspect locks. In Postgres, watch pg_locks for unexpected lock types during execution.
Test on a read replica. If it runs fine and doesn’t block replication, you’re likely safe.

Database-Specific Pitfalls

PostgreSQL pitfalls

CTE writes disguised by SELECT: A CTE can perform writes even if the outer statement is SELECT.
SELECT INTO: Creates a table; use CREATE VIEW (DDL) ahead of time instead of ad-hoc table creation.
Sequences: nextval() fails in read-only transactions and isn’t read-only.

MySQL pitfalls

SELECT ... LOCK IN SHARE MODE (older MySQL) or FOR UPDATE: Adds locks; not read-only.
Temporary tables: CREATE TEMPORARY TABLE writes. Prefer derived tables/CTEs.
READ ONLY transaction vs. functions: Some functions might be allowed but can still interfere with performance; avoid user locks like GET_LOCK().

SQL Server pitfalls

SELECT INTO: Writes by creating a table. Use a view or CTE instead.
NOLOCK: It’s not read-only enforcement. It allows dirty reads and can return inconsistent results. Prefer readable secondaries or snapshot isolation.
Permissions drift: Ensure the user is only in db_datareader and explicitly denied write privileges.

Designing Read-Only Queries for Alerting

When queries power alerts, aim for stability and low overhead.

Deterministic windows: Prefer keyset-based progress to time-only windows. If you must use time, include a small overlap and deduplicate externally.
Stable functions: Favor stable timestamps like clock_timestamp() vs. now() as needed in Postgres; understand evaluation timing to avoid drift in long queries.
No side effects: Never rely on temporary objects or locks; they can cascade into contention.
Explain and cap: Use LIMIT and make sure there’s a supporting index to bound work.

Examples for alerts

-- Postgres: Detect orders stuck in 'processing' for > 30 minutes
SELECT order_id
FROM   orders
WHERE  status = 'processing'
AND    updated_at < now() - interval '30 minutes'
ORDER  BY updated_at ASC
LIMIT  500;

-- SQL Server: High error rate in last 10 minutes
SELECT TOP 200 message, level, created_at
FROM   dbo.logs WITH (READCOMMITTED)
WHERE  created_at >= DATEADD(MINUTE, -10, SYSUTCDATETIME())
AND    level IN ('ERROR', 'FATAL')
ORDER  BY created_at DESC;

Safe Alternatives to Risky Constructs

Instead of SELECT INTO: Create a view once, or use a CTE in the query. Example:

WITH filtered AS (
  SELECT id, total
  FROM   payments
  WHERE  status = 'settled'
)
SELECT id, total FROM filtered;

Instead of temporary tables: Use inline subqueries or CTEs; modern optimizers handle them well.
Instead of FOR UPDATE: If you only need ordering consistency, use appropriate isolation or snapshot features; avoid write-style locks.

Security Hardening Beyond Permissions

Use least privilege connections: A dedicated account with only SELECT on required schemas.
Parameterize queries: Prevent SQL injection, even in read-only contexts; injected predicates can still leak data or cause expensive scans.
Encrypt connections: TLS to protect credentials and data in transit.
Audit and monitor: Log query texts and runtimes; set timeouts to prevent runaway scans.

Testing and Observability

Before production, run a “read-only suite” that:

Executes each query in an enforced read-only transaction.
Captures EXPLAIN plans and highlights sequential scans on large tables.
Checks latency and row counts against budgets.
Verifies the absence of locks beyond shared-read locks.

For PostgreSQL, sample lock inspection:

SELECT pid, relname, mode, granted
FROM   pg_locks l
JOIN   pg_class c ON l.relation = c.oid
WHERE  pid = pg_backend_pid();

FAQ: Quick Answers

Is every SELECT read-only? No. SELECT INTO, SELECT ... FOR UPDATE, and some function calls can make it non-read-only.
Can I run read-only queries on replicas? Yes, that’s ideal for offloading. Be mindful of replication lag.
Do window functions break read-only? No. They compute over the result set without side effects.
How do I guarantee a query is read-only? Combine permissions (GRANT SELECT only) with read-only transactions and automated tests.

Wrap-up

Designing read only SQL queries is less about avoiding DML and more about understanding locks, transaction modes, and subtle side effects. Use read-only transactions to validate, enforce permissions at the role level, index for your predicates, and favor deterministic patterns for monitoring and analytics. With these practices, you’ll gain confidence that your queries are safe on production, efficient under load, and reliable as building blocks for alerts or dashboards.

Looking for a lightweight way to run read-only checks on PostgreSQL, MySQL, or MongoDB and get notified when results change? An iOS app like Query Alert can schedule safe queries and deliver push or email alerts while keeping connections encrypted and queries restricted to read-only.