Questions tagged [postgres]
PostgreSQL, A.K.A Postgres, is an open source object-relational database management system (ORDBMS).
152 questions
1
vote
2
answers
251
views
How does a PostgreSQL cursor actually work? What are their tradeoffs?
I'm in charge of developing an application that sometimes needs to process massive amounts of data from a Greenplum (a PostgreSQL-derived) database. The process involves a Java 8 program running on a ...
- 31
0
votes
2
answers
157
views
Designing a social media notification system, would a single table with JSON column be the correct approach for data?
I am trying to create a notification system similar to Instagram, for the lack of better comparasion.
The Goal:
Getting user notifications in a unify way, for easy unparsing and displaying to user.
...
- 101
173
votes
21
answers
164k
views
Stored Procedures a bad practice at one of worlds largest IT software consulting firms?
I'm working at a project in one of the world's top 3 IT consulting firms, and was told by a DBA that company best practice's state stored procedures are not a "best practice". This is so contrary to ...
6
votes
4
answers
741
views
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
4
votes
2
answers
519
views
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 ...
- 159
0
votes
3
answers
327
views
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 ...
- 2,030
3
votes
1
answer
128
views
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
2
votes
2
answers
490
views
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
14
votes
5
answers
8k
views
How can I efficiently diff a CSV file against a database?
I have an inventory of products stored in Postgres. I need to be able to take a CSV file and get a list of changes—the things in the CSV file that are different to what is in the database. The CSV ...
- 630
134
votes
10
answers
72k
views
Storing a re-orderable list in a database
I'm working on a wishlist system, where users can add items to their various wishlists, and I plan to allow users to re-order the items later on. I am not really sure about the best way to go about ...
- 1,443
1
vote
2
answers
528
views
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
-3
votes
2
answers
188
views
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
0
votes
1
answer
121
views
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 ...
- 123
0
votes
2
answers
3k
views
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 ...
- 217
7
votes
1
answer
10k
views
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