Questions tagged [postgres]
PostgreSQL, A.K.A Postgres, is an open source object-relational database management system (ORDBMS).
144
questions
-3
votes
2
answers
145
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
1
vote
2
answers
276
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
0
votes
1
answer
104
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
2
answers
1k
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
0
votes
1
answer
65
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 ...
7
votes
1
answer
6k
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
-2
votes
1
answer
575
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
2
votes
1
answer
633
views
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
1
vote
1
answer
196
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 ...
- 189
1
vote
1
answer
292
views
Database schema advice : multiple tables with the shared columns in postgresql
Let's pretend I'm making a basic twitter clone. We could imagine our database is set up as the following:
1. Table 1 - contains user info {username, password}
2. Table 2 - contains session info {...
- 189
0
votes
1
answer
94
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 ...
14
votes
5
answers
7k
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
-2
votes
2
answers
163
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
0
answers
876
views
How does Spring boot and Postgres handle the concurrent updates?
I want to understand how does Spring Boot and Postgresql DB handle the concurrent requests for updating a value in DB. Consider this example of facebook likes, if there are multiple instances of ...
0
votes
1
answer
605
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
0
votes
1
answer
263
views
Best practices to deal with realtime data in REST API, Postgres and Redis
Overview
Today we have a monolithic service that deals with realtime time data. The monolithic are composed by follow stack: Django + AuroraDB Cluster (Postgres AWS) + Varnish cache and cloudfront at ...
1
vote
1
answer
175
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 ...
- 27
3
votes
4
answers
2k
views
HOWTO: Full-text search over an encrypted database?
I want to adhere to the best practices and obviously encrypt the data sent to me by user. And I also want to provide the full-text search feature to the user (user can only search their own data, if ...
- 105
2
votes
1
answer
54
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
108
views
Commit instead of rollback? [closed]
Simple example (PSEUDO CODE):
for (int i = 0; i < 100; i++) {
START TRANSACTION;
SELECT id, name FROM employees WHERE id = i;
IF (someFunction(id)) {
ROLLBACK;
...
- 11
-1
votes
1
answer
220
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 ...
0
votes
1
answer
166
views
Worried about too many joins when fetching multiple datatypes in a single postgres query
Its not exactly my use case (would take too long too explain), but imagine a task management application where tasks for employees are displayed in a list along with some information about that task. ...
- 113
2
votes
2
answers
523
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
158
views
Postgres: Storing a task instruction to be run on a schedule, with multiple workers
As a prerequisite I will say that we are wedded to postgres to provide this solution.
This is a very broad problem, and I think it must be solved many times over, but unfortunately as it's so general ...
- 103
0
votes
1
answer
271
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
2
answers
569
views
Foreign Keys and Eventual Consistency
We have a project that loads metric data from various etls and services into an AWS SQS queue, where that data is processed and inserted into a metrics database, also running on AWS RDS.
When we ...
- 2,323
3
votes
1
answer
207
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
2
votes
2
answers
265
views
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 "...
- 321
0
votes
0
answers
73
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
2
answers
1k
views
Database Design: Storing price date wise, or date range wise?
I must store prices for various items on multiple dates. The table schema would look like this:
CREATE TABLE date_wise_price (
item_code varchar,
date date,
price numeric(19,4)
)
An ...
- 177
2
votes
1
answer
136
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
-1
votes
3
answers
510
views
Should I de-normalize my DB schema for a multi tenant application
I am working on a multi-tenant application that's using a shared Postgres db for all tenants.
All entities in the DB are connected (directly or indirectly) to a tenant, for example:
Tenant --has many--...
0
votes
1
answer
589
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
-2
votes
2
answers
344
views
Caching expensive sql queries on first page load
I have a web application with an express/node backend using typeorm and PostgreSQL. The home page in my app is a query with lots of inner joins that shows the user a complex report. This query takes ...
- 113
0
votes
2
answers
513
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
-5
votes
1
answer
603
views
Sync local database with remote
My client has a business which work mostly in remote areas where internet felicity is limited, We have a central database and the branches in remote areas need to connect to the central database.
We ...
- 93
0
votes
1
answer
1k
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
-2
votes
1
answer
740
views
Adding unstructured data to a relational database (PostgreSQL)
I'm building a project and I only know the MERN stack. Everywhere I see the general consensus is that Mongo is not good and should only be used in very specific use cases. More importantly, I also ...
- 157
1
vote
1
answer
120
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
1
vote
1
answer
82
views
Data integrity on polymorphic association to two different table
Let's say I have a very large student list want to filter if each of these students is satisfying some specific classroom school and classroom limitations. So, in my data model, I have School and ...
- 336
0
votes
2
answers
106
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 ...
- 277
0
votes
0
answers
37
views
Recommendations for data clustering in an application
I am making a social media app. I have a use case in which i have to cluster similar type of data. For example, take Instagram. When a user likes a post, we get a notification that 'x likes your post'...
- 9
5
votes
1
answer
3k
views
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 ...
- 1,542
2
votes
0
answers
603
views
Running a high availability PostgreSQL cluster on native AWS services only
Backstory: I am unable to use RDS, as I need to install cartridges in my PostgreSQL instances.
I have been trying to pin down an architecture for PostgreSQL running on EC2 instances for a few days. ...
- 29
2
votes
1
answer
2k
views
Elasticsearch and PostgreSQL combination
I have a marketplace application and I stored data in a PostgreSQL. I have performance problem for product search, I know I can improve search performance if I use Elasticsearch instead of PostgreSQL ...
- 53
0
votes
1
answer
786
views
Optimizing inserting/storing Large amount of data in database
We have a POST API that takes data from the Client and send it to different applications (Payment, Email, SMS). We also store all the data sent by the Client in a database for future analytics ...
- 139
1
vote
0
answers
93
views
Architecture recommendation for dynamic integration of algorithms/scripts
I am looking for a simple software architecture that is able to evaluate user data using different versions of algorithms.
According to my concept, the user is able to upload tabular data via a Web ...
- 19
2
votes
2
answers
3k
views
Retry a failed REST API request - Java/Postgres
We have a REST API which calls a third-party REST API to Send Emails. The Third Party API is not super reliable and randomly fails every now and then with a 500.
Our Clients do not want to retry at ...
- 139
0
votes
0
answers
84
views
Building a Feed Handler and Persisting JSON Data to Postgresql
I'm starting a pet project and would like some advice. I need to perform the following:
Scrape JSON Data from a REST API
Save the Data into my Postgresql table
Paginate through the rest API and ...
- 101
-1
votes
1
answer
79
views
Which is better for my use case? A hybrid customer data model or a document store customer data model?
I am trying to get some preliminary insight into the pros and cons of using either a hybrid model (jsonb document-relational) or an all jsonb document store model for customer data in PostgreSQL. Both ...
- 7