That's great but I believe it's still over-engineered. Unless it's a web site that can tolerate no downtime at all during schema updates, SQLite in WAL2 mode is more than enough. Moreover, in Postgres version upgrades can't be done without significant downtime (or setting up replication and performing a complex dance), so even that isn't so cut and dry.

</ ... >

▲

inigyou 3 minutes ago | parent | next [-]

Every time I start with sqlite I outgrow it in some way, so I start with postgres now.

▲

throw2ih020 2 hours ago | parent | prev | next [-]

For some of the simpler sites sqlite would have been fine, but I do need Postgres anyway for some other stuff on my server so the overall burden of standardizing on Postgres only is less than having to do tuning for two different databases. In particular sqlite has worse defaults due to backwards compatibility, so you have to do a lot of upfront config for every table and DB.

Postgres version upgrades are basically painless, at my workplace our upgrades complete in so little time our users don't notice, and we have more data than probably 99.9999% of companies out there (mature company in aerospace industry).

▲

nasretdinov 2 hours ago | parent [-]

Well if you already have Postgres then yes, sounds fine. I don't really agree that SQLite needs tons of tweaking -- setting journal_mode to WAL/WAL2 is the only real thing worth doing, everything else I would consider optional.

Version upgrades for SQLite are basically a no-op though, that's kinda one of the reasons why it's so great to run in production (and deployment a maintenance being mostly a no-op as well, apart from setting up backups I guess).

I don't actually suggest that everyone should run their production on SQLite, but it's genuinely worth considering especially for small-ish projects.

	▲	throw2ih020 8 minutes ago \| parent \| next [-]
		Foreign key constraints, sync frequency, busy timeout, strict typing on tables, explicit types for integer primary keys, and a convention for storing dates and times are others I would want to do up front.
	▲	Induane 18 minutes ago \| parent \| prev [-]
		I set it to enforce foreign key constraint as well.

▲

ffsm8 3 hours ago | parent | prev | next [-]

From an engineering perspective, sqlite and PostgreSQL are equivalent.

The difference is only in the deployment/maintenance.

And considering the context he stated, zero downtime really doesn't seem like something he's worrying about.

▲

sroerick 3 hours ago | parent | prev [-]

(Me not taking this seriously)

I personally find Postgres WAY easier than SQLite simply because of the deployment story. I do a lot of hotfixes and dangerous stuff on personal projects, and having two databases complicates that tremendously. I like being able to run hot code on prod.

For the record, this is not the same reason I like postgres for actual production code

▲

nasretdinov 2 hours ago | parent [-]

Sorry can you please clarify what you mean by having two databases? You can share the same SQLite between applications if you really want

▲

kelnos an hour ago | parent [-]

I think they mean that they actually do need Postgres for some of their projects, and it's easier to just use Postgres for everything, than to use Postgres for some things and SQLite for others.

	▲	nasretdinov 35 minutes ago \| parent [-]
		Yes, but on the other hand what I'm implying is that you might not actually need it :)