Stale Data in Database View After Update? Fix It Fast

You update the base table, run a SELECT on the view, and get old data. I know that feeling. It's like the database is ignoring your changes. I've been there with SQL Server 2019, PostgreSQL 14, and MySQL 8.0. Let me show you the three things that usually cause this, starting with the most common one.

Cause #1: The View Is a Materialized View That Needs Refresh

This is the biggest gotcha. A regular view runs the query every time you access it. A materialized view stores the result on disk. It doesn't update automatically when the base table changes. You have to tell it to refresh.

I see this with PostgreSQL and Oracle mostly. A junior dev creates a materialized view for performance, forgets to set up a refresh schedule, and then wonders why the data is from last week.

How to check if you have a materialized view:

• PostgreSQL: Run \dmv in psql. Or query SELECT relname FROM pg_class WHERE relkind = 'm';
• SQL Server: Check if the view has WITH SCHEMABINDING and a clustered index. That makes it a materialized view (called an indexed view).
• MySQL: MySQL doesn't have materialized views natively, but some cloud providers do. Check your docs.

The fix:

-- PostgreSQL
REFRESH MATERIALIZED VIEW CONCURRENTLY your_view_name;

-- SQL Server (indexed view)
-- No direct refresh needed. The data updates when base table updates affect it.
-- But if you see stale data, rebuild the clustered index:
ALTER INDEX ALL ON your_view_name REBUILD;

-- Oracle
BEGIN
  DBMS_MVIEW.REFRESH('your_view_name', 'C');
END;
/

Pro tip: Set up a scheduled job to refresh hourly or daily. In PostgreSQL, use pg_cron. In SQL Server, use a SQL Agent job. Don't rely on manual refreshes in production.

Cause #2: Transaction Isolation Level Hiding the Update

This one tripped me up the first time too. You're running your SELECT on the view inside a transaction. But that transaction has a high isolation level like REPEATABLE READ or SERIALIZABLE. It sees a snapshot of the data from when the transaction started. Any updates from other transactions are invisible until you commit or rollback.

When this happens:

• Running a long report against the view.
• Using an ORM like Entity Framework or Hibernate with default isolation level set to REPEATABLE READ.
• Using pgAdmin with auto-commit turned off.

The fix:

Check your session's isolation level first. Then change it to READ COMMITTED (or READ UNCOMMITTED if dirty reads are okay).

-- SQL Server
-- Check current level:
SELECT transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
-- Level 1 = READ UNCOMMITTED, 2 = READ COMMITTED, 3 = REPEATABLE READ, 4 = SERIALIZABLE

-- Set to READ COMMITTED:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- PostgreSQL
-- Check:
SHOW default_transaction_isolation;

-- Set (per session):
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- MySQL
-- Check:
SELECT @@transaction_isolation;

-- Set to READ COMMITTED:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Real-world trigger: A developer in your team starts a transaction in a Python script, runs a SELECT on the view, then does an UPDATE on the base table, and finally re-runs the SELECT. The second SELECT still shows old data because the transaction hasn't committed. I debugged this for 2 hours once. The fix was to commit after the UPDATE.

Cause #3: View Definition Depends on Outdated Cache or Stats

Some databases cache query plans or statistics for views. If the underlying data changes drastically, the view might use a stale plan that returns old rows. This is rare, but I've seen it with SQL Server and PostgreSQL after bulk inserts or deletes.

How it shows up:

• You update 1 million rows in a table. The view still shows old row counts for hours.
• The view uses a function like GETDATE() or NOW() that should return current time, but it's frozen.

The fix:

Force the database to rebuild the plan or update statistics.

-- SQL Server
-- Update statistics on the base tables:
UPDATE STATISTICS your_table_name;
-- Or force recompile of the view:
EXEC sp_recompile N'dbo.your_view_name';

-- PostgreSQL
-- Update statistics:
ANALYZE your_table_name;
-- Or drop and recreate the view (last resort):
DROP VIEW IF EXISTS your_view_name;
CREATE VIEW your_view_name AS ... ;

-- MySQL
-- Update table statistics:
ANALYZE TABLE your_table_name;

Note: If your view includes a function like GETDATE(), the result is calculated when the query runs. If you materialize it (SQL Server indexed view), it's fixed until the index rebuilds. That's a feature, not a bug.

Quick-Reference Summary Table

Start with cause #1. It's the most common by far. If that doesn't fix it, move to cause #2. You'll rarely need cause #3, but it's good to know it exists.