Questions tagged [postgres]
PostgreSQL, A.K.A Postgres, is an open source object-relational database management system (ORDBMS).
152 questions
1
vote
2
answers
237
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
3
answers
315
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
0
votes
1
answer
120
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
-3
votes
2
answers
187
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
122
views
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
0
votes
1
answer
78
views
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 ...
-2
votes
1
answer
798
views
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, ...
- 9
1
vote
1
answer
322
views
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 ...
- 249
0
votes
1
answer
98
views
Am I making this web app right?
I am currently making a newsletter website that will send you a email with the top ten manga for that week. Currently I have a web scraper that retrieves the top ten along with descriptions and other ...
-2
votes
2
answers
246
views
How to filter and concatenate multiple sql files into one database [closed]
I have an issue where I have multiple databases from previous projects that I would like to combine into one large database. These databases are stored in .sql files. The issue is that I only need ...
- 7
0
votes
1
answer
792
views
Form builder app database design adding versioning
i'm making a custom form builder app similar to google forms or wufoo and i've completed the basic database design. However there is one thing i can't figure out. I'd like to keep track of version of ...
- 109
1
vote
1
answer
215
views
Synchronous replication of databases
Say we have a software system with a web server serving writes to a Master DB. This Master DB is then replicated to several read-only DB replicas.
From my understanding, the process of asynchronous ...
- 37
2
votes
1
answer
59
views
SQL: Analytics event value that can be string or number
I'm using a PostgreSQL database and I want to create an analytics table that has a many-to-one relationship with an analytics_type table where analytics_type_id is the foreign key. The table also has ...
- 47
-1
votes
1
answer
248
views
Should I use a database as gateway to other services? [closed]
The context
I have an IoT project where the sensors are sending data to my Postgres database. The sensors are manages by a stand-alone service which provides a REST API to query various information ...
2
votes
2
answers
616
views
Reading a large CSV file and then loading data to a DB
I have a Django application of 2 GB running and I need to receive a CSV file of more than 1 GB, read it and load the data to a PostgreSQL DB in IBM Cloud. The problem is that if I receive the file, it ...
- 29
0
votes
1
answer
342
views
Processing large number of messages in a thread-safe manner
I have the following use case and would like to implement it in a thread-safe manner. Think of a Stack Overflow-like web application.
Somebody upvotes a question.
A new event is sent to the message ...
- 767
3
votes
1
answer
247
views
Reorderable parent/children hierarchy in a relational database
I'm struggling on a design question for my Ruby on Rails website. I'm working on an estimation tool for construction companies for renovation projects.
It orders items this way by default:
Room (ex: ...
- 149
0
votes
0
answers
90
views
How can I design a secure content verification web application?
In my country, there is a high number of fraudulent doctor's sick letter as they are manually written on paper. I'm designing a web application to combat this issue and have the whole process ...
2
votes
1
answer
147
views
Sharding rule updating on DB scaling
When you have sharding, horizontal partitioning, you have multiple DBMS instances, and your server app chooses which one to use by some rule that uses record IDs. Well, for example, we have ...
- 191
0
votes
1
answer
639
views
Race conditions in API calls within Golang microservices
I have a microservice architecture running on Heroku. I am having some problems handling race conditions.
The problem is, that service A:
Needs to fetch the user's balance through an API call to ...
- 126
0
votes
2
answers
602
views
Is it true that ORM allows for changing the type of SQL that you use easily?
Hey so I have been looking into ORM since my last question and I am wondering.
Instead of worrying alot right now about whether I will use mysql, postgre, or sqlite, would it be better to use ORM ...
- 139
0
votes
1
answer
2k
views
Doing data manipulation on server side vs client side
Im building a project that stores time series data on a per user basis. On the dashboard of the user it'll show some simple statistical analysis like averages but more importantly, it'll create charts ...
- 157
1
vote
1
answer
144
views
Should I perform some minor denormalization to save myself several queries in the future?
I'm in charge of designing the entire backend for the REST API of an application that works more or less like an online browser game (think OGAME, Travian, and the likes). In this game, players are ...
- 11
0
votes
2
answers
125
views
How to use strict schema with seemingly fluid data type
Our company is trying to find a good generic way to have Many-to-One data for an entity. For example, a user might have 1 primary email, but many other emails also attached to their account.
So we ...
- 287