Questions tagged [postgres]
PostgreSQL, A.K.A Postgres, is an open source object-relational database management system (ORDBMS).
150 questions
0votes
3answers
147views
Transaction management with one connection
We have a desktop application written in Java that communicates with a DB. We support Oracle and Postgres. For the purpose of this question, let's focus on Postgres Our app uses one connection for all ...
- 1,531
2votes
2answers
272views
Best design pattern to synchronize local and cloud databases?
Right now I have an edge device (resource constrained) which is processing, storing, and querying time series data using a Postgres DB. When a data collection event has ended, a simple script on the ...
- 29
3votes
1answer
102views
Constraint to enforce pairwise distinctness of values in two columns in table
I'm kind of stumped by a seemingly simple problem in Db design, but the more I think about about it, the more it twists my mind. Let's say I have columns C1 and C2 in a table. How do I enforce that ...
- 141
6votes
4answers
579views
Why is there (practically) no 6-byte integer in common usage?
In Postgres, it used to be quite common to use a 4-byte integer auto field for primary keys, until it started becomming somewhat common to run into the 2147483647 limit of 4-byte integers. Now, it's ...
- 1,093
0votes
1answer
100views
Database structure for two-step registration flow
I'm trying to design a database for supporting a multi-step registration flow. The registration flow goes like this: the user logs in via OAuth (which creates a session and user), then they're asked ...
2votes
1answer
238views
Advice for pooling DB connections with serverless
I have a great, working web application which uses SvelteKit, DrizzleORM, a PostgreSQL database running on Amazon RDS. It is hosted on Netlify, which means that the “backend” consists of one big ...
- 129
-3votes
2answers
171views
How to properly choose between two equivalent implementations, and how to prove you picked the correct one? [closed]
Thinking about non functional requirements (the -ities) and other concerns, which approach would you choose and why? consider the two snippets Direct Supabase Connection import { useState, useEffect } ...
- 17
1vote
2answers
398views
Is it worth it to use a NoSQL database only for high availability and fast reading, while already using Postgres?
We have several Postgres databases on one AWS RDS cluster (for budget reasons apparently), sharing the same resources, and my team wants to use a NoSQL database alongside it, and store data there from ...
- 113
0votes
1answer
109views
Matching supersets
In my application I have requests and items, each of which are associated with property tuples (key value pairs, where keys can be repeated) stored in a Postgres database. The goal is to find a single ...
- 159
0votes
2answers
2kviews
Best approach to sync data between mobile app cache and the server db? [duplicate]
I have an offline-first mobile app. The user enters data, data is saved into local SQLite database. The server has Postgres database. I need to build a two-directional sync strategy that keeps data on ...
0votes
1answer
71views
How to handle concurrently caching expensive request data in postgres?
I have a kubernetes deployment which is fielding expensive (but cache-able) requests, let's say a website scraping service (not really) which takes about 15 seconds to scrape a website. In my backend ...
7votes
1answer
9kviews
Best way to store quite large JSON objects?
I'm building an app where I need access to quite large reports about different publicly accessible URLs, JSON objects about 200kb to 500kB in size. Each user would generate hundreds of these reports ...
- 171
-2votes
1answer
726views
How to create Business rule engine in PostgreSQL? [closed]
Our organization is into Health Care RCM business. In that, we use to receive medical records (called as accounts, which is a business phrase) and our staff (Medical coders) use to do medical coding, ...
2votes
1answer
732views
stress testing an application and failing at database connection
I currently have a nodejs(nestjs) app, and a postgres database. The database has a maximum connections available as 1700, and I'm trying to spike a 1000 request in a single instance of a second. The ...
- 139
1vote
1answer
281views
SQL - store "like" counts as separate column or infer from a query?
Say I have a feature in a web app where users can create a post and like it. In the frontend the user should see the number of likes a post has. I could store the data two ways: 1. Option 1: A small ...
