PostgreSQL interview question · Most asked
text or varchar(n)? timestamp or timestamptz?
Summary
In PostgreSQL text and varchar perform the same; use text (with a CHECK constraint if you need a limit). For moments in time use timestamptz, which stores an absolute instant (UTC internally) and converts to the session time zone on output; timestamp without time zone is just a wall-clock value with no zone.
Longer Answer
varchar(n) limits are mostly a convenience and changing them used to require a rewrite. char(n) pads with spaces and is rarely useful.
timestamptz does not store the time zone name, only the instant; keep the user's zone separately if you need to display it. Use timestamp only for truly zone-less values (a store's opening time "09:00 local"). Mixing the two causes off-by-hours bugs at DST changes.
Key Points
- text = varchar in performance.
- timestamptz for instants.
- timestamptz stores no zone name.
- timestamp only for zone-less wall times.
More most-asked PostgreSQL questions
- How does MVCC work in PostgreSQL?
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- What does VACUUM do, and how does autovacuum work?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?