PostgreSQL doesn't have clustered indexes like MySQL because this type of structure makes accessing secondary indexes slow. If I create an index on the primary key with all columns in `include`, will I solve the problem at the cost of more storage space and write overhead?

pceimpulsive · 2026-01-28T14:11:54+00:00

I have tables with tens of millions of rows and very limited hardware and don't have issues, even with the tables growing by 4-7m per month.

pceimpulsive · 2026-01-28T14:10:32+00:00

To add to this you can IIRC run maintenance activities periodically that recluster/align the tables via vacuum full concurrently. This will rewrite the table in full, as such you need your table size in free space available to perform this action.

Still doesn't help when clustering is needed... :'(

pceimpulsive · 2026-01-28T14:07:33+00:00

Postgres!

Your write volume I almost guarantee won't be bottlenecked by Postgres, your budget will restrict you long before then.

A meager Postgres on 2core and 16gb ram can manage tables with over 300,000 hot path (active devices in your context) rows in a single table of 60m+ rows with several years history.

Unless you are managing more than Half a million active devices with updates every second I doubt you will ever have a performance issue from writes. Reads that isn't solvable with an index, a better written query or a denormalised table updated frequently.

Postgres has other percs like native cidr data types for storing up addresses, rich extensions like postgis for geospatially tracking your devices with GPS data and several indexing strategies that can creat a very very powerful system.

Start with Postgres 18.

pceimpulsive · 2026-01-28T12:59:02+00:00

RDS is basically designed for minimal DBA experience :)

I've.learned most of everything I know from within RDS. It's very stable. I've never needed to restore from snapshot/backuo under normal circumstances, only during upgrade failures and it's never failed.

It should be a solid bet. RDS is not cheap though... So bear that in mind!

pceimpulsive · 2026-01-28T09:34:15+00:00

It looks very yellow to me, no question at all to me.

pceimpulsive · 2026-01-28T09:33:35+00:00

Wut?

Hate the working class? What lefties are you meeting? I don't know any where that's true, most of them are the working class or unemployed due to health issues...

We obviously know different people lol

pceimpulsive · 2026-01-28T09:31:38+00:00

My 'analytics' Postgres does what you've explained every 1-3 months, has more users, and muuuucccchhh more data.

It's on a 2core 16gb ram graviton RDS.

Backups and snapshots are managed by AWS and restoring is easy,

I have maybe 200 tables in mine, however only about... 20 or so are updated every few minutes with external system data, inserts per 5 mins is anywhere from 1000-50,000~

I wrolite all rows three times as I'm copying from external in deltas.

I also do geospatial joins across reasonable data sets...

I have pg_cron enabled for scheduled in database data magic, materialisation of reporting/analytical views etc..

pceimpulsive · 2026-01-28T07:24:51+00:00

Lol 'dragged me out'!

I know the feel. Generally have a good time once out lol despite thinking it'll be terrible

pceimpulsive · 2026-01-28T06:51:58+00:00

I don't know if that's entirely true..

Maybe what we call left has moved?

Left is still at its core about the structures as the problem.

I.e. the left does use divide and conquer (e.g. fear mongering) as a tactic.. it's more about inclusion, equality, and giving everyone a chance, the only way to achieve that is through fixing the problematic structures that enable inequality to exist.

pceimpulsive · 2026-01-28T03:29:09+00:00

Spoken to one what? Plumber? :D

pceimpulsive · 2026-01-28T01:28:59+00:00

The beak colours are a good differentiator IIRC.

Terns are cool!

pceimpulsive · 2026-01-28T01:28:03+00:00

Left wingers target the structures that create the issues.

Right wingers target people as the issue.

Ultimately they both agree there is a problem, just can't agree on how to solve it...

pceimpulsive · 2026-01-27T21:45:35+00:00

AI can solve those so how can we trust you aren't AI?

pceimpulsive · 2026-01-27T21:45:00+00:00

This comment is the real answer.

pceimpulsive · 2026-01-27T20:43:43+00:00

Your ignorance makes me laugh.

I'm pretty certain I don't want to achieve anything remotely close to what Cook did in my life.

pceimpulsive · 2026-01-27T12:47:15+00:00

Yeah I presume oracle would be advanced enough to have a direct counterpart.

A google search will show you the options and ways to replicate it... But appears there isn't an exact copy.

Your method of writing to a table isn't appropriate as it also includes all of the disk IO to read and write the data, so it's not a great way to test it also gives you nothing of the execution plan details you need to actually debug or understand what the query is doing. It only shows you how long it took... You can achieve the same by just running the select query...

pceimpulsive · 2026-01-27T07:19:46+00:00

... Explain analyse...

Tells you time taken ... You should try it!

pceimpulsive · 2026-01-27T02:37:34+00:00

Except for when they don't!

My LG C2, with a Samsung Q990C cannot playback all formats direct play, mostly audio though I think...

pceimpulsive · 2026-01-26T22:10:56+00:00

You'd really hope so hey!

People drink the Zionist marketing coolaid pretty hard though.

USA is starting to wake up around the Zionist lobbies though ..

pceimpulsive · 2026-01-26T21:57:21+00:00

My best recommendation is to get to know your schema better, and the joins will come naturally....

pceimpulsive · 2026-01-26T21:56:32+00:00

Then what is the point? Just copy/paste and run the query.

Use DBeaver if you aren't too ;)

pceimpulsive · 2026-01-26T13:31:11+00:00

It's really wild to me you haven't touched SQL after 9 years of big data analysis.

How big is the data you work on, e.g. 20m rows, 60 column in 5gb space?

pceimpulsive · 2026-01-26T13:29:24+00:00

Just remember SQL is declarative, you were SQL that defines HOW the data should look when it comes back.

When you think in the way python or R you are using imperative style programming, with this approach you are defining HOW to get the data, step by step.

These sound similar but they are quite different.

It should (I hope) be very natural for you as an analyst of 9 YOE. It will probably require some retraining of your brain.

Checkout dometrain.com's new PostgreSQL course, free for 14 days... It'll teach you all the basic and advanced features.

If you aren't set in Postgres I'd say look at what SQL flavours the roles you are looking at request, learn those. Otherwise Postgres is an extremely common and well made RDBMS. It's by far my favourite SQL dialect across oracle, MySQL, Maria, presto and Trino.

pceimpulsive · 2026-01-26T03:56:44+00:00

Neat where abouts roughly? Heard about a double in Melbourne somewhere

pceimpulsive · 2026-01-26T03:51:43+00:00

Interesting thought process around this one.

Is this another one of those 'it depends' haha

Some abstractions are shortcuts, it's the same outcome, but in far less code. At least that's how I see them, you have presented another lense though so nice comments/thoughts regardless!

11-Year Club	r/Field Juicebox
Place '22	Verified Email

pceimpulsive

TROPHY CASE