Posts by marta_k

For anyone arriving here now: 18 keeps most planner stats through pg_upgrade. You still want vacuumdb --all --analyze-in-stages --missing-stats-only, the release notes have details.

Welcome to having types.

You know the answer. Store UTC. In Python that's datetime.now(timezone.utc), or use integer epoch. Convert when you display. For the old rows, you can't tell the two 01:30s apart from the text alone, …

That's bloat. Check first whether autovacuum is keeping up: SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5; If last_autovacuum is old, look for a…

node-postgres gives you bigint columns as strings by default, for this reason. Someone will then "fix" that with parseInt.

"Identifiers aren't numbers" should go on a wall somewhere. Leading zeros have ruined more imports than bad encodings.

The same holds in Postgres. And on either one, if the query selects only columns that are in the index, you can skip the table entirely. Bonus speedup that people miss.

Better still to run the job with a MemoryMax= so it dies first without involving the OOM killer at all. Postgres getting a SIGKILL means a crash restart of everything.

Happens in Postgres as well, just with more connections piling up before you notice.

You can leave it on and raise jit_above_cost, or turn it off for that role. For a small OLTP box I'd just turn it off.

There's your extra second. It has decided the query's expensive enough to compile. Was jit off in your old config, or was it a different package build? Try SET jit = off in the session and run it agai…

EXPLAIN (ANALYZE, BUFFERS) it and paste it. Change the table names if you need to.

The in-stages version does a very rough pass first, so the worst of it clears up fast. Then finer passes. Check pg_stat_user_tables, last_analyze should be getting filled in.

Did you run the analyze step? pg_upgrade doesn't bring the planner statistics across, so right now it's guessing. It prints this at the end and everybody misses it. vacuumdb --all --analyze-in-stages

If it gets there, migrating to Postgres is a known week of work, and you've saved a couple of years of running it. The bigger trap is type sloppiness. SQLite will let you put text in an INTEGER column…

I run Postgres for a living and I'd say use SQLite for this. The question that matters is how many writers and whether they hold transactions open. One writer at a time, so if you do a slow thing insi…