> its parsing is well tested in core

In practice inet accepts formats my apps never send, and macaddr8 handling improved asset tracking once we stopped normalizing it to text and a check constraint. Why would you throw that away?

Same here. Dropped plain json years ago. Binary storage means containment operators and GIN actually perform, while plain json re-parses text every time. Same logic as picking bigint over integer by default. Storage is cheap, rewrites are not.

This reads like the classic Postgres docs page I've had bookmarked for years.

I was seeding a Postgres DB last month for a side project with about 10k test rows and needed quick UUIDs for the primary keys. I ended up using a simple UUID generator to bulk create v4s and paste them directly into the inserts. Handy to have this reference alongside that workflow.

The line about alternative names listed in the tables being recognized has always tripped me up in practice.

When you have int vs integer vs int4, or double precision vs float8 vs float, which of those does the parser treat as the canonical type name internally, and which are just aliases resolved at parse time? It matters when you query pg_type or information_schema.columns and get something back different from what you declared in CREATE TABLE.

Is there a reliable rule for which spelling will show up in a dump or in format_type output? For example, if I declare character varying(50), will pg_dump always normalize that to the same form, or does it preserve my original choice?

And separate from storage, are any of those alternative names actually the SQL standard spelling that I should prefer for portability, versus Postgres-specific shorthand that will break if I move the schema elsewhere?

CREATE TYPE and domains add plumbing without solving validation where it matters. The security worry about inet is overblown anyway; its parsing is well tested in core and has been for years. Using built-in types with a check constraint is simpler than rolling your own type and owning its input functions.

Yeah MySQL lays out int display widths and auto_increment more plainly. Postgres buries the lede that smallserial serial and bigserial are just shorthand for integer with a sequence, not true types. Migrating later means altering sequences and defaults by hand, and dump/restore surprises people every time!

Disagree, MySQL's docs gloss over unsigned vs signed mess while Postgres at least admits input/output quirks. But smallint/bigint clarity is overstated for newcomers, isn't the serial mess proof? Good coverage, confusing naming.

The extensibility pitch always sounds nice until you inherit five databases with five different money, email, status enums. Portability dies fast. Standards beat clever custom types.

CREATE TYPE status_mine AS ENUM ('ok');

CREATE TYPE domains save side project validation headaches.

If you actually need XML queries, you're stuck parsing it as text and reaching for lxml in the app instead of a decent index in postgres. The native types are mostly solid, but serial/bigserial being presented like real types still trips everyone up, it's just a sequence under the hood!

Casting to xml still validates well-formedness, unlike text, and xpath() plus xmlparse and xmlforest handle queries and extraction adequately. Indexing via expression indexes on xpath values works in practice for document-style access.

tsvector beats Elasticsearch for simple stuff.

time span has always sounded like a sci-fi feature for temporal tables, right on par with warp drive and dilithium crystals. Checking the docs, it is just interval dressed up for a Star Trek convention, no flux capacitor required for storage.

> Timestamp round-trips still lose microseconds somewhere, apparently.

That loss is usually client parsing or an intermediate format, not the native type. Calling the selection rich glosses over XML, which sits there bolted-on, barely distinguishable from a text blob without the operators a real type should have and proper indexing support in practice.

Range and multirange types finally make booking availability logic manageable without hand-rolled overlap checks everywhere. Exclusion constraints actually work, intersections are readable, and I stop writing awful date arithmetic for every reservation system. Most codebases still ignore them and pay for it later. That is on them.

Untrusted inet input reaching queries risks bypassing allowlist checks!

"Rich set" is generous when numeric vs money vs float footguns are still everywhere. Why pretend listing every type equals usability when precision traps remain?

I dropped plain json entirely. I liked the decomposed binary storage because GIN queries and containment operators just work.

ran into the json vs jsonb tradeoff on an old logging project, kept raw payloads in json to preserve spacing then copied searchable fields to jsonb for gin indexing. anyone still bother with plain json these days?

That misses the bottleneck. A hit isn't one query. The page notes dynamic pages use about 200 SQL statements per webpage, so 100K hits is roughly 20 million statements a day hitting a single file with one writer lock. Faster hardware helps reads, it doesn't change serialized writes or lock contention when every page view needs a handful of writes. The 100K figure was always a rule of thumb for most sites doing that kind of work, not a hard benchmark, and dismissing it as outdated ignores the query count per hit entirely.

Timestamp round-trips still lose microseconds somewhere, apparently.

The fopen line is about replacing ad-hoc file code, not beating fsync latency, so demanding power-loss iops misses the point. I clicked through a couple footnotes and one link just spun and never loaded, which didn't help.

On traffic, is it fewer than 100K hits per day or 400K to 500K requests per day? Those read like different units mashed together.

Exactly, same reason I reach for it over MySQL for prototyping. Requires no administration and just works.

> Small. Fast. Reliable. Choose any three

This SQL archives thing just sounds like zip for app assets, doesn't it? what's it do that a zip don't already handle fine?

I've used it for device-local storage on flaky field hardware and that part holds up. The fopen comparison is right, it beats rolling your own file format every time and requires no administration which actually matters when nobody can babysit the box.

Still not buying the edge hype as written. One file means one bad power cut or full disk and the whole app state is suspect. Backups and WAL help until they don't. Fine for caches and local queues. I wouldn't trust it as the sole store for anything I can't afford to rebuild.

Yeah exactly, Oracle is selling to teams that need concurrency, not a file that handles 80k hits. I liked using R to prototype against SQLite before moving to postgres, zero setup and the Tcl core just works. Why would anyone spin up a server for that stage? Also the 281 terabyte size limit shows it was never meant to be a tiny toy.

That concurrency argument only works if you ignore how SQLite actually behaves under write contention. One writer at a time is not a footnote, it is the design. WAL helps readers, it does not magically let fifty concurrent transactions update the same file. Those 200 statements per page turn into lock waits fast, and the app tier is not the bottleneck if the database is refusing to commit. Oracle sells pain, sure, but the pain starts inside the file first on any page with real write traffic.

SQLite is public domain, license cost zero. Commercial support is optional third-party help, not per-seat licensing. PostgreSQL support plans price by response time and coverage, not by the software itself.

I ran a regional booking service on it for two years and spent weekends clearing locked-database errors. The no pricing part is convenient. No vendor means no one to page when it wedges at 2am.

been running sqlite embedded in a couple python tools and one old tcl utility for years and the requires no administration thing is exactly why i haven't ripped it out.

people underestimate how much time you waste babysitting a real server. with postgres or mysql you got users, permissions, daemon config, backups that need their own cron job, some upgrade that breaks everything. with sqlite i just ship the file. python's sqlite3 is already there, tcl has had it forever, no installer, no service to restart. it just works.

what i liked on that page is how blunt they are about not competing with client-server databases. they say it competes with fopen and yeah that's the right framing. i'm not building a shared backend for 50 services, i'm trying to avoid writing my own busted file format and parser. why would i hand roll json lines or csv with locking when i can just use sql and get transactions for free?

and yet they don't oversell it either. they admit 100k hits a day is fine, 10x that is doable, and they even throw out their own numbers from 2015 with 400-500k requests. that's honest. most embedded use doesn't need more.

have you ever tried to deploy something to a box with flaky internet where you can't depend on a remote db? that's where it thrives. no admin, file is the db, copy it, move it, done.

281 terabytes limit is kind of funny, like you'll never hit it in this use case but nice to know it's not gonna be the bottleneck.