Questions tagged [postgres]
PostgreSQL, A.K.A Postgres, is an open source object-relational database management system (ORDBMS).
150 questions
0votes
3answers
148views
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 ...
- 12.2k
2votes
1answer
240views
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 ...
CommunityBot
- 1
4votes
1answer
569views
How to ease the pain of lack of diffs when using database migrations?
The pain that I've often felt when creating database migration files, is best described in this Speakerdeck: Sane Database Change Management with Sqitch. Paste entire function to new "up" script ...
CommunityBot
- 1
6votes
1answer
3kviews
Using PostgreSQL MD5 hash to calculate a 64 bit hash value for advisory lock functions?
I have a particular problem addressing the PostgreSQL advisory locking functions using the bigint variants. Basically I want to create a 64 bit bigint value from a text type obtained with the ...
CommunityBot
- 1
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 ...
- 12.2k
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 ...
- 12.8k
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 ...
- 118k
-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 } ...
- 218k
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 ...
- 81.4k
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 ...
CommunityBot
- 1
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 ...
-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
2answers
276views
What is the best practice for hierarchical container - item relationship in a SQL database?
I am in the process of designing a database organizing real world items in different storage locations. One main principle is that items can contain other items. So the relevant table is: An "...
- 7,635
1vote
3answers
6kviews
How to store product and pricing history in a product database?
What is the best way to store historical information about products and pricing in a database? Our suppliers provide us with catalogues in various formats that usually contain manufacturer's ID of ...
